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

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