还原 listagg/wm_concat 后的数据 pack_split_listatt ；

1.创建表并制作测试数据；

--创建测试表 ；
CREATE TABLE split_table (
NAME  VARCHAR2(100),
ID VARCHAR2(100)

);
--准备测试数据 ；
INSERT INTO  split_table VALUES('aa','11,12,13,14,15');
INSERT INTO  split_table VALUES('bb','aa,bb,cc,dd,');
INSERT INTO  split_table VALUES('cc','1a,2c,3d,,4c,55,,');
COMMIT ;

--原理 ;
SELECT * FROM TABLE(splitstr((SELECT ID FROM  split_table WHERE NAME='aa' ),',')) a ;

2.编写拆分函数 ：

CREATE OR REPLACE PACKAGE PACK_SPLIT_LISTAGG IS

  TYPE RECORD_SPLIT_TABLE_TYPE IS RECORD(
    V_NAME VARCHAR2(100),
    V_ID   VARCHAR2(100));
  REC_ST RECORD_SPLIT_TABLE_TYPE;
  TYPE SPLIT_TABLE_TYPE IS TABLE OF RECORD_SPLIT_TABLE_TYPE;
  CURSOR CURSOR_ST IS
    SELECT NAME, ID FROM SPLIT_TABLE;
  FUNCTION FUN_TEST_SPLITLISTAGG RETURN SPLIT_TABLE_TYPE
    PIPELINED;

END PACK_SPLIT_LISTAGG;
/

CREATE OR REPLACE PACKAGE BODY PACK_SPLIT_LISTAGG IS

  FUNCTION FUN_TEST_SPLITLISTAGG RETURN SPLIT_TABLE_TYPE
    PIPELINED AS
    ROW_ST      SPLIT_TABLE%ROWTYPE;
    V_START     NUMBER := 1;
    V_INDEX     NUMBER;
    V_LENGTH    NUMBER;
    P_DELIMITER VARCHAR2(100) := ',';
  BEGIN
    OPEN CURSOR_ST;
    LOOP
      FETCH CURSOR_ST
        INTO ROW_ST;
      EXIT WHEN CURSOR_ST%NOTFOUND;
      V_LENGTH := TO_NUMBER(LENGTH(ROW_ST.ID));
      WHILE (V_START <= V_LENGTH) LOOP
        V_INDEX := INSTR(ROW_ST.ID, P_DELIMITER, V_START);
        IF V_INDEX = 0 THEN
          REC_ST.V_NAME := ROW_ST.NAME;
          REC_ST.V_ID   := SUBSTR(ROW_ST.ID, V_START);
          V_START       := V_LENGTH + 1;
        ELSE
          REC_ST.V_NAME := ROW_ST.NAME;
          REC_ST.V_ID   := SUBSTR(ROW_ST.ID, V_START, V_INDEX - V_START);
          V_START       := V_INDEX + 1;
        END IF;
        PIPE ROW(REC_ST);
      END LOOP;
      V_START := 1;
    END LOOP;
    CLOSE CURSOR_ST;
    RETURN;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM);
  END FUN_TEST_SPLITLISTAGG;
END PACK_SPLIT_LISTAGG;
/

3.测试代码及结果：

--测试代码 ；
SELECT * FROM TABLE(pack_split_listagg.FUN_TEST_SPLITLISTAGG);

4.谢谢 ！

/* 总结：  pipelined  pipe row ();  

1. 一般用于返回一个集合；record ,index_table();  nested_table(); array_table();

 2.返回时一次性返回；  3.如果游标的 返回值是cur%rowtype 作为参数传递时应 应使用*（select * from ）

 4.因为RECODE()类型和 cursor%row类型一致 所以同3 ；  5.使用游标（weak 弱类型）没有返回值类型那么默认返回的是cur%rowtype 类型；

*/

 

SELECT *   FROM TABLE(FUNC_PDT_TAB(CURSOR(SELECT S.PRODUCTID  , S.PRODUCTCODE, S.PRODUCTNAME   FROM SYS_PRODUCT S   ORDER BY S.PRODUCTID)));

select * FROM sys_product ; select * FROM emp ;

select * FROM TABLE(TEST_EMP_CURSOR(CURSOR(SELECT e.empno,e.ename,e.job FROM emp e))) ;

select * FROM TABLE(TEST_EMP_CURSOR_1(CURSOR(SELECT * FROM emp e))) ; --正确的写法；

select * FROM TABLE(TEST_EMP_CURSOR_1(CURSOR(SELECT e.empno,e.ename,e.job FROM emp e))) ;--抛错！

select * FROM TABLE(TEST_EMP_CURSOR_2(CURSOR(SELECT e.empno,e.ename,e.job FROM emp e))) ;

-------------------------------------------------------------------------

PIPELINED 基础用法 ：

CREATE OR REPLACE PACKAGE REFCUR_PKG IS
  TYPE REFCUR_T IS REF CURSOR RETURN EMP_BAK%ROWTYPE;
  TYPE OUTREC_TYP IS RECORD(
    VAR_EMPNO   NUMBER(6),
    VAR_EMPNAME VARCHAR2(30),
    VAR_MGR     VARCHAR2(30));
  TYPE OUTRECSET IS TABLE OF OUTREC_TYP;
  FUNCTION F_TRANS(P REFCUR_T) RETURN OUTRECSET
    PIPELINED;
END REFCUR_PKG;
/

CREATE OR REPLACE PACKAGE BODY REFCUR_PKG IS
  FUNCTION F_TRANS(P REFCUR_T) RETURN OUTRECSET
    PIPELINED IS
    OUT_REC OUTREC_TYP;
    IN_REC  P%ROWTYPE;
  BEGIN
    LOOP
      FETCH P
        INTO IN_REC;
      EXIT WHEN P%NOTFOUND;
      -- first row
      OUT_REC.VAR_EMPNO := IN_REC.EMPNO;
      OUT_REC.VAR_ENAME := IN_REC.ENAME;
      OUT_REC.VAR_MGR   := IN_REC.MGR;
      PIPE ROW(OUT_REC);
      -- second row
      OUT_REC.VAR_NUM   := IN_REC.DEPTNO;
      OUT_REC.VAR_CHAR1 := IN_REC.DEPTNO;
      OUT_REC.VAR_CHAR2 := IN_REC.JOB;
      PIPE ROW(OUT_REC);
    END LOOP;
    CLOSE P;
    RETURN;
  END;
END REFCUR_PKG;