PL/SQL (Oracle 12c) 中是否有与此 T-SQL 查询等效的内容?
UPDATE A SET A.columnA = 10 WHERE A.columnB < 30 OUTPUT INSERTED.*, DELETED.*
查询更新表A,同时返回更新前和更新后记录的状态。
对于我来说,触发器以及更新前的 SELECT 记录和更新后的 SELECT 记录都不是一个解决方案。
不是直接的,而是使用RETURNING INTO https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/returninginto_clause.htm你将能够达到同样的效果:
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;
Idea taken from: Returning Old value during update http://www.orafaq.com/forum/t/177753/
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)