Oracle 常用函数

Oracle内置SQL函数主要分为一下类别：数字函数、字符函数、日期函数、转换函数、集合函数、分析/聚合函数

一、单行函数

1、数字函数

       abs(n)：返回数字n的绝对值

      ceil(n)：返回>=数字n的最小整数

      floor(n)：返回<=数字n的最大整数

      round(n,[m])：四舍五入运算,如果m缺省则四舍五入到整数位

                               m<0,四舍五入到小数点的前m位,m>0四舍五入到小数点的后m位

      trunc(n,[m])：截取数字,如果m缺省则将小数位截去

                              m<0,截取到小数点的前m位,m>0截取到小数点的后m位

 select ceil(14.1),floor(14.1) from dual;

执行后为

select abs（-100）from dual；

执行后为

  mod 取余函数

select mod(9,2) from dual;

执行后为：

  当2换为0时，则为9

 sign 返回数的符号 正数为1 负数为-1，0为0

select sign(-2),sign(0),sign(2) from dual;

执行后为

  Power(n1，n2)  返回 n1的n2次幂     Exp(n)  返回e的n次幂

select power(3,3),Exp(2) from dual;

执行后为

2、字符函数

      ascii(n)：返回字符c的ascii值

      chr(n)：将ascii值转换为对应的字符

      initcap(s)：将字符串s所有的单词(依空格或非字母字符划分单词区间)的首字母大写,其余小写

      lower(s)：将字符串s中所有的字符转换为小写

      upper(s)：将字符串s中所有的字符转换为大写

      concat(s1,s2)：将字符串s2连接在s1后面，等同于操作符||

      length(s)：将返回字串s的长度，返回的长度包括其中的所有空格(尾部空格也算)；如果s为null，则返回null

      cast转换类型

  select ascii('qwer') from dual;

  执行后为

         

select length('qwer') from dual;

    select cast('123'as Integer) from dual;

         

  lpad(s1,n,s2)：在字串s1的左端填充字串s2，直至填充后的s1的总长度为n

                                如果不指定s2则默认为空格

                                如果s1的长度>n，则直接返回s1左端的n个字符

  rpad(s1,n,s2)：在字串s1的右端填充字串s2，直至填充后的s1的总长度为n

                                如果不指定s2则默认为空格

                                如果s1的长度>n，则直接返回s1左端的n个字符

1. SELECT lpad('a',10,'8'),lpad('LaaaaaaR',6,'8'),  
2.        rpad('a',10,'8'),rpad('LaaaaaaR',6,'8')  
3.   FROM dual  

--执行结果：

lpad('a',10,'8')              888888888a

lpad('laaaaaar',6,'8')     laaaaa
rpad('a',10,'8')              a888888888
rpad('laaaaaar',6,'8')     laaaaa

  instr(s1,s2,n,m)：     取得子串s2在字串s1中的位置

                                    n表示在s1中开始搜索的位置，m表示字串s2出现的次数

                                    如果n为负数，则表示从尾部开始搜索，n与m默认为1

  substr(s1,m,n)：  取得字串s1从m开始，长度为n的子串，m>0表示从头开始搜索，m<0表示从尾开始

  replace(s1,s2,s3)：将s1字串中的子串s2用s3替代，如果s2为null则返回原来的字串s1

                                        注意：如果s3为null，则会去掉子串s2

3、日期函数

      sysdate：返回系统当前日期时间

      systimestamp：返回系统当前日期时间和时区

      current_date：返回当前回话时区所对应的日期和时间

      current_timestamp：返回当前回话时区所对应的日期时间

      localtimestamp：返回当前回话时区所对应的日期时间

      systimestamp：返回系统当前日期时间和时区

      sessiontimezone：返回当前回话所在的时区

      dbtimezone：返回资料库所在的时区

执行结果：

SYSDATE                         2015/3/25 13:29:38
SYSTIMESTAMP                25-3月 -15 01.29.38.867634 下午 +08:00
CURRENT_DATE               2015/3/25 13:29:38
CURRENT_TIMESTAMP      25-3月 -15 01.29.38.867641 下午 +08:00
LOCALTIMESTAMP            25-3月 -15 01.29.38.867641 下午
SESSIONTIMEZONE          +08:00
DBTIMEZONE                   +08:00

4.转换函数

to_date("要转换的字符串","转换的格式")  两个参数的格式必须匹配，否则会报错。

即按照第二个参数的格式解释第一个参数。

to_char(日期,"转换格式" )即把给定的日期按照“转换格式”转换。

select to_char(sysdate,'yy-mm-dd') from dual;

 

Select to_date('2005-12-25,13:25:59','yyyy-mm-dd,hh24:mi:ss') from dual

   

5.其他函数

Lvvnl   用于某个语句的where子句中的条件，如果条件为true就返回false；如果条件为UNKNOWN或者false就返回true。该函数不能用于复合条件如AND, OR, or BETWEEN中。

select * from empwhere lnnvl(sal<=3000)

 

nvl (expr1,expr2)   如果expr1为null 则返回expr2  否则返回expr1  替换时常用 Select  productname，nvl(quantity,0) from productinfo     表示若quantity为NULL返回0否则返回quantity的值 

select nvl(stuname,'xxxxxx') from student

Nvl2(expr1,expr2，expr3)  意思一样当expr1为空返回expr3，不为空返回expr2

select nvl2(stuname,'xxxxxx','yyyyyyyyy') from student

6.分组函数

order by 默认asc（从小到大排序）desc（从大到小排序）

select * from student order by stuno desc;

group By

select Count(*) from empgroup by hiredate;

执行完后可以发现有2个一样的

Having 一般与 Group by 公用的限制语句他不与单个值有关而是与组有关 

select Count(*) from empgroup by hiredate having count(*)>1;

执行完后查询按日期分组并且日期一样的

7、分析聚合函数

       rank(expr1,expr2,...) within group(order by col1,col2,...)：返回特定數值在統計數值中所佔據的等                   級,expr1,ex pr2,...必須為常數
       dense_rank(expr1,expr2,...) within group(order by expr1,expr2,...)：返回特定數值在一組行數據中的等         級
       percent_rank(expr1,expr2,...) within group(order by expr1,expr2,...)：返回特定數值在統計數值在統計         級別中所占的比例

        over()視窗函數
           over ([partition by col1] order by col2) ) 表示依col1分組,依col2在分組類排序
           over函數不能夠單獨使用，必須和其它的分組/分析函數配合使用 
           over函數不可用在where子句中

1. --row_number()結合over排序統計2012-04薪資水平中各部門最高的2位    
2.   -- select cqk09 姓名,cqk05 部門,cqk30 薪水,    
3.   --  row_number() over(partition by cqk05 order by cqk30 desc) as salary_order      
4.   --  from cqk_file    
5.   --  where cqk03=2012 and cqk031 = 4 and salary_order<=2 --此處無法引用salary_order,直接用over運算式也是錯誤的    
6.   -- 上述SQL段是錯誤的over查詢出來依別名做列也不可以于where子句中使用    
7.   select cqk09 姓名,cqk05 部門,cqk30 薪水,salary_order 排序     
8.     from (    
9.            select cqk09,cqk05,cqk30,    
10.                   row_number() over(partition by cqk05 order by cqk30 desc) salary_order      
11.            from cqk_file    
12.            where cqk03=2012 and cqk031 = 4 and cqk05 in('2G60','2G88')    
13.          )cqk_temp_file    
14.     where salary_order <= 2    

--结果：

姓名部門薪水排序BX2G60778841AX2G60330862SS2G88709941DX2G88562732

row_number() 、rownum、rank()、dense_rank()區別說明
row_number() over(partition by col1 order by col2)：此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)

                                                                                    row_number()和rownum差不多，功能更强一点(可以在各个分组内从1开时排序)

rank()：是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内)

dense_rank()：是连续排序,有两个第二名时仍然跟着第三名.相比之下row_number是没有重复值的.