下面分享一篇关于存储过程的示例:
CREATE OR REPLACE PROCEDURE NRMS_REP.P_GET_GLDD_REPORT (IN_CITY_CODE IN VARCHAR2,BETWEENDATE IN VARCHAR2,sourceId IN VARCHAR2,overdueId IN VARCHAR2,tasktypeId IN VARCHAR2
,a_out out PAK_REPORT.data_cursor,A_OUT_COLS OUT PAK_REPORT.DATA_CURSOR,A_OUT_FIELDS OUT PAK_REPORT.DATA_CURSOR,A_OUT_SHEET_INDEX out integer
)
IS
begin
IF overdueId='CQ' THEN
DELETE FROM REPORT_DDMX_GLDD WHERE PEXAMPLEID not in (select distinct PEXAMPLEID FROM REPORT_DDMX_GLDD WHERE nvl(SUBMITTIME,sysdate)>REQIRE_DATE or nvl(ACCEPTTIME,sysdate)>REQIRE_DATE and TASKNAME<>'业务确认') ;
END IF;
IF overdueId='WCQ' THEN
DELETE FROM REPORT_DDMX_GLDD WHERE PEXAMPLEID in (select distinct PEXAMPLEID FROM REPORT_DDMX_GLDD a WHERE nvl(SUBMITTIME,sysdate)>REQIRE_DATE or nvl(ACCEPTTIME,sysdate)>REQIRE_DATE and TASKNAME<>'业务确认') ;
END IF;
A_OUT_SHEET_INDEX := 0;
OPEN a_out FOR SELECT PEXAMPLEID, PEXAMPLENAME, SNAME, APPLY_TYPE, to_char(REQIRE_DATE,'YYYY-MM-DD') REQIRE_DATE, TASKNAME, TASKOWNER, ACCEPTTIME, SUBMITTIME FROM REPORT_DDMX_GLDD ORDER BY PEXAMPLEID,SID,ACCEPTTIME;
OPEN A_OUT_COLS FOR SELECT LEVEL,A.* FROM (SELECT * FROM REP_COLUMNS WHERE REP_ID='P_GET_GLDD_REPORT' OR REP_ID ='ROOT') A CONNECT BY A.COL_PID= PRIOR A.COL_ID START WITH A.COL_ID='ROOT' ORDER SIBLINGS BY A.SORT_COL;
OPEN A_OUT_FIELDS FOR SELECT * FROM REP_DATAFIELD WHERE REP_ID='P_GET_GLDD_REPORT' ORDER BY FIELD_SEQ;
end P_GET_GLDD_REPORT;
/