ORACLE更新，返回旧值和新值

Question

PL/SQL (Oracle 12c)中是否有与此T-SQL查询等效的语句？

UPDATE A SET A.columnA = 10 WHERE A.columnB < 30 OUTPUT INSERTED.*, DELETED.*

该查询更新表A，同时返回更新前和更新后记录的状态。

触发器不是我的解决方案，以及在更新之前选择记录和更新后选择记录。

Answer 1

在使用execute immediate的情况下，上面的代码会是什么样子？像这样的东西？为了简单起见，我跳过了插入部分。

DECLARE
    sql_update  VARCHAR2(4000) := NULL;
    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
    sql_update := '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;
    EXECUTE IMMEDIATE sql_update;
  -- 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;
END;
/

当我执行上面的代码时，我得到

ORA-00925: missing INTO keyword

Answer 2

你是说像这样吗？但我仍然得到相同的错误：

DECLARE
    sql_update  VARCHAR2(4000) := NULL;
    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
    sql_update := ' 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;
    EXECUTE IMMEDIATE sql_update;
  -- 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;
END;
/