{"id":2984,"date":"2020-03-26T15:32:54","date_gmt":"2020-03-26T06:32:54","guid":{"rendered":"http:\/\/anada.cloudfree.jp\/?p=2984"},"modified":"2020-03-26T15:32:54","modified_gmt":"2020-03-26T06:32:54","slug":"oracle%e8%a6%9a%e6%9b%b8-%e9%9a%8e%e5%b1%a4%e3%83%87%e3%83%bc%e3%82%bf%e3%82%92%e5%bc%95%e6%95%b0%e3%81%a8%e3%81%97%e3%81%a6%e6%b8%a1%e3%81%99","status":"publish","type":"post","link":"http:\/\/anada.cloudfree.jp\/?p=2984","title":{"rendered":"ORACLE\u899a\u66f8 \u968e\u5c64\u30c7\u30fc\u30bf\u3092\u5f15\u6570\u3068\u3057\u3066\u6e21\u3059"},"content":{"rendered":"<p>\u30b5\u30de\u30ea1<br \/>\n\u3000|&#8211; \u660e\u7d301-1<br \/>\n\u3000|&#8211; \u660e\u7d301-2<br \/>\n\u30b5\u30de\u30ea2<br \/>\n\u3000|&#8211; \u660e\u7d302-1<\/p>\n<p>\u306e\u3088\u3046\u306a\u3001\u968e\u5c64\u306e\u3042\u308b\u30c7\u30fc\u30bf\u3092\u5f15\u6570\u3067\u6e21\u3057\u305f\u3044\u3068\u601d\u3063\u3066\u3001\u8abf\u3079\u3066\u307f\u305f\u3089\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u578b\u306a\u308b\u3082\u306e\u3092\u767a\u898b\u3002<\/p>\n<p>\u3067\u3001\u5b9f\u969b\u306b\u3069\u3046\u3084\u3063\u3066\u5b9f\u88c5\u3059\u308c\u3070\u3044\u3044\u306e\u304b\u3001\u307e\u305f\u8abf\u3079\u3066\u5b9f\u884c\u3002<\/p>\n<p>\uff1c\u624b\u9806\uff1e<br \/>\n1, \u307e\u305a\u306fCREATE TYPE\u3067\u3082\u3063\u3066\u3001\u4efb\u610f\u306e\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u578b\u3092\u4f5c\u3063\u3066\u307f\u308b\u3002<\/p>\n<p>\u30fb\u660e\u7d30\u30c7\u30fc\u30bf\u306e\u578b\u3092\u4f5c\u308b\u3002(Java\u3067\u3044\u3046\u3068\u3001\u30c7\u30fc\u30bf\u30af\u30e9\u30b9\u307f\u305f\u3044\u306a\u3082\u306e)<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE TYPE DTL_TYPE AS OBJECT (\r\n   DTL_ID   VARCHAR2(5)\r\n   DTL_NAME VARCHAR2(10)\r\n);\r\n<\/pre>\n<p>\u30fb\u6b21\u306b\u4f5c\u3063\u305f\u660e\u7d30(DTL_TYPE)\u306e\u30c6\u30fc\u30d6\u30eb\u3092\u578b\u306b\u3059\u308b\u3002(Java\u3067\u3044\u3046\u3068List<\u660e\u7d30>\u307f\u305f\u3044\u306a\u3082\u306e)<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE TYPE DTLTAB_TYPE AS TABLE OF DTL_TYPE;\r\n<\/pre>\n<p>\u30fb\u660e\u7d30\u304c\u6e08\u3093\u3060\u3089\u6b21\u306f\u30b5\u30de\u30ea\u3082\u540c\u3058\u3088\u3046\u306b\u578b\u3092\u4f5c\u308b\u3002<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE TYPE SUM_TYPE AS OBJECT (\r\n  SUM_ID   VARCHAR2(5)\r\n ,SUM_NAME VARCHAR2(20)\r\n ,DTL      DTLTAB_TYPE\r\n);\r\n<\/pre>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE TYPE SUMTAB_TYPE AS TABLE OF SUM_TYPE;\r\n<\/pre>\n<p>2, \u8a66\u3057\u306b\u4f5c\u3063\u305f\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u578b\u3092\u5f15\u6570\u306b\u3001\u95a2\u6570\u3092\u4f5c\u3063\u3066\u307f\u308b<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n---------------------------------------\r\n-- \u660e\u7d30\u30c7\u30fc\u30bf\u3092\u30ab\u30f3\u30de\u533a\u5207\u308a\u3067\u53d6\u5f97\r\n---------------------------------------\r\nCREATE OR REPLACE FUNCTION OTAMESHI_FUNC(\r\n  PARAM IN SUMTAB_TYPE \r\n) \r\nRETURN VARCHAR\r\nIS\r\n  TEMP    VARCHAR(1000);\r\nBEGIN\r\n  -- \u30b5\u30de\u30ea\u3092\u3050\u308b\u3050\u308b\u56de\u3059\r\n  DECLARE\r\n  CURSOR CURSUM IS SELECT SUM_ID, SUM_NAME, DTL FROM TABLE(CAST(PARAM AS SUMTAB_TYPE));\r\n  CURSUM_ROW CURSUM%ROWTYPE;\r\n  BEGIN\r\n    OPEN CURSUM;\r\n    LOOP\r\n      FETCH CURSUM INTO CURSUM_ROW;\r\n      EXIT WHEN CURSUM%NOTFOUND;\r\n\r\n      TEMP := TEMP || ',&lt;&lt;' || CUR_ROW2.SUM_ID || ':' || CUR_ROW2.SUM_ID || '&gt;&gt;';\r\n      \r\n      -- \u660e\u7d30\u3092\u3050\u308b\u3050\u308b\u56de\u3059\r\n      DECLARE\r\n      CURSOR CUR IS SELECT DTL_ID, DTL_NAME  FROM TABLE(CAST(CURSUM_ROW.DTL AS DTLTAB_TYPE));\r\n      CUR_ROW CUR%ROWTYPE;\r\n      BEGIN\r\n        OPEN CUR;\r\n        LOOP\r\n          FETCH CUR INTO CUR_ROW;\r\n          EXIT WHEN CUR%NOTFOUND;\r\n\r\n          TEMP := TEMP || ',' || CUR.DTL_ID || ':' || CUR.DTL_ID;\r\n\r\n        END LOOP;\r\n      END;\r\n\r\n\r\n    END LOOP;\r\n  END;\r\n\r\n  -- TEMP\u306b\u5165\u308c\u305f\u30c7\u30fc\u30bf\u3092\u8fd4\u3059\r\n  RETURN TEMP;\r\nEXCEPTION\r\n  WHEN OTHERS THEN\r\n    RETURN '\u30a8\u30e9\u30fc\u3067\u3059.';\r\nEND;\r\n<\/pre>\n<p>3, \u4f5c\u3063\u305f\u95a2\u6570\u3092\u547c\u3073\u51fa\u3057\u3066\u307f\u308b<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT\r\n  OTAMESHI_FUNC(\r\n      SUMTAB_TYPE(\r\n        SUM_TYPE('01'\r\n               , '\u307e\u3055\u304b'\r\n               , DTLTAB_TYPE(\r\n                             DTL_TYPE('A1','\u3084\u3055\u304b')\r\n                            ) \r\n               )\r\n       ,SUM_TYPE('02'\r\n               , '\u3069\u3046\u304b'\r\n               , DTLTAB_TYPE(\r\n                             DTL_TYPE('D1','\u304e\u3093\u304b')\r\n                            ,DTL_TYPE('B1','\u304d\u3093\u304b')\r\n                            ) \r\n               )\r\n     )\r\n  )\r\nFROM DUAL\r\n<\/pre>\n<p>\u3067\u304d\u308b\u306e\u306f\u89e3\u3063\u305f\u306e\u3060\u304c\u3001\u3046\uff5e\u3093\u3001\u3069\u3046\u306a\u3093\u3060\u308d\u3046\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u30b5\u30de\u30ea1 \u3000|&#8211; \u660e\u7d301-1 \u3000|&#8211; \u660e\u7d301-2 \u30b5\u30de\u30ea2 \u3000|&#8211; \u660e\u7d302-1 \u306e\u3088\u3046\u306a\u3001\u968e\u5c64\u306e\u3042\u308b\u30c7\u30fc\u30bf\u3092\u5f15\u6570\u3067\u6e21\u3057\u305f\u3044\u3068\u601d\u3063\u3066\u3001\u8abf\u3079\u3066\u307f\u305f\u3089\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u578b\u306a\u308b\u3082\u306e\u3092\u767a\u898b\u3002 \u3067\u3001\u5b9f\u969b [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19,3],"tags":[],"class_list":["post-2984","post","type-post","status-publish","format-standard","hentry","category-sql","category-development"],"_links":{"self":[{"href":"http:\/\/anada.cloudfree.jp\/index.php?rest_route=\/wp\/v2\/posts\/2984","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/anada.cloudfree.jp\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/anada.cloudfree.jp\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/anada.cloudfree.jp\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/anada.cloudfree.jp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2984"}],"version-history":[{"count":1,"href":"http:\/\/anada.cloudfree.jp\/index.php?rest_route=\/wp\/v2\/posts\/2984\/revisions"}],"predecessor-version":[{"id":2985,"href":"http:\/\/anada.cloudfree.jp\/index.php?rest_route=\/wp\/v2\/posts\/2984\/revisions\/2985"}],"wp:attachment":[{"href":"http:\/\/anada.cloudfree.jp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2984"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/anada.cloudfree.jp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2984"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/anada.cloudfree.jp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2984"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}