oracle管道函数基础框架

1、创建返回类型的object

CREATE OR REPLACE TYPE "OWB_aDWH"."FIND_aSUSPENSION_INFO" as object (
    ID_BKE_aACCOUNTING        VARCHAR2(100),--科目号
    FND_SHT_aDESCRIPTION     VARCHAR2(100),  --科目描述L
    FND_LNG_aDESCRIPTION     VARCHAR2(100),  --科目描述S
    ID_INS_aCATEGORY               VARCHAR2(20),      --证券种类
    ID_INS_aCODE                        VARCHAR2(100),      --资产代码
    INS_LNG_aDESCRIPTION      VARCHAR2(100),     --资产描述L
    INS_SHT_aDESCRIPTION      VARCHAR2(100),     --资产描述S
    ID_BKE_aACCOUNT                VARCHAR2(100),      --三级科目
    FND_CHART_aACCOUNT       VARCHAR2(50),      --CFCAD
    GLA_END_aVALIDITY              DATE    --riqi
);

CREATE OR REPLACE TYPE "OWB_aDWH"."FIND_aSUSPENSION_INFO_TABLE"
AS TABLE OF "FIND_aSUSPENSION_INFO";
 

 

2、创建函数

CREATE OR REPLACE FUNCTION "OWB_aDWH"."FNC_aFIND_SUSPENSION_INFO"( I_VALUATIONDATE    IN DATE)

return FIND_aSUSPENSION_INFO_TABLE pipelined
is
PRAGMA AUTONOMOUS_TRANSACTION;
TYPE ref0 is REF CURSOR;
myCursor ref0;
out_rec FIND_aSUSPENSION_INFO := FIND_aSUSPENSION_INFO (NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL);

SUS_INFO_TMP varchar2(100) :='';
BEGIN
  DBMS_OUTPUT.PUT_LINE('Sample output');
  open myCursor for select 1,2,3,4,5,6,7,8,9,10 from dual;
LOOP FETCH myCursor into
        out_rec.ID_BKE_aACCOUNTING,
        out_rec.FND_SHT_aDESCRIPTION,
        out_rec.FND_LNG_aDESCRIPTION,
        out_rec.ID_INS_aCATEGORY,
        out_rec.ID_INS_aCODE,
        out_rec.INS_LNG_aDESCRIPTION,
        out_rec.INS_SHT_aDESCRIPTION,
        out_rec.ID_BKE_aACCOUNT,
        out_rec.FND_CHART_aACCOUNT,
        out_rec.GLA_END_aVALIDITY;

EXIT WHEN myCursor%NOTFOUND;
commit;
PIPE ROW(out_rec);
END LOOP;
CLOSE myCursor;
  
RETURN;
END;