插入记录到表上的异常

Question

这里是我的情况：

我想写Oracle中的过程中，有四个表格，TAB1，TAB2，TAB3，ERR。 tab1有一些数据在两列(id number, name varchar(250))，而其他是空的。

架构TAB2是

(id number, name varchar(50)), for tab3 (id number, name varchar(250))。

我想插入数据从tab1到tab2，当有像例如名称大于varchar（50）的异常时，它会插入到tab3中，并将错误消息插入到err表中。

因此，使用异常处理相应地将tab1中的所有记录插入tab2和tab3中。

这是我试过但失败了。

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; 

Answer 1

这只是一个简单的演示，根据您在问题中的输入。最好去BULK处理和SQL％BULK_EXCEPTIONS。 And don't use WHEN OTHERS blindly。

假设你有一张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. 

允许执行同为ename值超过5个字符从采购，从而引发错误，我们期待一个行插入到表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 

因此，错误被提出。让我们看看它是否记录在错误表中。

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> 

我们有错误的细节记录。