26ai新特性实战：BOOLEAN 数据类型

作者：胖头鱼的鱼缸（尹海文）
Oracle ACE Pro: Database
PostgreSQL ACE

10 年 + 数据库行业经验
拥有 OCM 11g/12c/19c、MySQL 8.0 OCP、Exadata、CDP 等认证
墨天轮 MVP，ITPUB 认证专家
圈内拥有“总监”称号，非著名社恐（社交恐怖分子）

全网同名：胖头鱼的鱼缸
ITPUB：yhw1809
除授权转载并标明出处外，均为“非法”抄袭

CREATE TABLE t (id NUMBER, flag BOOLEAN DEFAULT TRUE);
INSERT INTO t VALUES (1, TRUE);
SELECT * FROM t WHERE flag = TRUE;
UPDATE t SET flag = NOT flag WHERE id = 1;

CREATE TABLE test_bool (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(50),
  is_active BOOLEAN,
  is_vip    BOOLEAN
);

INSERT INTO test_bool VALUES (1, 'Alice', TRUE,  FALSE);
INSERT INTO test_bool VALUES (2, 'Bob',   FALSE, TRUE);
INSERT INTO test_bool VALUES (3, 'Carol', NULL,  NULL);

COMMIT;

SELECT id, name, is_active, is_vip
FROM test_bool
WHERE is_active = TRUE
ORDER BY id;

SELECT id, name, is_active, is_vip
FROM test_bool
WHERE NOT is_active
ORDER BY id;

SELECT
  id, name,
  is_active,
  is_vip,
  CASE WHEN is_active THEN 'Active' ELSE 'Inactive' END AS status,
  CASE WHEN is_vip    THEN 'VIP' ELSE 'Regular' END AS tier
FROM test_bool
ORDER BY id;

UPDATE test_bool
SET is_active = NOT is_active
WHERE id = 1;

COMMIT;

SELECT id, name, is_active, is_vip
FROM test_bool
WHERE id = 1;

CREATETABLE test_bool_defaults (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(50),
  is_enabled    BOOLEAN DEFAULT TRUE,
  is_deleted    BOOLEAN DEFAULT FALSE
);

INSERT INTO test_bool_defaults (id, name) VALUES (1, 'DefaultRow');
INSERT INTO test_bool_defaults (id, name, is_enabled, is_deleted)
VALUES (2, 'OverrideRow', FALSE, TRUE);

COMMIT;

SELECT id, name, is_enabled, is_deleted
FROM test_bool_defaults
ORDER BY id;

SELECT id, name, is_active, is_vip
FROM test_bool
WHERE is_active IS NULL
ORDER BY id;

SELECT id, name, is_active, is_vip
FROM test_bool
WHERE is_active IS NOT NULL
ORDER BY id;

SELECT id, name, is_active, is_vip
FROM test_bool
WHERE is_vip IS NOT NULL
ORDER BY id;