Oracle Database 23c Free - Developer Releaseと戯れる（その2）

# 初めに
Oracle Database 23cでは、SQLがシンプルに書けるような変更が加えられています。
今回はDML関連の変更を試してみました。

# 1. ダイレクトJOINを使用したUPDATE/DELETE
他の表の値を使用してUPDATEを実行する場合、従来は以下のようにSELECT句を使用する表記でした。
```sql
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が実行できます。
```sql
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句で変更後の値を取得）
```sql
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句で変更前、変更後の値を取得）
```sql
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文による列の変更前、変更後の値が取得できることが確認できました。