oracle 函数综合小例子 日期的操作等

前提有一张学生表student  里面有个birthday字段(日期字段)
让你求出 大于10岁的学生 这边仅仅以年份判断
 
这里思路是把birthday与当前的日期相比 通过年份的差来判断
首先要把日期类型转化成字符串,
to_char(obj,formart)
to_char(birthday,'yyyyMMdd')   顺带一句
转化成日期 to_date(str,formart)
to_date(sysdate,'yyyy-mm-dd') 
 
取得年份
substr(str,起始索引,截取长度)
substr(to_char(birthday,'yyyyMMdd'),0,4) )
字符串转化成数字
to_number函数 转化成数字
to_number(substr(to_char(birthday,'yyyyMMdd'),0,4) ))
最终sql
select * from student where birthday is not null
 and (to_number(substr(to_char(sysdate,'YYYYMMDD'),0,4)) -to_number(substr(to_char(birthday,'YYYYMMDD'),0,4)))>10
 如果是时分秒的话 yyyy-mm-dd hh24:mi:ss
row_number函数
有时候可用于oracle分页（不建议相比其他2中分页方式效率低）
row_number() over () 
row_number() over ([partition by col1] order by col2) ) as 别名
表示根据col1分组，在分组内部根据 col2排序
而这个“别名”的值就表示每组内部排序后的顺序编号（组内连续的唯一的）,[partition by col1] 可省略。
例子 查找各部门中薪水最高的前2位
select ename,job,deptno,sal,排名 from
(select ename,job,deptno,sal,(row_number() over(partition by deptno order by sal desc)) as 排名 
from scott.emp                   --先将各部门的员工按薪水排序，再在结果中取出需要的部分
) where 排名<=2;
参考http://daniel-tu.iyunv.com/blog/973761

oracle  row_number() over partition by去重复

 功能
　　参考http://blog.sina.com.cn/s/blog_4586764e0100ifjo.html
 
trunc
trunc（用于截取的数，截取的长度）
trunc(123.56,0) ==123
trunc(123.56,1) ==123.5
trunc(123.56,2) ==123.56
trunc(123.56,3) ==123.56
trunc(123.56,-1)==120trunc(123.56,-2)==100
trunc(123.56,-3)==0
trunc(123.56,-4)==0
trunc还可以操作日期   今天的日期2013/5/14trunc（sysdate）==2013/5/14 返回当前时间
yyyy-MM-dd hh:mm:ss  trunc（sysdate,'yyyy'） ==2013/1/1  返回当年第一天

trunc（sysdate,'MM'）==2013/5/1  返回五月份第一天trunc（sysdate,'dd'）==2013/5/14  返回当前时间trunc（sysdate,'hh'）==2013/5/14 10:00:00trunc（sysdate,'mi'）==2013/5/14 10:14:00 分用mi  秒没有提供方法

vsize（c）
返回c的字节数
 
cast（‘1’ as number（1）） 一般用于字符串转化成其他的
 
concat（c1,c2） 相当于 || 连接字符串
 
NVL(c1,c2) 逻辑等价于IF c1 is null THEN c2 ELSE c1 END。c1,c2可以是任何类型。如果两者类型不同，则oracle会自动将c2转换为c1的类型。
 

NVL2(expr1,expr2,expr3)[size=1em]->expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型


[size=1em]NULLIF函数语法为NULLIF（表达式1，表达式2），如果表达式1和表达式2相等则返回空值，如果表达式1和表达式2不相等则返回表达式1的结果。

WMSYS.WM_CONCAT 函数可以完成行转列的效果
wmsys.wm_concat(列名)
详见http://blog.sina.com.cn/s/blog_5c4736800100l2qw.html
 
lpad（原字符创，填充后总长度，填充字符串）
 lpad('asd',10,'*')==*******asd
 
sign函数
sign(value)  里面的value大于0则返回1 小于0则返回-1 等于0则返回0
 
decode函数 相当于if else
decode（判断语句,条件1，结果，条件2，结果....最后结果）
下面的意思是 如果teacher_name字符串长度大于了36 就截断 否则就取原来的
 select decode(sign(vsize(teacher_name)-36),-1,teacher_name,substr(teacher_name,0,18))as jc_004  from base_teacher
 

--查找某个字符串
instr(gnfl_dm,'DSFXX',1,1)
从gnfl_dm字段里查找 字符DSFXX  从gnfl_dm字段的第一个字符开始算，找到第一个匹配的位置
 
 
关于数字的格式
使用fmxxx.xxx可以实现   dddd.xxxx  dddd是小数点前的位置xxxx是小数点后的位置。dddd需要预判最大有几位，xxxx可以用来实现精度  注意 这里的 990 小数点前一位要是0其他用9来代替 ，小数点后面的用0，否则会有 “100.” 这样的后面带点的数据
　　select to_char(mx.mxfz,'fm990.00') from fxyd_ydmx mx
 详见 http://blog.csdn.net/folio/article/details/7010976
 
 
Oracle比较一列的最大值或者最小值，我们会不假思索地用MAX和MIN函数，但是对于比较一行的最大值或最小值呢？是不是日常用的少，很多人都不知道有ORACLE也有内置函数实现这个功能：COALESCE / GREATEST /LEAST.
 
1. COALESCE 返回该表达式列表的第一个非空value。
    格式： COALESCE(value1, value2, value3, ...)
    含义： 返回value列表第一个非空的值。
               value列表必须是相同类型，也可以是一个表的同一行、不同列的值进行比较。
    EXAMPLE: select coalesce (1, null, 2 ) from dual ; -- 返回1
                     select coalesce ( null, 2, 1 ) from dual ; -- 返回2
                     select coalesce (t.empno, t.mgr ) from  scott.emp t ; -- 效果类似 NVL( t.empno, t.mgr )
2. GREATEST 返回值列表中最大值
    格式： GREATEST(value1, value2, value3, ...)
    含义： 返回value列表最大的值。
               value列表必须是相同类型，也可以是一个表的同一行、不同列的值进行比较。
               当value值列表中有一个为NULL，则返回NULL值。
    EXAMPLE: select greatest (1, 3, 2 ) from dual ; -- 返回3
                     select greatest ( 'A', 'B', 'C' ) from dual ; -- 返回C
                     select greatest (null, 'B', 'C' ) from dual ; -- 返回null
                     select greatest (t.empno, t.mgr ) from  scott.emp t ; -- 返回empno和mgr 较大值
3. LEAST 返回值列表中最小值
    格式： LEAST(value1, value2, value3, ...)
    含义： 返回value列表最小的值。
               value列表必须是相同类型，也可以是一个表的同一行、不同列的值进行比较。
               当value值列表中有一个为NULL，则返回NULL值。
    EXAMPLE: select least (1, 3, 2 ) from dual ; -- 返回1
                     select least ( 'A', 'B', 'C' ) from dual ; -- 返回A
                     select least (null, 'B', 'C' ) from dual ; -- 返回null
                     select least (t.empno, t.mgr ) from  scott.emp t ; -- 返回empno和mgr 较小值