初めに
Oracle Database 23cでは、SQLがシンプルに書けるような変更が加えられています。
今回はDML関連の変更を試してみました。
1. ダイレクトJOINを使用したUPDATE/DELETE
他の表の値を使用してUPDATEを実行する場合、従来は以下のようにSELECT句を使用する表記でした。
UPDATE employees e
SET e.salary = (
SELECT j.max_salary FROM jobs j
WHERE e.job_id = j.job_id
)
WHERE e.employee_id=100;
Oracle Database 23cでは、以下のようにSELECT句を使用せずに上記と同様のUPDATEが実行できます。
UPDATE employees e
SET e.salary = j.max_salary FROM jobs j
WHERE j.job_id = e.job_id
AND e.employee_id=100;
では、実行してみます。
と、その前にemployees表、jobs表のデータを確認しておきます。
SQL> SELECT employee_id,last_name,job_id,salary FROM employees
2 WHERE employee_id=100;
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
100 King AD_PRES 2400
1 row selected.
SQL> SELECT job_id, max_salary FROM jobs WHERE job_id='AD_PRES';
JOB_ID MAX_SALARY
---------- ----------
AD_PRES 40000
1 row selected.
SQL>
まずは従来の記法で実行してみます。
SQL> UPDATE employees e
2 SET e.salary = (
3 SELECT j.max_salary FROM jobs j
4 WHERE e.job_id = j.job_id
5 )
6 WHERE e.employee_id=100;
1 row updated.
SQL> SELECT employee_id,last_name,job_id,salary FROM employees
2 WHERE employee_id=100;
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
100 King AD_PRES 40000
1 row selected.
SQL>
もちろん、問題なく実行できます。
一旦、変更内容をロールバックします。
SQL> ROLLBACK;
Rollback complete.
SQL>
次にOracle Database 23cの記法で実行してみます。
SQL> UPDATE employees e
2 SET e.salary = j.max_salary FROM jobs j
3 WHERE j.job_id = e.job_id
4 AND e.employee_id=100;
1 row updated.
SQL> SELECT employee_id,last_name,job_id,salary FROM employees
2 WHERE employee_id=100;
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
100 King AD_PRES 40000
1 row selected.
SQL>
Oracle Database 23cでは、ダイレクトJOINを使用して、SELECT句を使用せずにUPDATEが実行できることが確認できました。
2. RETURNING句が拡張され、更新前のデータが取得可能に
従来はUPDATE文をRETURNING句とともに使用した際に取得できる列の値は、UPDATE文によって更新された後の値のみでした。
Oracle Database 23cではDMLのRETURNING句が拡張され、UPDATE文によって更新された列の更新前と更新後の値が取得できるようになりました。
変更前の値を取得するには列名の前にOLD、変更後の値を取得するには列名の前にNEWを記述します。
例えば、salary列の変更前の値を取得するには「OLD salary」、変更後の値を取得するには「NEW salary」のように記述します。
今回は、employees表に対してemployee_id列の値が100のレコードのsalary列の値を1.1倍にし、salary列の更新前後の値を表示するPL/SQLブロックで検証してみます。
・従来の方法
（あらかじめSELECT文で変更前の値を取得、UPDATE文のRETURNING句で変更後の値を取得）
DECLARE
TYPE EmpRec IS RECORD (
last_name employees.last_name%TYPE,
salary employees.salary%TYPE
);
emp_info EmpRec;
old_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO old_salary
FROM employees
WHERE employee_id = 100;
UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = 100
RETURNING last_name, salary INTO emp_info;
DBMS_OUTPUT.PUT_LINE (
'Salary of '||emp_info.last_name||' raised from '||old_salary||' to '||emp_info.salary
);
END;
/
・Oracle Database 23cでの方法
（UPDATE文のRETURNING句で変更前、変更後の値を取得）
DECLARE
TYPE EmpRec IS RECORD (
last_name employees.last_name%TYPE,
old_salary employees.salary%TYPE,
new_salary employees.salary%TYPE
);
emp_info EmpRec;
BEGIN
UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = 100
RETURNING last_name, OLD salary, NEW salary INTO emp_info;
DBMS_OUTPUT.PUT_LINE (
'Salary of '||emp_info.last_name||' raised from '||old_salary||' to '||emp_info.salary
);
END;
/
では、実行してみます。
まずは従来の記法で実行してみます。
SQL> set serveroutput on
SQL> set echo on
SQL> set feed on
SQL>
SQL> SELECT employee_id, last_name, salary FROM employees WHERE employee_id=100;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
100 King 2400
1 row selected.
SQL>
SQL> DECLARE
2 TYPE EmpRec IS RECORD (
3 last_name employees.last_name%TYPE,
4 salary employees.salary%TYPE
5 );
6 emp_info EmpRec;
7 old_salary employees.salary%TYPE;
8 BEGIN
9 SELECT salary INTO old_salary
10 FROM employees
11 WHERE employee_id = 100;
12
13 UPDATE employees
14 SET salary = salary * 1.1
15 WHERE employee_id = 100
16 RETURNING last_name, salary INTO emp_info;
17
18 DBMS_OUTPUT.PUT_LINE (
19 'Salary of '||emp_info.last_name||' raised from '||old_salary||' to '||emp_info.salary
20 );
21 END;
22 /
Salary of King raised from 2400 to 2640
PL/SQL procedure successfully completed.
SQL>
もちろん、問題なく実行できます。
一旦、Kingのsalaryを元の値に戻します。
SQL> UPDATE employees SET salary=2400 WHERE employee_id=100;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL>
次にOracle Database 23cの記法で実行してみます。
SQL> set serveroutput on
SQL> set echo on
SQL> set feed on
SQL>
SQL> SELECT employee_id, last_name, salary FROM employees WHERE employee_id=100;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
100 King 2400
1 row selected.
SQL>
SQL> DECLARE
2 TYPE EmpRec IS RECORD (
3 last_name employees.last_name%TYPE,
4 old_salary employees.salary%TYPE,
5 new_salary employees.salary%TYPE
6 );
7 emp_info EmpRec;
8 BEGIN
9
10 UPDATE employees
11 SET salary = salary * 1.1
12 WHERE employee_id = 100
13 RETURNING last_name, OLD salary, NEW salary INTO emp_info;
14
15 DBMS_OUTPUT.PUT_LINE (
16 'Salary of '||emp_info.last_name||' raised from '||emp_info.old_salary||' to '||emp_info.new_salary
17 );
18 END;
19 /
Salary of King raised from 2400 to 2640
PL/SQL procedure successfully completed.
SQL>
Oracle Database 23cでは、RETURNING句を使用して、UPDATE文による列の変更前、変更後の値が取得できることが確認できました。