oracle:单行函数和条件表达式

1、建立使用字符函数的范例脚本
–select lower(region_name) from regions; --lower(string string)输出结果小写表示

–select upper(region_name) from regions; --大写

–select initcap(region_name) from regions; --首写字母大写，后面都小写

–select concat(street_address,city) from locations;
–concat(string1,string2)连接两个字符串(只能两个，如果要多个否则可以递归调用)

–select substr(street_address,5) from locations;
–substr(string string,int a,int b),截取字符串，其中a表示开始位置，b表示长度

–select length(street_address) as street_address_length from locations; --length(string)查看字符串长度

–select instr(street_address,‘i’,2,1) from locations where location_id=1000; --instr(母串,子串,起始位置,第几个)

–select lpad(street_address,20,‘x’) from locations; --左边填充字符x直到满20个，如果少于20个会只截20个

–select rpad(street_address,20,‘x’) from locations; --右边填充字符x直到满20个，如果少于20个会只截20个

–select trim(both ‘e’from street_address) from locations;
–trim(both|leading|trailing ‘e’ from street_address)去除两边、前面、后面的指定字符

–select street_address,replace(street_address,‘a’,‘i’) from locations; --将字符串里面a替换成i

2、建立数值函数规范脚本
–select location_id,round(location_id/110) from locations; --四舍五入

–select location_id,trunc(location_id/110,2) from locations; --截取，通常用于截取日期和数值

–select location_id,mod(location_id,233) from locations; --取模

3、建立日期函数使用脚本
–select to_char(sysdate,‘yyyy-MM-dd HH24:mi:ss’) from locations; --将日期转换为字符串

–select months_between(to_date(‘2009-02-28’, ‘yyyy-mm-dd’), to_date(‘2008-02-28’, ‘yyyy-mm-dd’)) as months from locations;
–输出一段时间中间相隔的月份

–select next_day(sysdate,‘星期四’) from locations;
–输出当前指定日期的下一个指定日期

–select last_day(sysdate) from locations;
–指定日期对应月份最后一天

–select trunc(sysdate) from dual; --默认截取到天

–select sysdate,round(sysdate),round(sysdate,‘ddd’),round(sysdate,‘day’),round(sysdate,‘month’),round(sysdate,‘year’) from dual;
–按照年、月、日四舍五入

4、建立有关转换格式的脚本
–select to_date('2005-01-01 ',‘yyyy-MM-dd’) from dual; --转换成日期

–select to_char(sysdate,‘yyyy-MM-dd HH24:mi:ss’) from dual; --转换成字符

–select to_number(‘000012134’) from dual; --转换成数值

5、建立有关空值的脚本
select state_province,nvl(state_province, -1) from locations;
–如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

–select state_province,nvl2(state_province, -1,1) from locations;
–如果该函数的第一个参数为空那么显示第三个参数的值，如果第一个参数的值不为空，则显示第二个参数的值

–select nullif(5,5) from locations;
–NULLIF (expr1, expr2) ->相等返回NULL，不等返回expr1

–select coalesce(null,3,4,5) from dual;
–返回第一个非NULL表达式的类型（拓展：COALESCE(expression1,…n) 与此 CASE 函数等价）

6、建立有关case表达式的脚本
select location_id,
case location_id when 1000 then ‘low’
when 1100 then ‘midium’
else ‘high’
end
from locations;
–1. 简单case只能是when后面的表达式完全匹配case后的表达式，相当于 =，所以也不能匹配null。
–2. searched case可以作为比较条件，那么可以使用like、!=、between …and、<、=、is null、is not null等
–比简单case的使用更加广泛，完全可以替代简单case。

7、建立使用decode函数查询的范例脚本
select location_id,decode(location_id,1000,1,1100,2,1200,3,1300,4,5) from locations;
–decode (expression, search_1, result_1, search_2, result_2, …, search_n, result_n, default) 比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；如果未定义default值，则返回空值。

8、比较case和decode的不同和应用场景
首先case和decode效果都一样，都能够较好处理表达式查询的问题，但是decode的代码比较简洁，而且也比较直观；但是case虽然表达式比较复杂，但是更加灵活，实现的功能比decode更多，decode主要是等值判断，若想要实现其他功能，要加上其他函数辅助实现。

有关脚本代码如下：

--select table_name from user_tables; --查看当前用户所有表
--select table_name from all_tables; --查看所有用户的表  
--select table_name from dba_tables; --查看包括系统表
select * from LOCATIONS;
select * from regions;

select lower(region_name) from regions; --lower(string string)输出结果小写表示
select upper(region_name) from regions; --大写
select initcap(region_name) from regions; --首写字母大写，后面都小写
select concat(street_address,city) from locations; --concat(string1,string2)连接两个字符串(只能两个，如果要多个否则可以递归调用)
select substr(street_address,5) from locations; --substr(string string,int a,int b),其中a表示开始位置，b表示长度
--select length(street_address) as street_address_length from locations; --length(string)查看字符串长度
--select instr(street_address,'i',2,1) from locations where location_id=1000;  --instr(母串,子串,起始位置,第几个)
--select lpad(street_address,20,'x') from locations; --左边填充字符x直到满20个，如果少于20个会只截20个
--select rpad(street_address,20,'x') from locations; --右边填充字符x直到满20个，如果少于20个会只截20个
--SELECT TRIM(both 'e' FROM street_address) FROM locations; --TRIM(both|leading|trailing 'e' FROM street_address)去除前面、后面、两边的指定字符
--SELECT replace(street_address,'a','i') FROM locations; --将字符串里面a替换成i

--select round(location_id/110) from locations;  --四舍五入
--select trunc(location_id/110,2) from locations; --截取，通常用于截取日期和数值
--select mod(location_id,7) from locations; --取模

--select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from locations;
--select months_between(to_date('20090228', 'yyyymmdd'), to_date('20080228', 'yyyymmdd')) as months from locations;
--select  next_day(sysdate,'星期四') from locations;
--select  last_day(sysdate) from locations;
--select trunc(sysdate) from dual; --默认截取到天
--select sysdate,round(sysdate),round(sysdate,'ddd'),round(sysdate,'day'),round(sysdate,'month'),round(sysdate,'year') from dual; 

--select to_date('2005-01-01 ','yyyy-MM-dd') from dual;
--select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;
--select to_number('000012134') from dual;  

--select location_id,NVL(state_province, -1) from locations; --如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。
--select location_id,NVL2(state_province, -1,1) from locations; --如果该函数的第一个参数为空那么显示第三个参数的值，如果第一个参数的值不为空，则显示第二个参数的值
--select nullif(5,5) from locations; --NULLIF (expr1, expr2) ->相等返回NULL，不等返回expr1 
--select coalesce(null,3,4,5) from dual; --返回第一个非NULL表达式的类型（拓展：COALESCE(expression1,...n) 与此 CASE 函数等价）

/*select                                       --1. 简单case只能是when后面的表达式完全匹配case后的表达式，相当于 =，所以也不能匹配null。
    case  location_id   when 1000 then 'low' --2. searched case可以作为比较条件，那么可以使用like、!=、between ..and、<、=、is null、is not null等
                        when 1100 then 'midium'  --比简单case的使用更加广泛，完全可以替代简单case。
                        else 'high'
    end
from locations;*/