oracle - 在异常处理程序中处理异常
问题描述
我有下面提到的插入触发器。我在 - 检查记录是否已存在,如果抛出“未找到数据”异常,则插入相同的数据。在异常中执行此插入时,我必须处理一些异常,例如列约束。
因此,请就如何在异常处理程序中处理异常提出建议。
CREATE OR REPLACE TRIGGER BRANCH_INSERT_SYNC_TR
AFTER INSERT
ON PM_FIN_INST_DIR_MAST
FOR EACH ROW
FOLLOWS BANK_INSERT_SYNC_TR
DECLARE
BRANCHTEMP VARCHAR (100);
BEGIN
SELECT BRANCH_KEY
INTO BRANCHTEMP
FROM RTBK_BADM_BRANCH_STB
WHERE LOCAL_CODE LIKE ''
||
:NEW.COD_EXT_INST_KEY
||'';
EXCEPTION
WHEN no_data_found THEN
INSERT
INTO RTBK_BADM_BRANCH_STB
(
BRANCH_CODE,
BRANCH_NAME,
BRANCH_ADDR1,
BRANCH_ADDR2,
BRANCH_ADDR3,
BANK_KEY,
LOCAL_CODE,
CITY_KEY,
POSTAL_CODE,
TELEPHONE,
FAX_NO,
FLEXI_FIELD1,
FLEXI_FIELD2,
FLEXI_FIELD3,
STATUS,
STATUS_CHANGE_DATE,
CREATED_BY,
CREATED_DATE,
LAST_ACTION,
MODIFIED_BY,
MODIFIED_DATE,
VERSION$,
FLAG,
DISPLAY_SEQ
)
VALUES
(
(select ltrim(:NEW.COD_CC_BRN, '0') from dual),
--:NEW.COD_CC_BRN,
:NEW.NAM_BRANCH,
:NEW.TXT_BRN_ADD1,
:NEW.TXT_BRN_ADD2,
:NEW.TXT_BRN_ADD3,
(SELECT BANK_KEY
FROM RTBK_BADM_BANK_STB
WHERE BANK_NAME =
(:NEW.NAM_BANK)
),
:NEW.COD_EXT_INST_KEY,
(SELECT CITY_KEY
FROM RTBK_BADM_CITY_STB
WHERE CITY_NAME =
(:NEW.NAM_BRN_CITY)
),
:NEW.TXT_BRN_ZIP,
NULL,
NULL,
'N',
'IMPS,RTGS,NEFT',
NULL,
'A',
CURRENT_TIMESTAMP,
'Sync_Trigger',
CURRENT_TIMESTAMP,
'INSERT',
'Sync_Trigger',
CURRENT_TIMESTAMP,
1,1,
NULL
);
END;
/
ALTER TRIGGER BRANCH_INSERT_SYNC_TR ENABLE;
我尝试了以下方法,但没有运气
CREATE OR REPLACE TRIGGER BRANCH_INSERT_SYNC_TR AFTER
INSERT ON PM_FIN_INST_DIR_MAST FOR EACH ROW FOLLOWS BANK_INSERT_SYNC_TR DECLARE BRANCHTEMP VARCHAR (100);
BEGIN
SELECT BRANCH_KEY
INTO BRANCHTEMP
FROM RTBK_BADM_BRANCH_STB
WHERE LOCAL_CODE LIKE ''
|| :NEW.COD_EXT_INST_KEY
||'';
EXCEPTION
WHEN no_data_found THEN
BEGIN
INSERT
INTO RTBK_BADM_BRANCH_STB
(
BRANCH_CODE,
BRANCH_NAME,
BRANCH_ADDR1,
BRANCH_ADDR2,
BRANCH_ADDR3,
BANK_KEY,
LOCAL_CODE,
CITY_KEY,
POSTAL_CODE,
TELEPHONE,
FAX_NO,
FLEXI_FIELD1,
FLEXI_FIELD2,
FLEXI_FIELD3,
STATUS,
STATUS_CHANGE_DATE,
CREATED_BY,
CREATED_DATE,
LAST_ACTION,
MODIFIED_BY,
MODIFIED_DATE,
VERSION$,
FLAG,
DISPLAY_SEQ
)
VALUES
(
(SELECT ltrim(:NEW.COD_CC_BRN, '0') FROM dual
)
,
--:NEW.COD_CC_BRN,
:NEW.NAM_BRANCH,
:NEW.TXT_BRN_ADD1,
:NEW.TXT_BRN_ADD2,
:NEW.TXT_BRN_ADD3,
(SELECT BANK_KEY FROM RTBK_BADM_BANK_STB WHERE BANK_NAME = (:NEW.NAM_BANK)
),
:NEW.COD_EXT_INST_KEY,
(SELECT CITY_KEY
FROM RTBK_BADM_CITY_STB
WHERE CITY_NAME = (:NEW.NAM_BRN_CITY)
),
:NEW.TXT_BRN_ZIP,
NULL,
NULL,
'N',
'IMPS,RTGS,NEFT',
NULL,
'A',
CURRENT_TIMESTAMP,
'Sync_Trigger',
CURRENT_TIMESTAMP,
'INSERT',
'Sync_Trigger',
CURRENT_TIMESTAMP,
1,1,
NULL
);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.put_line('Contraint violated');
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Contraint violated');
END;
END;
/
ALTER TRIGGER BRANCH_INSERT_SYNC_TR ENABLE;
解决方案
为什么你把事情弄得这么复杂。只需使用标志来指示数据是否存在并相应地插入。
CREATE OR REPLACE TRIGGER BRANCH_INSERT_SYNC_TR AFTER
INSERT ON PM_FIN_INST_DIR_MAST FOR EACH ROW FOLLOWS BANK_INSERT_SYNC_TR DECLARE
--BRANCHTEMP VARCHAR (100);
LV_RECORD_COUNT NUMBER := 0;
BEGIN
SELECT COUNT(*) -- IT WILL ALWYAS RETURN number
INTO LV_RECORD_COUNT
FROM RTBK_BADM_BRANCH_STB
WHERE LOCAL_CODE LIKE ''
|| :NEW.COD_EXT_INST_KEY
||'';
IF LV_RECORD_COUNT = 0 THEN -- Use IF
BEGIN
INSERT
INTO RTBK_BADM_BRANCH_STB
(
BRANCH_CODE,
BRANCH_NAME,
BRANCH_ADDR1,
BRANCH_ADDR2,
BRANCH_ADDR3,
BANK_KEY,
LOCAL_CODE,
CITY_KEY,
POSTAL_CODE,
TELEPHONE,
FAX_NO,
FLEXI_FIELD1,
FLEXI_FIELD2,
FLEXI_FIELD3,
STATUS,
STATUS_CHANGE_DATE,
CREATED_BY,
CREATED_DATE,
LAST_ACTION,
MODIFIED_BY,
MODIFIED_DATE,
VERSION$,
FLAG,
DISPLAY_SEQ
)
VALUES
(
(SELECT ltrim(:NEW.COD_CC_BRN, '0') FROM dual
)
,
--:NEW.COD_CC_BRN,
:NEW.NAM_BRANCH,
:NEW.TXT_BRN_ADD1,
:NEW.TXT_BRN_ADD2,
:NEW.TXT_BRN_ADD3,
(SELECT BANK_KEY FROM RTBK_BADM_BANK_STB WHERE BANK_NAME = (:NEW.NAM_BANK)
),
:NEW.COD_EXT_INST_KEY,
(SELECT CITY_KEY
FROM RTBK_BADM_CITY_STB
WHERE CITY_NAME = (:NEW.NAM_BRN_CITY)
),
:NEW.TXT_BRN_ZIP,
NULL,
NULL,
'N',
'IMPS,RTGS,NEFT',
NULL,
'A',
CURRENT_TIMESTAMP,
'Sync_Trigger',
CURRENT_TIMESTAMP,
'INSERT',
'Sync_Trigger',
CURRENT_TIMESTAMP,
1,1,
NULL
);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.put_line('Contraint violated');
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Other error'); -- use different message for proper debug
END;
END IF;
EXCEPTION -- use Exception if needed here
WHEN OTHERS THEN
.....
END;
/
ALTER TRIGGER BRANCH_INSERT_SYNC_TR ENABLE;
推荐阅读
- python - 有没有办法判断换行符是否在 Python 中拆分了两个不同的单词?
- reactjs - 动画反应路线不起作用
零件 - vba - 如何删除活动窗口 visio 中的所有 .png 形状?VBA
- html - 单击html中的按钮时如何使弹出窗口出现
- excel - 从 Excel 插件发布后,TFS 中史诗和功能的树状结构已丢失
- python - 在 Python 中引用父或祖父文件夹中的自定义模块会导致“ModuleNotFoundError: No module named”
- laravel - 如何为一组用户声明通知?
- c - C 命令行参数验证错误
- r - 在 ggplot2 x 轴中添加所有年月日期
- excel - 如何判断 UI 是否发起了 SaveAs 操作?