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;
Oracle中批量保存一個月的資料的存儲過程
最新推荐文章于 2021-04-06 15:49:27 发布