{"id":1380,"date":"2020-06-19T16:36:57","date_gmt":"2020-06-19T08:36:57","guid":{"rendered":"http:\/\/www.821121.com\/?p=1380"},"modified":"2021-01-19T16:37:10","modified_gmt":"2021-01-19T08:37:10","slug":"12c%e6%96%b0%e7%89%b9%e6%80%a7%ef%bc%9awm_concat%e5%87%bd%e6%95%b0%e7%9a%84%e6%9b%bf%e4%bb%a3%e6%96%b9%e5%bc%8f%ef%bc%881%ef%bc%89","status":"publish","type":"post","link":"http:\/\/www.821121.com\/?p=1380","title":{"rendered":"12C\u65b0\u7279\u6027\uff1aWM_CONCAT\u51fd\u6570\u7684\u66ff\u4ee3\u65b9\u5f0f\uff081\uff09"},"content":{"rendered":"

\u4eceOracle 12.1.0.1\u8d77\uff0c\u5c06\u7981\u7528WM_CONCAT\u51fd\u6570\u3002\u5b98\u65b9\u63a8\u8350\u4f7f\u7528LISTAGG\u51fd\u6570\u66ff\u4ee3<\/p>\n

\u4e3a\u4ec0\u4e48\u8981\u6539\u7528\u9009\u62e9LISTAGG\uff1f
\n\u5b98\u65b9\u7684\u56de\u7b54\u4e3a\uff1aLISTAGG\u4f18\u4e8eWM_CONCAT\uff0c\u5b83\u7684\u95e9\u9501\u5c11\u4e8ewm_concat\u3002\u672c\u6587\u7684\u76ee\u7684\u5728\u4e8e\u6d4b\u8bd5LISTAGG\u80fd\u5426\u66ff\u4ee3WM_CONCAT\u3002\u6d4b\u8bd5\u73af\u5883\u4e3a19.3\u4e0e11.2.0.4\u4e4b\u95f4\u7684\u6bd4\u5bf9\u3002<\/p>\n

1\u300119.3\u4e0a\u6d4b\u8bd5<\/h2>\n

\u5148\u6d4b\u8bd5 WM_CONCAT\u662f\u5426\u53ef\u7528\uff1a<\/p>\n

[oracle@whs1 ~]$ sqlplus \/ as sysdba\n\nSQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 19 15:26:36 2021\nVersion 19.3.0.0.0\n\nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\n\nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0\n\nSQL> SELECT wmsys.WM_CONCAT(TYPE) from v$parameter;\nSELECT wmsys.WM_CONCAT(TYPE) from v$parameter\n       *\nERROR at line 1:\nORA-00904: "WMSYS"."WM_CONCAT": invalid identifier<\/code><\/pre>\n
\u7528listagg\u66ff\u6362\u6d4b\u8bd5<\/p>\n
SQL> SELECT listagg(TYPE,',')  from v$parameter;\n\nLISTAGG(TYPE,',')\n--------------------------------------------------------------------------------\n2,3,3,1,3,1,3,3,1,2,3,3,2,2,2,6,2,1,3,3,1,2,1,6,6,6,6,6,6,3,3,6,2,2,1,2,2,2,2,2,\n2,2,2,2,2,2,2,2,2,2,2,2,1,1,3,1,2,2,3,1,1,2,2,3,3,2,2,3,3,3,3,2,1,2,2,2,1,2,1,3,\n3,2,1,6,6,6,2,2,2,3,3,2,2,3,6,6,6,6,6,6,6,6,6,2,2,3,2,2,2,6,2,2,2,2,2,2,2,2,2,2,\n2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,\n2,2,2,2,2,2,2,2,2,2,2,2,2,2,1,2,2,3,2,2,3,3,2,2,3,3,2,3,6,3,3,3,3,3,1,1,3,2,2,2,\n2,2,2,2,6,2,1,3,2,3,3,1,2,3,1,1,3,6,1,2,1,3,1,3,3,1,2,2,2,3,2,3,3,2,6,6,2,2,1,2,\n2,2,2,2,2,2,2,2,3,3,2,2,2,2,2,2,1,2,1,1,1,2,3,1,1,1,2,3,2,2,2,1,3,3,3,2,2,3,3,3,\n3,3,1,2,2,2,2,2,1,3,2,2,1,2,2,1,3,2,2,1,1,2,3,1,3,3,3,2,2,3,3,2,3,3,2,6,3,3,2,2,\n2,2,2,2,2,2,2,1,3,3,1,3,3,3,2,2,2,2,2,2,3,3,1,1,2,2,2,2,2,3,4,1,2,2,1,1,2,2,1,3,\n3,3,2,2,6,2,3,2,2,1,1,1,3,1,1,1,1,2,2,1,1,1,3,2,1,1,1,2,1,1,2,1,1,2,2,2,3,1,1,6,\n6,2,1,2,3,1,2,1,2,2,6,2,2,2,1,3,3,2,3,1,1,2,2,2,1,2,1,2,2,1,2,3,3,1,2,2,2,2,6,3,\n2,2,2,3,2,2<\/code><\/pre>\n
listagg\u4e5f\u80fd\u652f\u6301distinct\uff0c\u5982\u4e0b\uff1a<\/p>\n
SQL> SELECT listagg(distinct TYPE,',')  from v$parameter;\n\nLISTAGG(DISTINCTTYPE,',')\n--------------------------------------------------------------------------------\n1,2,3,4,6<\/code><\/pre>\n
2\u3001\u752811.2.0.4\u6d4b\u8bd5<\/h2>\n
[oracle@whs ~]$ sqlplus \/ as sysdba\n\nSQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 19 15:29:13 2021\n\nCopyright (c) 1982, 2013, Oracle.  All rights reserved.\n\nConnected to:\nOracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production\nWith the Partitioning, OLAP, Data Mining and Real Application Testing options\n\nSQL> SET LONG 10000000 \nSQL> set pagesize 200\nSQL> SELECT WM_CONCAT(TYPE) from v$parameter;\n\nWM_CONCAT(TYPE)\n--------------------------------------------------------------------------------\n2,3,3,1,3,1,3,3,3,2,2,6,2,1,3,3,1,1,2,6,6,6,6,6,3,3,2,2,2,2,2,2,2,2,2,2,2,2,2,2,\n2,2,2,2,2,2,2,3,1,1,1,1,3,1,3,2,2,1,3,3,6,6,6,2,2,2,3,3,2,2,3,6,6,6,6,6,6,6,6,3,\n2,2,2,6,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,\n2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,1,2,2,3,2,2,2,3,2,1,2,2,\n3,3,3,3,3,3,3,1,1,1,3,3,2,2,2,2,2,2,2,6,2,1,3,3,3,1,2,3,1,3,3,1,3,3,2,2,2,3,2,3,\n3,2,2,2,2,2,2,2,1,2,3,1,1,1,1,2,3,1,2,2,1,3,3,3,2,2,2,3,3,3,3,3,2,2,2,1,3,2,2,2,\n1,2,2,1,3,2,2,1,1,2,3,3,3,3,2,2,3,3,2,3,3,6,3,3,2,2,2,2,2,2,2,3,3,3,3,3,2,2,2,2,\n2,2,3,3,1,1,2,2,2,2,2,3,4,1,2,2,1,1,2,2,1,3,1,1,3,3,2,2,6,2,3,2,2,1,1,3,1,1,1,1,\n2,2,1,1,1,3,2,2,3,1,1,6,6,3,1,2,1,2,2,6,2,2,2,3,2,3,2,2,2,2,1,2<\/code><\/pre>\n
\u4f7f\u7528distinct\u6d4b\u8bd5<\/p>\n
SQL> SELECT WM_CONCAT(DISTINCT TYPE) from v$parameter;\n\nWM_CONCAT(DISTINCTTYPE)\n--------------------------------------------------------------------------------\n1,2,3,4,6<\/code><\/pre>\n
\u6d4b\u8bd5listagg<\/p>\n
SQL> SELECT listagg(TYPE,',')  from v$parameter;\nSELECT listagg(TYPE,',')  from v$parameter\n                          *\nERROR at line 1:\nORA-02000: missing WITHIN keyword<\/code><\/pre>\n
11.2.0.4\u4e0d\u652f\u6301\u4ee5\u4e0a\u8fd9\u79cd\u5199\u6cd5\uff0c\u9700\u8981\u52a0\u4e0awithin group (order by xxx) \uff0c\u6d4b\u8bd5\u5982\u4e0b<\/p>\n
SQL>  select listagg( TYPE,',') within group (order by type) from v$parameter;\n\nLISTAGG(TYPE,',')WITHINGROUP(ORDERBYTYPE)\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,\n2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,\n2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,\n3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,4,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6<\/code><\/pre>\n
3\u3001\u7ed3\u679c\u5bf9\u6bd4<\/h2>\n
\u5c0619.3\u7684  listagg(TYPE,',') \u7684\u7ed3\u679c\u4e0e 11.2.0.4\u7684WM_CONCAT(TYPE)\u7ed3\u679c\u5bf9\u6bd4\uff0c\u7ed3\u679c\u662f\u76f8\u4f3c\u7684\uff0c\u4e0d\u540c\u70b9\u662fWM_CONCAT\u7684\u7ed3\u679c\u662fclob\uff0clistagg\u7684\u7ed3\u679c\u662fvarchar\u3002\u6240\u4ee5\u6709\u5b57\u7b26\u578b\u4e0a\u9700\u8981\u505a\u4fee\u6539\uff1b\u4e14\u5b57\u6bb5\u957f\u5ea6\u662f\u4e0d\u4e00\u6837\u7684\uff0clistagg\u957f\u5ea6\u4e0d\u591f\u6709\u53ef\u80fd\u4f1a\u6ee1\u8db3\u4e0d\u4e86\u9700\u6c42\u3002\u5982\u4e0b\u5c31\u4f1a\u62a5\u9519\uff1a<\/p>\n
SQL> SELECT listagg(object_id,',')  from dba_objects;\nSELECT listagg(object_id,',')  from dba_objects\n                                    *\nERROR at line 1:\nORA-01489: result of string concatenation is too long<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
\u4eceOracle 12.1.0.1\u8d77\uff0c\u5c06\u7981\u7528WM_C<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19,39],"tags":[],"class_list":["post-1380","post","type-post","status-publish","format-standard","hentry","category-oracle-12c","category-39"],"_links":{"self":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1380","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1380"}],"version-history":[{"count":1,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1380\/revisions"}],"predecessor-version":[{"id":1381,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1380\/revisions\/1381"}],"wp:attachment":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1380"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1380"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1380"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}