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.iteye.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)->expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型

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 较小值