UPDATE 문의 RETURNING INTO 절 개선

Oracle 23c부터 UPDATE 문의 RETURNING INTO 절에 OLD 키워드와 NEW 키워드를 사용하여 갱신 전후 값을 반환할 수 있습니다.

The RETURNING INTO clause for INSERT, UPDATE, and DELETE statements are enhanced to report old and new values affected by the respective statement. This allows developers to use the same logic for each of these DML types to obtain values pre- and post-statement execution. Old and new values are valid only for UPDATE statements. INSERT statements don't report old values and DELETE statements don't report new values. The ability to obtain old and new values affected by INSERT, UPDATE, and DELETE statements, as part of the SQL command’s execution, offers developers a uniform approach to reading these values and reduces the amount of work the database must perform.

 

테스트를 위해 아래와 같이 테이블을 생성하겠습니다.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1, c2) AS SELECT 1, 1 FROM DUAL;

 

아래 예제는 UPDATE 문의 RETURNING INTO 절에 OLD 키워드와 NEW 키워드를 사용합니다. 갱신 전후 값이 반환되는 것을 확인할 수 있습니다.

-- 2
SET SERVEROUT ON

DECLARE
    v_old t1%ROWTYPE;
    v_new t1%ROWTYPE;
BEGIN
    UPDATE t1
       SET c2 = 2
     WHERE c1 = 1
    RETURN OLD c1, OLD c2, NEW c1, NEW c2
      INTO v_old.c1, v_old.c2, v_new.c1, v_new.c2;

    DBMS_OUTPUT.PUT_LINE (v_old.c1 || ',' || v_old.c2 || ',' || v_new.c1 || ',' || v_new.c2);
END;
/

1,1,1,2