oracle - 如何在oracle中使用插入触发器之前更改表的旧数据？

Question

如果新行插入到表中，如果重复行已经存在，我想将两行合并为一个

表 TEST（定义为主键的 ID 列）

ID     ITEM     QUANTITY
--     ----     --------
1       KA1        5
2       KA2        2

如果用值 (KA1,6)将新行插入到表测试中，因为项目KA1已经存在，所以应该插入新行，总数为 11，旧行应该被删除。

结果集应如下所示：

ID     ITEM     QUANTITY
--     ----     --------
2       KA2        2
3       KA1       11

使用的触发器和内联过程是：

CREATE OR REPLACE TRIGGER  MERG_DUP
BEFORE INSERT ON TEST
FOR EACH ROW
BEGIN
  FOR VAL IN(SELECT ID,ITEM,QUANTITY, FROM TEST)
  LOOP
  IF VAL.ITEM=:NEW.ITEM THEN
            :NEW.QUANTITY:=:NEW.QUANTITY+VAL.QUANTITY;
           XXI_MULTI_PR_REMOVE(VAL.ID);
        EXIT;
  END IF;
  END LOOP;
end; 
/


CREATE  OR REPLACE PROCEDURE XXI_MULTI_PR_REMOVE(ID number)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
L_TID NUMBER;
BEGIN
 L_TID:=ID;
  DELETE FROM  TEST WHERE ID=L_TID;
commit;
END;
/

标签： oracleplsqldatabase-trigger

Answer 1

You do not need to use PRAGMA AUTONOMOUS_TRANSACTION, and do not use COMMIT inline inside your procedures.

For your case it's nice to CREATE TABLE test with ID column defined as number generated always as identity primary key.

Therefore, the statements in the following order may be used :

 SQL> CREATE TABLE test(
                  id       number generated always as identity primary key,
                  item     varchar2(100),
                  quantity int
 );
 /
 SQL> INSERT INTO test(item,quantity) VALUES ('KA1',5);
 SQL> INSERT INTO test(item,quantity) VALUES ('KA2',2);

 SQL> CREATE OR REPLACE PROCEDURE XXI_MULTI_PR_REMOVE( I_ITEM varchar2 ) IS
 BEGIN
  DELETE TEST WHERE ITEM = I_ITEM;
 END;
 /
 SQL> CREATE OR REPLACE TRIGGER MERG_DUP
 BEFORE INSERT ON TEST
 FOR EACH ROW
 DECLARE  
      v_qty NUMBER;
 BEGIN
    BEGIN
      SELECT SUM(NVL(QUANTITY,0)) INTO v_qty FROM TEST WHERE ITEM = :NEW.ITEM;
     EXCEPTION WHEN OTHERS THEN v_qty := NULL; 
    END;  
    IF ( v_qty IS NOT NULL ) THEN
        XXI_MULTI_PR_REMOVE(:NEW.ITEM);
       :NEW.QUANTITY:=:NEW.QUANTITY+v_qty;    
    END IF;
 END MERG_DUP;
 /
 SQL> INSERT INTO test(item,quantity) VALUES ('KA3',6);
 SQL> COMMIT;
 SQL> SELECT * FROM test;