Oracle中批量保存一個月的資料的存儲過程

create or replace procedure IE_SMTMANAGESAVE
(
  P_LINE VARCHAR2,
  P_CLASS VARCHAR2,
  P_MONITOR VARCHAR2,
  P_TECHOPERATOR VARCHAR2,
  P_DATE  VARCHAR2,
  P_FLAG VARCHAR2,
  P_USER VARCHAR2,
  P_NEWORUP VARCHAR2,
  P_OLDMONITOR VARCHAR2,
  P_OLDTECHOPERATOR VARCHAR2,
  P_STARTDATE  VARCHAR2,
  P_ENDDATE VARCHAR2,
  P_OUT OUT NUMBER
)
is
 V_MONTSTART VARCHAR2(25);
 V_MONTEND VARCHAR2(25);
 V_MONTCOUNT NUMBER(10);
 V_INITCOUNT NUMBER(10);
 V_UPCOUNT NUMBER(10);
 V_OK EXCEPTION;
 V_ERROR EXCEPTION;
begin
  V_MONTCOUNT:=0;
  V_INITCOUNT:=0;
  V_UPCOUNT:=0;
  IF(P_NEWORUP = '1') THEN
  IF(P_FLAG IS NULL) THEN
   SELECT COUNT(FDATE) INTO V_UPCOUNT FROM TBLSMTMANAGER WHERE FDATE >=TO_CHAR(TRUNC(TO_DATE(P_DATE,'MM'),'MONTH'),'YYYY/MM/DD') AND FDATE<=TO_CHAR(LAST_DAY(TRUNC(TO_DATE(P_DATE,'MM'),'MONTH')),'YYYY/MM/DD') AND FREALLINE = P_LINE AND FCLASS = P_CLASS;
   IF(V_UPCOUNT >0) THEN
     RAISE V_ERROR;
   END IF;
  SELECT TRUNC(TO_DATE(P_DATE,'MM'),'MONTH') AS FMONTHSTARTDAY ,LAST_DAY(TRUNC(TO_DATE(P_DATE,'MM'),'MONTH')) AS FMONTHENDDAY,LAST_DAY(TRUNC(TO_DATE(P_DATE,'MM'),'MONTH'))-TRUNC(TO_DATE(P_DATE,'MM'),'MONTH') AS FDAYCOUNT INTO V_MONTSTART,V_MONTEND,V_MONTCOUNT FROM DUAL ;
  WHILE (V_INITCOUNT <= V_MONTCOUNT) LOOP
    INSERT INTO TBLSMTMANAGER (FREALLINE,FCLASS,FMONITOR,FTECHOPERATOR,FDATE,FBUILDER,FINDATE) VALUES(P_LINE,P_CLASS,P_MONITOR,P_TECHOPERATOR,TO_CHAR((TRUNC(TO_DATE(P_DATE,'MM'),'MONTH')+V_INITCOUNT),'YYYY/MM/DD'),P_USER,TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'));
    COMMIT;
    V_INITCOUNT:=V_INITCOUNT+1;
  END LOOP;
  RAISE V_OK;
  ELSE
    UPDATE TBLSMTMANAGER  SET FREALLINE =P_LINE,FCLASS = P_CLASS,FMONITOR = P_MONITOR,FTECHOPERATOR =P_TECHOPERATOR  WHERE ROWID = P_FLAG;
    COMMIT;
    RAISE V_OK;
  END IF;
  ELSE
    --SELECT COUNT(FDATE) INTO V_UPCOUNT FROM TBLSMTMANAGER WHERE FREALLINE = P_LINE AND FCLASS = P_CLASS AND FDATE >=P_STARTDATE AND FDATE <=P_ENDDATE AND FMONITOR =P_OLDMONITOR AND FTECHOPERATOR = P_OLDTECHOPERATOR;
   -- IF(V_UPCOUNT >0) THEN
     IF(P_MONITOR IS NOT NULL AND P_TECHOPERATOR IS NOT NULL) THEN
     UPDATE TBLSMTMANAGER SET FMONITOR =P_MONITOR,FTECHOPERATOR = P_TECHOPERATOR WHERE FREALLINE = P_LINE AND FCLASS = P_CLASS AND FDATE >=P_STARTDATE AND FDATE <=P_ENDDATE AND FMONITOR =P_OLDMONITOR AND FTECHOPERATOR = P_OLDTECHOPERATOR;
     COMMIT;
     RAISE V_OK;
     END IF;
     IF(P_MONITOR IS NOT NULL AND P_TECHOPERATOR IS NULL) THEN
        UPDATE TBLSMTMANAGER SET FMONITOR =P_MONITOR WHERE FREALLINE = P_LINE AND FCLASS = P_CLASS AND FDATE >=P_STARTDATE AND FDATE <=P_ENDDATE AND FMONITOR =P_OLDMONITOR;
        COMMIT;
        RAISE V_OK;
     END IF;
      IF(P_MONITOR IS  NULL AND P_TECHOPERATOR IS NOT NULL) THEN
        UPDATE TBLSMTMANAGER SET FTECHOPERATOR = P_TECHOPERATOR WHERE FREALLINE = P_LINE AND FCLASS = P_CLASS AND FDATE >=P_STARTDATE AND FDATE <=P_ENDDATE  AND FTECHOPERATOR = P_OLDTECHOPERATOR;
        COMMIT;
        RAISE V_OK;
     END IF;
   --  ELSE
     -- RAISE V_ERROR;
    -- END IF;
  END IF;
  EXCEPTION
  WHEN V_OK THEN
  P_OUT:=1;
  WHEN V_ERROR THEN
  P_OUT:=2;
  WHEN OTHERS THEN
  P_OUT:=0;
end IE_SMTMANAGESAVE;