5,889
社区成员
发帖
与我相关
我的任务
分享ORACLE触发器转换成DB2触发器
现在有一个ORACLE的触发器脚本，想要转成DB2的触发器脚本，表结构都已经做好，对ORACLE和DB2的语法都不熟悉，求大神帮忙转换一下，下面是ORACLE触发器的脚本。
CREATE OR REPLACE TRIGGER T_TRANSACTION_FOR_SPLITED BEFORE INSERT ON M_TRANSACTION_SPLITED REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
DECLARE
v_path_len NUMBER;
v_path_gene VARCHAR2(2000);
v_char_len INT;
v_rel_times INT;
v_i INT;
v_id INT;
v_ret INT;
v_sql VARCHAR2(2000);
v_clas VARCHAR2(2);
v_temp VARCHAR2(20);
v_path_entry VARCHAR2(200);
v_path_exit VARCHAR2(200);
v_serial_entry NUMBER(16);
v_serial_exit NUMBER(16);
v_serial NUMBER(16);
v_count LONG;
v_bl NUMBER(8,2);
v_bl_curr NUMBER(8,2);
v_len NUMBER;
v_dhm DATE;
v_part NUMBER(3);
v_netno CHAR(2);
v_plazno CHAR(2);
BEGIN
:NEW.PART := MOD(TRUNC(:NEW.DHM ,'J') - TO_DATE('20000101','YYYYMMDD'),93) ;
IF :NEW.PART = 0 THEN
:NEW.PART := 93;
END IF;
IF :NEW.BILL_CODE = '01' THEN
BEGIN
v_netno := :NEW.net_no;
v_plazno := :NEW.plaz_no;
--IF v_netno='05' AND v_plazno='38' THEN
-- v_netno:='04';
--END IF;
SELECT road_line,road_len INTO v_path_gene, v_path_len FROM M_SPLIT_CONST_USED
WHERE road_name = :NEW.entr_net_no || :NEW.entr_plaz_no || v_netno || v_plazno AND
VER_NO = :NEW.VER_NO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
--没找到路径
BEGIN
SELECT road_line,road_len INTO v_path_gene, v_path_len FROM M_SPLIT_CONST_USED
WHERE road_name = '9995' || :NEW.net_no || :NEW.plaz_no AND
VER_NO = :NEW.VER_NO;
INSERT INTO O_TRANS_ERROR_PATH ( SERIAL, PLAZ_ENTRY, PLAZ_EXIT)
VALUES( :NEW.serial, :NEW.entr_net_no || :NEW.entr_plaz_no, :NEW.net_no || :NEW.plaz_no );
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001,'(将路口站替换为9995也未找到)在拆分常量表中未发现记录'||:NEW.entr_net_no || :NEW.entr_plaz_no || :NEW.net_no || :NEW.plaz_no||'，请检查帐务拆分系统(ABS)是否配置 '||:NEW.entr_net_no || :NEW.entr_plaz_no ||'或'|| :NEW.net_no || :NEW.plaz_no );
END;
END;
v_clas := TO_CHAR(:NEW.key_in_type );
v_char_len := LENGTH( v_path_gene ) ;
v_rel_times := v_char_len / 8 ;
SELECT DHM,TO_NUMBER( TO_CHAR( DHM,'MM') ) INTO v_dhm,v_part FROM O_BANK_REBACK WHERE SERIAL = :NEW.SERIAL_BANK_IN;
-- INSERT OR UPDATE MONEY_REAL
v_sql := 'UPDATE M_MONEY_TOTAL_BANKIN SET MONEY_REAL' ||'= MONEY_REAL + ' || :NEW.TOD_FARE ||
' WHERE PART='||v_part||' AND NET_NO = '''||:NEW.NET_NO||''' AND PLAZ_NO='''||:NEW.PLAZ_NO||''' AND BANK_IN_SERIAL='||:NEW.SERIAL_BANK_IN||
' AND VER_NO='||:NEW.VER_NO||' AND DHM='||'TRUNC(TO_DATE('''||v_dhm||'''),''J'') ';
v_id :=DBMS_SQL.OPEN_CURSOR;
dbms_sql.PARSE(v_id, v_sql ,dbms_sql.v7);
v_ret := dbms_sql.EXECUTE(v_id);
IF v_ret = 0 THEN
v_sql := 'INSERT INTO M_MONEY_TOTAL_BANKIN ( BANK_IN_SERIAL,VER_NO,NET_NO,PLAZ_NO,MONEY_REAL, DHM)' ||
' VALUES('||:NEW.SERIAL_BANK_IN||','||:NEW.VER_NO||','''||:NEW.NET_NO||''','''||:NEW.PLAZ_NO||''', '||:NEW.TOD_FARE ||
', TRUNC(TO_DATE('''||v_dhm||'''),''J'') ) ';
dbms_sql.PARSE(v_id, v_sql ,dbms_sql.v7);
v_ret := dbms_sql.EXECUTE(v_id);
END IF;
v_bl :=0;
v_len :=0;
FOR v_i IN 1..v_rel_times LOOP
-- INSERT OR UPDATE M_MONEY_DETAIL ;
IF v_i <> v_rel_times THEN
IF v_path_len = 0 THEN
v_bl_curr := TRUNC(1/ v_rel_times,2) ;
v_bl := v_bl + v_bl_curr;
ELSE
v_len := v_len + TO_NUMBER(SUBSTR(v_path_gene,8*(v_i - 1) +5,4)) ;
IF v_len = v_path_len THEN --如果还未到最后一站路长已经等于累加长时表明后面的站拆分因子都为0；
v_bl_curr := 1 - v_bl;
ELSE
v_bl_curr := TRUNC(TO_NUMBER(SUBSTR(v_path_gene,8*(v_i - 1) +5,4)) /TO_NUMBER(v_path_len) ,2) ;
END IF;
v_bl := v_bl + v_bl_curr ;
END IF;
v_temp := TO_CHAR( TO_NUMBER(:NEW.tod_fare) * v_bl_curr ,'FM99999999999.9999');
ELSE
v_temp := TO_CHAR( TO_NUMBER(:NEW.tod_fare) * ( 1 - v_bl ) ,'FM99999999999.9999');
END IF;
v_sql := 'UPDATE M_MONEY_DETAIL SET TRAN_NUM = TRAN_NUM + 1,'||'MONEY= MONEY + ' || V_TEMP ||
' WHERE BANK_IN_SERIAL = '||:NEW.SERIAL_BANK_IN||' AND NET_NO_EXIT = '''||:NEW.NET_NO||''' AND PLAZ_NO_EXIT='''||:NEW.PLAZ_NO||
''' AND NET_NO_GET='''||SUBSTR(V_PATH_GENE,8*(V_I - 1) + 1,2)||''' AND PLAZ_NO_GET='''||SUBSTR(V_PATH_GENE,8*(V_I - 1) +3,2)||''' AND MOP='''||:NEW.BILL_CODE||''' AND CLASS='||V_CLAS||
' AND PART='||V_PART;
DBMS_SQL.PARSE(v_id, v_sql ,dbms_sql.v7);
v_ret := dbms_sql.EXECUTE(v_id);
IF v_ret = 0 THEN
v_sql := 'INSERT INTO M_MONEY_DETAIL ( BANK_IN_SERIAL,NET_NO_EXIT,PLAZ_NO_EXIT,' ||
'NET_NO_GET,PLAZ_NO_GET,MOP,CLASS,TRAN_NUM,MONEY ) '||
' VALUES('||:NEW.SERIAL_BANK_IN||','''||:NEW.net_no||''','''||:NEW.plaz_no||''','''||SUBSTR(v_path_gene,8*(v_i - 1) +1,2)|| ''', '''||SUBSTR(v_path_gene,8*(v_i - 1) +3,2)||''','''||
:NEW.BILL_CODE||''','''||v_clas||''',1,'|| v_temp ||' ) ';
DBMS_SQL.PARSE(V_ID, V_SQL ,DBMS_SQL.V7);
V_RET := DBMS_SQL.EXECUTE(V_ID);
END IF;
--INSERT OR UPDATE M_MONEY_TOTAL_BANKIN;
-- INSERT OR UPDATE MONEY_IN OR MONEY_OUT
IF :NEW.net_no||:NEW.plaz_no <> SUBSTR(v_path_gene,8*(v_i - 1) +1,4) THEN
-- UPDATE MONEY_OUT
SELECT TO_NUMBER( TO_CHAR( v_dhm,'MM')) INTO v_part from dual;
v_sql := 'UPDATE M_MONEY_TOTAL_BANKIN SET MONEY_OUT' ||'= MONEY_OUT + ' || v_temp ||
' WHERE PART='||v_part||' AND NET_NO = '''||:NEW.NET_NO||''' AND PLAZ_NO='''||:NEW.PLAZ_NO||''' AND BANK_IN_SERIAL='||:NEW.SERIAL_BANK_IN||
' AND VER_NO='||:NEW.VER_NO||' AND DHM='||'TRUNC(TO_DATE('''||v_dhm||'''),''J'') ';
dbms_sql.PARSE(v_id, v_sql ,dbms_sql.v7);
v_ret := dbms_sql.EXECUTE(v_id);
IF v_ret = 0 THEN
v_sql := 'INSERT INTO M_MONEY_TOTAL_BANKIN ( BANK_IN_SERIAL,VER_NO,NET_NO,PLAZ_NO,MONEY_REAL, DHM)' ||
' VALUES('||:NEW.SERIAL_BANK_IN||','||:NEW.VER_NO||','''||:NEW.NET_NO||''','''||:NEW.PLAZ_NO||''', '||:NEW.TOD_FARE ||
', TRUNC(TO_DATE('''||v_dhm||'''),''J'') ) ';
dbms_sql.PARSE(v_id, v_sql ,dbms_sql.v7);
v_ret := dbms_sql.EXECUTE(v_id);
END IF;
-- update money_in
v_sql := 'UPDATE M_MONEY_TOTAL_BANKIN SET MONEY_IN' ||'= MONEY_IN + ' ||v_temp ||
'WHERE PART = '||v_part||' AND NET_NO = '''|| SUBSTR(v_path_gene,8*(v_i - 1) +1,2)||''' and PLAZ_NO='''|| SUBSTR(v_path_gene,8*(v_i - 1) +3,2)||
''' AND BANK_IN_SERIAL='||:NEW.SERIAL_BANK_IN||' AND VER_NO='||:NEW.VER_NO||
' AND DHM='||'TRUNC(TO_DATE('''||v_dhm||'''),''J'') ';
dbms_sql.PARSE(v_id, v_sql ,dbms_sql.v7);
v_ret := dbms_sql.EXECUTE(v_id);
IF v_ret = 0 THEN
v_sql := 'INSERT INTO M_MONEY_TOTAL_BANKIN (BANK_IN_SERIAL,VER_NO,NET_NO,PLAZ_NO,MONEY_IN, DHM)' ||
' VALUES('||:NEW.SERIAL_BANK_IN||','||:NEW.VER_NO||','''|| SUBSTR(v_path_gene,8*(v_i - 1) +1,2)||''','''|| SUBSTR(v_path_gene,8*(v_i - 1) +3,2)||''', '|| v_temp ||
', TRUNC(TO_DATE('''||v_dhm||'''),''J'') ) ';
dbms_sql.PARSE(v_id, v_sql ,dbms_sql.v7);
v_ret := dbms_sql.EXECUTE(v_id);
END IF;
END IF;
END LOOP;
dbms_sql.CLOSE_CURSOR(v_id);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_ret := 0 ;
--insert into m_temp values(:new.serial);
END T_TRANSACTION_FOR_SPLITED ;
...全文
请发表友善的回复…
发表回复
qq_343194731 2015-08-21
- 打赏
- 举报
顶一下，求大神帮忙！
qq_343194731 2015-08-20
- 打赏
- 举报
哪位大神帮帮忙，跪谢！
