環境情報

データベース：Oracle Database 19c
データベースツール：PhpStorm Database Tool

はじめに

OracleでUpdate文を実行すると、3 row affected と更新された件数が結果として返されます。

SQL > UPDATE test_users SET note = '成年' WHERE age >= 20
3 row affected

しかし、これでは件数しか分かりません。実際の更新対象者を知るにはSelect文を実行する必要があります。

SQL > SELECT * FROM test_users WHERE age >= 20
 3 rows retrieved

+--+----+---+-----+
|ID|NAME|AGE|NOTE |
+--+----+---+-----+
|1 |花子  |20 |成年 |
|2 |一郎  |25 |成年 |
|4 |null |23 |成年 |
+--+----+---+-----+

また、nullになっているNAMEの値を更新する場合は、更新後には該当のデータが無くなるため、Select文で確認することができません。

SQL > UPDATE test_users SET name = '名無し' WHERE name IS NULL
1 row affected

SQL > SELECT * FROM test_users WHERE name IS NULL
0 rows retrieved

このような場合に、RETURNING INTO句を利用することができます。

RETURNING INTO句の利用方法

RETURNING INTO句では更新結果を格納するための変数を指定することができます。その変数をDBMS_OUTPUTで出力することで、Select文を実行せずに結果を知ることができるようになります。

SQL > DECLARE
            TYPE result_item IS RECORD (
                                           id test_users.id%TYPE,
                                           note test_users.note%TYPE
                                       );
            TYPE result IS TABLE OF result_item;
            output result;
        BEGIN
            UPDATE test_users
            SET
                note = '成年'
            WHERE age >= 20
                RETURNING id, note BULK COLLECT INTO output;
        
            FOR I IN output.FIRST..output.LAST LOOP
                    DBMS_OUTPUT.PUT_LINE('id：' || output(i).id || ' note: ' || output(i).note );
                END LOOP;
        END;

completed in 67 ms
id：1 note: 成年
id：2 note: 成年
id：4 note: 成年

実行したPL/SQLを説明していきたいと思います。

TYPE result_item IS RECORD (                            
                               id test_users.id%TYPE,   
                               note test_users.note%TYPE
                           );                           
TYPE result IS TABLE OF result_item;                    
output result;

上記ではRETURNING INTO句で格納するための変数の型を定義しています。今回は複数件格納されるので、変数( output )にはコレクション型( result )を定義しています。

UPDATE test_users                                 
SET                                               
    note = '成年'                                   
WHERE age > 20                                    
    RETURNING id, note BULK COLLECT INTO output;

RETURNING INTO句で変数( output )に格納しています。コレクション型に格納するため、BULK COLLECT句を使用しています。

FOR I IN output.FIRST..output.LAST LOOP                                                 
        DBMS_OUTPUT.PUT_LINE('id：' || output(i).id || ' note: ' || output(i).note );    
    END LOOP;

RETURNING INTO句で格納された変数( output )を展開して1行ずつDBMS_OUTPUT.PUT_LINEで出力しています。

RETURNING INTO句についてより知りたい場合はこちらを御覧ください。

docs.oracle.com