1、Oracle函数介绍：decode
Sql代码
select sum(DECODE(C810000125,'是',1,0))/COUNT(1) 合格率 FROM t581
统计合格率，如果 C810000125这个字段为“是”结果1，不为是结果为0
还可以这样写：
Sql代码
select sum(case when C810000125 = '是' then 1 else 0 end)/COUNT(1) 合格率 FROM t581
含义解释：
decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
该函数的含义如下：
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
......
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF
decode(字段或字段的运算，值1，值2，值3）
这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多
使用方法：
1、比较大小
select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
例如：
变量1=10，变量2=20
则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。
2、此函数用在SQL语句中，功能介绍如下：
Decode函数与一系列嵌套的 IF-THEN-ELSE语句相似。base_exp与compare1,compare2等等依次进行比较。
如果base_exp和 第i个compare项匹配，就返回第i 个对应的value 。如果base_exp与任何的compare值都不匹配，则返回default。
每个compare值顺次求值，如果发现一个匹配，则剩下的 compare值（如果还有的话）就都不再求值。
一个为NULL的base_exp被认为和NULL compare值等价。如果需要的话，每一个compare值都被转换成和第一个compare 值相同的数据类型，这个数据类型也是返回值的类型。
Decode函数在实际开发中非常的有用
结合Lpad函数，如何使主键的值自动加1并在前面补0
select LPAD(decode(count(记录编号),0,1,max(to_number(记录编号)+1)),14,'0') 记录编号 from tetdmis
eg:
select decode(dir,1,0,1) from a1_interval
dir 的值是1变为0，是0则变为1
比如我要查询某班男生和女生的数量分别是多少?
通常我们这么写:
select count(*) from 表 where 性别 ＝ 男；
select count(*) from 表 where 性别 ＝ 女；
要想显示到一起还要union一下，太麻烦了
用decode呢，只需要一句话
select sum(decode(性别，男，1，0))，sum(decode(性别，女，1，0)) from 表
补充：同事遇到一个问题，分组的条件是动态变化的，比如：一组数据最多按A、B、C三种条件分组，但是根据情况这三个条件会动态的参与到分组中，有8中情况，例如：按null，按A，按B，按C，按A、B，按A、C，按B、C，按A、B、C。
Sql如下
select t.e
,t.f
,decode(param1,'A',t.a,null) as A
,decode(param2,'B',t.b,null) as B
,decode(param3,'C',t.c,null)as C
from test t
group by
t.e,
t.f,
decode(param1,'A',t.a,null),
decode(param2,'B',t.b,null),
decode(param3,'C',t.c,null)
order by t.e
2、诡异的DECODE函数
今天同事遇到下面一个问题：
order by decode(column_id,1,null,2,null,3,null,column_id);
有个问题，就是当列数大于10列时，column_id 的顺序成10，11，12，13，4，5，6，7，8，9了
这个排序的主要目的是让前3列排在后面，这3列的顺序无所谓。
对于小于10列的表是没问题的：
SQL> create table t(c1 number,c2 number,c3 number,c4number,c5 number);
表已创建。
SQL> col column_name format a20
SQL> select column_name,column_id
2 from user_tab_columns
3 where table_name='T'
4 order bydecode(column_id,1,null,2,null,3,null,column_id);
COLUMN_NAME COLUMN_ID
-------------------- ----------
C4 4
C5 5
C2 2
C1 1
C3 3
但是当表的列数大于10的时候就会混乱了。
SQL> select column_name,column_id,decode(column_id,1,null,2,null,3,null,column_id) sortcolumn
2 from user_tab_columns
3 where table_name='T'
4 order bydecode(column_id,1,null,2,null,3,null,column_id)
5 /
COLUMN_NAME COLUMN_IDSORTCOLUMN
-------------------- ---------- --------------------
C10 10 10
C11 11 11
C12 12 12
C4 4 4
C5 5 5
C6 6 6
C7 7 7
C8 8 8
C9 9 9
C3 3
C2 2
C1 1
已选择12行。
显然ORACLE把SORTCOLUMN列作为为字符类型排序了。
加个TO_NUMBER即可解决这个问题。
SQL> select column_name,column_id,decode(column_id,1,null,2,null,3,null,column_id) sortcolumn
2 from user_tab_columns
3 where table_name='T'
4 order byto_number(decode(column_id,1,null,2,null,3,null,column_id));
COLUMN_NAME COLUMN_IDSORTCOLUMN
-------------------- ---------- --------------------
C4 4 4
C5 5 5
C6 6 6
C7 7 7
C8 8 8
C9 9 9
C10 10 10
C11 11 11
C12 12 12
C1 1
C3 3
C2 2
已选择12行。
但是为什么会导致这个问题，DECODE函数为何返回了字符类型。
这个问题yangtingkun大师专门写个几篇文章介绍。
有兴趣的可以找找看看。
在这里我借花献佛简单稍微说一下：
对于NULL 类型,ORACLE的默认返回类型是VARCHAR。
对于DECODE函数 ORACLE返回的类型依赖于第一个值。
如下所示：
SQL> CREATE TABLE A AS SELECTDECODE(DUMMY,'X',1,'Y','2',DUMMY) C1, <---由于第一个返回的值1是整数类型，因此整个表达式返回整数类型
2 DECODE(DUMMY,'X','1','Y',2,DUMMY) C2 ,<---由于第一个返回的值'1'是字符类型，因此整个表达式返回CHAR类型
3 DECODE(DUMMY,'X',NULL,'Y','HUATENG',DUMMY)C3 FROM DUAL; <---由于第一个返回的值是NULL，因此整个表达式返回CHAR类型
表已创建。
SQL> DESC A
名称 是否为空? 类型
----------------------------------------- ------------------------------------
C1 NUMBER
C2 VARCHAR2(1)
C3 VARCHAR2(7)
也正是因为DECODE函数的这种依赖于第一次的值类型作为返回类型，对于其他返回的值如果和第一个类型不匹配，可能会让你遇到很蛋疼的问题：
SQL> DESC A;
名称 是否为空? 类型
----------------------------------------- ------------------------------------
C1 NUMBER
C2 VARCHAR2(1)
C3 VARCHAR2(7)
SQL> INSERT INTO A VALUES(2,2,2);
已创建 1 行。
SQL> SELECT * FROM A;
C1 C2 C3
---------- -- --------------
1 1
2 2 2
SQL> SELECT DECODE(C1,1,1,2,'E',C1) FROM A;
ERROR:
ORA-01722: 无效数字
未选定行
SQL> SELECT DECODE(C1,1,1,2,'E',C1) FROM A WHERE C1=1;
DECODE(C1,1,1,2,'E',C1)
-----------------------
1
SQL> SELECT DECODE(C1,1,1,2,'E',C1) FROM A WHERE C1=2;
SELECT DECODE(C1,1,1,2,'E',C1) FROM A WHERE C1=2
*
第 1 行出现错误:
ORA-01722: 无效数字
上面的问题主要是字符'E'无法转为整数类型导致的。
3、Decode函数返回类型的确定
今天在QQ上一个朋友发出问题，说min函数返回错误的取值。详细如下：一个数据表列类型为number(6,2)，其中有三行记录，分别为0，0.6和1。用min获取最小值，得到0.6。
min是Oracle SQL的一个基础函数，理论上不会出现这样的Bug之类的。下面一起来模拟下实验环境。
1、环境构建
在实验数据库Oracle11g环境下，构建实验数据表t。填入实验数据。
SQL> create table t (num number(6,2));
Table created
SQL> insert into t values (0);
1 row inserted
SQL> insert into t values (0.6);
1 row inserted
SQL> insert into t values (1);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t;
NUM
--------
0.00
0.60
1.00
实验那位兄弟的说法。
SQL> select min(num) from t;
MIN(NUM)
----------
0
SQL> select min(to_number(num)) from t;
MIN(TO_NUMBER(NUM))
-------------------
0
没有什么问题，详细问了一下，获取到了SQL结构如下。
SQL> select min(decode(num,-1,null,num)),min(num)from t;
MIN(DECODE(NUM,-1,NULL,NUM)) MIN(NUM)
---------------------------------------- ----------
.6 0
果然，诡异的现象发生了。
2、问题分析
一时间还是很唬人的，那么我们先抛开min函数，单独看数据列情况。抽丝剥茧吧。
SQL> select decode(num,-1,null,num),num from t;
DECODE(NUM,-1,NULL,NUM) NUM
---------------------------------------- --------
0 0.00
.6 0.60
1 1.00
这里只剩下一个decode函数的使用。从含义上看，当num为-1的时候，返回null值，否则就是原有的num值。但是有两个疑点，首先是0.60是如何转变为.6的呢？其次就是decode函数处理列的列对其方式，数字类型默认是右对齐，只有字符串是左对齐的。难道说经过decode函数处理之后，返回值变成了字符串？
那么，如果decode处理之后，变成了字符串的话，我们调整一个decode的结构，看看是否是由于处理变成字符串造成了问题。
SQL> selectmin(to_number(decode(num,-1,null,num))),min(num) from t;
MIN(TO_NUMBER(DECODE(NUM,-1,NU MIN(NUM)
------------------------------ ----------
0 0
看来原因就在于decode函数使用处理之后，返回数据列是一个字符串类型。但是decode函数命名指定了num列，返回值是什么类型呢？
num是数字肯定没有什么问题？难道说疑点出现在null的返回值类型上？继续实验。
SQL> select decode(num,-1,num,num),num from t;
DECODE(NUM,-1,NUM,NUM) NUM
---------------------- --------
0 0.00
0.6 0.60
1 1.00
果然，临时取消掉null，decode返回类型就正常。看来真是受到了null的影响。这个时候，笔者思考一个问题，Oracle Decode函数如何确定返回值类型列呢？
SQL> select decode(num,-1,'d',num),numfrom t;
DECODE(NUM,-1,'D',NUM) NUM
---------------------------------------- --------
0 0.00
.6 0.60
1 1.00
SQL> select decode(num,-1,'k',num),to_char(num)from t;
DECODE(NUM,-1,'K',NUM) TO_CHAR(NUM)
---------------------------------------- ----------------------------------------
0 0
.6 .6
1 1
上面的实验，让我们得出了和null值是相同的效果。这样，我们对decode有下面猜想：
ü Oracle在调用decode函数的时候，是需要预先确定列的类型，因为毕竟出现在相同的列上；
ü 确定decode返回值类型，是依据参数中第一个条件返回类型。之后所有的返回类型都依据第一个类型进行强制类型转换；
ü Oracle在第一个条件返回类型为null的时候，默认将其作为字符串处理；
如果三个假设成立，那么所有问题就得到解释。
那个朋友的SQL中，decode函数第一个可选返回值是null，Oracle识别返回类型为字符类型。之后对所有的其他返回值均使用了to_char方法类似的转换逻辑。
那么，往后想一步，如果Oracle decode函数真是依靠第一条件来确定列类型，其他列进行强行转换，那么如果出现不匹配的时候怎么办？
SQL> select decode(num,1,num,'k') from t;
select decode(num,1,num,'k') from t
ORA-01722:无效数字
这个案例中的decode函数，根据第一个前条件取值num是数字类型，那么其他所有都会被强制转换为数字类型。但是我们写定的其他条件取值是’k’，不能进行强制类型转换。于是报错无效数字。
3、问题解决
了解了问题decode的根源，剩下的就好解释了。min函数可以接受字符串和数字。在数字类型时，依据数字类型的比较规则，选择出0是最小值。当接受字符串时，使用的是二进制对比策略。其中.小数点的排序位最小。于是选择出.6作为结果也就不奇怪了。
解决问题的方法很多，笔者推荐的方式是对null进行数字化处理。让Oracle识别为数字类型。
SQL> select min(decode(num,-1,to_number(null),num)),min(num)from t;
MIN(DECODE(NUM,-1,TO_NUMBER(NU MIN(NUM)
------------------------------ ----------
0 0
4、结论
通过这个案例，我们除了重新认识到decode的原理外，还有几个收获。
首先是要重视null值，null在Oracle中是一种很特殊的类型。在运算和函数调用中，都有很多特殊之处。遇到问题，要注意考虑null的因素，是我们解决问题的思路；
其次就是重视函数的本质。Decode是我们常见的函数，但是我们对一些细节缺乏思考研究。比如Decode返回值类型如何确定？这些都是细节，但是细节也反映了我们的能力和修行。
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
