返回类型为SYS_REFCURSOR的存储过程示例（备忘）含调用代码
1、准备全局临时表：
DROP TABLE "REPORT"."TEMPPOPSETTLEMENTDETAIL";
/
CREATE GLOBAL TEMPORARY TABLE "REPORT"."TEMPPOPSETTLEMENTDETAIL"
( "TEMPPOPSETTLEMENTDETAILID" NUMBER(10,0),
"SETTLEMENTID" NUMBER(10,0),
"STATUSFLAG" NUMBER(5,0),
"ENCOUNTERID" NUMBER(10,0),
"DISPLAYNAME" VARCHAR2(50) DEFAULT '',
"ADMITDATEON" DATE DEFAULT to_date('1900-01-01','yyyy-mm-dd'),
"SEQNOTEXT" VARCHAR(50) DEFAULT '',
"RECEIPTSEQNOTEXT" VARCHAR(50) DEFAULT '',
"TOTALAMOUNT" NUMBER(18,4) DEFAULT 0,
"ACCOUNTAMOUNT" NUMBER(18,4) DEFAULT 0,
"STATUSDESC" VARCHAR(50) DEFAULT '',
"ROWVERSION" DATE DEFAULT SYSDATE
) ON COMMIT DELETE ROWS ;
/2、存储过程内容：
CREATE OR REPLACE PROCEDURE REPORT.PGETOPSETTLEMENTDETAILREPORT
(
V_IN_DAILYSTATFLAG IN NUMBER DEFAULT 0
, V_IN_STARTDATE IN DATE
, V_IN_ENDDATE IN DATE
, V_IN_SETTLEMENTEMPLOYEEIDS IN VARCHAR2
, V_IN_DEPARTMENTIDS IN VARCHAR2
, CV_out_RDReport OUT SYS_REFCURSOR
)
AS
/*
2024-09-19 guoshaoyue 参照REPORT.PGETOPSETTLEMENTTOTALREPORT==>REPORT.PGETOPSETTLEMENTDETAILREPORT
2018-05-21 xumin 创建存储过程 收费员汇总表(REPORT.PGETOPSETTLEMENTTOTALREPORT)
测试脚本（仅返回一行，另测试时需注释COMMIT;语句）：
declare
c SYS_REFCURSOR;
v_row REPORT.TEMPPOPSETTLEMENTDETAIL%rowtype;
begin
REPORT.PGETOPSETTLEMENTDETAILREPORT(0,to_date('2019-02-01','yyyy-MM-dd'),to_date('2020-09-30','yyyy-MM-dd'),'1462,1739,1799','837069,837111,837114,837090',c);
FETCH c INTO v_row;
DBMS_OUTPUT.PUT_LINE('SETTLEMENTID: ' || v_row.SETTLEMENTID || ', STATUSFLAG: ' || v_row.STATUSFLAG || ', DISPLAYNAME: ' || v_row.DISPLAYNAME || ', TOTALAMOUNT: ' || v_row.TOTALAMOUNT || ', STATUSDESC: ' || v_row.STATUSDESC);
CLOSE c;
end;
*/
V_ListID number(10,0);
V_EndDATE DATE;
BEGIN
REPORT.GETPROCLISTID('report.PGETOPSETTLEMENTDETAILREPORT',V_ListID);
V_EndDATE:=V_IN_ENDDATE+1;
DELETE FROM REPORT.TEMPPOPSETTLEMENTDETAIL WHERE ROWVERSION < SYSDATE - 1.0/24/60*5;
IF V_IN_DAILYSTATFLAG = 0 THEN
BEGIN
INSERT INTO report.TEMPPOPSETTLEMENTDETAIL(TEMPPOPSETTLEMENTDETAILID, SETTLEMENTID, STATUSFLAG, ENCOUNTERID, DISPLAYNAME, ADMITDATEON, SEQNOTEXT, RECEIPTSEQNOTEXT, TOTALAMOUNT, ACCOUNTAMOUNT, STATUSDESC)
SELECT V_ListID, a.SETTLEMENTID, a.STATUSFLAG, a.ENCOUNTERID, b.DISPLAYNAME, b.ADMITDATEON, b.SEQNOTEXT, a.RECEIPTNOPREFIX || a.RECEIPTSEQNO, a.AMOUNT, a.amount-a.prechargeamount-a.selfpayamount, ctv.DISPLAYNAME AS STATUSDESC
FROM fiab.opsettlement a, prpa.encounter b, concept.codedtermvalue ctv
WHERE a.encounterid=b.encounterid AND a.isdeleted=0 AND b.isdeleted=0 AND b.encounterkindcodeid=0 AND a.statusflag=ctv.codeid
AND a.SETTLEMENTEMPLOYEEID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_SETTLEMENTEMPLOYEEIDS,',')))
AND a.SETTLEMENTORGANIZATIONID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_DEPARTMENTIDS,',') ))
AND a.DAILYSTATID < 0
AND a.SUPPLYSTATID<0
AND a.STATUSFLAG < 3
AND ctv.CODEDTERMID=11667
UNION ALL
-- 退费,作废,重打
SELECT V_ListID, a.SETTLEMENTID, a.STATUSFLAG, a.ENCOUNTERID, b.DISPLAYNAME, b.ADMITDATEON, b.SEQNOTEXT, a.RECEIPTNOPREFIX || a.RECEIPTSEQNO, a.AMOUNT, a.amount-a.prechargeamount-a.selfpayamount, ctv.DISPLAYNAME AS STATUSDESC
FROM fiab.opsettlement a, prpa.encounter b, concept.codedtermvalue ctv
WHERE a.encounterid=b.encounterid AND a.isdeleted=0 AND b.isdeleted=0 AND b.encounterkindcodeid=0 AND a.statusflag=ctv.codeid
AND a.DROPEMPLOYEEID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_SETTLEMENTEMPLOYEEIDS,',')))
AND a.SETTLEMENTORGANIZATIONID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_DEPARTMENTIDS,',') ))
AND (a.DROPSTATID < 0 OR a.DROPSTATID IS NULL )
AND a.SUPPLYSTATID<0
AND a.STATUSFLAG > 0
AND a.STATUSFLAG < 3
AND ctv.CODEDTERMID=11667;
END;
ELSIF V_IN_DAILYSTATFLAG = 1 THEN
BEGIN
INSERT INTO report.TEMPPOPSETTLEMENTDETAIL(TEMPPOPSETTLEMENTDETAILID, SETTLEMENTID, STATUSFLAG, ENCOUNTERID, DISPLAYNAME, ADMITDATEON, SEQNOTEXT, RECEIPTSEQNOTEXT, TOTALAMOUNT, ACCOUNTAMOUNT, STATUSDESC) --统计除补打外的所有发票
SELECT V_ListID, a.SETTLEMENTID, a.STATUSFLAG, a.ENCOUNTERID, b.DISPLAYNAME, b.ADMITDATEON, b.SEQNOTEXT, a.RECEIPTNOPREFIX || a.RECEIPTSEQNO, a.AMOUNT, a.amount-a.prechargeamount-a.selfpayamount, ctv.DISPLAYNAME AS STATUSDESC
FROM fiab.opsettlement a, prpa.encounter b, DATA.DAILYSTATLIST c, DATA.Dailystat d, concept.codedtermvalue ctv
WHERE a.encounterid=b.encounterid AND a.isdeleted=0 AND b.isdeleted=0 AND b.encounterkindcodeid=0 AND a.statusflag=ctv.codeid
AND a.SETTLEMENTID = c.DAILYSTATRESOURCEID
AND c.DAILYSTATID = d.DAILYSTATID
AND c.SETTLEMENTSTATUSCODEID =0
AND c.SUPPLYCODEID< 1
AND d.DAILYSTATKINDCODEID = 2
AND d.STATDATETIME>=V_IN_STARTDATE
AND d.STATDATETIME<V_EndDATE
AND d.STATORGANIZATIONID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_DEPARTMENTIDS,',') ))
AND d.STATEMPLOYEEID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_SETTLEMENTEMPLOYEEIDS,',')))
AND ctv.CODEDTERMID=11667
UNION ALL
SELECT V_ListID, a.SETTLEMENTID, a.STATUSFLAG, a.ENCOUNTERID, b.DISPLAYNAME, b.ADMITDATEON, b.SEQNOTEXT, a.RECEIPTNOPREFIX || a.RECEIPTSEQNO, a.AMOUNT, a.amount-a.prechargeamount-a.selfpayamount, ctv.DISPLAYNAME AS STATUSDESC
FROM fiab.opsettlement a, prpa.encounter b, DATA.DAILYSTATLIST c, DATA.Dailystat d, concept.codedtermvalue ctv
WHERE a.encounterid=b.encounterid AND a.isdeleted=0 AND b.isdeleted=0 AND b.encounterkindcodeid=0 AND a.statusflag=ctv.codeid
AND a.SETTLEMENTID = c.DAILYSTATRESOURCEID
AND c.DAILYSTATID = d.DAILYSTATID
AND c.SETTLEMENTSTATUSCODEID IN (1,2)
AND c.SUPPLYCODEID< 1
AND d.DAILYSTATKINDCODEID = 2
AND d.STATDATETIME>=V_IN_STARTDATE
AND d.STATDATETIME<V_EndDATE
AND d.STATORGANIZATIONID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_DEPARTMENTIDS,',') ))
AND d.STATEMPLOYEEID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_SETTLEMENTEMPLOYEEIDS,',')))
AND ctv.CODEDTERMID=11667;
END;
ELSIF V_IN_DAILYSTATFLAG = 2 THEN
BEGIN
INSERT INTO report.TEMPPOPSETTLEMENTDETAIL(TEMPPOPSETTLEMENTDETAILID, SETTLEMENTID, STATUSFLAG, ENCOUNTERID, DISPLAYNAME, ADMITDATEON, SEQNOTEXT, RECEIPTSEQNOTEXT, TOTALAMOUNT, ACCOUNTAMOUNT, STATUSDESC)
SELECT V_ListID, a.SETTLEMENTID, a.STATUSFLAG, a.ENCOUNTERID, b.DISPLAYNAME, b.ADMITDATEON, b.SEQNOTEXT, a.RECEIPTNOPREFIX || a.RECEIPTSEQNO, a.AMOUNT, a.amount-a.prechargeamount-a.selfpayamount, ctv.DISPLAYNAME AS STATUSDESC
FROM fiab.opsettlement a, prpa.encounter b, concept.codedtermvalue ctv
WHERE a.encounterid=b.encounterid AND a.isdeleted=0 AND b.isdeleted=0 AND b.encounterkindcodeid=0 AND a.statusflag=ctv.codeid
AND a.SETTLEMENTEMPLOYEEID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_SETTLEMENTEMPLOYEEIDS,',')))
AND a.SETTLEMENTORGANIZATIONID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_DEPARTMENTIDS,',') ))
AND a.SETTLEMENTTIME>=V_IN_STARTDATE
AND a.SETTLEMENTTIME <V_EndDATE
AND a.SUPPLYSTATID<0
AND a.STATUSFLAG < 3
AND ctv.CODEDTERMID=11667
UNION ALL
SELECT V_ListID, a.SETTLEMENTID, a.STATUSFLAG, a.ENCOUNTERID, b.DISPLAYNAME, b.ADMITDATEON, b.SEQNOTEXT, a.RECEIPTNOPREFIX || a.RECEIPTSEQNO, a.AMOUNT, a.amount-a.prechargeamount-a.selfpayamount, ctv.DISPLAYNAME AS STATUSDESC
FROM fiab.opsettlement a, prpa.encounter b, concept.codedtermvalue ctv
WHERE a.encounterid=b.encounterid AND a.isdeleted=0 AND b.isdeleted=0 AND b.encounterkindcodeid=0 AND a.statusflag=ctv.codeid
AND a.DROPEMPLOYEEID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_SETTLEMENTEMPLOYEEIDS,',')))
AND a.DROPORGANIZATIONID IN (SELECT cast(column_value AS number(10,0)) AS str FROM TABLE(report.fnIDInString(V_IN_DEPARTMENTIDS,',') ))
AND a.DROPTIME >=V_IN_STARTDATE
AND a.DROPTIME <V_EndDATE
AND a.statusflag > 0
AND a.SUPPLYSTATID<0
AND a.STATUSFLAG < 3
AND ctv.CODEDTERMID=11667;
END;
END IF ;
OPEN CV_out_RDReport FOR
SELECT * FROM REPORT.TEMPPOPSETTLEMENTDETAIL WHERE TEMPPOPSETTLEMENTDETAILID=V_ListID;
COMMIT;
END PGETOPSETTLEMENTDETAILREPORT;
