Oracle sql语句中不支持boolean类型（decode&case）

[转自] http://blog.csdn.net/t0nsha/article/details/7828538

Oracle sql语句中不支持boolean类型（decode&case）

版本信息：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

假设我们现在想知道1>0是否为真：

直接查，不行！

SQL> select 1>0 from dual;
select 1>0 from dual
        *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

用decode转，还是不行！

SQL> select decode(1>0,true,'true','false') from dual;
select decode(1>0,true,'true','false') from dual
               *
ERROR at line 1:
ORA-00907: missing right parenthesis

用case转，依旧不行！

SQL> select case 1>0 when true then 'true' else 'false' end from dual;
select case 1>0 when true then 'true' else 'false' end from dual
            *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

原因在于sql不支持boolean类型（手头没有其他库，不知道mysql和sqlserver是否支持）：

SQL> create or replace function is_true return boolean
is
begin
return true;
end;
/

Function created.

SQL> select is_true from dual;
select is_true from dual
       *
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type

直接放sql语句中行不通，试着放到fuction里：
decode还是出错：

SQL> CREATE OR REPLACE FUNCTION is1gt0
  RETURN VARCHAR2
IS
BEGIN
  RETURN DECODE (1 > 0, TRUE, 'true', 'false');
END;
/

Warning: Function created with compilation errors.

SQL> show err;
Errors for FUNCTION IS1GT0:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3      PL/SQL: Statement ignored
5/10     PLS-00306: wrong number or types of arguments in call to 'DECODE'
SQL>

case完美通过：

SQL> CREATE OR REPLACE FUNCTION is1gt0
  RETURN VARCHAR2
IS
BEGIN
  RETURN CASE 1 > 0
    WHEN TRUE
      THEN 'true'
    ELSE 'false'
  END;
END;
/

Function created.

SQL> show err;
No errors.
SQL> select is1gt0 from dual;

IS1GT0
--------------------------------------------------------------------------------
true

SQL>

小结：
1. Oracle sql语句中不支持boolean类型；
2. decode是oracle独有的；而case是标准sql，mysql和sqlserver也可以使用，而且case还能把boolean转换输出。


REF:
1.Decode function to Oracle 7
http://www.groupsrv.com/computers/about56979.html