今回は急遽予定を変更して、Oracle DatabaseにおけるBoolean(論理)型について調べてみました。
0. はじめに
私はOracle Databaseを9iから触り始めていて、Oracle DatabaseではSQLでTRUE/FALSEを扱うBoolean型を使用できないと刷り込まれていたのですが、実は26ai(23ai)からBoolean型を扱える様になっていることに、今更ながら気が付きました！
ということで、今回はOracle AI Database 26ai(23.26.1)を使用して、Boolean型が使えるようになっていることを紹介したいと思います。
1. マニュアルの比較
Boolean型について記載されているマニュアル「データベースPL/SQL言語リファレンス」について、Oracle Database 19cでは、SQLにおけるBoolean型について以下の様な記載があり、SQLでBoolean型が扱えないことがわかります。
SQLにはBOOLEANに相当するデータ型がないため、次の操作は実行できません。
- データベース表の列にBOOLEAN値を割り当てる操作
- データベース表の列の値を選択またはフェッチしてBOOLEAN変数に入れる操作
- SQLファンクションでBOOLEAN値を使用する操作
- SQL文(SQL問合せで起動されるPL/SQLファンクションの引数を除く)またはPL/SQL無名ブロックでのBOOLEAN式の使用
一方、Oracle AI Database 26aiでは、同マニュアルから上記のような制約の記載が無くなっています。また、Oracle AI Database 26aiのマニュアル「Oracle AI Database新機能」に、以下の様にBoolean型がサポートされる旨の記載があることから、Boolean型を取り扱えるようになったことがわかります。
Oracle AI Databaseでは、ISO SQL標準準拠のBOOLEANデータ型がサポートされるようになりました。これにより、TRUE値およびFALSE値を表に格納したり、SQL文でBOOLEAN式を使用できます。
2. 実機確認
実際にBoolean型を使ってみた結果をいくつか紹介します。
2.1 Boolean型データの使用と見え方
まずはBoolean型のカラム(col2)を持ったテーブルを作成し、レコードをINSERTしてみます。Boolean型のカラムを作成する際はデータ型として「BOOLEAN」または「BOOL」と指定します。レコードをINSERTする際は、Boolean型なので、TRUEまたはFALSEと指定します。NOT NULL制約がなければNULLも指定可能です。
-- Boolean型を持ったテーブルの作成 SQL> CREATE TABLE tabB(col1 NUMBER, col2 BOOLEAN); 表が作成されました。 -- Boolean型データのINSERT SQL> INSERT INTO tabB VALUES(1, TRUE); 1行が作成されました。 SQL> INSERT INTO tabB VALUES(2, FALSE); 1行が作成されました。 SQL> INSERT INTO tabB VALUES(3, NULL); 1行が作成されました。 SQL> COMMIT; コミットが完了しました。
実際にレコードを取り出してみると、Boolean型の部分は非NULLであればTRUE/FALSEと表示されます。WHERE句で指定する際は、Boolean型なのでTRUEであることを条件とするのであれば「カラム名」だけを、FALSEであることを条件とするのであれば「NOT カラム」名と指定します。
-- Boolean型の見え方
SQL> SELECT * FROM tabB;
COL1 COL2
---------- -----------
1 TRUE
2 FALSE
3
-- 指定カラムがTRUEのレコードのみを取得するSELECT例
SQL> SELECT * FROM tabB WHERE col2;
COL1 COL2
---------- -----------
1 TRUE
-- 指定カラムがFALSEのレコードのみを取得するSELECT例
SQL> SELECT * FROM tabB WHERE NOT col2;
COL1 COL2
---------- -----------
2 FALSE
2.2 数値型における扱い
Boolean型がなかった頃は数値の0をFALSEに、数値の1をTRUEに見立ててSQLを作成したことがある方もいらっしゃるかと思いますが、Boolean型はこの見立てが正式に採用されており、数値の0はFALSEに、数値の1はTRUEに(暗黙的に)変換され比較等を行うことができます。正確には、0はFALSE、0以外の数値はTRUEとして扱われます。
-- 0はFALSEの扱い SQL> SELECT * FROM tabB WHERE col2 = 0; COL1 COL2 ---------- ----------- 2 FALSE -- 0以外はTRUEの扱い SQL> SELECT * FROM tabB WHERE col2 = 1; COL1 COL2 ---------- ----------- 1 TRUE SQL> SELECT * FROM tabB WHERE col2 = 2; COL1 COL2 ---------- ----------- 1 TRUE SQL> SELECT * FROM tabB WHERE col2 = 3; COL1 COL2 ---------- ----------- 1 TRUE
2.3 文字型における扱い
Boolean型はTRUE/FALSEと表示されますが、そのまま(暗黙的な型変換により)'TRUE'や'FALSE'という文字列として比較等で扱うことができます。また、'TRUE'や'FALSE'以外に以下の文字列(大文字小文字問わず)も扱うことができますが、それ以外の文字列はエラーとなります。
| TRUE | FALSE |
|---|---|
| 'YES' | 'NO' |
| 'ON' | 'OFF' |
| '1' | '0' |
| 'T' | 'F' |
| 'Y' | 'N' |
以下は'TRUE'と'FALSE'とそれ以外の文字列とBoolean型の列を比較してみた結果となります。
-- 'TRUE'という文字列はTRUEの扱い SQL> SELECT * FROM tabB WHERE col2 = 'TRUE'; COL1 COL2 ---------- ----------- 1 TRUE -- 'FALSE'という文字列はFALSEの扱い SQL> SELECT * FROM tabB WHERE col2 = 'FALSE'; COL1 COL2 ---------- ----------- 2 FALSE -- Boolean型に無関係な文字列はエラー SQL> SELECT * FROM tabB WHERE col2 = 'AAA'; SELECT * FROM tabB WHERE col2 = 'AAA' * 行1でエラーが発生しました。: ORA-61800: 無効なブール・リテラル: AAA ヘルプ: https://docs.oracle.com/error-help/db/ora-61800/
2.4 ファンクションを通したBoolean型の利用
Boolean型が扱える様になったことで、PL/SQLで作成したファンクションの返り値をBoolean型にして、それをSQL文の中でそのまま使用できることもできます。以下は実行したときの時刻の秒数が偶数ならFALSE、奇数ならTRUEを返すファンクションを用意して、それをWHERE句で呼び出した例となります。
-- Boolean型を返すファンクションの作成 SQL> CREATE OR REPLACE FUNCTION get_bl RETURN BOOLEAN 2 IS 3 v_sec NUMBER; 4 BEGIN 5 SELECT TO_NUMBER(TO_CHAR(SYSTIMESTAMP, 'SS')) INTO v_sec FROM dual; 6 IF MOD(v_sec, 2) = 0 THEN 7 RETURN FALSE; 8 ELSE 9 RETURN TRUE; 10 END IF; 11 END; 12 / ファンクションが作成されました。 -- ファンクションの返り値がTRUEなのでレコードが出力される例 SQL> SELECT * FROM tabB WHERE get_bl(); COL1 COL2 ---------- ----------- 1 TRUE 2 FALSE 3 -- ファンクションの返り値がFALSEなのでレコードが出力されない例 SQL> SELECT * FROM tabB WHERE get_bl(); レコードが選択されませんでした。
3.さいごに
今回は26ai(23ai)から使用できる様になったBoolean型について調べてみました。Boolean型に関する詳細について知りたい場合は マニュアル「Oracle AI Database SQL言語リファレンス 26ai」をご参照ください。
前の記事：Oracle Database Vaultの構成方法について調べてみた
