Oracle cartrige 扩展/自定义实现 ListAgg

最新推荐文章于 2022-11-29 11:53:31 发布

五柳-先生

最新推荐文章于 2022-11-29 11:53:31 发布

阅读量1.3k

点赞数

分类专栏： Oracle 基础

本文链接：https://blog.csdn.net/wuliusir/article/details/9192721

版权

Oracle 基础专栏收录该内容

54 篇文章 0 订阅

订阅专栏

1. 自定义Cartrige type

CREATE OR REPLACE TYPE 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 );

2. 实现Cartrige Type body

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 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;

3. 自定义函数调用Cartrige type

CREATE OR REPLACE
FUNCTION zh_concat(
P1 VARCHAR2)
RETURN CLOB AGGREGATE USING zh_concat_im ;

4. 在SQL 中调用自定义的集合函数

select t.*,zh_concat(employee_name) over(partition by employee_name) from EMPLOYEE_SALARY_T t；

--------------------------------------------------------
-- DDL for Table EMPLOYEE_SALARY_T
--------------------------------------------------------

CREATE TABLE "SYS"."EMPLOYEE_SALARY_T"
( "EMPLOYEE_ID" VARCHAR2(20 BYTE),
"EMPLOYEE_NAME" VARCHAR2(20 BYTE),
"DEPARTMENT" VARCHAR2(10 BYTE),
"COMPANY" VARCHAR2(10 BYTE),
"SALARY" NUMBER(6,0),
"SALARY_YEAR" VARCHAR2(10 BYTE)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
REM INSERTING into SYS.EMPLOYEE_SALARY_T
SET DEFINE OFF;
Insert into SYS.EMPLOYEE_SALARY_T (EMPLOYEE_ID,EMPLOYEE_NAME,DEPARTMENT,COMPANY,SALARY,SALARY_YEAR) values ('001','znp','dep001','VisSoft',30000,'2005');
Insert into SYS.EMPLOYEE_SALARY_T (EMPLOYEE_ID,EMPLOYEE_NAME,DEPARTMENT,COMPANY,SALARY,SALARY_YEAR) values ('001','znp','dep001','VisSoft',40000,'2006');
Insert into SYS.EMPLOYEE_SALARY_T (EMPLOYEE_ID,EMPLOYEE_NAME,DEPARTMENT,COMPANY,SALARY,SALARY_YEAR) values ('001','znp','dep001','VisSoft',50000,'2007');
Insert into SYS.EMPLOYEE_SALARY_T (EMPLOYEE_ID,EMPLOYEE_NAME,DEPARTMENT,COMPANY,SALARY,SALARY_YEAR) values ('001','znp','dep001','VisSoft',60000,'2008');
Insert into SYS.EMPLOYEE_SALARY_T (EMPLOYEE_ID,EMPLOYEE_NAME,DEPARTMENT,COMPANY,SALARY,SALARY_YEAR) values ('002','zsj','dep001','AvsSoft',3000,'2005');
Insert into SYS.EMPLOYEE_SALARY_T (EMPLOYEE_ID,EMPLOYEE_NAME,DEPARTMENT,COMPANY,SALARY,SALARY_YEAR) values ('002','zsj','dep001','AvsSoft',4000,'2006');
Insert into SYS.EMPLOYEE_SALARY_T (EMPLOYEE_ID,EMPLOYEE_NAME,DEPARTMENT,COMPANY,SALARY,SALARY_YEAR) values ('002','zsj','dep001','AvsSoft',5000,'2007');
Insert into SYS.EMPLOYEE_SALARY_T (EMPLOYEE_ID,EMPLOYEE_NAME,DEPARTMENT,COMPANY,SALARY,SALARY_YEAR) values ('002','zsj','dep001','AvsSoft',6000,'2008');
Insert into SYS.EMPLOYEE_SALARY_T (EMPLOYEE_ID,EMPLOYEE_NAME,DEPARTMENT,COMPANY,SALARY,SALARY_YEAR) values ('003','ts1','dep002','VisSoft',30000,'2005');
Insert into SYS.EMPLOYEE_SALARY_T (EMPLOYEE_ID,EMPLOYEE_NAME,DEPARTMENT,COMPANY,SALARY,SALARY_YEAR) values ('003','ts1','dep002','VisSoft',40000,'2006');
Insert into SYS.EMPLOYEE_SALARY_T (EMPLOYEE_ID,EMPLOYEE_NAME,DEPARTMENT,COMPANY,SALARY,SALARY_YEAR) values ('003','ts1','dep002','VisSoft',50000,'2007');
Insert into SYS.EMPLOYEE_SALARY_T (EMPLOYEE_ID,EMPLOYEE_NAME,DEPARTMENT,COMPANY,SALARY,SALARY_YEAR) values ('003','ts1','dep002','VisSoft',60000,'2008');
Insert into SYS.EMPLOYEE_SALARY_T (EMPLOYEE_ID,EMPLOYEE_NAME,DEPARTMENT,COMPANY,SALARY,SALARY_YEAR) values ('004','ts2','dep002','AvsSoft',3000,'2005');
Insert into SYS.EMPLOYEE_SALARY_T (EMPLOYEE_ID,EMPLOYEE_NAME,DEPARTMENT,COMPANY,SALARY,SALARY_YEAR) values ('004','ts2','dep002','AvsSoft',4000,'2006');
Insert into SYS.EMPLOYEE_SALARY_T (EMPLOYEE_ID,EMPLOYEE_NAME,DEPARTMENT,COMPANY,SALARY,SALARY_YEAR) values ('004','ts2','dep002','AvsSoft',5000,'2007');
Insert into SYS.EMPLOYEE_SALARY_T (EMPLOYEE_ID,EMPLOYEE_NAME,DEPARTMENT,COMPANY,SALARY,SALARY_YEAR) values ('004','ts2','dep002','AvsSoft',6000,'2008');