Oracle multi-row record merging custom functions

Oracle multi-row record merging user-defined functions (udfs) are used to merge fields in oracle databases. You can use the wm_concat (column) function. However, this method is not recommended by Oracle, because WMSYS users are used for Workspace Manager, their function objects may be abnormal due to different versions. This change is reflected in 11.2.0.3 and 10.2.0.5. The original return value of the WM_CONCAT function is VARCHAR2, but in some versions it will become more CLOB. This change causes many program exceptions. Oracle recommends that you use a user-defined function to implement this function, instead of using the internal function of WorkSpace. This Function contains a Type, Type Body, and Function. You can refer to the implementation method of Oracle to implement this Function. The following describes the use of the wm_concat (column) function in oracle and the use of the custom zh_concat (column) function in oracle. The t_student table is used for the wm_concat (column) function in oracle: sname sdree sage sex Li Kun tianrong letter 26 male Cao guisheng Bank of China 26 male Liu Bo aiqibao 27 male Ji contention IBM 23 female Li xueyu Microsoft 25 female Li Xueqi wensi 25 female Chen Xu CNOOC 26 male Han Zhengyang CNOOC 24 male Chen Weidong nuclear 24 male Liu Bing Youku 24 male Ding Cheng Yun Lenovo 25 Queen Peng ZTE 25 male expected results: ------------------------------ statement: select t. sex, wmsys. wm_c Oncat (t. sname) from t_student tgroup by t. sex 2. The custom function zh_concat (extracted from the Internet and available directly) wm_concat appears in oracle10g, which is not only encrypted, but also in a single user, which is inconvenient to use. And versions earlier than 10 Gb cannot be used. After cracking and transplantation, you can easily execute the creation script of a type and function below to build your own wm_concat (zh_concat ). The usage is the same as that of wm_concat: type:

Create or replace TYPE into CURRENT_USER as object (CURR_STR VARCHAR2 (32767), static function odciaggresponinitialize (sctx in out zh_concat_im) return number, member function Merge (self in out zh_concat_im, P1 IN VARCHAR2) return number, member function odciaggresponterminate (self in zh_concat_im, returnvalue out VARCHAR2, flags in number) return number, member function compute (self in out zh_concat_im, SCTX2 IN zh_concat_im) return number ); /create or replace type body zh_concat_imIS static function odciaggresponinitialize (sctx in out zh_concat_im) return number is begin sctx: = zh_concat_im (NULL); return odciconst. SUCCESS; END; member function odciaggregateiterate (self in out zh_concat_im, P1 IN VARCHAR2) return number is begin if (CURR_STR is not null) THEN CURR_STR: = CURR_STR | ': '| P1; ELSE CURR_STR: = P1; end if; return odciconst. SUCCESS; END; member function odciaggresponterminate (self in zh_concat_im, returnvalue out VARCHAR2, flags in number) return number is begin returnvalue: = CURR_STR; return odciconst. SUCCESS; END; member function odciaggregatemerge (self in out zh_concat_im, SCTX2 IN zh_concat_im) return number is begin if (SCTX2.CURR _ str is not null) then self. CURR_STR: = SELF. CURR_STR | ':' | SCTX2.CURR _ STR; end if; return odciconst. SUCCESS; END;/FUNCTION: create or replace FUNCTION zh_concat (P1 VARCHAR2) RETURN VARCHAR2 aggregate using zh_concat_im;