ORACLE更新并返回OLD和NEW值

cnh2zyt3  于 2023-06-22  发布在  Oracle
关注(0)|答案(2)|浏览(167)

PL/SQL(Oracle 12 c)中是否存在与此T-SQL查询等效的查询?
UPDATE A SET A.columnA = 10 WHERE A.columnB < 30 OUTPUT INSERTED.*, DELETED.*
该查询更新表A,同时返回更新前和更新后的记录状态。
触发器不是一个解决方案,为我以及SELECT记录之前和更新后的SELECT记录。

js4nwp54

js4nwp541#

不是直接的,但是使用RETURNING INTO你将能够达到同样的效果:

CREATE TABLE A(columnA VARCHAR2(10), columnB INT);
INSERT INTO A(columnA, columnB) VALUES ('Test', 10);
INSERT INTO A(columnA, columnB) VALUES ('Row 2', 20);
CREATE TABLE audit_table(col_new VARCHAR2(10),col_old VARCHAR2(10));

DECLARE
   TYPE rec IS RECORD (actual A.columnA%TYPE, old A.columnA%TYPE);
   TYPE col_a_t IS TABLE OF rec;
   v_a col_a_t;
BEGIN
  UPDATE (SELECT A.*, (SELECT A.columnA FROM dual) AS old_columnA FROM A)
  SET columnA = 'XYZ'
  WHERE columnB < 30
  RETURNING columnA, old_columnA BULK COLLECT INTO v_a;
  COMMIT;

  -- printing for debug
  FOR i IN v_a.first .. v_a.last LOOP
     dbms_output.put_line('Old =>' || v_a(i).old || ' new => ' || v_a(i).actual);
  END LOOP;

  -- additional
  FORALL i IN v_a.first .. v_a.last
    INSERT INTO audit_table VALUES v_a(i);
  COMMIT;
END;
/

SELECT * FROM A;
SELECT * FROM audit_table;

DBFiddle Demo

想法取自:Returning Old value during update

pxyaymoc

pxyaymoc2#

在Oracle 12c中,不能在update语句中返回旧值和新值,但在Oracle Database 23c中引入了它们,在那里可以从UPDATE语句所涉及的行中返回列的旧值和新值。你的代码应该是这样的:

DECLARE
  TYPE OLD_columnA_T IS TABLE OF A.columnA%TYPE;
  TYPE NEW_columnA_T IS TABLE OF A.columnA%TYPE;

  OLD_columnA OLD_columnA_T;
  NEW_columnA NEW_columnA_T;
BEGIN
  UPDATE A
     SET A.columnA = 10
   WHERE A.columnB < 30
  RETURNING OLD columnA, NEW columnA 
  BULK COLLECT INTO OLD_columnA, NEW_columnA;

  FOR I IN 1 .. OLD_columnA.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Old.columnA = ' || OLD_columnA(I));
    DBMS_OUTPUT.PUT_LINE('New.columnA = ' || NEW_columnA(I));
  END LOOP;

END;

欲了解更多详情,请查看以下链接:
返回条款
DML RETURNING Clause Enhancements in Oracle Database 23c

相关问题