本文共 2191 字，大约阅读时间需要 7 分钟。
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转载地址：http://cjtai.baihongyu.com/