こんにちは。エキサイト株式会社のあはれんです。
今回は、OracleのRETURNING INTO句について紹介します。
環境情報
- データベース：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句についてより知りたい場合はこちらを御覧ください。
tips：DataGripでDBMS_OUTPUT.PUT_LINEを出力する方法
SET serveroutput ON; を実行してDBMS_OUTPUT.PUT_LINEを有効化するのが一般的ですが、
PhpStorm Database Toolでは、Data Sources > Properties > Options から設定します。
最後に
RETURNING INTO句を使うことでSelect文を実行せずに更新結果を知ることができました。🚀
Oracle Database 23cでRETURNING句の機能が拡張され、更新前後の値が1度に取得できるようになったそうです。 どこかの機会で使ってみたいと思います。
