Oracle-day02 下

史蒂夫和斯凯

　　五、单行函数
　　（一）字符函数

　　常用字符函数讲解：
　　（1）求字符串长度 LENGTH
　　语句：
　　select length('ABCD') from dual;
　　显示结果为：

　　（2）求字符串的子串 SUBSTR
　　语句：
　　select substr('ABCD',2,2) from dual;
　　显示结果为：

　　（3）字符串拼接 CONCAT
　　语句：
　　select concat('ABC','D') from dual;
　　查询结果如下：

　　我们也可以用|| 对字符串进行拼接
　　select 'ABC'||'D' from dual;
　　查询结果同上。
　　（二）数值函数

　　常用数值函数讲解：
　　（1）四舍五入函数 ROUND
　　语句：
　　select round(100.567) from dual
　　查询结果如下：

　　语句：
　　select round(100.567,2) from dual
　　查询结果如下：

　　（2）截取函数 TRUNC
　　语句：
　　select trunc(100.567) from dual
　　查询结果：

　　语句：
　　select trunc(100.567,2) from dual

　　（3）取模 MOD
　　语句：
　　select mod(10,3) from dual
　　结果：

　　（三）日期函数

　　我们用 sysdate 这个系统变量来获取当前日期和时间
　　语句如下：
　　select sysdate from dual
　　查询结果如下：

　　常用日期函数讲解：
　　（1）加月函数 ADD_MONTHS ：在当前日期基础上加指定的月
　　语句：
　　select add_months(sysdate,2) from dual
　　查询结果如下：

　　（2）求所在月最后一天 LAST_DAY
　　语句：
　　select last_day(sysdate) from dual
　　查询结果如下：

　　（3）日期截取 TRUNC
　　语句：
　　

　　
select TRUNC(sysdate) from dual
　　

　　查询结果如下：

　　语句：
　　

select TRUNC(sysdate,'yyyy') from dual　　

　　查询结果如下：

　　语句：
　　select TRUNC(sysdate,'mm') from dual
　　查询结果如下：

　　（四）转换函数

　　常用转换函数讲解：
　　（1）数字转字符串 TO_CHAR
　　语句：
　　select TO_CHAR(1024) from dual
　　查询结果：

　　（2）日期转字符串 TO_CHAR
　　语句：
　　

　　
select TO_CHAR(sysdate,'yyyy-mm-dd') from dual
　　

　　查询结果：

　　语句：
　　select TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
　　查询结果：

　　（3）字符串转日期 TO_DATE
　　语句：
　　select TO_DATE('2017-01-01','yyyy-mm-dd') from dual
　　查询结果如下：

　　（4）字符串转数字 TO_NUMBER
　　语句：
　　select to_number('100') from dual
　　（五）其它函数
　　（1）空值处理函数 NVL
　　用法：
　　NVL（检测的值，如果为 null 的值）；
　　语句：
　　select NVL(NULL,0) from dual
　　查询结果如下：

　　需求：

　　显示价格表中业主类型>　　语句：
　　

　　
select PRICE,MINNUM,NVL(MAXNUM,9999999)
　　
from T_PRICETABLE where OWNERTYPEID=1
　　

　　查询结果：

　　（2）空值处理函数 NVL2
　　用法：
　　NVL2（检测的值，如果不为 null 的值，如果为 null 的值）；
　　需求：显示价格表中业主类型>　　限”.

　　语句：
　　

select PRICE,MINNUM,NVL2(MAXNUM,to_char(MAXNUM) , '不限')　　
from T_PRICETABLE where OWNERTYPEID=1
　　

　　（3）条件取值 decode
　　语法：
　　

　　
decode(条件,值 1,翻译值 1,值 2,翻译值 2,...值 n,翻译值 n,缺省值)
　　

　　【功能】根据条件返回相应值
　　需求：显示下列信息（不要关联查询业主类型表，直接判断 1 2 3 的值）

　　语句：
　　

select name,decode( ownertypeid,1,' 居 民 ',2,' 行 政 事 业 单 位　　
',3,'商业') as 类型
　　
from T_OWNERS
　　

　　上边的语句也可以用 case when then 语句来实现
　　

select name ,(case ownertypeid　　
when 1 then '居民'
　　
when 2 then '行政事业单位'
　　
when 3 then '商业'
　　
else '其它'
　　
end
　　
) from T_OWNERS
　　

　　还有另外一种写法：
　　

select name,(case　　
when ownertypeid= 1 then '居民'
　　
when ownertypeid= 2 then '行政事业'
　　
when ownertypeid= 3 then '商业'
　　
end )
　　
from T_OWNERS
　　

　　六、行列转换
　　需求：按月份统计 2012 年各个地区的水费，如下图

　　需求：按季度统计 2012 年各个地区的水费，如下图

　　语句如下：

　　七、分析函数
　　以下三个分析函数可以用于排名使用。
　　下图为三种排名方式的举例

　　（1） RANK
　　相同的值排名相同，排名跳跃
　　需求：对 T_ACCOUNT 表的 usenum 字段进行排序，相同的值排名相同，排名跳跃
　　语句：
　　

　　
select rank() over(order by usenum desc ),usenum from
　　
T_ACCOUNT
　　

　　结果：

　　（2） DENSE_RANK 相同的值排名相同，排名连续
　　需求：对 T_ACCOUNT 表的 usenum 字段进行排序，相同的值排名相同，排名连续
　　语句：
　　

select dense_rank() over(order by usenum desc ),usenum　　
from T_ACCOUNT
　　

　　结果：

　　（3） ROW_NUMBER
　　返回连续的排名，无论值是否相等
　　需求：对 T_ACCOUNT 表的 usenum 字段进行排序，返回连续的排名，无论值是
　　否相等
　　语句：
　　

select row_number() over(order by usenum desc ),usenum　　
from T_ACCOUNT
　　

　　用 row_number()分析函数实现的分页查询相对三层嵌套子查询要简单的多:
　　

select * from　　
(select row_number() over(order by usenum desc )
　　
rownumber,usenum from T_ACCOUNT)
　　
where rownumber>10 and rownumber
union all

　　
select * from t_owners where>　　

　　结果如下：

　　UNION 去掉重复记录
　　


　　
select * from t_owners where>　　
union
　　
select * from t_owners where>　　

　　（三）交集运算

　　

select * from t_owners where>
intersect
　　
select * from t_owners where>　　

　　结果：

　　（四）差集运算

　　

select * from t_owners where>
minus

　　
select * from t_owners where>　　

　　结果:

　　如果我们用 minus 运算符来实现分页，语句如下：
　　

select rownum,t.* from T_ACCOUNT t where rownum