创建wm_concat函数

1、一.解锁wmsys用户alter user wmsys account unlock;解锁后，如果还是登录不了，可以登录system找到wmsys用户，设置密码和权限

2、二.创建包、包体和函数以wmsys用户登录数据库，执行下面的命令CREATEORREPLACETYPEWM_CONCAT_IMPLASOBJECT--AUTHIDCURRENT_USERASOBJECT(CURR_STRVARCHAR2(32767),STATICFUNCTIONODCIAGGREGATEINITIALIZE(SCTXINOUTWM_CONCAT_IMPL)RETURNNUMBER,MEMBERFUNCTIONODCIAGGREGATEITERATE(SELFINOUTWM_CONCAT_IMPL,P1INVARCHAR2)RETURNNUMBER,MEMBERFUNCTIONODCIAGGREGATETERMINATE(SELFINWM_CONCAT_IMPL,RETURNVALUEOUTVARCHAR2,FLAGSINNUMBER)RETURNNUMBER,MEMBERFUNCTIONODCIAGGREGATEMERGE(SELFINOUTWM_CONCAT_IMPL,SCTX2INWM_CONCAT_IMPL)RETURNNUMBER);

3、--定义类型CREATEORREPLACETYPEBODYWM_CONCAT_IMPLISS哌囿亡噱TATICFUNCTIONODCIAGGREGATEINITIALIZE(SC皈其拄攥TXINOUTWM_CONCAT_IMPL)RETURNNUMBERISBEGINSCTX:=WM_CONCAT_IMPL(NULL);RETURNODCICONST.SUCCESS;END;MEMBERFUNCTIONODCIAGGREGATEITERATE(SELFINOUTWM_CONCAT_IMPL,P1INVARCHAR2)RETURNNUMBERISBEGINIF(CURR_STRISNOTNULL)THENCURR_STR:=CURR_STR||','||P1;ELSECURR_STR:=P1;ENDIF;RETURNODCICONST.SUCCESS;END;MEMBERFUNCTIONODCIAGGREGATETERMINATE(SELFINWM_CONCAT_IMPL,RETURNVALUEOUTVARCHAR2,FLAGSINNUMBER)RETURNNUMBERISBEGINRETURNVALUE:=CURR_STR;RETURNODCICONST.SUCCESS;END;MEMBERFUNCTIONODCIAGGREGATEMERGE(SELFINOUTWM_CONCAT_IMPL,SCTX2INWM_CONCAT_IMPL)RETURNNUMBERISBEGINIF(SCTX2.CURR_STRISNOTNULL)THENSELF.CURR_STR:=SELF.CURR_STR||','||SCTX2.CURR_STR;ENDIF;RETURNODCICONST.SUCCESS;END;END;

4、--自定义行变列函数:CREATEORREPLACEFUNCTIONwm_concat(P1VARCHAR2)RETURNVARCHAR2AGGREGATEUSINGWM_CONCAT_IMPL;/-- 创建同义词并授权createpublicsynonym WM_CONCAT_IMPLforwmsys.WM_CONCAT_IMPL;createpublicsynonym wm_concatforwmsys.wm_concat;grantexecuteonWM_CONCAT_IMPLtopublic;grantexecuteonwm_concattopublic;