PLSQL管道表函数和其他写法比较

本文是学习笔记，没有太多说明，请自行参考

 

create table T_SS_NORMAL
(
  owner          VARCHAR2(30),
  object_name    VARCHAR2(128),
  subobject_name VARCHAR2(30),
  object_id      NUMBER,
  data_object_id NUMBER,
  object_type    VARCHAR2(19),
  created        DATE,
  last_ddl_time  DATE,
  timestamp      VARCHAR2(19),
  status         VARCHAR2(7),
  temporary      VARCHAR2(1),
  generated      VARCHAR2(1),
  secondary      VARCHAR2(1)
);
/

create table T_TARGET
(
  owner       VARCHAR2(30),
  object_name VARCHAR2(128),
  comm        VARCHAR2(10)
);
/

INSERT INTO T_SS_NORMAL
  SELECT owner,
         object_name,
         subobject_name,
         object_id,
         data_object_id,
         object_type,
         created,
         last_ddl_time,
         timestamp,
         status,
         temporary,
         generated,
         secondary
    FROM DBA_OBJECTS;
insert into T_SS_NORMAL select * from T_SS_NORMAL;
insert into T_SS_NORMAL select * from T_SS_NORMAL;
commit;

create type obj_target as object(
owner varchar2(30),
object_name varchar2(128),
comm varchar2(10)
);
/

create or replace type typ_array_target as table of obj_target;
/

 

create or replace package pkg_test1 is
  CURSOR MYCURSOR RETURN T_SS_NORMAL%ROWTYPE;
  procedure load_target;
end pkg_test1;
/

CREATE OR REPLACE PACKAGE BODY PKG_TEST1 IS
  CURSOR MYCURSOR RETURN T_SS_NORMAL%ROWTYPE IS SELECT * FROM T_SS_NORMAL;
  PROCEDURE LOAD_TARGET IS
    TYPE TYP_SOURCE_DATA IS TABLE OF T_SS_NORMAL%ROWTYPE INDEX BY PLS_INTEGER;
    AA_SOURCE_DATA TYP_SOURCE_DATA;
    BEGIN
      OPEN MYCURSOR;
      LOOP
        FETCH MYCURSOR BULK COLLECT INTO AA_SOURCE_DATA;
        EXIT WHEN AA_SOURCE_DATA.COUNT=0;

        FOR I IN 1..AA_SOURCE_DATA.COUNT LOOP
          INSERT INTO T_TARGET VALUES (AA_SOURCE_DATA(I).OWNER,AA_SOURCE_DATA(I).OBJECT_NAME,'XXX');
        END LOOP;
      END LOOP;
      COMMIT;
      close MYCURSOR;
    END;

END PKG_TEST1;
/

create or replace package pkg_test2 is
  CURSOR MYCURSOR RETURN T_SS_NORMAL%ROWTYPE;
  procedure load_target;
end pkg_test2;
/

CREATE OR REPLACE PACKAGE BODY PKG_TEST2 IS
  CURSOR MYCURSOR RETURN T_SS_NORMAL%ROWTYPE IS SELECT * FROM T_SS_NORMAL;
  PROCEDURE LOAD_TARGET IS
    TYPE TYP_SOURCE_DATA IS TABLE OF T_SS_NORMAL%ROWTYPE INDEX BY PLS_INTEGER;
    AA_SOURCE_DATA TYP_SOURCE_DATA;
    TMPTABLE TYP_SOURCE_DATA
    IDX NUMBER :=1;
    BEGIN
      OPEN MYCURSOR;
      LOOP
        FETCH MYCURSOR BULK COLLECT INTO AA_SOURCE_DATA;
        EXIT WHEN AA_SOURCE_DATA.COUNT=0;
        
        FOR I IN 1..AA_SOURCE_DATA.COUNT LOOP
          IF MOD(AA_SOURCE_DATA(I).OBJECT_ID,2)=0 THEN
            TMPTABLE(IDX) := AA_SOURCE_DATA(I);
            IDX := IDX+1;
          END IF;
        END LOOP;
          
        FORALL I IN 1..TMPTABLE.COUNT
          INSERT INTO T_TARGET VALUES (TMPTABLE(I).OWNER,TMPTABLE(I).OBJECT_NAME,'XXX');
      END LOOP;
      COMMIT;
      close MYCURSOR;
    END;
END PKG_TEST2;
/

create or replace package pkg_test3 is
  CURSOR MYCURSOR RETURN T_SS_NORMAL%ROWTYPE;
  procedure load_target;
end pkg_test3;
/

CREATE OR REPLACE PACKAGE BODY PKG_TEST3 IS
  CURSOR MYCURSOR RETURN T_SS_NORMAL%ROWTYPE IS SELECT * FROM T_SS_NORMAL;
  PROCEDURE LOAD_TARGET IS
    TMPROW T_SS_NORMAL%ROWTYPE;
    BEGIN
      OPEN MYCURSOR;
      LOOP
        FETCH MYCURSOR INTO TMPROW;
        EXIT WHEN MYCURSOR%NOTFOUND;
        IF MOD(TMPROW.OBJECT_ID,2) =0 THEN
          INSERT INTO T_TARGET VALUES (TMPROW.OWNER,TMPROW.OBJECT_NAME,'XXX');
        END IF;
      END LOOP;
      COMMIT;
      close MYCURSOR;
    END;
END PKG_TEST3;
/

create or replace package pkg_test4 is
  function pipe_target(p_source_data in sys_refcursor) return typ_array_target
    pipelined;
  procedure load_target;
end pkg_test4;
/

CREATE OR REPLACE PACKAGE BODY PKG_TEST4 IS
  FUNCTION PIPE_TARGET(p_source_data in sys_refcursor)
    return typ_array_target
    pipelined IS
    R_TARGET_DATA OBJ_TARGET := OBJ_TARGET(NULL,NULL,NULL);

    TYPE TYP_SOURCE_DATA IS TABLE OF T_SS_NORMAL%ROWTYPE INDEX BY PLS_INTEGER;
    AA_SOURCE_DATA TYP_SOURCE_DATA;

    BEGIN
      LOOP
        FETCH P_SOURCE_DATA BULK COLLECT INTO AA_SOURCE_DATA;
        EXIT WHEN AA_SOURCE_DATA.COUNT=0;

        FOR I IN 1..AA_SOURCE_DATA.COUNT LOOP
          IF MOD(AA_SOURCE_DATA(I).OBJECT_ID,2) =0 then
            R_TARGET_DATA.OWNER := AA_SOURCE_DATA(I).OWNER;
            R_TARGET_DATA.OBJECT_NAME := AA_SOURCE_DATA(I).OBJECT_NAME;
            R_TARGET_DATA.COMM := 'XXX';
          PIPE ROW(R_TARGET_DATA);
          END IF;
        END LOOP;
      END LOOP;

      CLOSE P_SOURCE_DATA;

      RETURN;
    END;

  PROCEDURE LOAD_TARGET IS
    BEGIN
      delete from T_TARGET;
      INSERT INTO T_TARGET
        SELECT OWNER,OBJECT_NAME,COMM
          FROM TABLE(PIPE_TARGET(CURSOR(SELECT * FROM T_SS_NORMAL)));
      COMMIT;
    END;

END PKG_TEST4;
/

begin
  pkg_test1.load_target();
end;
/

begin
  pkg_test2.load_target();
end;
/

begin
  pkg_test3.load_target();
end;
/

begin
  pkg_test4.load_target();
end;
/