解决字符串连接设置间隔符的问题

Oracle10g提供了字符串连接的功能，使用WMSYS.WM_CONCAT(TNAME)就可以实现这个功能。不过在文中也提到了，利用Oracle提供的方法只能使用逗号作为字符串的分隔符。

10g新增功能实现字符串连接：http://yangtingkun.itpub.net/post/468/484171

对字符串的“sum”——在Oracle中自定义聚集函数的例子：http://yangtingkun.itpub.net/post/468/3380

 

 

使用自定义聚集函数的方法，可以方便的设置任何的字符作为字符串的间隔符。不过如果间隔符经常的发生变化，那么每次重建自定义聚集函数，或者为每个不同的间隔符建立一个函数的方法就显得过于麻烦了。

其实解决方法很简单，只需要额外创建一个包来设置间隔字符：

SQL> CREATE OR REPLACE PACKAGE P_LINK AS
  2  V_SEP VARCHAR2(1) DEFAULT ',';
  3  END;
  4  /

程序包已创建。

SQL> CREATE OR REPLACE TYPE T_LINK AS OBJECT (
  2  STR VARCHAR2(32767),
  3  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER,
  4  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER,
  5  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER,
  6  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER
  7  )
  8  /

类型已创建。

SQL> CREATE OR REPLACE TYPE BODY T_LINK IS
  2  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER IS
  3  BEGIN
  4  SCTX := T_LINK(NULL);
  5  RETURN ODCICONST.SUCCESS;
  6  END;
  7 
  8  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS
  9  BEGIN
 10  SELF.STR := SELF.STR || VALUE || P_LINK.V_SEP;
 11  RETURN ODCICONST.SUCCESS;
 12  END;
 13  
 14  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
 15  BEGIN
 16  RETURNVALUE := SUBSTR(SELF.STR, 1, LENGTH(SELF.STR) - 1);
 17  RETURN ODCICONST.SUCCESS;
 18  END;
 19 
 20  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER IS
 21  BEGIN
 22  NULL;
 23  RETURN ODCICONST.SUCCESS;
 24  END;
 25  END;
 26  /

类型主体已创建。

SQL> CREATE OR REPLACE FUNCTION F_LINK(P_STR VARCHAR2) RETURN VARCHAR2
  2  AGGREGATE USING T_LINK;
  3  /

函数已创建。

默认情况下，分隔符为逗号，如果需要设置其他字符为分隔符，只需要对PACKAGE中的变量赋值即可：

SQL> SELECT F_LINK(TNAME) FROM TAB;

F_LINK(TNAME)
-------------------------------------------------------------------------------------------
CHAINED_ROWS,T_EXTERNAL,T_SESSION_STAT,T_TEMP2,T_INDEX_ORG,T_EXTERNAL2,T_INTER,T1,T2

SQL> EXEC P_LINK.V_SEP := '|'

PL/SQL 过程已成功完成。

SQL> SELECT F_LINK(TNAME) FROM TAB;

F_LINK(TNAME)
-------------------------------------------------------------------------------------------
CHAINED_ROWS|T_EXTERNAL|T_SESSION_STAT|T_TEMP2|T_INDEX_ORG|T_EXTERNAL2|T_INTER|T1|T2

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-594775/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-594775/