Oracle之存储过程

下面分享一篇关于存储过程的示例：

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;
/