Oracle中的decode函数详解，等同于mysql的if()函数和case when语句_select decode

decode(expr，if val1，value1，if val2，value2，…，valueN，default)函数

大致意思就是：
当你传的expr值和后面的val1或者val2等等匹配时，就会将expr的值变成对应的val值。

举个例子

现在有表example，有以下数据

select decode(e.name,'qqq','匹配001','不匹配') from example;
1

执行完sql语句后，结果如下

类似，匹配多一个www，sql语句如下：

select decode(e.name,'qqq','匹配001','www','匹配002','不匹配') from example;
1

执行完sql语句后，结果如下

总的来说就是，当decode函数传入的值等于xxx时，就会执行转换。当全部都不匹配时，就匹配default值。

decode()函数扩展用法

1.结合sign(expr)函数进行大小比较后的取值。

提示：
sign（expr>0）返回1
sign（expr=0）返回0
sign（expr<0）返回-1
现在有a表（学生成绩表），需求：根据成绩评级（A：90-100，B:80-89，C：70-79，D：60-69，E：0-59）

Oracle-sql如下：

select a.id,a.name,a.score,
	   decode(sign(a.score-90),1,'A',0,'A',-1,
	   	decode(sign(a.score-80),1,'B',0,'B',-1,
	   	  decode(sign(a.score-70),1,'C',0,'C',-1,
	   	    decode(sign(a.score-60),1,'D',0,'D',-1,
	   	      decode(sign(a.score),1,'E',0,'E',-1,'无效成绩'))))) as lv from a
1
2
3
4
5
6

结果：

附mysql-sql使用case when实现如下

SELECT a.id,a.score,
	CASE 
	WHEN (a.score <=100 AND a.score >=90) THEN 'A'
	WHEN (a.score <90 AND a.score >=80) THEN 'B'
	WHEN (a.score <80 AND a.score >=70) THEN 'C'
	WHEN (a.score <70 AND  a.score >=60) THEN 'D'
	WHEN (a.score <60 AND a.score >=0) THEN 'E'
	ELSE
		'无效数据'
END AS lv
FROM a
1
2
3
4
5
6
7
8
9
10
11

附mysql-sql使用if()函数实现如下：

SELECT a.id,a.score,
 IF(a.score <=100 AND a.score >=90,'A',
  IF(a.score <90 AND a.score >=80,'B',
   IF(a.score <80 AND a.score >=70,'C',
    IF(a.score <70 AND a.score >=60,'D',
     IF(a.score <60 AND a.score >=0,'E','无效数据'))))) AS lv FROM a
1
2
3
4
5
6

2.结合Oracle中的instr()函数实现某些字段的匹配。

instr函数详解可以先看这篇文章： Oracle数据库的instr()函数详解
initcap（首字母大写）
lower（全小写）
upper（全大写）
首先，使用lower()函数将目标字符和匹配字符同转小写，然后使用instr()函数匹配，返回值为匹配字符的位置数据，再用sign()函数判断，当返回的数据大于0时（即匹配成功），就返回对应结果字段。0和-1代表没有匹配成功。

SELECT DECODE(SIGN(instr(LOWER('本项目中使用了spring、springMVC、hibernate等框架实现对数据的展示和操作'),LOWER('Spring'))),1,'使用了spring框架',0,'没有匹配成功',-1,'没有匹配成功') FROM dual
1

mysql中没有decode函数，但是我们可以用case when 语句或if()函数实现类似效果。

if（expr，expr1，expr2）：当expr的条件满足时，就返回expr1的值，反之返回expr2的值。
expr可以为一个值，或有返回值的函数等。

case when语法如下：

case
	when  字段a = val1  then  value1
	when  字段a = val2  then  value2
	when  字段a = val3  then  value3
	...
	else value4
	end  as 别名
1
2
3
4
5
6
7

case when语句一般常用于对某种数据的特殊处理。
提示当case xx when的时候，只能等于比较。
例如：

case 字段 
  when 1 then 10
  when 0 then -10
end
1
2
3
4

例子1：
表a(sex字段，0代表女，1代表男，查询数据库的时候要做转换)

select a.id,a.name,
	case when a.sex = '1' then '男' when a.sex = '0' then '女' else '无' end as sex 
from a
1
2
3

执行sql后，结果如下：

例子2：
当项目中有两张人员表a，b。且a表和b表各有自己的状态，现在需求是：当a表人员状态修改的时候，同步到b表对应的状态。（状态为数据字典表）

a表字段（aid，aname，astatus(p有效，d无效)）、b表字段（bid，bname，bstatus(bp有效,bd无效)）

编写简易触发器（Oracle）

CREATE OR REPLACE TRIGGER "cfq_persion" AFTER DELETE OR INSERT OR UPDATE ON "a" 
REFERENCING OLD AS "OLD" NEW AS "NEW" 
FOR EACH ROW 
BEGIN
  CASE 
  WHEN INSERTING THEN
    INSERT INTO b(BID,BNAME,bstatus)
    VALUES(:NEW.AID,:NEW.ANAME,:NEW.astatus);
  WHEN UPDATING THEN
    UPDATE b 
    SET BID = :NEW.AID,BNAME = :NEW.ANAME,
    bstatus = (case when :NEW.astatus = 'p' then 'bp' when :NEW.astatus = 'D' then 'bd' else 'null')
    WHERE ID = :NEW.AID;
  WHEN DELETING THEN
    DELETE FROM b WHERE ID = :NEW.AID;
  END CASE;
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

CREATE OR REPLACE TRIGGER “cfq_persion” AFTER DELETE OR INSERT OR UPDATE ON “a”：代表创建或覆盖触发器，在对a表进行删除、插入、更新操作后（after，before是之前）触发。
FOR EACH ROW ：代表行级触发器（当一行数据发生改变时出发），不写则是表级触发器（只在一次sql中触发）。
REFERENCING OLD AS “OLD” NEW AS “NEW” ：old代表行级触发器时的旧数据行，new代表新增数据行。
begin xxxx end;：触发器触发的时候执行的sql片段。
case when xxx then xxx when xxx then xxx end case;：匹配不同的操作，执行不同的sql片段。比如：inserting（插入时），执行insert into b表的操作