Oracle内置函数的相关知识

Oracle常用内置函数介绍及用法

 

lower     (str):将字符串转化为小写;  

upper     (str):将字符串转化为大写;  

initcap     (str):将字符串首字母转化为大写;  

select           lower     (     'AaBb'     )      as      aaa,      upper     (     'AaBb'     )      as      AAA,      initcap     (     'AaBb'     )      as      Aaa      from      dual;  

  

  

length     (str):返回字符串的长度  

lengthb     (str):返回字符串的长度(按照字节)  

select            length     (     'Mart'     )      name     ,      length     (     '烟台'     ) addr,      lengthb     (     '烟台'     ) addrb      from      dual;  

  

  

lpad           rpad  

lpad     (char1,n,char2):  

在字符串char1的左端填充字符串char2，直至字符串总长度为n,  

char2的默认值为空格，如果char1长度大于n，则该函数返回char1左端的前n个字符。  

如果输入参数值存在NULL，则返回NULL  

select           lpad     (     rpad     (     'gao'     ,      10     ,      '$'     ),      17     ,      '#'     )      from      dual;      --#######gao$$$$$$$       

select           lpad     (     rpad     (     'gao'     ,      10     ,      '$'     ),      15     ,      '#'     )      from      dual;      --#####gao$$$$$$$       

select           lpad     (     rpad     (     'gao'     ,      10     ,      '$'     ),      10     ,      '#'     )      from      dual;      --gao$$$$$$$       

select           lpad     (     rpad     (     'gao'     ,      10     ,      '$'     ),      8     ,      '#'     )      from      dual;      --gao$$$$$       

select           lpad     (     rpad     (     'gao'     ,      5     ,      '$'     ),      17     ,      '#'     )      from      dual;      --############gao$$       

select           lpad     (     rpad     (     'gao'     ,      2     ,      '$'     ),      17     ,      '#'     )      from      dual;      --###############ga       

       

       

-- trim ltrim rtrim  

select           rtrim     (     '  abce  '     )     ,      ltrim     (     '  abce  '     )     ,      trim     (     '  abce  '     )      from      dual;  

select           trim     (     's'           from           'string'     )     ,      trim     (     'g'           from           'string'     )     ,      trim     (     'r'           from           'string'     )      from      dual;      --tring       

select           trim      (     both           from           ' DWEYE '     ) "TRIM e.g."      from      dual;  

select           trim      (     trailing           from           ' DWEYE '     ) "TRIM e.g."      from      dual;      --  DWEYE       

select           trim      (     leading           from           ' DWEYE '     ) "TRIM e.g."      from      dual;      --DWEYE         

select           trim      (     'x'           from           'xxxxDWEYExxxx'     ) "TRIM e.g."      from      dual;      --DWEYE       

select           trim      (     both           'x'           from           'xxxxDWEYExxxx'     ) "TRIM e.g."      from      dual;      --DWEYE       

select           trim      (     trailing           'x'           from           'xxxxDWEYExxxx'     ) "TRIM e.g."      from      dual;      --xxxxDWEYE       

select           trim      (     leading           'x'           from           'xxxxDWEYExxxx'     ) "TRIM e.g."      from      dual;      --DWEYExxxx       

-- 'both' 参数表示同时去除字符串前后所指定的内容(默认情况下删除空格)       

-- 'trailing' 参数表示去除字符串后所指定的内容(默认情况下删除空格)       

-- 'leading' 参数表示去除字符串前所指定的内容(默认情况下删除空格)       

-- 注意：       

trim      删除指定字符的功能中，参数只允许包含一个字符，不支持多字符。可用ltrim      rtrim      来实现相应的功能  

select           ltrim     (     rtrim     (     '   Mart Li   '     ,      ' '     ),      ' '     )      from      dual;      --Mart Li       

select           ltrim     (     rtrim     (     'xxxMart Lizzz'     ,      'z'     ),      'x'     )      from      dual;      --Mart Li       

select           rtrim     (     'xyxxDWEYExyyx'     ,     'xy'     ) "e.g."      from      dual;      --xyxxDWEYE       

select           ltrim     (     'xyxxDWEYExyyx'     ,     'xy'     ) "e.g."      from      dual;      --DWEYExyyx       

-- 使用RTRIM和LTRIM函数时的注意事项：       

-- “xy”不表示整个“xy”字符串进行匹配，而是发现任意的字符“x”或字符“y”均做删除操作       

       

       

-- substr(src,          start          ,length) 截取字符串          ,下标从         1         开始              

select           substr     (     '1234567890'     ,      3     ,      5     ) aaa      from      dual;      --34567       

select           substr     (     '1234567890'     ,      0     ,      length     (     '1234567890'     )-     1     )      from      dual;      --123456789       

select           substr     (     '1234567890'     ,      1     ,      length     (     '1234567890'     )-     1     )      from      dual;      --123456789       

       

       

-- replace('String','oldstr','newstr') 字符串替换  

String        用于指定字符串   

oldstr   用于指定要被替换的子串  

newstr   用于指定替换后的子串  

如果 oldstr 为      NULL     ，则返回原有字符串，如果 newstr 为      NULL     ，则会去掉指定子串。  

  

  

-- count(column_name)  统计一个表中 column_name 列的数量  

select           count     (column_name)      from      authors;  

-- 如果相同的 column_name 出现了不止一次，则会被计算多次。       

可以通过关键字      distinct      得到不同 column_name 的数目：  

select           count     (     distinct      column_name)      from      authors  

-- 如果相同的column出现了不止一次，它将只被计算一次。       

count     (column_name),如果某一行的 column_name 是null，那么这一行将不计算，所以count(column_name)是计算所有值不为NULL的数据。  

即count(column_name) 就只会统计非空行.   

count     (*) 和count(     'x'     )，     count     (     'y'     ),     count     (     1     ),     count     (     2     ) 这3个都代表所有行,因为每行数据都不为null  

补充，在表的主键不同情况下，     count     (*)和count(数字)的执行效率是不同的:  

count     (字段)时，如果字段为主键，则效率最高，     count     (     1     )次之，     count     (*)最慢;  

如果字段不为主键，则count(     1     )最快，     count     (*)次之，     count     (字段)最慢;  

  

  

-- abs(n)  

用于返回数字n的绝对值，如果输入为NULL，则返回值也是NULL  

select           abs     (     100     ),      abs     (-     100     )      from      dual;      --100 100       

-- ceil(n)   

返回大于等于数字n的最小整数，若输入NULL，则返回为NULL  

select           ceil     (     3.1415927     )      from      dual;      --4       

-- floor(n)  

返回小于等于数字n的最大整数，若输入NULL，则返回NULL  

select           floor     (     2345.67     )      from      dual;      --2345       

       

       

-- sign(n) 用于检测数字的正负       

  若n<     0     ; 则返回-     1     ;  

  若n=     0     ; 则返回0;  

  若n>     0     ; 则返回1;  

  若输入值为NULL，则返回NULL  

select           sign     (     123     ),      sign     (-     100     ),      sign     (     0     )      from      dual;      -- 1 -1 0       

       

-- mod(n1,n2)  

返回n1除以n2的余数  

select           mod     (     10     ,      3     ),      mod     (     3     ,      3     ),      mod     (     2     ,      3     )      from      dual;      --1 0 2       

       

       

-- roundn(n[,m]) truncn(n[,m]) cut  

round     (n[,m])：返回n的四舍五入值,其中n可为任意数字，m必须为整数。  

  若省略m或者m=     0     ,则四舍五入到整数位;  

  若m<     0     , 则四舍五入到小数点前的第m位;  

  若m>     0     , 则四舍五入到小数点后的第m位;  

  若输入NULL, 则输出NULL.  

trunc     (n[,m])：该函数用于截取数字,其中n可以是任意数字, m必须是整数。  

  若m省略，则会将数字n的小数部分截去;  

  若m>     0     , 则将数字n截取至小数点后的第m位;  

  若m<     0     , 则将数字n截取至小数点前的第m位.  

select           round     (     55.6     ),      round     (-     55.4     ),      trunc     (     55.6     ),      trunc     (-     55.4     )      from      dual;      --56 -55 55 -55       

       

round     (d[,fmt]):返回日期时间的四舍五入结果。  

  参数d用于指定日期时间值，参数fmt用于指定四舍五入的方式。  

  如果设置fmt为YEAR，则7月1日为分界线；  

  如果设置fmt为MONTH，则16日为分界线；  

  如果设置fmt为DAY，则中午12：     00     时为分界线。  

trunc     (d[,fmt])：用于截断日期时间数据。fmt用于指定截断日期时间数据的方法。  

  如果设置fmt为YEAR，则结果为本年度的1月1日；  

  如果设置fmt为MONTH，则结果为本月1日。  

select           round     (     sysdate     ,     'YEAR'     )      from      dual;      --2012-1-1 (today is 2011-07-01)       

select           round     (     sysdate     ,     'YEAR'     )-     1           from      dual;      --2011-12-31(today is 2011-07-01)       

select           trunc     (     sysdate     ,     'YEAR'     )      from      dual;      --2011-1-1(today is 2011-07-27)       

select           trunc     (     124.1666     , -     2     ) trunc1,      trunc     (     124.16666     , -     1     ) trunc2      from      dual;      --100 120       

select           trunc     (     124.1666     ,      0     ) trunc1,      trunc     (     124.16666     ,      2     ) trunc2      from      dual;      --124 124.16       

       

       

-- add_months(d,n)  

返回特定日期时间之后或之前的几个月所对应的日期时间  

d:给定的日期时间;  

n:可以是任意整数; 当n<     0     时,返回d之前n个月对应的日期时间;当n>     0     时,返回d之后n个月对应的日期时间.  

select      to_char(add_months(to_date(     '199912'     ,     'yyyymm'     ),     2     ),     'yyyymm'     )      from      dual;  

select      to_char(add_months(to_date(     '199912'     ,     'yyyymm'     ),-     2     ),     'yyyymm'     )      from      dual;  

select      to_char(add_months(to_date(     '2010-12'     ,      'yyyy-mm'     ),      2     ),      'yyyy-mm'     )  

       from      dual;      --2011-02       

select      to_char(add_months(to_date(     '2010-12'     ,      'yyyy-mm'     ), -     2     ),      'yyyy-mm'     )  

       from      dual;      --2010-10       

       

       

-- last_day(d)  

返回特定日期所在月份的最后一天，参数d表示给定日期。  

select      to_char(     sysdate     ,      'yyyy-mm-dd'     ), to_char((     sysdate     ) +      1     ,      'yyyy-mm-dd'     )      from      dual;      --2011-07-01 2011-07-02       

select      to_char(last_day(     sysdate     ),      'yyyy-mm-dd'     )      from      dual;      --2011-07-31       

       

       

-- months_between(d1,d2) 返回日期d1和d2之间相差的月数;  

  如果d1小于d2,则返回负数;  

  如果d1和d2的天数相同或都是月底,则返回整数;  

  否则Oracle以每月31天为准，计算结果的小数部分。  

select      months_between(     '19-12月-2010'     ,      '19-3月-2010'     ) mon_between      from      dual;      --9       

select      months_between(to_date(     '2010-03-19'     ,      'yyyy-mm-dd'     ), to_date(     '2010-12-19'     ,      'yyyy-mm-dd'     )) mon_betw      from      dual;      -- -9       

select      months_between(to_date(     '2005.05.20'     ,      'yyyy.mm.dd'     ), to_date(     '2010.05.20'     ,      'yyyy.mm.dd'     )) mon_betw      from      dual;      -- -60       

       

       

-- new_time(date,zone1,zone2)  

select      to_char(     sysdate     ,      'yyyy.mm.dd hh24:mi:ss'     ) bj_time,  

       to_char(new_time(     sysdate     ,      'PDT'     ,      'GMT'     ),      'yyyy.mm.dd hh24:mi:ss'     ) los_angles  

       from      dual;      --2011.07.01 14:45:31 2011.07.01 21:45:31       

       

       

-- next_day(date,char) 返回特定日期之后的第一个工作日所对应的日期。  

  date表示给定日期时间;  

  char用于指定工作日,工作日必须与日期语言匹配。  

select      next_day(     '18-5月-2010'     ,      '星期五'     ) next_day      from      dual;      --2010-5-21       

select      next_day(to_date(     '2011-07-01'     ,      'yyyy-mm-dd'     ),      '星期一'     ) next_day      from      dual;      --2011-7-4       

select      next_day(to_date(     '2011-07-01'     ,      'yyyy-mm-dd'     ),      '星期二'     ) next_day      from      dual;      --2011-7-5       

       

       

-- sysdate  getdate()  

sysdate     : Oracle数据库 获得系统当前时间的函数  

getdate():SQLServer数据库 获得当前的日期和时间的函数  

select      getdate()      from      dual;      --NOV 30 1997 3:29AM       

select      to_char(     sysdate     ,      'yyyy-mm-dd day'     )      from      dual;      --2011-07-01 星期五       

       

       

-- greatest least  

greatest     :返回一组表达式中的最大值,即比较字符的编码大小.  

least     :返回一组表达式中的最小值.  

select           greatest     (     'AA'     ,     'AB'     ,     'AC'     ) gtc      from      dual;      --AC       

select           greatest     (     '啊'     ,     '安'     ,     '天'     ) gtc      from      dual;      --天       

select           least     (     '啊'     ,     '安'     ,     '天'     ) ltc      from      dual;      --啊       

       

       

-- avg(distinct|all) 函数返回数值列的平均值  

all     :表示对所有的值求平均值;  

distinct     :只对不同的值求平均值  

NULL      值不包括在计算中。  

select           avg     (     distinct      t.data_length)      from      user_tab_cols t;      --323.2325       

select           avg     (     all      t.data_length)      from      user_tab_cols t;      --48.39710       

       

       

-- max min  

函数MAX（）返回一个数值型字段的所有值中的最大值。  

函数MIN（）返回一个数值型字段的所有值中的最小值。  

如果字段是空的，     MAX     ()      MIN     ()返回空值  

select           max     (     all      t.data_length)      from      user_tab_cols t;      --4000       

select           min     (     all      t.data_length)      from      user_tab_cols t;      --0       

       

       

-- decode(condition, case1, value1, [case2, value2, ...,] default_value)  

-- 条件判断函数       

select           decode     (     'red'     ,     'red'     ,     '红灯'     ,     'green'     ,     '绿灯'     ,     '黄灯'     ) color      from      dual;      --红灯       

select           decode     (     'green'     ,     'red'     ,     '红灯'     ,     'green'     ,     '绿灯'     ,     '黄灯'     ) color      from      dual;      --绿灯       

select           decode     (     'yellow'     ,     'red'     ,     '红灯'     ,     'green'     ,     '绿灯'     ,     '黄灯'     ) color      from      dual;      --黄灯       

       

       

-- NVL(expr1, expr2)   

若expr1为NULL，返回expr2;  

若expr1不为NULL，返回expr1;  

注意两者的类型要一致   

常用于 存储过程中 条件的判断  

select           nvl     (     'abc'     ,     'def'     )      from      dual;      -- abc       

select           nvl     (     null     ,     'Replace Null Column'     ) ideacol      from      dual;      --Replace Null Column       

-- NVL2 (expr1, expr2, expr3)   

若expr1不为NULL，返回expr2;  

若expr1为NULL，返回expr3;  

expr2和expr3类型不同的话，expr3会转换为expr2的类型  

  

  

-- NULLIF (expr1, expr2)  

相等返回NULL，不等返回expr1  

  

  

-- sum(colname)  计算字段值的和  

select           sum     (     null     )      from      dual;      --(空)       

select           sum     (t.num_rows)      from      user_tables t;      --598441       

函数SUM（）的返回值代表字段中所有值的和。字段的数据类型也许是MONEY型，但你也可以对其它数值型字段使用函数SUM（）。  

  

  

-- to_char(n[,fmt]) 数字转换成字符  

其实：to_number,to_char,to_date等转换函数都可以在很多数据类型之间进行转换,  

to_lob一般只能将long、     long      raw转换为clob、     blob     、nclob类型  

  

  

-- like 模糊查询  

用到like关键字和通配符，有时候还得用到转义字符:  

其中通配符有两个："_"代表一个字符，"%"代表零个或多个字符;  

如果一个字符串中本身含有 "%"或"_",那么我们就要用转义字符,      ' \ '      就是一个转义字符，我们还可以自己定义一个转移字符：  

select      ename      from      emp      where      ename      like           '%$%%'           escape           '$'     ;  

其中escape将指明$符号为转移字符，默认情况下oracle把      ' \ '      作为转移字符.  

  

  

-- rownum rowid  

Oracle中有两个伪列叫做：rownum和rowid。  

rowid是每条记录的唯一标示，而rownum则是每条记录在表中从第一条记录到最后的一个排序标号;  

可以通过      rownum      选出某个表中前几条记录入：  

select      *      from      emp      where           rownum      <      5      则可以选出前五行记录  

但是不能用      rownum      选出后几条记录来,如：  

select      *      from      emp      where           rownum      >      10      后十条记录：这是错误的方法。  

凡是大于或等于的都选不出来。  

rownum      本身不显示在表中，它是隐藏着的，只有通过select语句显示的在select列表中注明rownum字段才能被选出来展示，  

如子查询选出的一张新表，它同时也具有了一个伪列  

--扩展: Oracle 分页的实现：       

select      ename, sal   

       from      (     select      ename, sal,      rownum        

           from      (     select      ename, sal      from      emp      order           by      sal      desc      ))  

      where           rownum      >=      6        

        and           rownum      <=     10     ;  

  

最后总结一句话就是rownum是oracle加在你最后取出来的结果集上的一个伪字段，它记录着每一行的行号，  

而这个行号只能和小于、小于等于使用,不能和等于、大于或大于等于一起使用,  

要强行使用的话只能用子查询解决。