Oracle 中的布尔字段

昨天我想向 Oracle 表中添加一个布尔字段.但是，Oracle 中实际上没有布尔数据类型.这里有人知道模拟布尔值的最佳方法吗?谷歌搜索这个主题发现了几种方法

1. 使用整数，并且不要为它分配 0 或 1 以外的任何值.

2. 使用带有Y"或N"作为仅有的两个值的字符字段.

3. 使用带有 CHECK 约束的枚举.

有经验的 Oracle 开发人员是否知道哪种方法是首选/规范的?

解决方案

我发现 这个链接很有用.

这是突出显示每种方法的一些优点/缺点的段落.

<块引用>

最常见的设计是模仿许多类似布尔的设计Oracle 的数据字典视图使用的标志，选择Y"为真'N' 表示假.但是，要与主机正确交互环境，例如 JDBC、OCCI 和其他编程环境，最好为 false 选择 0，为 true 选择 1，以便它可以工作正确使用 getBoolean 和 setBoolean 函数.

基本上他们提倡方法2，为了效率，使用

• 值为 0/1(因为与 JDBC 的 getBoolean() 等的互操作性)，带有检查约束
• 一个类型的CHAR(因为它使用的空间少于NUMBER).

他们的例子:

<块引用>

create table tbool (bool char check (bool in (0,1));插入 tbool 值(0)；插入到 tbool 值(1);`

Yesterday I wanted to add a boolean field to an Oracle table. However, there isn't actually a boolean data type in Oracle. Does anyone here know the best way to simulate a boolean? Googling the subject discovered several approaches

1. Use an integer and just don't bother assigning anything other than 0 or 1 to it.

2. Use a char field with 'Y' or 'N' as the only two values.

3. Use an enum with the CHECK constraint.

Do experienced Oracle developers know which approach is preferred/canonical?

解决方案

I found this link useful.

Here is the paragraph highlighting some of the pros/cons of each approach.

The most commonly seen design is to imitate the many Boolean-like flags that Oracle's data dictionary views use, selecting 'Y' for true and 'N' for false. However, to interact correctly with host environments, such as JDBC, OCCI, and other programming environments, it's better to select 0 for false and 1 for true so it can work correctly with the getBoolean and setBoolean functions.

Basically they advocate method number 2, for efficiency's sake, using

• values of 0/1 (because of interoperability with JDBC's getBoolean() etc.) with a check constraint
• a type of CHAR (because it uses less space than NUMBER).

Their example:

create table tbool (bool char check (bool in (0,1));
insert into tbool values(0);
insert into tbool values(1);`