Oracle存储过程备份

CREATE OR REPLACE PROCEDURE YBTRAIN."PKG_TBL_STUDENT_ARCHIVES" (STARTDATE_ITEM IN DATE,
                                                     ENDDATE_ITEM   IN DATE) IS
  --create or replace procedure PKG_TBL_STUDENT_ARCHIVES(I_DATE in DATE DEFAULT SYSDATE) IS
  STARTDATE DATE;
  ENDDATE   DATE;
BEGIN

  STARTDATE := STARTDATE_ITEM;
  ENDDATE   := ENDDATE_ITEM;
  DELETE FROM TBL_STUDENT_ARCHIVES
   WHERE TRUNC(TRAINDATE, 'mm') >= TRUNC(STARTDATE, 'mm') AND TRUNC(TRAINDATE, 'mm') <= TRUNC(ENDDATE, 'mm');
  --COMMIT;
  INSERT INTO TBL_STUDENT_ARCHIVES
    (ID,
     OPERATORID,
     EMPNO,
     NEWPERSONCODE,
     NAME,
     ORGID,
     ORGNAME,
     ORGSEQ,
     DEGREECODE,
     TRAINTYPEID,
     TRAINTYPECODE,
     TRAINTYPENAME,
     CLASSID,
     CLASSNAME,
     TRANSMITID,
     TRAINDATE,
     IFATTENDANCES)
    SELECT SEQ_STUDENT_ARCHIVES.NEXTVAL,
           TSM.OPERATORID,
           TSM.EMPNO,
           TSM.NEW_PERSON_CODE,
           TSM.NAME,
           TSM.ORGID,
           TOP.ORGNAME,
           TOP.ORGSEQ,
           TSM.DEGREECODE,
           TTT.ID,
           TTT.DICT_CODE,
           TTT.DICT_NAME,
           TSL.CLASS_ID,
           TC.CLASS_NAME,
           TSL.TRANSMIT_ID,
           TC.END_DATE,
           (CASE
             WHEN TSL.TRAIN_CONFIRM != 0 THEN
              2
            /* WHEN (SELECT COUNT(*) AS ATTENDANCES
                     FROM TBL_STUDENT_ATTENDANCE TSA
                    WHERE TSA.STUDENT_ID = TSM.OPERATORID
                      AND TSA.CLASS_ID = TC.ID
                      AND (TSA.MORNING_ATTENDANCE != '0' OR
                          TSA.NOON_ATTENDANCE != '0' OR
                          TSA.NIGHT_ATTENDANCE != '0')
                      AND TSA.ENABLED_FLAG = '1') = 0 THEN
              0*/
             ELSE
              1
           END) AS IFATTENDANCES
      FROM TBL_STUDENT_LIST    TSL,
           TBL_STUDENT_MESSAGE TSM,
           TBL_CLASSINFO       TC,
           TBL_ORG_PATH        TOP,
           TBL_TRAIN_TYPE      TTT
     WHERE TSL.CLASS_ID = TC.ID
       AND TSL.STUDENT_ID = TSM.OPERATORID
       AND TSM.ORGID = TOP.ID
       AND TC.CLASS_TYPE_CODE = TTT.DICT_CODE
       AND TSL.ENABLED_FLAG = '1'
       AND TC.ENABLED_FLAG = '1'
       AND TRUNC(TC.END_DATE, 'mm') >= TRUNC(STARTDATE, 'mm')
       AND TRUNC(TC.END_DATE, 'mm') <= TRUNC(ENDDATE, 'mm')
       AND TC.STATE = '4';
  COMMIT;
 -- sp_lecturerinfo(sysdate);
END PKG_TBL_STUDENT_ARCHIVES;