case表达式

1 case简介 　　 Case表达式，是可以在sql中使用if ..then..else的逻辑判断，而避免使用PL/SQL的有效方法，在sql中主要有两种：简单case和搜索case，在plsql中还有两种种case 语句。与decode 类似。(有一定的区别，见下面第四条)

　　1.Case 表达式返回的是一个确定的value，如果没有else，若前面的都不匹配，则返回null。<else 不是必须的,都没有匹配返回null，这与pl/sql 中的case 语句不同，case 语句如果不写else，都没有匹配，则报case_not_found异常> 　　2.简单case 中的表达式，when 后面的表达式类型应该全部保持一致。如： 　　select case 'a' when 'a' then 1 when 9 then 3 end from dual;--所有的when 类型必须与第case之后的表达式值类型保持一致，资料的9应该是’9’,没有自动转换成char,和一般的sql中自动转换不同。 　　3.所有的then 后面的return_value类型要保持一致 　　select case 'a' when 'a' then '1' when '9' then '3' else 3 end from dual;--红色部分类型应该保持一致，没有自动转换，else后面的3应该是’3’. 　　4.对于简单case 表达式，也就是case 表达式 when…那么when null 总是取不到。也就是case 后面的表达式如果值为null，不会与when null 匹配，只会与else 匹配。如： 　　select case null when null then 'null' else 'not matched!' end from dual;--case的null不会与when后面的null匹配，只会返回else的结果。 　　关于这点，如果case 后面的表达式有可能为null，如果需要匹配null，那么可以使用decode 和searched case。 　　Decode： 　　decode(exp, 　　value1,res1, 　　value2,res2,…., 　　valuen resn, 　　elsevalue)。 　　如果其中有存在exp为null，那么如果valuei中有null，则会匹配，返回resi。如： 　　select decode(null,'a1','1','a2','2',null,'null','not know!') from dual;--返回字符串null 　　searched case: 　　case when 　　condition_1 then value1 　　when condition_2 then value2… 　　when condtion_i then valuei 　　else 　　elsevalue 　　end 　　如果要匹配null,只需要 case when exp is null then ..就可以了 　　5.对于searched case来说，有自动类型转换，只要条件成立就可以。如：select case when 1='1' then 1 end from dual;--1=’1’条件成立 　　6.参数最高限制255 个。包括case exp 中的exp 和else 中的，以及when exp1 value 1 算两个参数。如果语句复杂，超过这个限制，可以考虑使用嵌套case。 　　2 两种case 　　简单的case： 　　 　　语法：case exp when comexp then returnvalue 　　..when comexp then returnvalue 　　Else 　　Returnvalue 　　End 　　使用规则case简介。 　　Case到end之间相当于一个具体的值，可以做运算，取别名，嵌套case 等等。只要把case到end当作一个运算结果的表达式就可以了。 　　《注意，中间一直到end 都没有其他标点符号》 　　搜索case： 　　 　　直接使用case when +比较条件 到end为返回的结果，end之后可以做运算。 　　语法：case when Boolean then returnvalue 　　..when Boolean then return value 　　Else 　　Returnvalue 　　End 　　简单case例子 　　 　　SELECT cust_last_name, 　　CASE credit_limit WHEN 100 THEN ’Low’ 　　WHEN 5000 THEN ’High’ 　　ELSE ’Medium’ END 　　FROM customers; 　　搜索case例子 　　 　　select case when id between 1 and 10 then 'low' 　　when id between 20 and 30 then 'mid' 　　when id between 40 and 50 then 'high' 　　else 'unknow' 　　end 　　from product; 　　update emp set 　　salary= 　　case when salary<2000 then salary*1.1 　　when salary between 2000 and 4000 then salary*1.05 　　when salary>4000 then salary*1.04 　　else 　　null 　　end; 　　select case when name like '全球%' then 'true' 　　when name like '神州行' then 'false' 　　else 'mm' 　　end 　　from trademark; 　　比较操作，可以使用like，between … and ..,!=,<,>=等操作符以及其他返回boolean类型的操作符。 　　简单case和searched case之间的区别： 　　1. 简单case只能是when后面的表达式完全匹配case后的表达式，相当于 =，所以也不能匹配null。 　　2. searched case可以作为比较条件，那么可以使用like，!=,between ..and,<,=，is null,is not null等，比简单case的使用更加广泛，完全可以替代简单case。 　　7.3 综合实例 　　 　　问题描述：如何对比两个结构一致的表的数据差异 　　比如两个表如下： 　　A表： 　　 　　产品编号 数量 　　01 10 　　02 20 　　03 15 　　B表： 　　 　　产品编号 数量 　　01 10 　　02 18 　　04 17 　　对于他们的数据差异： 　　编号和数量相同的不比较 　　Case语句中可以使用子查询，但是必须返回一行，不可以是多行，如： 　　 　　select case (select count(*) as s1 from t1 where a = 1) 　　when (select count(*) as s2 　　from t1, t2 　　where t1.a = t2.a 　　and t2.a = 1) then 　　'相等' 　　else 　　'不相等' 　　end 　　from dual; 　　【技术开发 技术文章 】 　　1 case简介 　　 　　Case表达式，是可以在sql中使用if ..then..else的逻辑判断，而避免使用PL/SQL的有效方法，在sql中主要有两种：简单case和搜索case，在plsql中还有两种种case 语句。与decode 类似。(有一定的区别，见下面第四条) 　　1.Case 表达式返回的是一个确定的value，如果没有else，若前面的都不匹配，则返回null。<else 不是必须的,都没有匹配返回null，这与pl/sql 中的case 语句不同，case 语句如果不写else，都没有匹配，则报case_not_found异常> 　　2.简单case 中的表达式，when 后面的表达式类型应该全部保持一致。如： 　　select case 'a' when 'a' then 1 when 9 then 3 end from dual;--所有的when 类型必须与第case之后的表达式值类型保持一致，资料的9应该是’9’,没有自动转换成char,和一般的sql中自动转换不同。 　　3.所有的then 后面的return_value类型要保持一致 　　select case 'a' when 'a' then '1' when '9' then '3' else 3 end from dual;--红色部分类型应该保持一致，没有自动转换，else后面的3应该是’3’. 　　4.对于简单case 表达式，也就是case 表达式 when…那么when null 总是取不到。也就是case 后面的表达式如果值为null，不会与when null 匹配，只会与else 匹配。如： 　　select case null when null then 'null' else 'not matched!' end from dual;--case的null不会与when后面的null匹配，只会返回else的结果。 　　关于这点，如果case 后面的表达式有可能为null，如果需要匹配null，那么可以使用decode 和searched case。 　　Decode： 　　decode(exp, 　　value1,res1, 　　value2,res2,…., 　　valuen resn, 　　elsevalue)。 　　如果其中有存在exp为null，那么如果valuei中有null，则会匹配，返回resi。如： 　　select decode(null,'a1','1','a2','2',null,'null','not know!') from dual;--返回字符串null 　　searched case: 　　case when 　　condition_1 then value1 　　when condition_2 then value2… 　　when condtion_i then valuei 　　else 　　elsevalue 　　end 　　如果要匹配null,只需要 case when exp is null then ..就可以了 　　5.对于searched case来说，有自动类型转换，只要条件成立就可以。如：select case when 1='1' then 1 end from dual;--1=’1’条件成立 　　6.参数最高限制255 个。包括case exp 中的exp 和else 中的，以及when exp1 value 1 算两个参数。如果语句复杂，超过这个限制，可以考虑使用嵌套case。 　　2 两种case 　　简单的case： 　　 　　语法：case exp when comexp then returnvalue 　　..when comexp then returnvalue 　　Else 　　Returnvalue 　　End 　　使用规则case简介。 　　Case到end之间相当于一个具体的值，可以做运算，取别名，嵌套case 等等。只要把case到end当作一个运算结果的表达式就可以了。 　　《注意，中间一直到end 都没有其他标点符号》 　　搜索case： 　　 　　直接使用case when +比较条件 到end为返回的结果，end之后可以做运算。 　　语法：case when Boolean then returnvalue 　　..when Boolean then return value 　　Else 　　Returnvalue 　　End 　　简单case例子 　　 　　SELECT cust_last_name, 　　CASE credit_limit WHEN 100 THEN ’Low’ 　　WHEN 5000 THEN ’High’ 　　ELSE ’Medium’ END 　　FROM customers; 　　搜索case例子 　　 　　select case when id between 1 and 10 then 'low' 　　when id between 20 and 30 then 'mid' 　　when id between 40 and 50 then 'high' 　　else 'unknow' 　　end 　　from product; 　　update emp set 　　salary= 　　case when salary<2000 then salary*1.1 　　when salary between 2000 and 4000 then salary*1.05 　　when salary>4000 then salary*1.04 　　else 　　null 　　end; 　　select case when name like '全球%' then 'true' 　　when name like '神州行' then 'false' 　　else 'mm' 　　end 　　from trademark; 　　比较操作，可以使用like，between … and ..,!=,<,>=等操作符以及其他返回boolean类型的操作符。 　　简单case和searched case之间的区别： 　　1. 简单case只能是when后面的表达式完全匹配case后的表达式，相当于 =，所以也不能匹配null。 　　2. searched case可以作为比较条件，那么可以使用like，!=,between ..and,<,=，is null,is not null等，比简单case的使用更加广泛，完全可以替代简单case。 　　7.3 综合实例 　　 　　问题描述：如何对比两个结构一致的表的数据差异 　　比如两个表如下： 　　A表： 　　 　　产品编号 数量 　　01 10 　　02 20 　　03 15 　　B表： 　　 　　产品编号 数量 　　01 10 　　02 18 　　04 17 　　对于他们的数据差异： 　　编号和数量相同的不比较 　　Case语句中可以使用子查询，但是必须返回一行，不可以是多行，如： 　　 　　select case (select count(*) as s1 from t1 where a = 1) 　　when (select count(*) as s2 　　from t1, t2 　　where t1.a = t2.a 　　and t2.a = 1) then 　　'相等' 　　else 　　'不相等' 　　end 　　from dual;