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比较一列的最大值或者最小值,我们会不假思索地用MAXMIN函数,但是对于比较一行的最大值或最小值呢?是不是日常用的少,很多人都不知道有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 较小值

    原文作者:zwxiaole
    原文地址: https://blog.csdn.net/zwxiaole/article/details/84413625
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。
点赞