使用DUP_VAL_ON_INDEX的PL/SQL更新值 [英] PL/SQL Update values using DUP_VAL_ON_INDEX

问题描述

DECLARE
    ins NUMBER := 0;
    upd NUMBER := 0;
    CURSOR c1 IS
        SELECT cid
        FROM tbl_cust
        WHERE cid 
        IN ('1','2','3','4');
BEGIN
    FOR rec IN c1 LOOP
        INSERT INTO tbl2 (id_tbl2, name_tbl2)
        VALUES(rec.cid, DECODE(rec.cid, '1', 'A',
                                        '2', 'B',
                                        '3', 'C',
                                        '4', 'D'));
        ins := ins + 1;
    END LOOP;
    dbms_output.put_line('Updated: ' || ins);
    dbms_output.put_line('Inserted: ' || upd);

EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        FOR rec IN c1 LOOP
            UPDATE tbl2 set name_tbl2 = DECODE(rec.cid, '1', 'A', 
                                                        '2', 'B',
                                                        '3', 'C',
                                                        '4', 'D')
            WHERE cust_cust_code = rec.cid;
            upd := upd + 1;    
        END LOOP;
        dbms_output.put_line('Updated: ' || upd);
        dbms_output.put_line('Inserted: ' || ins);
END;

第一次运行此代码并且tbl_cust中没有与tbl2相同的值时，输出如下:

when running this code for the first time and there are no values from tbl_cust that is the same as tbl2, the output is like this:

已更新:0

插入:4

这是正确的.在第二次运行中，输出如下所示:

which is correct. In the 2nd run the output is like this:

已更新:4

插入:0

这也是正确的. 但是，当我从数据库中删除cid'1'和'3'时，结果是这样的:

which is also correct. But when i delete for example cid '1' and '3' from the database, the result is like this:

已更新:4

插入:2

删除cid'1'和'3'后，我想要的输出是这样的:

The output that I wanted is like this after deleting cid '1' and '3':

已更新:2

插入:2

推荐答案

在更新的情况下，无需遍历所有记录. 这就是为什么您得到4个更新而不是预期的2个的原因.

There is no need to loop through all the records in case of the update. That's why you are getting 4 updates instead of expected 2.

相反，只有在DUP_VAL_ON_INDEX异常的情况下，才应进行更新，并且仅在导致异常的行中进行更新.

Instead, you should update only in the case of the DUP_VAL_ON_INDEX exception and only the row that caused the exception.

尝试类似这样的方法.

Try something like this.

DECLARE
    ins NUMBER := 0;
    upd NUMBER := 0;
    CURSOR c1 IS
        SELECT cid
        FROM tbl_cust
        WHERE cid 
        IN ('1','2','3','4');
BEGIN
    FOR rec IN c1 LOOP
        begin 
           INSERT INTO tbl2 (id_tbl2, name_tbl2)
           VALUES(rec.cid, DECODE(rec.cid, '1', 'A',
                                        '2', 'B',
                                        '3', 'C',
                                        '4', 'D'));
           ins := ins + 1;
        EXCEPTION   WHEN DUP_VAL_ON_INDEX THEN
           UPDATE tbl2 set name_tbl2 = DECODE(rec.cid, '1', 'A',
                                        '2', 'B',
                                        '3', 'C',
                                        '4', 'D'));
           WHERE cust_cust_code = rec.cid;
           upd := upd + 1;
           continue; 
         end;    
    END LOOP;
        dbms_output.put_line('Updated: ' || upd);
        dbms_output.put_line('Inserted: ' || ins);
END;

这篇关于使用DUP_VAL_ON_INDEX的PL/SQL更新值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！