Oracle Database 23c の独自構文を試す

　Oracle Database 23c は SQL 構文に大きな変更が加えられました。PostgreSQL や MySQL で実行可能な構文が利用できるようになっています。本記事では Oracle Database 23c に加えられた SQL 構文について検証しています。

FROM 句の省略

　従来はリテラルを返す SELECT 文でも FROM 句が必須でした。このため FROM DUAL 句を指定していました。Oracle Database 23c では FROM 句は必須ではなくなりました。

SQL> SELECT SYSDATE;

SYSDATE
----------
2023/04/05

実行計画を確認すると FAST DUAL と表示されます。

SQL> SET AUTOTRACE ON
SQL> SELECT SYSDATE;

SYSDATE
----------
2023/04/05

実行計画
----------------------------------------------------------
Plan hash value: 1388734953
-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        603  bytes sent via SQL*Net to client
        108  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

GROUP BY 句に番号指定

　GROUP BY 句にエイリアスや、ポジション番号を指定できます。ただしこの構文はデフォルトでは無効になっています。初期化パラメーター group_by_position_enabled をTRUE に変更してから実行します。以下の例ではデフォルト状態で失敗している SELECT 文が初期化パラメーターを設定してから成功しています。ポジション番号やエイリアスを使うことができています。

SQL> SELECT job_id, MAX(salary) FROM employees WHERE job_id LIKE 'AD%' GROUP BY 1;
SELECT job_id, MAX(salary) FROM employees WHERE job_id LIKE 'AD%' GROUP BY 1
       *
ERROR at line 1:
ORA-00979: "JOB_ID": must appear in the GROUP BY clause or be used in an
aggregate function

SQL> ALTER SESSION SET group_by_position_enabled = TRUE;
Session altered.

SQL> SELECT job_id, MAX(salary) FROM employees WHERE job_id LIKE 'AD%' GROUP BY 1;

JOB_ID     MAX(SALARY)
---------- -----------
AD_PRES          24000
AD_VP            17000
AD_ASST           4400

SQL> SELECT job_id j, MAX(salary) FROM employees WHERE job_id LIKE 'AD%' GROUP BY j;

J          MAX(SALARY)
---------- -----------
AD_PRES          24000
AD_VP            17000
AD_ASST           4400

オブジェクトの作成に IF EXISTS

　もしオブジェクトが存在した場合に削除する、オブジェクトが存在しなかった場合に作成するといった処理を自動的に行うための構文が追加されました。オブジェクト操作のための DDL 文に IF NOT EXISTS 句や IF EXISTS 句が使えます。これによりオブジェクトの操作失敗を防ぐことができます。

SQL> CREATE TABLE IF NOT EXISTS regions (region_id NUMBER CONSTRAINT region_id_nn NOT NULL, region_name VARCHAR2(25));
Table created.

SQL> ALTER TABLE IF EXISTS regions ADD area VARCHAR2(25);
Table altered.

SQL> DROP TABLE IF EXISTS regions;
Table dropped.

複数レコードの一括挿入

　INSERT 文に複数レコードを一括で挿入できます。指定されたレコード内でエラーが発生した場合は全レコードがエラー対象となります。

SQL> CREATE TABLE data1(c1 NUMBER PRIMARY KEY, c2 VARCHAR2(10));
Table created.

SQL> INSERT INTO data1 VALUES (100, 'data1'),(200, 'data2'),(300, 'data3');
3 rows created.

SQL> COMMIT;
Commit complete.

SQL> INSERT INTO data1 VALUES (400, 'data4'),(100, 'duplicate');
INSERT INTO data1 VALUES (400, 'data4'),(100, 'duplicate')
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C008305) violated

SQL> COMMIT;
Commit complete.

SQL> SELECT * FROM data1;
        C1 C2
---------- ----------
       100 data1
       200 data2
       300 data3

BOOLEAN 型

PL/SQL ではなく、テーブルの列データ型として BOOLEAN 型を使用できるようになりました。TRUE / FALSE / NULL を格納できます。入力時に TRUE, FALSE 以外の値を指定した場合、以下のルールで変換されます。

指定値	変換される値	備考
TRUE	TRUE
FALSE	FALSE
'true'	TRUE	文字列指定
'false'	FALSE	文字列指定
'YES'	TRUE	文字列指定
'NO'	FALSE	文字列指定
'y'	TRUE	文字列指定
'f'	FALSE	文字列指定
0	FALSE
1	TRUE
2	TRUE
'0'	FALSE	文字列指定
'1'	TRUE	文字列指定

検索時は「WHERE 列名」だけで検索できます。

SQL> CREATE TABLE data2(c1 BOOLEAN, c2 VARCHAR2(10));
Table created.

SQL> INSERT INTO data2 VALUES (TRUE, 'TRUE'),(FALSE, 'FALSE');
2 rows created.

SQL> INSERT INTO data2 VALUES (0, 'zero'),(1, 'one'),(NULL, 'NULL');
3 rows created.

SQL> SELECT * FROM data2;

C1          C2
----------- ----------
TRUE        TRUE
FALSE       FALSE
FALSE       zero
TRUE        one
null        NULL

SQL> SELECT * FROM data2 WHERE c1;

C1          C2
----------- ----------
TRUE        TRUE
TRUE        one

シンプル化された UPDATE

テーブルの結合結果を用いて UPDATE 文を記述できるようになりました。下記の構文は旧バージョンではエラー「ORA-00933: SQLコマンドが正しく終了されていません。」が発生していました。

SQL> CREATE TABLE src1(c1 NUMBER PRIMARY KEY, c2 VARCHAR2(10));
Table created.

SQL> INSERT INTO src1 VALUES (10, 'src1'),(20, 'src2'),(30, 'src3');
3 rows created.

SQL> COMMIT;
Commit complete.

SQL> CREATE TABLE dst1(c1 NUMBER PRIMARY KEY, c2 VARCHAR2(10));
Table created.

SQL> INSERT INTO dst1 VALUES (10, 'dst1'),(20, 'dst2'),(30, 'dst3');
3 rows created.

SQL> COMMIT;
Commit complete.

SQL> UPDATE dst1 d SET d.c2 = s.c2 FROM src1 s WHERE d.c1 = s.c1;
3 rows updated.

SQL> COMMIT;
Commit complete.

Author: Noriyoshi Shinoda / Date: April 5, 2023