[20221227]19c LISTAGG Enhancements.txt

[20221227]19c LISTAGG Enhancements.txt --//学习了解19c以上版本LISTAGG的新特性. --//http://blog.dbi-services.com/oracle-12cr2-sql-new-feature-listagg-overflow/ After the delimiter string you can add: ON OVERFLOW ERROR which is the default. Same behavior as in previous releases. ON OVERFLOW TRUNCATE with a string that is added in case of truncation (default: '…') and optionally WITH COUNT (or WITHOUT COUNT which is the default) 1.环境: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 SCOTT@test01p> create table tx as select object_id,object_name,object_type from all_objects where rownum<=3e3; Table created. 2.测试: SCOTT@test01p> select listagg(object_name, ',' on overflow truncate) within group(order by object_id) c200 from tx; C200 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- C_OBJ#,I_OBJ#,TAB$,CLU$,C_TS#,I_TS#,C_FILE#_BLOCK#,I_FILE#_BLOCK#,C_USER#,I_USER#,FET$,UET$,SEG$,UNDO$,TS$,FILE$,OBJ$,IND$,ICOL$,COL$,USER$,PROXY_DATA$,I_PROXY_DATA$,PROXY_ROLE_DATA$,I_PROXY_ROLE_DATA $_1,I_PROXY_ROLE_DATA$_2,CON$,C_COBJ#,I_COBJ#,CDEF$,CCOL$,I_TAB1,I_UNDO1,I_UNDO2,I_OBJ1,I_OBJ2,I_OBJ3,I_OBJ4,I_OBJ5,I_IND1,I_ICOL1,I_FILE1,I_FILE2,I_TS1,I_USER1,I_USER2,I_COL1,I_COL2,I_COL3,I_CON1,I_C ON2,I_CDEF1,I_CDEF2,I_CDEF3,I_CDEF4,I_CCOL1,I_CCOL2,BOOTSTRAP$,OBJERROR$,OBJAUTH$,I_OBJAUTH1,I_OBJAUTH2,C_OBJ#_INTCOL#,I_OBJ#_INTCOL#,HISTGRM$,I_H_OBJ#_COL#,HIST_HEAD$,I_HH_OBJ#_COL#,I_HH_OBJ#_INTCOL# ,FIXED_OBJ$,I_FIXED_OBJ$_OBJ#,TAB_STATS$,I_TAB_STATS$_OBJ#,IND_STATS$,I_IND_STATS$_OBJ#,OBJECT_USAGE,I_STATS_OBJ#,PARTOBJ$,I_PARTOBJ$,DEFERRED_STG$,I_DEFERRED_STG1,DEPENDENCY$,ACCESS$,I_DEPENDENCY1,I_ ... CS,V$SEGMENT_STATISTICS,V_$SEGSTAT_NAME,V$SEGSTAT_NAME,V_$SEGSTAT,V$SEGSTAT,V_$LIBRARY_CACHE_MEMORY,V$LIBRARY_CACHE_MEMORY,V_$JAVA_LIBRARY_CACHE_MEMORY,V$JAVA_LIBRARY_CACHE_MEMORY,V_$SHARED_POOL_ADVIC E,V$SHARED_POOL_ADVICE,V_$JAVA_POOL_ADVICE,V$JAVA_POOL_ADVICE,V_$STREAMS_POOL_ADVICE,V$STREAMS_POOL_ADVICE,V_$GOLDENGATE_CAPABILITIES,V$GOLDENGATE_CAPABILITIES,V_$SGA_CURRENT_RESIZE_OPS,V$SGA_CURRENT_ RESIZE_OPS,V_$SGA_RESIZE_OPS,V$SGA_RESIZE_OPS,V_$SGA_DYNAMIC_COMPONENTS,V$SGA_DYNAMIC_COMPONENTS,V_$SGA_DYNAMIC_FREE_MEMORY,...(985) --//被截断了,显示还有985个记录没有显示. SCOTT@test01p> select listagg(object_name, ',' on overflow truncate 'string is too long') within group(order by object_id) c200 from tx; C200 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- C_OBJ#,I_OBJ#,TAB$,CLU$,C_TS#,I_TS#,C_FILE#_BLOCK#,I_FILE#_BLOCK#,C_USER#,I_USER#,FET$,UET$,SEG$,UNDO$,TS$,FILE$,OBJ$,IND$,ICOL$,COL$,USER$,PROXY_DATA$,I_PROXY_DATA$,PROXY_ROLE_DATA$,I_PROXY_ROLE_DATA $_1,I_PROXY_ROLE_DATA$_2,CON$,C_COBJ#,I_COBJ#,CDEF$,CCOL$,I_TAB1,I_UNDO1,I_UNDO2,I_OBJ1,I_OBJ2,I_OBJ3,I_OBJ4,I_OBJ5,I_IND1,I_ICOL1,I_FILE1,I_FILE2,I_TS1,I_USER1,I_USER2,I_COL1,I_COL2,I_COL3,I_CON1,I_C ON2,I_CDEF1,I_CDEF2,I_CDEF3,I_CDEF4,I_CCOL1,I_CCOL2,BOOTSTRAP$,OBJERROR$,OBJAUTH$,I_OBJAUTH1,I_OBJAUTH2,C_OBJ#_INTCOL#,I_OBJ#_INTCOL#,HISTGRM$,I_H_OBJ#_COL#,HIST_HEAD$,I_HH_OBJ#_COL#,I_HH_OBJ#_INTCOL# ,FIXED_OBJ$,I_FIXED_OBJ$_OBJ#,TAB_STATS$,I_TAB_STATS$_OBJ#,IND_STATS$,I_IND_STATS$_OBJ#,OBJECT_USAGE,I_STATS_OBJ#,PARTOBJ$,I_PARTOBJ$,DEFERRED_STG$,I_DEFERRED_STG1,DEPENDENCY$,ACCESS$,I_DEPENDENCY1,I_ ... EMORY_TARGET_ADVICE,GV_$MEMORY_RESIZE_OPS,GV$MEMORY_RESIZE_OPS,GV_$MEMORY_CURRENT_RESIZE_OPS,GV$MEMORY_CURRENT_RESIZE_OPS,GV_$MEMORY_DYNAMIC_COMPONENTS,GV$MEMORY_DYNAMIC_COMPONENTS,V_$SEGMENT_STATISTI CS,V$SEGMENT_STATISTICS,V_$SEGSTAT_NAME,V$SEGSTAT_NAME,V_$SEGSTAT,V$SEGSTAT,V_$LIBRARY_CACHE_MEMORY,V$LIBRARY_CACHE_MEMORY,V_$JAVA_LIBRARY_CACHE_MEMORY,V$JAVA_LIBRARY_CACHE_MEMORY,V_$SHARED_POOL_ADVIC E,V$SHARED_POOL_ADVICE,V_$JAVA_POOL_ADVICE,V$JAVA_POOL_ADVICE,V_$STREAMS_POOL_ADVICE,V$STREAMS_POOL_ADVICE,V_$GOLDENGATE_CAPABILITIES,V$GOLDENGATE_CAPABILITIES,V_$SGA_CURRENT_RESIZE_OPS,V$SGA_CURRENT_ RESIZE_OPS,V_$SGA_RESIZE_OPS,V$SGA_RESIZE_OPS,V_$SGA_DYNAMIC_COMPONENTS,V$SGA_DYNAMIC_COMPONENTS,string is too long(986) --//结尾加入string is too long代替...,并且显示计数,怎么比前面多1个呢. SCOTT@test01p> select listagg(object_name, ',' on overflow truncate without count) within group(order by object_id) c200 from tx; C200 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- C_OBJ#,I_OBJ#,TAB$,CLU$,C_TS#,I_TS#,C_FILE#_BLOCK#,I_FILE#_BLOCK#,C_USER#,I_USER#,FET$,UET$,SEG$,UNDO$,TS$,FILE$,OBJ$,IND$,ICOL$,COL$,USER$,PROXY_DATA$,I_PROXY_DATA$,PROXY_ROLE_DATA$,I_PROXY_ROLE_DATA $_1,I_PROXY_ROLE_DATA$_2,CON$,C_COBJ#,I_COBJ#,CDEF$,CCOL$,I_TAB1,I_UNDO1,I_UNDO2,I_OBJ1,I_OBJ2,I_OBJ3,I_OBJ4,I_OBJ5,I_IND1,I_ICOL1,I_FILE1,I_FILE2,I_TS1,I_USER1,I_USER2,I_COL1,I_COL2,I_COL3,I_CON1,I_C ON2,I_CDEF1,I_CDEF2,I_CDEF3,I_CDEF4,I_CCOL1,I_CCOL2,BOOTSTRAP$,OBJERROR$,OBJAUTH$,I_OBJAUTH1,I_OBJAUTH2,C_OBJ#_INTCOL#,I_OBJ#_INTCOL#,HISTGRM$,I_H_OBJ#_COL#,HIST_HEAD$,I_HH_OBJ#_COL#,I_HH_OBJ#_INTCOL# ... EMORY_TARGET_ADVICE,GV_$MEMORY_RESIZE_OPS,GV$MEMORY_RESIZE_OPS,GV_$MEMORY_CURRENT_RESIZE_OPS,GV$MEMORY_CURRENT_RESIZE_OPS,GV_$MEMORY_DYNAMIC_COMPONENTS,GV$MEMORY_DYNAMIC_COMPONENTS,V_$SEGMENT_STATISTI CS,V$SEGMENT_STATISTICS,V_$SEGSTAT_NAME,V$SEGSTAT_NAME,V_$SEGSTAT,V$SEGSTAT,V_$LIBRARY_CACHE_MEMORY,V$LIBRARY_CACHE_MEMORY,V_$JAVA_LIBRARY_CACHE_MEMORY,V$JAVA_LIBRARY_CACHE_MEMORY,V_$SHARED_POOL_ADVIC E,V$SHARED_POOL_ADVICE,V_$JAVA_POOL_ADVICE,V$JAVA_POOL_ADVICE,V_$STREAMS_POOL_ADVICE,V$STREAMS_POOL_ADVICE,V_$GOLDENGATE_CAPABILITIES,V$GOLDENGATE_CAPABILITIES,V_$SGA_CURRENT_RESIZE_OPS,V$SGA_CURRENT_ RESIZE_OPS,V_$SGA_RESIZE_OPS,V$SGA_RESIZE_OPS,V_$SGA_DYNAMIC_COMPONENTS,V$SGA_DYNAMIC_COMPONENTS,V_$SGA_DYNAMIC_FREE_MEMORY,V$SGA_DYNAMIC_FREE_MEMORY,V_$RESUMABLE,... --//截断,并且不显示计数. --//还可以使用XMLAGG. SCOTT@test01p> select  xmlagg(xmlelement(e,object_name||',')).extract('//text()') c200 from tx; C200 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- I_FILE#_BLOCK#,I_OBJ3,I_TS1,I_CON1,IND$,CDEF$,C_TS#,I_CCOL2,I_PROXY_DATA$,I_CDEF4,I_TAB1,CLU$,I_PROXY_ROLE_DATA$_1,I_OBJ1,UNDO$,I_UNDO2,I_TS#,I_FILE1,I_COL2,I_OBJ#,C_OBJ#,I_CDEF3,C_COBJ#,CCOL$,I_OBJ5, PROXY_ROLE_DATA$,I_CDEF1,C_USER#,C_FILE#_BLOCK#,FET$,I_CON2,I_OBJ4,CON$,I_CDEF2,ICOL$,I_COL3,I_CCOL1,COL$,I_ICOL1,UET$,PROXY_DATA$,USER$,I_PROXY_ROLE_DATA$_2,I_OBJ2,TAB$,I_COBJ#,I_USER#,FILE$,OBJ$,TS$ ,I_UNDO1,BOOTSTRAP$,I_COL1,I_FILE2,I_IND1,I_USER2,I_USER1,SEG$,OBJERROR$,OBJAUTH$,I_OBJAUTH1,I_OBJAUTH2,C_OBJ#_INTCOL#,I_OBJ#_INTCOL#,HISTGRM$,I_H_OBJ#_COL#,HIST_HEAD$,I_HH_OBJ#_COL#,I_HH_OBJ#_INTCOL# .... R,GV_$AQ_BACKGROUND_COORDINATOR,GV$AQ_BACKGROUND_COORDINATOR,V_$AQ_JOB_COORDINATOR,V$AQ_JOB_COORDINATOR,GV_$AQ_JOB_COORDINATOR,GV$AQ_JOB_COORDINATOR,V_$AQ_SERVER_POOL,V$AQ_SERVER_POOL,GV_$AQ_SERVER_PO OL,GV$AQ_SERVER_POOL,V_$AQ_CROSS_INSTANCE_JOBS,V$AQ_CROSS_INSTANCE_JOBS,GV_$AQ_CROSS_INSTANCE_JOBS,GV$AQ_CROSS_INSTANCE_JOBS,GV_$CON_SYSMETRIC,GV$CON_SYSMETRIC,GV_$CON_SYSMETRIC_HISTORY,GV$CON_SYSMETR IC_HISTORY,GV_$CON_SYSMETRIC_SUMMARY,GV$CON_SYSMETRIC_SUMMARY,GV_$SYSMETRIC_SUMMARY,GV$SYSMETRIC_SUMMARY,GV_$SESSMETRIC,GV$SESSMETRIC,GV_$FILEMETRIC,GV$FILEMETRIC,GV_$FILEMETRIC_HISTORY,GV$FILEMETRIC_ HISTORY,GV_$EVENTMETRIC,GV$EVENTMETRIC,GV_$WAITCLASSMETRIC,GV$WAITCLASSMETRIC,GV_$WAITCLASSMETRIC_HISTORY,GV$WAITCLASSMETRIC_HISTORY,GV_$SERVICEMETRIC, --//全部显示,并且结尾有1个,不熟悉语法,不知道如何按照object_id排序输出. select listagg(object_name, ',' on overflow truncate) within group(order by object_id) c200 from tx; select listagg(object_name, ',' on overflow truncate 'string is too long') within group(order by object_id) c200 from tx; select listagg(object_name, ',' on overflow truncate without count) within group(order by object_id) c200 from tx; Select deptno,listagg(ename,'!') within group (order by sal) enames from emp group by deptno ;