AIです。（Artificial Intelligence ではありません）
最近スーパーでマイナーなお肉が売られているのを発見し、ちょっと興味がわいているこの頃です。カンガルー肉…エルク肉…どんなお味なんでしょうか…
さて、今回は 23ai から新たに追加された ブール・データ型やブール式について、見ていきます！
■ブール・データ型（BOOLEAN データ型）とは
表内に TRUE / FALSE で値を格納するデータ型です。このデータ型を使うと SQL 文でブール式を使った条件を指定することができるようになります。
また、TRUE / FALSE に変換可能な文字をブール・データ型の列に格納した場合、自動で TRUE / FALSE に変換されて格納されます。
■文字の自動変換
以下の文字列リテラルをブール・データ型の列に格納した場合、それぞれ以下のように TRUE/FALSE に変換されます。
TRUE FALSE
-------------- --------------
'true' 'false'
'yes' 'no'
'on' 'off'
0以外の数値 '0'
't' 'f'
'y' 'n'では、実際に表に値を格納してみましょう。
SQL> CREATE TABLE Woodcutter.bool_test (id number(6),value varchar2(100),bool_value boolean);
表が作成されました。
SQL> INSERT INTO Woodcutter.bool_test VALUES(1,'TRUE','TRUE'); ★value(VARCHAR2データ型) と bool_value(BOOLEANデータ型)に同じ値を格納
1行が作成されました。
SQL> INSERT INTO Woodcutter.bool_test VALUES(2,'FALSE','FALSE');
1行が作成されました。
SQL> INSERT INTO Woodcutter.bool_test VALUES(3,'ON','ON');
1行が作成されました。
SQL> INSERT INTO Woodcutter.bool_test VALUES(4,'OFF','OFF');
1行が作成されました。
SQL> INSERT INTO Woodcutter.bool_test VALUES(5,'1',1);
1行が作成されました。
SQL> INSERT INTO Woodcutter.bool_test VALUES(6,'0',0);
1行が作成されました。
SQL> INSERT INTO Woodcutter.bool_test VALUES(7,'YES','YES');
1行が作成されました。
SQL> INSERT INTO Woodcutter.bool_test VALUES(8,'NO','NO');
1行が作成されました。
SQL> INSERT INTO Woodcutter.bool_test VALUES(9,'T','T');
1行が作成されました。
SQL> INSERT INTO Woodcutter.bool_test VALUES(10,'F','F');
1行が作成されました。
SQL> INSERT INTO Woodcutter.bool_test VALUES(11,'Y','Y');
1行が作成されました。
SQL> INSERT INTO Woodcutter.bool_test VALUES(12,'N','N');
1行が作成されました。
SQL> SELECT * FROM Woodcutter.bool_test;
ID VALUE BOOL_VALUE
---------- ---------- -----------
1 TRUE TRUE
2 FALSE FALSE
3 ON TRUE ★それぞれの値に応じて TRUE / FALSE として格納されています。
4 OFF FALSE
5 1 TRUE
6 0 FALSE
7 YES TRUE
8 NO FALSE
9 T TRUE
10 F FALSE
11 Y TRUE
12 N FALSE
12行が選択されました。表に記載の通りに ブール・データ型の列は、TRUE / FALSE に変換されました！
■ブール式を使用した SELECT
ブール・データ型の実装と併せて、SQL 内でブール式を使用することができるようになりました。
例えば、先ほど作成した表から BOOL_VALUE が TRUE の値のレコードのみ SELECT したい場合
以下の様に IS TRUE を使って条件を指定することができます。
SQL> SELECT * FROM Woodcutter.bool_test WHERE bool_value IS TRUE;
ID VALUE BOOL_VALUE
---------- ---------- -----------
1 TRUE TRUE
3 ON TRUE
5 1 TRUE
7 YES TRUE
9 T TRUE
11 Y TRUE
6行が選択されました。IS NOT TRUE を使うこともできます。
SQL> SELECT * FROM Woodcutter.bool_test WHERE bool_value IS NOT TRUE;
ID VALUE BOOL_VALUE
---------- ---------- -----------
2 FALSE FALSE
4 OFF FALSE
6 0 FALSE
8 NO FALSE
10 F FALSE
12 N FALSE
6行が選択されました。もちろん IS FALSE や IS NOT FALSE も利用可能です。
また、IS をつけなくてもブール・データ型は列名のみを条件として指定することが可能です。この場合、TRUE でないものを選択したい場合、NOT <ブール・データ型の列名>と指定します。
SQL> SELECT * FROM Woodcutter.bool_test WHERE bool_value; ★列名だけの指定
ID VALUE BOOL_VALUE
---------- ---------- -----------
1 TRUE TRUE
3 ON TRUE
5 1 TRUE
7 YES TRUE
9 T TRUE
11 Y TRUE
6行が選択されました。
SQL> SELECT * FROM Woodcutter.bool_test WHERE NOT bool_value;
ID VALUE BOOL_VALUE
---------- ---------- -----------
2 FALSE FALSE
4 OFF FALSE
6 0 FALSE
8 NO FALSE
10 F FALSE
12 N FALSE
6行が選択されました。■ブール・データ型ではない列に対してブール式を使用
更に、ブール・データ型ではない列に対してもブール式で問合せをすることが可能です。その場合「■文字の自動変換」で記載した文字列に従って TRUE/FALSE が判断されます。
SQL> SELECT * FROM Woodcutter.bool_test WHERE value IS FALSE;
ID VALUE BOOL_VALUE
---------- ---------- -----------
2 FALSE FALSE
4 OFF FALSE
6 0 FALSE
8 NO FALSE
10 F FALSE
12 N FALSE
6行が選択されました。
SQL> SELECT * FROM Woodcutter.bool_test WHERE value;
ID VALUE BOOL_VALUE
---------- ---------- -----------
1 TRUE TRUE
3 ON TRUE
5 1 TRUE
7 YES TRUE
9 T TRUE
11 Y TRUE
6行が選択されました。これはつまり、dba_users などの静的ディクショナリビューで Y/N や YES/NO で
データが格納されている列に対してもブール式で問合せができるということですね…！
SQL> desc dba_users
名前 NULL? 型
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(128)
:
COMMON VARCHAR2(3)★
SQL> SELECT username,common FROM dba_users WHERE common;
USERNAME COM
------------------------- ---
SYS YES
SYSTEM YES
SYSRAC YES
:(一部割愛)
SYSKM YES
SYS$UMF YES
SYSDG YES
33行が選択されました。■ブール・データ型に変換できないデータの場合
さて、ブール・データ型やブール式の特徴について見てきましたが、次はブール・データ型に変換できないデータが入っていた場合の挙動を確認します。
・ブール・データ型の列に変換できないデータを挿入した場合
SQL> INSERT INTO Woodcutter.bool_test VALUES(13,'AI','I');
INSERT INTO Woodcutter.bool_test VALUES(13,'AI','I')
*
行1でエラーが発生しました。:
ORA-61800: 無効なブール・リテラル: I ヘルプ:
https://docs.oracle.com/error-help/db/ora-61800/・ブール式で条件指定した列にブール・データ型に変換できないデータが入っていた場合
SQL> SELECT * FROM Woodcutter.bool_test;
ID VALUE BOOL_VALUE
---------- ---------- -----------
1 TRUE TRUE
2 FALSE FALSE
3 ON TRUE
4 OFF FALSE
5 1 TRUE
6 0 FALSE
7 YES TRUE
8 NO FALSE
9 T TRUE
10 F FALSE
11 Y TRUE
12 N FALSE
13 AI TRUE
13行が選択されました。
SQL> SELECT * FROM Woodcutter.bool_test WHERE value;
ERROR:
ORA-61800: 無効なブール・リテラル: AI ヘルプ:
https://docs.oracle.com/error-help/db/ora-61800/
レコードが選択されませんでした。いずれも ORA-61800 が返されました。
■AND / OR 条件
ブール式での条件を二つ以上組み合わせて AND/OR 条件で絞ることもできます。
SQL> SELECT * FROM Woodcutter.bool_test2;
ID BOOL_VALUE1 BOOL_VALUE2
---------- ----------- -----------
1 TRUE TRUE
2 TRUE FALSE
3 FALSE TRUE
4 FALSE FALSE
SQL> SELECT * FROM Woodcutter.bool_test2 WHERE bool_value1 AND bool_value2;
ID BOOL_VALUE1 BOOL_VALUE2
---------- ----------- -----------
1 TRUE TRUE
SQL> SELECT * FROM Woodcutter.bool_test2 WHERE bool_value1 OR bool_value2;
ID BOOL_VALUE1 BOOL_VALUE2
---------- ----------- -----------
1 TRUE TRUE
2 TRUE FALSE
3 FALSE TRUE■TO_BOOLEAN 関数
また、ブール・データ型に変換可能なデータであれば、TO_BOOLEAN 関数を使ってブール・データ型として表示させることも可能です。
SQL> SELECT TO_BOOLEAN(1),TO_BOOLEAN('ON'),TO_BOOLEAN('F');
TO_BOOLEAN( TO_BOOLEAN( TO_BOOLEAN(
----------- ----------- -----------
TRUE TRUE FALSE
SQL> SELECT TO_BOOLEAN(1),TO_BOOLEAN('ON'),TO_BOOLEAN('F'),TO_BOOLEAN('I');
SELECT TO_BOOLEAN(1),TO_BOOLEAN('ON'),TO_BOOLEAN('F'),TO_BOOLEAN('I')
*
行1でエラーが発生しました。:
ORA-61800: 無効なブール・リテラル: I ヘルプ:
https://docs.oracle.com/error-help/db/ora-61800/仮想列で TO_BOOLEAN 関数を使えば既存のデータを BOOLEAN データ型で表示させることも簡単にできます。
SQL> ALTER TABLE Woodcutter.bool_test ADD (fuction_value AS (TO_BOOLEAN(value)));
表が変更されました。
SQL> SELECT * FROM Woodcutter.bool_test;
ID VALUE BOOL_VALUE FUCTION_VAL
---------- --------- ----------- -----------
1 TRUE TRUE TRUE
2 FALSE FALSE FALSE
3 ON TRUE TRUE
4 OFF FALSE FALSE
5 1 TRUE TRUE
6 0 FALSE FALSE
7 YES TRUE TRUE
8 NO FALSE FALSE
9 T TRUE TRUE
10 F FALSE FALSE
11 Y TRUE TRUE
12 N FALSE FALSE
12行が選択されました。■おわりに
さて、ブール・データ型およびブール式について色々と検証してきましたがいかがでしょうか。
AI的には、ブール・データ型が実装されたことにより、今まで静的ディクショナリビューでTRUE/FALSE で表現できそうなデータに変更が出るのか気になっていましたが、特定の文字列であれば特に意識することなくブール型で条件を指定できることが驚きでした！
今後、調査で静的ディクショナリビューや v$ ビューを見るときの SQL の作り方にも影響しそうですね。