【Oracle26ai新機能】BOOLEAN型の使い方｜格納できる値と、0以外の数値がTRUEになる注意点【実行ログ付き】

SQL> CREATE TABLE V26_APP.B (id NUMBER, flag BOOLEAN);

Table created.

SQL> INSERT INTO V26_APP.B VALUES (1, TRUE), (2, FALSE), (3, NULL), (4, 'yes'), (5, 0), (6, 'off'), (7, 1), (8, 't');

8 rows created.

SQL> SELECT id, flag FROM V26_APP.B ORDER BY id;

        ID FLAG
---------- -----------
         1 TRUE
         2 FALSE
         3
         4 TRUE
         5 FALSE
         6 FALSE
         7 TRUE
         8 TRUE

8 rows selected.

SQL> INSERT INTO V26_APP.B VALUES (9, 'maybe');

INSERT INTO V26_APP.B VALUES (9, 'maybe')
                                 *
ERROR at line 1:
ORA-61800: invalid boolean literal: maybe

SQL> INSERT INTO V26_APP.B VALUES (10, 2);

1 row created.

SQL> SELECT id, flag FROM V26_APP.B ORDER BY id;

        ID FLAG
---------- -----------
         1 TRUE
         2 FALSE
         3
         4 TRUE
         5 FALSE
         6 FALSE
         7 TRUE
         8 TRUE
        10 TRUE

9 rows selected.

SQL> CREATE TABLE V26_BLOG.n (v NUMBER, b BOOLEAN);

Table created.

SQL> INSERT INTO V26_BLOG.n VALUES (-1, -1), (0, 0), (0.5, 0.5), (1, 1), (2, 2), (100, 100), (NULL, NULL);

7 rows created.

SQL> SELECT v, b FROM V26_BLOG.n ORDER BY v NULLS LAST;

         V B
---------- -----------
        -1 TRUE
         0 FALSE
        .5 TRUE
         1 TRUE
         2 TRUE
       100 TRUE


7 rows selected.

SQL> SELECT CAST(-1 AS BOOLEAN) AS m1, CAST(0.5 AS BOOLEAN) AS half, CAST(2 AS BOOLEAN) AS two, CAST(100 AS BOOLEAN) AS hundred;

M1          HALF        TWO         HUNDRED
----------- ----------- ----------- -----------
TRUE        TRUE        TRUE        TRUE

1 row selected.

SQL> SELECT TO_BOOLEAN(2) AS b;

B
-----------
TRUE

1 row selected.

SQL> SELECT TO_BOOLEAN('2') AS b;

SELECT TO_BOOLEAN('2') AS b
       *
ERROR at line 1:
ORA-61800: invalid boolean literal: 2

SQL> SELECT id, flag IS TRUE AS is_t, flag IS NOT FALSE AS not_f, TO_CHAR(flag) AS chr, TO_NUMBER(flag) AS num FROM V26_APP.B ORDER BY id;

        ID IS_T        NOT_F       CHR          NUM
---------- ----------- ----------- ----- ----------
         1 TRUE        TRUE        TRUE           1
         2 FALSE       FALSE       FALSE          0
         3 FALSE       TRUE
         4 TRUE        TRUE        TRUE           1
         5 FALSE       FALSE       FALSE          0
         6 FALSE       FALSE       FALSE          0
         7 TRUE        TRUE        TRUE           1
         8 TRUE        TRUE        TRUE           1

8 rows selected.

SQL> SELECT s, TO_BOOLEAN(s) AS b FROM (VALUES ('true'), ('TRUE'), ('yes'), ('y'), ('on'), ('1'), ('false'), ('no'), ('n'), ('off'), ('0')) v(s);

S        B
-------- -----------
true     TRUE
TRUE     TRUE
yes      TRUE
y        TRUE
on       TRUE
1        TRUE
false    FALSE
no       FALSE
n        FALSE
off      FALSE
0        FALSE

11 rows selected.

SQL> SELECT TO_BOOLEAN('Y') AS b;

B
-----------
TRUE

1 row selected.

SQL> SELECT CAST('true' AS BOOLEAN) AS c, CAST(0 AS BOOLEAN) AS z, CAST(TRUE AS NUMBER) AS n, CAST(FALSE AS VARCHAR2(10)) AS v;

C           Z                    N V
----------- ----------- ---------- ----------
TRUE        FALSE                1 FALSE

1 row selected.

SQL> SELECT 1 > 0 AS gt, 1 = 2 AS eq, (1 > 0) AND (2 > 3) AS a, (1 > 0) OR NULL AS o, NOT (1 > 0) AS n;

GT          EQ          A           O           N
----------- ----------- ----------- ----------- -----------
TRUE        FALSE       FALSE       TRUE        FALSE

1 row selected.

SQL> CREATE TABLE V26_BLOG.task (id NUMBER PRIMARY KEY, title VARCHAR2(20), done BOOLEAN DEFAULT FALSE NOT NULL);

Table created.

SQL> INSERT INTO V26_BLOG.task (id, title) VALUES (1, 'a');

1 row created.

SQL> INSERT INTO V26_BLOG.task VALUES (2, 'b', TRUE), (3, 'c', 'yes');

2 rows created.

SQL> INSERT INTO V26_BLOG.task VALUES (4, 'd', NULL);

INSERT INTO V26_BLOG.task VALUES (4, 'd', NULL)
                                          *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("V26_BLOG"."TASK"."DONE")

SQL> CREATE INDEX V26_BLOG.task_done_ix ON V26_BLOG.task (done);

Index created.

SQL> SELECT id, title, done FROM V26_BLOG.task ORDER BY done, id;

        ID TITLE                DONE
---------- -------------------- -----------
         1 a                    FALSE
         2 b                    TRUE
         3 c                    TRUE

3 rows selected.

SQL> SELECT COUNT(*) FILTER (WHERE done) AS done_cnt, COUNT(*) FILTER (WHERE NOT done) AS open_cnt FROM V26_BLOG.task;

  DONE_CNT   OPEN_CNT
---------- ----------
         2          1

1 row selected.

SQL> UPDATE V26_BLOG.task SET done = NOT done WHERE id = 1;

1 row updated.

SQL> SELECT id, done FROM V26_BLOG.task WHERE id = 1;

        ID DONE
---------- -----------
         1 TRUE

1 row selected.

SQL> CREATE FUNCTION V26_BLOG.is_even(n NUMBER) RETURN BOOLEAN IS BEGIN RETURN MOD(n, 2) = 0; END;

Function created.

SQL> SELECT id, V26_BLOG.is_even(id) AS even FROM V26_BLOG.task ORDER BY id;

        ID EVEN
---------- -----------
         1 FALSE
         2 TRUE
         3 FALSE

3 rows selected.

SQL> DECLARE b BOOLEAN; BEGIN SELECT done INTO b FROM V26_BLOG.task WHERE id = 2; DBMS_OUTPUT.PUT_LINE('PL/SQL got: ' || CASE WHEN b THEN 'TRUE' ELSE 'FALSE' END); END;

PL/SQL got: TRUE

PL/SQL procedure successfully completed.