ORACLE基础：常用函数

最新推荐文章于 2024-06-18 21:26:27 发布

原创最新推荐文章于 2024-06-18 21:26:27 发布 · 617 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #函数

java 专栏收录该内容

24 篇文章

订阅专栏

本文总结了Oracle数据库中常用的函数，包括字符串操作、数值处理、日期时间处理等，并提供了实际应用场景的SQL示例。

  oracle内置函数非常多，但在一般的开发中能用到的几乎不到一半，也可能是个人水平过低，反正我工作以来有些函数一次都没用过。
  今天把常用的部分函数做一个整理，方便日后查用。不常用的放到后边作为补充。

  0.avg()求平均值，max()求最大值,min()求最小值，distinct 去重

  1.CONCAT (' ',' ')连接两个字符串; ’||’符号也代表拼接
  SQL> select concat('010-','88888888')||'转23'   电话 from dual;
  电话
  ----------------
  010-88888888转23

  2.LENGTH 返回字符串的长度;
  SQL> select length(name),length(addr),length(to_char(sal)) from .db_user;
   LENGTH(NAME)           LENGTH(ADDR)       SAL LENGTH(TO_CHAR(SAL))
  ------------                            -------------------                  --------------------
           3                                      6                                               7

  3.LOWER 返回字符串,并将所有的字符小写
  SQL> select lower('AaBbCcDd')AaBbCcDd from dual;
  AABBCCDD
  --------
  aabbccdd

  4.UPPER 返回字符串,并将所有的字符大写
  SQL> select upper('AaBbCcDd') upper from dual;
  UPPER
  --------
  AABBCCDD

  5.LTRIM和RTRIM
  LTRIM 删除左边出现的字符串
  RTRIM 删除右边出现的字符串
  SQL> select ltrim(rtrim('   gao   ',' '),' ') from dual;
  LTRIM(RTRIM('
  -------------
  gao

  6.SUBSTR(string,start,count)
  取子字符串,从start开始,取count个
  SQL> select substr('13088888888',3,8) from dual;
  SUBSTR('
  --------
  08888888

  7.REPLACE('string','s1','s2')
   string   希望被替换的字符或变量
   s1       被替换的字符串
   s2       要替换的字符串
   SQL> select replace('he love you','he','i') from dual;
   REPLACE('HELOVEYOU','HE','I')
   ------------------------------
   i love you

  8.TRIM('s' from 'string')
   select trim('s' from ' string ') from dual
   TRIM('s' from 'string')
   ------------------------------
    tring

  9.CEIL 返回大于或等于给出数字的最小整数,向上取整
   SQL> select ceil(3.1415927) from dual;

   CEIL(3.1415927)
   ---------------
   4

  10.FLOOR 对给定的数字取整数，向下取整
   SQL> select floor(2345.67) from dual;

   FLOOR(2345.67)
   --------------
   2345

   11.ROUND和TRUNC
   按照指定的精度进行舍入
   SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
   ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
   ----------- ------------ ----------- ------------
   56          -55          55          -55

   12.MOD(n1,n2) 返回一个n1除以n2的余数
   SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;
   MOD(10,3) MOD(3,3) MOD(2,3)
   --------- --------- ---------
   1         0         2

   13.TRUNC
   按照指定的精度截取一个数
   SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;

   TRUNC1 TRUNC(124.16666,2)
   --------- ------------------
   100             124.16

   14.ADD_MONTHS
   增加或减去月份
   SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;
   TO_CHA
   ------
   200002
   SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;
   TO_CHA
   ------
   199910

   15.LAST_DAY
   返回日期的最后一天
   SQL> select to_char(sysdate,'yyyy.mm.dd') date1,to_char((sysdate)+1,'yyyy.mm.dd')  date2 from dual;

   date1      date2
   ---------- ----------
   2004.05.09 2004.05.10
   SQL> select last_day(sysdate)  lastdate from dual;
   lastdate 
   ----------
   31-5月 -04

   16.MONTHS_BETWEEN(date2,date1)
   给出date2-date1的月份
   SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual;
   MON_BETWEEN
   -----------
   9
   SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.dd')) mon_betw from dual;
   MON_BETW
   ---------
   -60

   17.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;
   BJ_TIME                                 LOS_ANGLES
   -------------------                        -------------------
   2017.02.16 10:53:43         2017.02.16 17:53:43

   18.NEXT_DAY(date,'day')
   给出日期date和星期x之后计算下一个星期的日期
   SQL> select next_day('18-5月-2001','星期五') next_day from dual;
   NEXT_DAY
   ----------
   25-5月 -01

   19.SYSDATE 用来得到系统的当前日期
   SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual;

   TO_CHAR(SYSDATE,'
   -----------------
   16-02-2017 星期四

   20.TO_CHAR(date,'format')
   SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mm:ss') from dual;

   TO_CHAR(SYSDATE,'YY
   -------------------
   2004/05/09 21:14:41

  21.TO_DATE(string,'format') 将字符串转化为ORACLE中的一个日期

  22.TO_MULTI_BYTE 将字符串中的单字节字符转化为多字节字符
   SQL> select to_multi_byte('高') from dual;
   TO
   --
   高

   23.TO_NUMBER
   将给出的字符转换为数字
   SQL> select to_number('1999') year from dual;

   YEAR
   ---------
   1999


   24.GREATEST
   返回一组表达式中的最大值,即比较字符的编码大小.
   SQL> select greatest('AA','AB','AC') from dual;
   GR
   --
   AC
   SQL> select greatest('啊','安','天') from dual;
   GR
   --
   天


   25.LEAST
   返回一组表达式中的最小值
   SQL> select least('啊','安','天') from dual;

   LE
   --
   啊


   26..INSTR(C1,C2,I,J)
   在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
   C1    被搜索的字符串
   C2    希望搜索的字符串
   I     搜索的开始位置,默认为1
   J     出现的位置,默认为1
   SQL> select instr('oracle traning','ra',1,2) instring from dual;
    INSTRING
    ---------
        9

   27.RPAD和LPAD(粘贴字符)

   RPAD  在列的右边粘贴字符
   LPAD  在列的左边粘贴字符
   SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual;
   LPAD(RPAD('GAO',1
   -----------------
   *******gao*******
   不够字符则用*来填满

   以上函数为出现频率较高，非常实用的函数，下来是个人认为用的较少的函数
	
   28.ABS
   返回指定值的绝对值
   SQL> select abs(100),abs(-100) from dual;
    ABS(100) ABS(-100)
   --------- ---------
      100       100

   29.ACOS
    给出反余弦的值
   SQL> select acos(-1) from dual;
    ACOS(-1)
   ---------
   3.1415927

   30.ASIN
   给出反正弦的值
   SQL> select asin(0.5) from dual;
   ASIN(0.5)
   ---------
   .52359878

   31.ATAN
   返回一个数字的反正切值
   SQL> select atan(1) from dual;
     ATAN(1)
   ---------
   .78539816

   32.COS 
   返回一个给定数字的余弦
   SQL> select cos(-3.1415927) from dual;
   COS(-3.1415927)
   ---------------
             -1

   33.COSH
   返回一个数字反余弦值
   SQL> select cosh(20) from dual;
    COSH(20)
   ---------
   242582598

   34.EXP
   返回一个数字e的n次方根
   SQL> select exp(2),exp(1) from dual;
      EXP(2)    EXP(1)
   --------- ---------
   7.3890561 2.7182818

   35.LN
   返回一个数字的对数值
   SQL> select ln(1),ln(2),ln(2.7182818) from dual;
    LN(1)     LN(2) LN(2.7182818)
   --------- --------- -------------
        0 .69314718     .99999999

   36.LOG(n1,n2)
   返回一个以n1为底n2的对数 
   SQL> select log(2,1),log(2,4) from dual;
    LOG(2,1)  LOG(2,4)
    --------- ---------
        0         2

   37. SIGN
   取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
   SQL> select sign(123),sign(-100),sign(0) from dual;
   SIGN(123) SIGN(-100)   SIGN(0)
   --------- ---------- ---------
        1         -1         0

   38.SIN
   返回一个数字的正弦值
   SQL> select sin(1.57079) from dual;
   SIN(1.57079)
   ------------
           1

   39.SIGH
    返回双曲正弦的值
   SQL> select sin(20),sinh(20) from dual;
     SIN(20)  SINH(20)
   --------- ---------
   .91294525 242582598

   40.SQRT   返回数字n的根
   SQL> select sqrt(64),sqrt(10) from dual;
    SQRT(64)  SQRT(10)
   --------- ---------
        8 3.1622777

   41.TAN
   返回数字的正切值
   SQL> select tan(20),tan(10) from dual;
     TAN(20)   TAN(10)
   --------- ---------
   2.2371609 .64836083

    42.TANH
   返回数字n的双曲正切值
   SQL> select tanh(20),tan(20) from dual;
    TANH(20)   TAN(20)
   --------- ---------
        1          2.2371609

   43.CHARTOROWID
    将字符数据类型转换为ROWID类型
   SQL> select rowid,rowidtochar(rowid),ename from scott.emp;
   ROWID              ROWIDTOCHAR(ROWID) ENAME
   ------------------ ------------------ ----------
   AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH
   AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN
   AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD
   AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES
   
   44.HEXTORAW
    将一个十六进制构成的字符串转换为二进制

   45.RAWTOHEXT
   将一个二进制构成的字符串转换为十六进制

   46.BFILENAME(dir,file)
   指定一个外部二进制文件
   SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));

   47.USER
   返回当前用户的名字
   SQL> select user from  dual;
   USER
   ------------------------------
   GAO

   48.USEREVN
   返回当前用户环境的信息,opt可以是:
   ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
   ISDBA  查看当前用户是否是DBA如果是则返回true
   SQL> select userenv('isdba') from dual;
   USEREN
   ------
   FALSE
   SQL> select userenv('isdba') from dual;
   USEREN
   ------
   TRUE
   SESSION
   返回会话标志
   SQL> select userenv('sessionid') from dual;
   USERENV('SESSIONID')
   --------------------
                 152
   ENTRYID
   返回会话人口标志
   SQL> select userenv('entryid') from dual;
   USERENV('ENTRYID')
   ------------------
                 0
   INSTANCE
   返回当前INSTANCE的标志
   SQL> select userenv('instance') from dual;
   USERENV('INSTANCE')
   -------------------
                  1
   LANGUAGE
   返回当前环境变量
   SQL> select userenv('language') from dual;
   USERENV('LANGUAGE')
   ----------------------------------------------------
   SIMPLIFIED CHINESE_CHINA.ZHS16GBK
   LANG
   返回当前环境的语言的缩写
   SQL> select userenv('lang') from dual;
   USERENV('LANG')
   ----------------------------------------------------
   ZHS
   TERMINAL
   返回用户的终端或机器的标志
   SQL> select userenv('terminal') from dual;
   USERENV('TERMINA
   ----------------
   GAO
   VSIZE(X)
   返回X的大小(字节)数
   SQL> select vsize(user),user from dual;
   VSIZE(USER) USER
   ----------- ------------------------------
          6 SYSTEM

   49.STDDEV(distinct|all)   求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
      SQL> select stddev(sal) from scott.emp;
   STDDEV(SAL)
   -----------
  1182.5032
   SQL> select stddev(distinct sal) from scott.emp;
   STDDEV(DISTINCTSAL)
   -------------------
           1229.951

   50.VARIANCE(DISTINCT|ALL)
   求协方差
   SQL> select variance(sal) from scott.emp;
   VARIANCE(SAL)
   -------------
    1398313.9