Boolean Field in Oracle

 

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

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

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

  • 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);`

    Oracle itself uses Y/N for Boolean values. For completeness it should be noted that pl/sql has a boolean type, it is only tables that do not.

    If you are using the field to indicate whether the record needs to be processed or not you might consider using Y and NULL as the values. This makes for a very small (read fast) index that takes very little space.

    To use the least amount of space you should use a CHAR field constrained to 'Y' or 'N'. Oracle doesn't support BOOLEAN, BIT, or TINYINT data types, so CHAR's one byte is as small as you can get.

    链接地址: http://www.djcxy.com/p/25138.html

    上一篇: 在C中使用布尔值

    下一篇: Oracle中的布尔字段