oracle常见函数1

一。trunc

trunc函数用于截取数字

1. 截取日期
   trunc(日期数据，截取格式)

trunc(sysdate，'yyyy');//返回当前年的第一天
trunc(sysdate, 'mm');//返回当前月的第一天
trunc(sysdate, 'dd');//返回当前时间的年月日
trunc(sysdate, 'd');//返回当前星期的第一天
trunc(sysdate, 'hh');//返回当前小时

2. 截取数字
   trunc(数字，截取位数)，截取位数默认为0，截取时不进行四舍五入

 - 为0表示截取整数部分
 - 为正数n表示截取到小数点右边n位
 - 为负数表示小数点左边n位及右边的全置为0。如果n大于整数位数，则整个数变为0

二。rownum

rownum是返回的查询结果集中每行记录的行号

• rownum用在子查询中时，必须要起一个别名，外层查询才能正常使用
• 在where子句中使用rownum做等值查询时要注意，rownum只能=1,或者使用<、<=、!=1或者大于1的数
• rownum在与排序一起使用时，如果想先排序再用rownum筛选，可以用子查询返回排序结果，再用rownum筛选。或者给order by 使用的列加上索引或者主键，那么可以直接实现排序再用rownum筛选，不用子查询

三。concat

CONCAT(char1 , char2)

可以拼接两个字符串，但是一次只能拼接两个，拼接多个字符串时要嵌套多层，可读性较差，可以采用"||"字符来拼接多个字符串

四。instr

1. 查找是否包含字串

instr( string1, string2 )    // instr(源字符串, 目标字符串)

2. 查找子串第n次出现的位置

instr( string1, string2 ,start,n )   // instr(源字符串, 目标字符串, 起始位置, 匹配序号)

比较常见的是第一种instr(name,'helloworld')>0，用于查找字段值中是否包含指定字符串
如果start是正数，则从左到右查找，如果是负数，则从右到左查找。注意，oracle不像java，索引从1开始而不是0

五。substr

1. 截取指定区间的字子串

substr(string, int a, int b)

2. 从索引a开始截取右边的所有字符

substr(string string, int a)

六。nvl和nvl2

nvl

nvl(expr,expr1)

如果expr为null，则返回expr1，如果不为null则返回expr
常见用法;nvl(expr,0)，连接查询时将不满足条件的null值转换为0
nvl2

NVL2(expr1,expr2,expr3)

如果参数表达式expr1值为NULL，则NVL2()函数返回参数表达式expr3的值；如果参数表达式expr1值不为NULL，则NVL2()函数返回参数表达式expr2的值。

七。trim、ltrim、rtrim

trim

• trim(string) 去除string的左右空格
• trim(leading 's1' from 's2') 去除s2中等于s1的开头字符
• trim(trailing 's1' from 's2') 去除s2中等于s1的结尾字符
• trim(both 's1' from 's2') 如果指定了both或者上述三参数都没指定，则去除s2中等于s1的开头、结尾字符
• 如果只指定leading/both/trailing，没有指定s1,则默认去除空格
• 如果s1和s2有一个为null，则trim函数返回null

ltrim

ltrim(s1,s2)

s2并不作为一个整体参与匹配，而是从左开始遍历s1,删除在s2中包含的任意字符，遇到第一个不在s2中的字符停止遍历
rtrim

rtrim(s1,s2)

s2并不作为一个整体参与匹配，而是从右开始遍历s1,删除在s2中包含的任意字符，遇到第一个不在s2中的字符停止遍历

八。round

ROUND( number, n)

与trunc不同，round会对number四舍五入到小数点后n位

九。decode

decode(expr,if1,then1，if2，then2...else)

从左到右判断，如果expr值和某个if值相等，则返回对应的then值，if-then可以配置多对。如果和所有if值都不相等，则返回else值。如果else值没有配置，则默认返回null值。
ps：与case-when效果基本一致，但是case-when功能更强大，所以decode只适合做简单的等值多条件判断，复杂的区间多条件判断还是用case-when

十。connect by

connect by在oracle中主要有两种用途，树型结构查询与连续数据生成
连续数据生成
一般会生成连续的日期或者数字，例：

 Select TO_CHAR((to_date(#{beginDate},'yyyymmdd')+level-1),'yyyymmdd') daylist from dual
        connect by level <= (to_date(#{endDate},'yyyymmdd'))-(to_date(#{beginDate},'yyyymmdd')) +1

• 生成连续数据时，一般用level或者rownum关键字来从1开始生成连续数据，不使用prior、starts with等条件关键字。
• 在本例中生成了begindate~enddate之间的连续日期。需要注意的是，因为level起始为1，正常情况下是从begindate+1开始生成。所以生成连续日期时，如果想包含起始日期，要+(level-1),相当于整体前移一天。此时为了同时显示最后一天的数据，connect by level <= xxx还需要+1

树型结构查询
语法如下

select … from tablename
[start with 条件1]
connect by [nocycle] [prior] 条件2
where 条件3;

• 简述：在例如菜单表的有层级关系字段的表中，可以从某条记录开始，根据connect by 指定的按中序遍历树的模式不断地递归查找"子节点"
• 条件一指定从哪个"节点"开始，递归查找符合条件二指定的记录。如果不指定starts with，则会以表中每条记录为根，按条件二都递归查找一次
• 在条件二中，如果是 connect by prior a=b，则会查找所有记录的b字段值等于当前节点a字段值的记录，也即从上到下遍历树，查找子节点;如果是connect by a= prior b,则递归查找所有字段a的值与当前记录字段b的值相等的记录，即从下到上遍历树，查找父节点。
• 条件三则是所有递归查询完成后，筛选整个结果集

重要的函数和关键字

1. level:表示当前记录是"第几层"节点，其实就是递归查询的次数+1，经历两次递归查找到的满足条件二的记录，level即为3，因为根节点的level为1
2. nocycle:如果当前记录的a、b字段值相同，那么递归就会无限循环，使用该关键字可以忽略环结构。
3. CONNECT_BY_ROOT ：给字段添加该关键字，可以显示当前记录的父节点记录同名字段的值
4. CONNECT_BY_ISLEAF：可以显示当前记录是否还有满足条件二的子节点记录，返回0或1。0不是，1是
5. SYS_CONNECT_BY_PATH(字段名，分隔符):该函数可以将查找到的每条记录的某个字段值连接成一个字符串，以指定的分隔符分隔，一般从starts with指定的节点记录开始。

十一。pivot与unpivot

pivot
pivot函数可以将指定列的某些列值转化为列名，同时还可以对每条记录的某一列做聚合函数计算
语法：pivot(任一聚合函数 for 需专列的值所在列名 in (需转为列名的值))
注意：

• "需转为列名的值"不能是动态的，也就是说in(…)不接受子查询，只能写死。在mybatis中写sql语句的时候，如果需要动态传入参数，请使用${}
• pivot在将一列的列值提升为列名时，原结果集中某一其他列的列值可能并没有需转化的列值，那么提升完毕后，就会出现null值。
  比如5.28日有22107号业务运行了5分钟，但是5月29日并没有22107号业务运行。当22107被提升为列名后，5月29日这行统计记录，22107这一列的值就会为null，导致查询出来时5月29号这条记录没有22107号业务对应的时间值。目前并没有找到比较好的方法处理这个问题，因为某些时候无法控制pivot函数的结果列，只能用select * 查询，查询到java代码中再手动填充0值。

unpivot
unpivot函数可以将指定列名降为列值。
语法：unpivot(新增值所在列的列名 for 新增列转为行后所在列的列名 in (需转为行的列名))

• 在转化之前，每一行的每一列都对应着一个值，所以需要指定列名降为列值后，对应的原值存放的列，同时要指定降为列值后列名存放的新列。
• 假设现在一行记录有五列要转化，有一列不动，指定两个新列存放转化后的列名与列值。那么转化完成后，原本的一行记录就变成了五行，对应三列。

十二。over()

over函数被称为分析函数，传统的分组+聚合的组合，每组只有一条记录。而分析+聚合每组可以有多条记录。
over()本身
over本身带有三大子句，语法为：OVER (PARTITION BY子句 ORDER BY子句 ROWS/RANGE子句)

• partition by ：按某个字段值分组
• order by :按某个字段值排序
• rows/range：每行记录形成物理/逻辑窗口，俗称开窗子句，必须要和order by一起使用，排序后才能开窗

rows/range 全称为rows/range between xxx preceding and xxx following

1.xxx可以是具体的数字，也可以是unbounded和current row关键字
unbounded表示无限，即可以统计当前行前面/后面无限的记录
current row表示到当前行为止，即窗口可以从当前行开始也可以到当前行结束
2.物理窗口rows：根据between … and …指定的范围形成窗口，比如 between1 preceding and 2following,即当前行的前一行到当前行的后两行为止，形成窗口
3.逻辑窗口range:与物理窗口不同，逻辑窗口以order by 指定的字段值为准，比如order by num between current row preceding and 2 following,那么从当前行开始，后续 num值<当前行的num+2都在窗口范围内

• 如果只分组不排序和开窗，那么与sum()、count()配合时，统计的范围是整个组
• 如果只排序，那么统计的范围是整个表；如果分组了再排序，统计的范围是整个组内。但注意，与sum()、count()配合时，每行记录的统计范围是第一行到当前行，即第一行统计自己，第二行统计前两行，第三行统计前三行。所以注意，传统的聚合统计类函数与分析函数配合使用时，慎重使用排序子句。
• 如果使用了开窗子句，那么统计的范围仅限基于当前行形成的窗口。
• 如果分组之后再开窗，那么开窗形成的窗口仅限当前组内，即开窗的范围可能是大于当前组的，但是会被截断。
• 开窗与排序配合使用时，如果是order by +rows,那么order by 可以指定多列；如果是order by+range，order by 只能指定一列
• rows指定范围时，必须使用数字或返回数字的表达式；range指定范围时，必须使用数字或者日期类型
• rank()+over()使用时，over()必须使用order by子句
• lead/lag、rank/dense_rank、row_number、FIRST_VALUE/LAST_VALUE、ntile不支持开窗子句。

over()支持的函数

• rank()/dense_rank over(partition by … order by …)

rank()和dense_rank()都会按照order by指定的字段为排序后的每条记录指定一个"级别",级别从1开始，相同字段值的级别会相同，所以此时order by指定单列
rank()在指定级别时，如果遇到连续的相同字段值，两个级别都是2，那么下一个不同字段值的级别将会是四，俗称为跳跃排序
dense_rank()在指定级别时，如果有多个连续相同级别，下一个不同的字段值的级别并不会跳跃，而是递增。

• min()/max() over(partition by …)

min()/max()可以取出组内或者整张表的某个字段的最大最小值
注意，如果使用了排序子句，那么max()+over()必须降序排序，因为单纯的排序子句是之前的所有行到当前行为止，如果不降序排列就取不到组内的最大值，min()的排序则相反。

• lead/lag(列名,n,m) over(partition by … order by …)

lead/lag(列名,n,m) 可以取到当前行后面/前面某一行的记录
如果不指定n、m,则默认查找前/后一行的数据，如果找不到则默认返回null；如果指定了n、m，则返回前/后第n行记录的字段值，找不到则返回m

• FIRST_VALUE/LAST_VALUE() OVER(PARTITION BY …)

取出组内或全表第一行/最后一行记录的字段值

• ROW_NUMBER() OVER(PARTITION BY… ORDER BY …)

如果不使用分组子句，那么给整张表的记录从1开始指定一个序号，与rownum()相似；如果使用了分组子句，那么在各个组内从1开始指定序号，各组之间不影响

• sum/avg/count() over(partition by …)

功能与传统聚合函数一致，统计组内或者窗口内的字段总和、平均值、记录数
但要注意的是，如果只排序不开窗，那么每行的统计范围就是之前的所有行到当前行，所以尽量要么不排序要么排序+开窗。不过与min/max的区别就是单纯排序不限升序还是降序。

• NTILE(n) OVER(PARTITION BY… ORDER BY …)

可以将整张表或者组内数据切成n份，即可以实现组内再分块，每条记录都会有一个块号
如果不使用排序子句，则默认逆序排序
如果不能均匀分成n份，则多出来的数据将会被放到第一份中
不支持开窗

• min,/max/sum/avg/count… keep(dense_rank first/last ORDER BY …) [OVER(PARTITION BY… )]

keep可以在按某个字段排序后，取出最大/最小值的一条/多条记录–因为值可能相同，再统计这些记录中另一个字段的聚合值,所以只能是聚合函数+keep+[over],不能使用序列函数。可以使用over分组，但不能使用over排序、开窗

其他

• NULLS FIRST/LAST

可以将字段值为空值的记录放到表内/组内第一行或者最后一行
在使用RANK()、DENSE_RANK()、ROW_NUMBER()时，如果字段出现空值，则空值所在记录会在第一行。但有时候我们希望第一行从有效数据开始，所以我们可以设置空值放到最后一行。

本文只列出了over支持的比较常用的函数，更全的比如percent_rank()的使用请看oracle分析函数汇总

十三。sign

sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

十四。lpad与rpad

lpad从字符串左边开始对字符串追加字符，rpad则从右边开始
lpad

lpad( string, padded_length, [ pad_string ] )

• string:准备被填充的字符串；
• padded_length:填充之后的字符串长度，也就是该函数返回的字符串长度，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;
• pad_string:填充字符串，是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数将会在string的左边粘贴空格。

rpad

rpad(string,padded_length,[pad_string])

• string： 被填充的字符串
• padded_length：字符的长度，是返回的字符串的数量，如果这个数量比原字符串的长度要短，rpad函数将会把字符串截取成从左到右的n个字符;
• pad_string：是个可选参数，这个字符串是要粘贴到string的右边，如果这个参数未写，rpad函数将会在string的右边粘贴空格。