[23c SQL新機能] とうとうboolean型の列が作成可能に！

環境

・Oracle Database 23c
　※DBエディションやOSによって差異はなし

やれること

23cより前までは、Oracleのテーブル作成では、boolean型の列が作成できませんでした。
そのため真偽値を扱いたいときは、CHAR型はNUMBER形で 1 や 0 に置き換えたりしていました。

23cでは、boolean型の列を作成できるようになりました。
NULLの扱いや、使えなかった時からの移行についてもみてみます。

実行例

(1) BOOLEAN型のあるテーブル作成

テーブル作成とデータの挿入。
trueを入れたいときは、そのまま true、
falseを入れたいときは、そのまま false と書きます。
文字ではないため、シングルクォーテーションで囲わないように注意。

SQL> CREATE TABLE testbool ( id NUMBER , val BOOLEAN );

Table created.

SQL> INSERT INTO testbool values (1 , true);

1 row created.

SQL> INSERT INTO testbool values(2 , false);

1 row created.

(2) true/falseを検索してみる

trueの検索は、列名を書くだけ。
falseの検索は、not 列名。

SQL> SELECT * FROM testbool WHERE val;

        ID VAL
---------- -----------
         1 TRUE

SQL> SELECT * FROM testbool WHERE not val;

        ID VAL
---------- -----------
         2 FALSE

(3) nullの扱いを確認してみる

nullは trueでもfalseでもないため、trueの検索、falseの検索でも条件にあてはまりません。
他のデータ型と同じように is null , is not nullで nullに関する条件を指定します。

SQL> INSERT INTO testbool values (3 , null);

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT * FROM testbool WHERE val;

        ID VAL
---------- -----------
         1 TRUE

SQL>  SELECT * FROM testbool WHERE not val;

        ID VAL
---------- -----------
         2 FALSE

SQL>  SELECT * FROM testbool WHERE val is null;

        ID VAL
---------- -----------
         3

SQL>  SELECT * FROM testbool WHERE val is not null;

        ID VAL
---------- -----------
         1 TRUE
         2 FALSE

(4) BOOLEAN型のデータ型変換

TO_BOOLEAN , TO_CHAR , TO_NUMBERでBoolean形とのデータ型変換が可能です。
デフォルトで true が 1 、 false が 0 として扱われます。

SQL> SELECT id , val , to_number(val) , to_char(val) FROM testbool;

        ID VAL         TO_NUMBER(VAL) TO_CHAR(VAL)
---------- ----------- -------------- -----
         1 TRUE                     1 TRUE
         2 FALSE                    0 FALSE
         3

(5) 過去のSQLを変更する必要があるか？

いままでboolean型が使えなかったため、CHAR型の列を使って、1と0でboolean型を再現していることが多々あるかと思います。

その時のSQL[select * from testbool where val = '1';]はそのまま使えるのでしょうか？

もちろん推奨されるのは明示的なデータ型変換ですが、暗黙的なデータ型変換が行われるため、いままでのSQLでも使用可能です。

SQL> select * from testbool where val = '1';

        ID VAL
---------- -----------
         1 TRUE

autotraceで実行計画をとってみましたが、 val = '1' が val = true に変換されているようです。

SQL> set autotrace on
SQL> select * from testbool where val = '1';

～省略～

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("VAL"=TRUE)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)