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

Oracle多行记录合并自定义函数

Oracle多行记录合并自定义函数
 
在oracle数据库中,进行字段合并,可以使用wm_concat(column)函数,但是在这种方法不被Oracle所推荐,因为WMSYS用户用于Workspace Manager,其函数对象可能因版本而不同而出现异常,这种变化在11.2.0.3及10.2.0.5中体现出来。原本WM_CONCAT函数返回值为VARCHAR2,但在一些版本下就会变更为CLOB。这一变化导致了很多程序的异常。
Oracle建议用户使用自定义函数来实现该功能,而不是使用WorkSpace的这个内部函数。这个函数包含一个Type、Type Body、Function,可以参考Oracle的实现方式来实现这个函数。
 
下面分别介绍oracle中wm_concat(column)函数的使用和多行记录合并自定义函数 zh_concat
一、oracle中wm_concat(column)函数的使用
t_student表:
SNAME        SDREE        SAGE        SEX
李坤        天融信        26        男
曹贵生        中银        26        男
柳波        买卖宝        27        男
纪争光        IBM        23        女
李学宇        微软        25        女
李雪琪        文思        25        女
陈绪        中海油        26        男
韩正阳        中海油        24        男
陈伟东        中核        24        男
刘兵        优酷        24        男
丁成云        联想        25        女
王鹏        中兴        25        男
 
想要的结果为:
--------------------------------
男        李坤,曹贵生,柳波,陈伟东,韩正阳,陈绪,刘兵,王鹏
女        纪争光,李学宇,丁成云,李雪琪
---------------------------------
 
SQL语句为:
select t.易做图,wmsys.wm_concat(t.sname)from t_student tgroup by t.易做图
 
二、自定义函数zh_concat(从网上摘,直接可用)
wm_concat出现在oracle10g版本中,不仅是加密的,而且是在一个单独的用户中,不方便使用。并且10g以前的版本也用不上。经过破解移植,可以方便大家使用
将下边的一个type,一个函数的创建脚本执行,就可以构建自己的wm_concat(这里叫zh_concat)。使用方法同wm_concat:
type:
create or replace TYPE zh_concat_im
AUTHID CURRENT_USER AS OBJECT
(
  CURR_STR VARCHAR2(32767),
  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 VARCHAR2,
                                 FLAGS IN NUMBER)
                     RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,
                    SCTX2 IN  zh_concat_im) RETURN NUMBER
);
/

create or replace TYPE BODY 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 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;
END;
/
函数:
create or replace FUNCTION zh_concat(P1 VARCHAR2)
RETURN VARCHAR2 AGGREGATE USING zh_concat_im ;

 

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