Oracle数据库常用函数

Oracle 数据库中主要使用两种类型的函数:

  1. 单行函数:操作一行数据,返回一个结果
    • 字符串函数:对字符串操作
    • 数字函数:对数字进行计算,返回一个数字
    • 日期函数:对日期和时间进行处理
    • 转换函数:可以将一种数据类型转换为另外一种数据类型
  2. 聚合函数(多行函数、分组函数、组函数):操作多行数据,并返回一个结果
    • SUM

字符串函数

字符函数接受字符参数,这些参数可以是表中的列,也可以是一个字符串表达式。

函数 说明
ASCII(X) 返回字符X的ASCII码
CONCAT(X,Y) 连接字符串X和Y
INSTR(X,STR[,START][,N) 从X中查找str,可以指定从start开始,也可以指定从n开始
LENGTH(X) 返回X的长度
LOWER(X) X转换成小写
UPPER(X) X转换成大写
LTRIM(X[,TRIM_STR]) 把X的左边截去trim_str字符串,缺省截去空格
RTRIM(X[,TRIM_STR]) 把X的右边截去trim_str字符串,缺省截去空格
TRIM([TRIM_STR FROM]X) 把X的两边截去trim_str字符串,缺省截去空格
REPLACE(X,old,new) 在X中查找old,并替换成new
SUBSTR(X,start[,length]) 返回X的字串,从start处开始,截取length个字符,缺省length,默认到结尾
示例 示例结果
SELECT ASCII('a') FROM dual; 97
SELECT CONCAT('Hello','world') FROM dual; Helloworld
SELECT INSTR('Hello world','or') FROM dual; 8
SELECT LENGTH('Hello') FROM dual; 5
SELECT LOWER('Hello') FROM dual; hello
SELECT UPPER('hello') FROM dual; HELLO
SELECT LTRIM('=Hello=','=') FROM dual; Hello=
SELECT RTRIM('=Hello=','=') FROM dual; =Hello
SELECT TRIM('='FROM'=Hello=') FROM dual; Hello
SELECT REPLACE('ABCDE','CD','AAA')FROM dual; ABAAAE
SELECT SUBSTR('ABCDE',2,3) FROM dual; BCD

数字函数

数字函数接受数字参数,参数可以来自表中的一列,也可以是一个数字表达式。

函数 说明 示例
ABS(X) X的绝对值 ABS(-3)=3
ACOS(X) X的反余弦 ACOS(1)=0
COS(X) 余弦 COS(1)=0.54030230586814
CEIL(X) 大于或等于X的最小值 CEIL(5.4)=6
FLOOR(X) 小于或等于X的最大值 FLOOR(5.8)=5
LOG(X,Y) X为底Y的对数 LOG(2,4)=2
MOD(X,Y) X除以Y的余数 MOD(8,3)=2
POWER(X,Y) X的Y次幂 POWER(2,3)=8
ROUND(X[,Y]) X在第Y位四舍五入 ROUND(3.456,2)=3.46
SQRT(X) X的平方根 SQRT(4)=2
TRUNC(X[,Y]) X在第Y位截断 TRUNC(3.456,2)=3.45

说明:

  1. ROUND(X[,Y]),四舍五入。

    在缺省 y 时,默认 y=0;比如:ROUND(3.56)=4。

    y 是正整数,就是四舍五入到小数点后 y 位。ROUND(5.654,2)=5.65。

    y 是负整数,四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400。

  2. TRUNC(x[,y]),直接截取,不四舍五入。

    在缺省 y 时,默认 y=0;比如:TRUNC (3.56)=3。

    Y是正整数,就是四舍五入到小数点后 y 位。TRUNC (5.654,2)=5.65。

    y 是负整数,四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300。

  3. TRUNC(x[,y]),直接截取,不四舍五入。

日期函数

日期函数对日期进行运算。常用的日期函数有:

  1. ADD_MONTHS(d,n),在某一个日期 d 上,加上指定的月数 n,返回计算后的新日期。

    d 表示日期,n 表示要加的月数。

    例:SELECT SYSDATE,add_months(SYSDATE,5) FROM dual;

  2. LAST_DAY(d),返回指定日期当月的最后一天。

    例:SELECT SYSDATE,last_day(SYSDATE) FROM dual;

  3. ROUND(d[,fmt]),返回一个以 fmt 为格式的四舍五入日期值, d 是日期, fmt 是格式模型。默认 fmt 为 DDD,即月中的某一天。

    • 如果 fmt 为“YEAR”则舍入到某年的 1 月 1 日,即前半年舍去,后半年作为下一年。
    • 如果 fmt 为“MONTH”则舍入到某月的 1 日,即前月舍去,后半月作为下一月。
    • 默认为“DDD”,即月中的某一天,最靠近的天,前半天舍去,后半天作为第二天。
    • 如果 fmt 为“DAY”则舍入到最近的周的周日,即上半周舍去,下半周作为下一周周日。

    例:

SELECT SYSDATE,
       ROUND(SYSDATE),
       ROUND(SYSDATE, 'day'),
       ROUND(SYSDATE, 'month'),
       ROUND(SYSDATE, 'year')
  FROM dual;

与 ROUND 对应的函数时 TRUNC(d[,fmt]) 对日期的操作, TRUNC 与 ROUND 非常相似,只是不对日期进行舍入,直接截取到对应格式的第一天。

  1. EXTRACT(fmt FROM d),提取日期中的特定部分。

    fmt 为:YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中 YEAR、MONTH、DAY可以为 DATE 类型匹配,也可以与 TIMESTAMP 类型匹配;但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配。
    HOUR 匹配的结果中没有加上时区,因此在中国运行的结果小 8 小时。

    例:

SELECT SYSDATE "date",
       EXTRACT(YEAR FROM SYSDATE) "year",
       EXTRACT(MONTH FROM SYSDATE) "month",
       EXTRACT(DAY FROM SYSDATE) "day",
       EXTRACT(HOUR FROM SYSTIMESTAMP) "hour",
       EXTRACT(MINUTE FROM SYSTIMESTAMP) "minute",
       EXTRACT(SECOND FROM SYSTIMESTAMP) "second"
  FROM dual;

转换函数

转换函数将值从一种数据类型转换为另外一种数据类型。常见的转换函数有:

  1. TO_CHAR(d|n[,fmt])
    把日期和数字转换为制定格式的字符串。Fmt是格式化字符串。
    代码解析:在格式化字符串中,使用双引号对非格式化字符进行引用。
参数 示例 说明
9 999 指定位置处显示数字
. 9.9 指定位置返回小数点
, 99,99 指定位置返回一个逗号
$ $999 数字开头返回一个美元符号
EEEE 9.99EEEE 科学计数法表示
L L999 数字前加一个本地货币符号
PR 999PR 如果数字式负数则用尖括号进行表示

例:SELECT TO_CHAR(-123123.45,'L9.9EEEEPR')"date" FROM dual;

  1. TO_DATE(X,[,fmt])
    把一个字符串以fmt格式转换成一个日期类型

  2. TO_NUMBER(X,[,fmt])
    把一个字符串以fmt格式转换为一个数字

    例:SELECT TO_NUMBER('-$12,345.67','$99,999.99')"num" FROM dual;

其它单行函数

  1. NVL(X,VALUE) 如果X为空,返回value,否则返回X

  2. NVL2(x,value1,value2) 如果x非空,返回value1,否则返回value2

聚合函数

参考文献

Oracle常用函数详解(详细)