我想把一个触发器修改成存储过程
请大家帮下忙. 机会都一样. 功能都不需要只一个功能需要改动 如果愿意帮忙的. 请加我QQ 411911153
.
DECLARE
I_CALL_ROW NUMBER(2);
I_CALL_MAXROW NUMBER(2);
I_LSH_LAST NUMBER(5);
DATE_CALL_MAXDATE DATE;
DATE_CALL_MAXDATE_NEW DATE;
DATE_SYSDATE DATE;
C_CALL_MAXLSH VARCHAR2(20);
C_CALL_NOWLSH VARCHAR2(20);
C_LSH_BEGIN VARCHAR2(8);
C_LSH_NUMBER VARCHAR2(20);
I_HJCS_NOW NUMBER(2);
--C_CALL_HOUR VARCHAR2(2);
--C_LSH VARCHAR2(20);I_ZDHJCS NUMBER(10);
I_HJCS NUMBER(10);
I_CGBZ NUMBER(10);I_HOUR NUMBER(10);BEGINI_ZDHJCS := :NEW.ZDHJCS;
I_HJCS := :NEW.HJCS;
I_CGBZ := :NEW.CGBZ;DATE_SYSDATE := SYSDATE();IF :NEW.CGBZ <> 999 AND :NEW.CGBZ <> 888 AND :NEW.CGBZ <> 111 AND :NEW.CGBZ <> 112 AND :NEW.CGBZ <> 114 THEN
--同一时间只催8条记录
I_CALL_MAXROW := 8 ; --插入到呼叫结果表中
INSERT INTO YIZHIYYCF.T_YYCF_RESULT (YHBH,YHMC,YHDZ,DFNYF,DYDF,LJDF,YJDF,ZQDF,LXDH,ZDHJCS,HJCS,HJSJ,HJLX,CGBZ,ZJHM,LSH,CLSJ )
VALUES(:NEW.YHBH,:NEW.YHMC,:NEW.YHDZ,:NEW.DFNYF,:NEW.DYDF,:NEW.LJDF,:NEW.YJDF,:NEW.ZQDF,:NEW.LXDH,:NEW.ZDHJCS,:NEW.HJCS,:NEW.HJSJ,:NEW.HJLX,:NEW.CGBZ,:NEW.ZJHM,:OLD.LSH,:NEW.CLSJ );
IF (( I_ZDHJCS > I_HJCS ) AND ( I_CGBZ <> 0 AND I_CGBZ <> 5 AND I_CGBZ <> 4640 AND I_CGBZ <> 4102 AND I_CGBZ <> 6 )) THEN
--得到最大的流水号和最大呼叫时间
SELECT MAX(HJSJ),MAX(LSH) INTO DATE_CALL_MAXDATE,C_CALL_MAXLSH FROM YIZHIYYCF.T_YYCF_MID_COPY; IF DATE_CALL_MAXDATE < DATE_SYSDATE THEN
DATE_CALL_MAXDATE := DATE_SYSDATE + 0.042;
END IF;
C_LSH_BEGIN := RTRIM(LTRIM(SUBSTR(C_CALL_MAXLSH,1,8))); C_LSH_NUMBER := RTRIM(LTRIM(TO_CHAR(TO_NUMBER(SUBSTR(C_CALL_MAXLSH,9,18)+1),'0000000000')));
C_CALL_NOWLSH := C_LSH_BEGIN||C_LSH_NUMBER;
--得到最大呼叫时间的记录数
SELECT COUNT(*) INTO I_CALL_ROW FROM T_YYCF_MID_COPY WHERE HJSJ = DATE_CALL_MAXDATE; --判断记录数是否大于呼叫最大记录
IF I_CALL_ROW < I_CALL_MAXROW THEN DATE_CALL_MAXDATE_NEW := DATE_CALL_MAXDATE;
ELSE DATE_CALL_MAXDATE_NEW := DATE_CALL_MAXDATE + 0.00006;
--C_CALL_HOUR := RTRIM(LTRIM(TO_CHAR(DATE_CALL_MAXDATE_NEW,'HH24')));
I_HOUR := TO_NUMBER(TO_CHAR(DATE_CALL_MAXDATE_NEW,'HH24')) ; IF I_HOUR < 9 THEN DATE_CALL_MAXDATE_NEW := TO_DATE((TO_CHAR(DATE_CALL_MAXDATE_NEW,'YYYY-MM-DD')||' 09:00:00'),'YYYY-MM-DD HH24:MI:SS') ;
END IF; IF I_HOUR >= 12 AND I_HOUR < 15 THEN DATE_CALL_MAXDATE_NEW := TO_DATE((TO_CHAR(DATE_CALL_MAXDATE_NEW,'YYYY-MM-DD')||' 15:00:00'),'YYYY-MM-DD HH24:MI:SS') ;
END IF; IF I_HOUR >= 21 THEN DATE_CALL_MAXDATE_NEW := DATE_CALL_MAXDATE_NEW + 1;
DATE_CALL_MAXDATE_NEW := TO_DATE((TO_CHAR(DATE_CALL_MAXDATE_NEW,'YYYY-MM-DD')||' 09:00:00'),'YYYY-MM-DD HH24:MI:SS') ;
END IF;
END IF; I_HJCS_NOW := :NEW.HJCS + 1 ;
:NEW.HJCS := I_HJCS_NOW;
:NEW.CGBZ := 999;
SELECT SEQ_LSH_LAST.NEXTVAL INTO I_LSH_LAST FROM DUAL ;
:NEW.LSH := RTRIM(LTRIM(TO_CHAR(SYSDATE,'YYMMDDHH24MISSSSS')))||RTRIM(LTRIM(TO_CHAR(I_LSH_LAST,'00000')));
--:NEW.LSH := C_CALL_NOWLSH;
:NEW.HJSJ := DATE_CALL_MAXDATE_NEW;
UPDATE YIZHIYYCF.T_YYCF_MID_COPY SET HJCS = I_HJCS_NOW,CGBZ = 999,LSH = :NEW.LSH,HJSJ = DATE_CALL_MAXDATE_NEW
WHERE YHBH = :NEW.YHBH AND DFNYF = :NEW.DFNYF ;
END IF;END IF;
END;
请大家帮下忙. 机会都一样. 功能都不需要只一个功能需要改动 如果愿意帮忙的. 请加我QQ 411911153
.
DECLARE
I_CALL_ROW NUMBER(2);
I_CALL_MAXROW NUMBER(2);
I_LSH_LAST NUMBER(5);
DATE_CALL_MAXDATE DATE;
DATE_CALL_MAXDATE_NEW DATE;
DATE_SYSDATE DATE;
C_CALL_MAXLSH VARCHAR2(20);
C_CALL_NOWLSH VARCHAR2(20);
C_LSH_BEGIN VARCHAR2(8);
C_LSH_NUMBER VARCHAR2(20);
I_HJCS_NOW NUMBER(2);
--C_CALL_HOUR VARCHAR2(2);
--C_LSH VARCHAR2(20);I_ZDHJCS NUMBER(10);
I_HJCS NUMBER(10);
I_CGBZ NUMBER(10);I_HOUR NUMBER(10);BEGINI_ZDHJCS := :NEW.ZDHJCS;
I_HJCS := :NEW.HJCS;
I_CGBZ := :NEW.CGBZ;DATE_SYSDATE := SYSDATE();IF :NEW.CGBZ <> 999 AND :NEW.CGBZ <> 888 AND :NEW.CGBZ <> 111 AND :NEW.CGBZ <> 112 AND :NEW.CGBZ <> 114 THEN
--同一时间只催8条记录
I_CALL_MAXROW := 8 ; --插入到呼叫结果表中
INSERT INTO YIZHIYYCF.T_YYCF_RESULT (YHBH,YHMC,YHDZ,DFNYF,DYDF,LJDF,YJDF,ZQDF,LXDH,ZDHJCS,HJCS,HJSJ,HJLX,CGBZ,ZJHM,LSH,CLSJ )
VALUES(:NEW.YHBH,:NEW.YHMC,:NEW.YHDZ,:NEW.DFNYF,:NEW.DYDF,:NEW.LJDF,:NEW.YJDF,:NEW.ZQDF,:NEW.LXDH,:NEW.ZDHJCS,:NEW.HJCS,:NEW.HJSJ,:NEW.HJLX,:NEW.CGBZ,:NEW.ZJHM,:OLD.LSH,:NEW.CLSJ );
IF (( I_ZDHJCS > I_HJCS ) AND ( I_CGBZ <> 0 AND I_CGBZ <> 5 AND I_CGBZ <> 4640 AND I_CGBZ <> 4102 AND I_CGBZ <> 6 )) THEN
--得到最大的流水号和最大呼叫时间
SELECT MAX(HJSJ),MAX(LSH) INTO DATE_CALL_MAXDATE,C_CALL_MAXLSH FROM YIZHIYYCF.T_YYCF_MID_COPY; IF DATE_CALL_MAXDATE < DATE_SYSDATE THEN
DATE_CALL_MAXDATE := DATE_SYSDATE + 0.042;
END IF;
C_LSH_BEGIN := RTRIM(LTRIM(SUBSTR(C_CALL_MAXLSH,1,8))); C_LSH_NUMBER := RTRIM(LTRIM(TO_CHAR(TO_NUMBER(SUBSTR(C_CALL_MAXLSH,9,18)+1),'0000000000')));
C_CALL_NOWLSH := C_LSH_BEGIN||C_LSH_NUMBER;
--得到最大呼叫时间的记录数
SELECT COUNT(*) INTO I_CALL_ROW FROM T_YYCF_MID_COPY WHERE HJSJ = DATE_CALL_MAXDATE; --判断记录数是否大于呼叫最大记录
IF I_CALL_ROW < I_CALL_MAXROW THEN DATE_CALL_MAXDATE_NEW := DATE_CALL_MAXDATE;
ELSE DATE_CALL_MAXDATE_NEW := DATE_CALL_MAXDATE + 0.00006;
--C_CALL_HOUR := RTRIM(LTRIM(TO_CHAR(DATE_CALL_MAXDATE_NEW,'HH24')));
I_HOUR := TO_NUMBER(TO_CHAR(DATE_CALL_MAXDATE_NEW,'HH24')) ; IF I_HOUR < 9 THEN DATE_CALL_MAXDATE_NEW := TO_DATE((TO_CHAR(DATE_CALL_MAXDATE_NEW,'YYYY-MM-DD')||' 09:00:00'),'YYYY-MM-DD HH24:MI:SS') ;
END IF; IF I_HOUR >= 12 AND I_HOUR < 15 THEN DATE_CALL_MAXDATE_NEW := TO_DATE((TO_CHAR(DATE_CALL_MAXDATE_NEW,'YYYY-MM-DD')||' 15:00:00'),'YYYY-MM-DD HH24:MI:SS') ;
END IF; IF I_HOUR >= 21 THEN DATE_CALL_MAXDATE_NEW := DATE_CALL_MAXDATE_NEW + 1;
DATE_CALL_MAXDATE_NEW := TO_DATE((TO_CHAR(DATE_CALL_MAXDATE_NEW,'YYYY-MM-DD')||' 09:00:00'),'YYYY-MM-DD HH24:MI:SS') ;
END IF;
END IF; I_HJCS_NOW := :NEW.HJCS + 1 ;
:NEW.HJCS := I_HJCS_NOW;
:NEW.CGBZ := 999;
SELECT SEQ_LSH_LAST.NEXTVAL INTO I_LSH_LAST FROM DUAL ;
:NEW.LSH := RTRIM(LTRIM(TO_CHAR(SYSDATE,'YYMMDDHH24MISSSSS')))||RTRIM(LTRIM(TO_CHAR(I_LSH_LAST,'00000')));
--:NEW.LSH := C_CALL_NOWLSH;
:NEW.HJSJ := DATE_CALL_MAXDATE_NEW;
UPDATE YIZHIYYCF.T_YYCF_MID_COPY SET HJCS = I_HJCS_NOW,CGBZ = 999,LSH = :NEW.LSH,HJSJ = DATE_CALL_MAXDATE_NEW
WHERE YHBH = :NEW.YHBH AND DFNYF = :NEW.DFNYF ;
END IF;END IF;
END;
我现在在准备把他改成存储过程..
但是 里面用 NEW.XXX 这个写法我不知道是什么意思 请大家帮忙