oracle 出现异常时将记录插入表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28669224/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
insert record into table on exception
提问by Niha Mialk
Here is my scenario:
这是我的场景：
I want to write a procedure in oracle, there are four tables, tab1, tab2, tab3, err. tab1 has some data in two columns (id number, name varchar(250)), while others are empty.
我想在oracle中写一个程序，有四个表，tab1，tab2，tab3，err。tab1 在两列中有一些数据(id number, name varchar(250))，而其他的则为空。
schema for tab2 is
tab2 的架构是
(id number, name varchar(50)), for tab3 (id number, name varchar(250)).
(id number, name varchar(50)), for tab3 (id number, name varchar(250)).
I want to insert data from tab1 to tab2 and when there is exception like name is greater than varchar(50), it will insert into tab3 and also insert error message into err table.
我想将数据从 tab1 插入到 tab2 并且当出现 name 大于 varchar(50) 的异常时，它将插入到 tab3 并将错误消息插入到 err 表中。
So all record from tab1 should be inserted into tab2 and tab3 accordingly using exception handling.
因此，应使用异常处理将 tab1 中的所有记录相应地插入到 tab2 和 tab3 中。
Here is what I tried but failed.
这是我尝试过但失败的方法。
CREATE OR REPLACE
PROCEDURE exception_handler
IS
vSqlErr VARCHAR2(200) ;
vSqlCode VARCHAR2(5) ;
id2 NUMBER;
name2 VARCHAR(250);
BEGIN
INSERT ALL INTO tab3 VALUES
(id, name
)
SELECT * FROM tab1 t;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO tab2 VALUES
(id, name
);
vSqlErr := SUBSTR(sqlerrm, 1, 200) ;
vSqlCode := SUBSTR(SQLCODE, 1, 5) ;
INSERT INTO err VALUES
(vSqlErr, vSqlCode
) ;
COMMIT ;
RAISE;
END;
回答by Lalit Kumar B
This is just a simple demonstration based on your inputs in the question. Better go for BULKprocessing and SQL%BULK_EXCEPTIONS. And don't use WHEN OTHERS blindly.
这只是基于您在问题中的输入的简单演示。最好使用BULK处理和SQL%BULK_EXCEPTIONS。并且不要盲目地使用 WHEN OTHERS。
Let's say you have an EMPtable, and you have a check constrainton employee name as not more than 5 characters. There is an EMP_ERRtable to log the error values and error message. Lets see a test case:
假设您有一张EMP桌子，并且您的check constraint员工姓名不超过 5 个字符。有一个EMP_ERR表来记录错误值和错误消息。让我们看一个测试用例：
SQL> DROP TABLE emp_new PURGE;
Table dropped.
SQL> CREATE TABLE emp_new AS
2 SELECT * FROM emp WHERE 1 =2;
Table created.
SQL> ALTER TABLE emp_new ADD CONSTRAINT check_ename CHECK(LENGTH(ename)<=5);
Table altered.
SQL> DROP TABLE emp_err PURGE;
Table dropped.
SQL>
SQL> CREATE TABLE emp_err
2 (
3 empno NUMBER,
4 ename VARCHAR2(100),
5 err_msg VARCHAR2(250)
6 );
Table created.
SQL> CREATE OR REPLACE
2 PROCEDURE p
3 (
4 v_empno NUMBER,
5 v_ename VARCHAR2
6 )
7 IS
8 vSqlErr VARCHAR2(200) ;
9 vSqlCode VARCHAR2(5) ;
10 empno2 NUMBER;
11 ename2 VARCHAR2(250);
12 BEGIN
13 INSERT INTO emp_new
14 (empno, ename
15 ) VALUES
16 (v_empno, v_ename
17 );
18 COMMIT;
19 EXCEPTION
20 WHEN OTHERS THEN
21 vSqlErr := SUBSTR(sqlerrm, 1, 200) ;
22 vSqlCode := SUBSTR(SQLCODE, 1, 5) ;
23 INSERT
24 INTO emp_err
25 (
26 empno,
27 ename,
28 err_msg
29 )
30 VALUES
31 (
32 v_empno,
33 v_ename,
34 vSqlErr
35 ||' - '
36 ||vSqlCode
37 );
38 COMMIT ;
39 raise;
40 END;
41 /
Procedure created.
Lets execute the procure with ename value as more than 5 characters, so that it raises an error, and we expect a row to be inserted into the emp_errtable.
让我们以超过 5 个字符的 ename 值执行 procure，因此它会引发错误，并且我们希望将一行插入到emp_err表中。
SQL> exec p(1, 'abcdef');
BEGIN p(1, 'abcdef'); END;
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHECK_ENAME) violated
ORA-06512: at "SCOTT.P", line 38
ORA-06512: at line 1
So, the error is raised. Lets see if it is logged in the error table.
因此，错误被引发。让我们看看它是否记录在错误表中。
SQL> column ename format a10
SQL> column err_msg format a100
SQL> set linesize 150
SQL> select * from emp_err;
EMPNO ENAME ERR_MSG
---------- ---------- ----------------------------------------------------------------
1 abcdef ORA-02290: check constraint (SCOTT.CHECK_ENAME) violated - -2290
SQL>
We have the error details logged.
我们记录了错误详细信息。
