当前位置:数据库 > Oracle >>

oracle中,行转列函数wm_concat()结果有长度限制,重写该函数解决

oracle中,行转列函数wm_concat()结果有长度限制,重写该函数解决
 
--Type 
CREATE OR REPLACE TYPE dict_maint.zh_concat_im AUTHID CURRENT_USER AS OBJECT 
( 
  CURR_STR clob, 
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im) 
    RETURN NUMBER, 
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im, 
                                       P1   IN VARCHAR2) RETURN NUMBER, 
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN zh_concat_im, 
                                         RETURNVALUE OUT clob, 
                                         FLAGS       IN NUMBER) 
    RETURN NUMBER, 
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT zh_concat_im, 
                                     SCTX2 IN zh_concat_im) RETURN NUMBER 
); 
/ 
--TYPE BODY 
CREATE OR REPLACE TYPE BODY dict_maint.zh_concat_im IS 
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(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 ODCIAGGREGATETERMINATE(SELF        IN zh_concat_im, 
                                         RETURNVALUE OUT clob, 
                                         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; 
END;  
/ 
--FUNCTION  
create or replace FUNCTION dict_maint.zh_concat(P1 VARCHAR2) RETURN clob AGGREGATE USING dict_maint.zh_concat_im;

 


CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,