Oracle常用的系统函数

╰+攻爆jí腚メ 2021-09-12 02:24 532阅读 0赞

SQL语言是一种脚步语言，它提供了大量内置函数，使用这些内置函数可以大大增强SQL语言的运算和判读功能。

# 1、字符类函数 #

字符类函数是专门用于字符串处理的函数，处理的对象可以是字符或字符串常量，也可以是字符类型的列，常用的字符类函数有如下几种：

## 1.1 ASCII(c)和CHR(i)函数 ##

ASCII(c)函数用于返回一个字符串的ASCII码，其中参数c表示一个字符；CHR(i)函数用于返回给出ASCII码值所对应的字符，i表示一个ASCII码值。

**【实例】**分别求得“A、Z、D和空格”的ASCII码。

SQL> SELECT ASCII('Z') Z, ASCII('H') H, ASCII('D') D, ASCII(' ') SPACE FROM DUAL;
             Z          H          D      SPACE
    ---------- ---------- ---------- ----------
            90         72         68         32

**【实例】**使用chr函数再返回其对应的字符。

SQL> SELECT CHR(90),CHR(72),CHR(68),(32)S FROM DUAL;
    CHR(90) CHR(72) CHR(68)          S
    ------- ------- ------- ----------
    Z       H       D               32

## 1.2 CONCAT(s1,s2)函数 ##

该函数将字符串s2连接到字符串s1的后面。

**【实例】**使用CONCAT函数连接“Hello”和“World!”两个字符串。

SQL> SELECT CONCAT('HELLO','WORLD!') INFORMATION FROM DUAL;
    INFORMATION
    -----------
    HELLOWORLD!

## 1.3 INITCAP(s)函数 ##

该函数将字符串s的每个单词的第一个字母大写，其他字母小写。单词之间用空格、控制字符、标点符号来区分。

**【实例】**使用INITCAP函数转换字符串“oh my god!”的输出。

SQL> SELECT INITCAP('oh my god!') INFORMATION FROM DUAL;
    INFORMATION
    -----------
    Oh My God!

## 1.4 INSTR(s1,s2\[,i\]\[,j\])函数 ##

该函数用于返回字符串s2在字符串s1中第j次出现时的位置，搜索从字符串s1的第i个字符开始。当没有发现要查找的字符时，该函数返回值为0；如果i为负数，那么搜索将从右到左进行，但函数的返回位置还是按从左到右来计算。其中，s1和s2均为字符串；i和j均为正整数，默认值为1.

**【实例】**在字符串“oracle 11g”中，从第3个字符开始查询字符串“1”第2次出现的位置。

SQL> SELECT INSTR('oracle 11g','1',3,2) ABC FROM DUAL;
           ABC
    ----------
             9

## 1.5 LENGTH(s)函数 ##

该函数用于返回字符串s的长度，如果s为null，则返回值为null。

**【实例】**在SCOTT模式下，通过使用LENGTH函数返回雇员名称长度大于5的雇员信息及所在部门信息。

SQL> SELECT E.EMPNO,E.ENAME,D.DNAME
      2  FROM EMP E INNER JOIN DEPT D
      3  ON E.DEPTNO = D.DEPTNO
      4  WHERE LENGTH(E.ENAME)>5;
    EMPNO ENAME      DNAME
    ----- ---------- --------------
     7654 MARTIN     SALES
     7844 TURNER     SALES
     7934 MILLER     ACCOUNTING

## 1.6 LOWER(s)函数和UPPER(s)函数 ##

LOWER(s)函数和UPPER(s)函数分别用于返回字符串s的小写形式和大写形式。

**【实例】**在HR模式下，在EMPLOYEES表中检索雇员名称以字母“a”开头的员工信息，并将first\_name字段的值转换为小写，将last\_name字段的值转换为大写。

SQL> SELECT EMPLOYEE_ID,LOWER(FIRST_NAME),UPPER(LAST_NAME) FROM EMPLOYEES WHERE LOWER(FIRST_NAME) LIKE 'a%';

## 1.7 LTRIM(s1,s2)函数、RTRIM(s1,s2)函数和TRIM(s1,s2)函数 ##

这3个函数分别用来删除字符串s1左边的字符串s2、删除字符串s1右边的字符串s2，删除字符串s1左右两端字符串s2。如果这3个函数中不指定字符串s2，则表示去除相应方位的空格。

**【实例】**使用LTRIM、RTRIM和TRIM函数分别去掉字符串“\#\#\#\#East\#\#\#\#”、“East    ”和“\#\#\#East\#\#\#\#”中左侧“\#”、右侧空格和左右两侧的“\#”。

SQL> SELECT LTRIM('####East####','#'),RTRIM('East    '),TRIM('#'FROM'###East####') FROM DUAL;
    LTRIM('####EAST####','#') RTRIM('EAST') TRIM('#'FROM'###EAST####')
    ------------------------- ------------- --------------------------
    East####                  East          East

## 1.8 REPLACE(s1,s2,\[,s3\])函数 ##

该函数使用s3字符串替换出现在s1字符串中所有s2字符串，并返回替换后的新字符串。

**【实例】**使用REPLACE函数把字符串“Bad Luck Bad Gril”中的“Bad”字符串用“Good”替换。

SQL> SELECT REPLACE('Bad Luck Bad Gril','Bad','Good') FROM DUAL;
    REPLACE('BADLUCKBADGRIL','BAD'
    ------------------------------
    Good Luck Good Gril

## 1.9 SUBSTR(s,i,\[j\])函数 ##

该函数表示从字符串s的第i个位置开始截取长度为j的子字符串。如果省略参数j，则直接截取到尾部。其中，i和j为正整数。

**【实例】**使用SUBSTR函数在字符串“MessageBox”中从第8个位置截取长度为3的子字符串。

SQL> SELECT SUBSTR('MessageBox',8,3) FROM DUAL;
    SUBSTR('MESSAGEBOX',8,3)
    ------------------------
    Box

# 2、数字类函数 #

数字类函数主要用于执行各种数据计算，所有的数字类函数都有数字参数并返回数字值。Oracle提供了大量的数字类函数，这些函数大大增强了Oracle系统的科学计算能力。下面列出Oracle系统常见的数字类函数。

ABS(n)：返回n的绝对值。

CEIL(n)：返回大于或等于数值n的最小整数。

COS(n)：返回n的余弦值，n为弧度。

EXP(n)：返回e的n次幂，e=2.71828183。

FLORR(n)：返回小于或等于n的最大整数。

LOG(n1,n2)：返回以n1为底n2的对数。

MOD(n1,n2)：返回n1除以n2的余数。

POWER(n1,n2)：返回n1的n2次方。

ROUND(n1,n2)：返回舍入小数点右边n2位的n1的值，n2的默认值为0，这会返回小数点最接近的整数。如果n2为负数，就舍入到小数点左边对应的位上，n2必须是整数。

SIGN(n)：若n为负数，则返回-1，若n为正数，则返回1，若n=0，则返回0。

SIN(n)：返回n的正弦值，n为弧度。

SQRT(n)：返回n的平方根，n为弧度。

TRUNC(n1,n2)：返回结尾到n2位小数的n1的值，n2默认设置为0，当n2为默认设置时，会将n1截尾为整数，如果n2为负值，就截尾在小数点左边相应的位上。

## 2.1 CEIL(n)函数 ##

该函数返回大于或等于数值n的最小整数。

**【实例】**使用CEIL函数返回3个指定小数的整数值。

SQL> SELECT CEIL(7.3),CEIL(7),CEIL(-7.3) FROM DUAL;
     CEIL(7.3)    CEIL(7) CEIL(-7.3)
    ---------- ---------- ----------
             8          7         -7

## 2.2 ROUND(n1,n2)函数 ##

该函数返回舍入小数点右边n2位的n1的值，n2的默认值为0，这会返回小数点最接近的整数。如果n2为负数，就舍入到小数点左边对应的位上，n2必须是整数。

**【实例】**使用ROUND函数返回PI为两位小数的值。

SQL> SELECT ROUND(3.1415926,2) FROM DUAL;
    ROUND(3.1415926,2)
    ------------------
                  3.14

## 2.3 POWER(n1,n2)函数 ##

该函数返回n1的n2次方，其中n1和n2都为整数。

**【实例】**使用POWER函数计算2的3次方的值。

SQL> SELECT POWER(2,3) FROM DUAL;
    POWER(2,3)
    ----------
             8

# 3、日期和时间类函数 #

在Oracle中，系统提供了许多用于处理日期和时间的函数，通过这些函数可以实现计算需要的特定日期和时间，常用的日期和时间函数如下：

ADD\_MONTHS(d,i)：返回日期d加上i个月之后的结果，其中i为任意整数。

LAST\_DAY(d)：返回包含日期d月份的最后一天。

MONTHS\_BETWEEN(d1,d2)：返回d1和d2之间的数目，若d1和d2的日期都相同，或者都是该月的最后一天，则返回一个整数，否则返回的结果将包含一个小数。

NEW\_TIME(d1,t1,t2)：其中，d1是一个日期数据类型，当时区t1中的日期和时间是d1时，返回时区t2中的日期和时间，t1和t2是字符串。

SYSDATE()：返回系统当前的日期。

## 3.1 SYSDATE()函数 ##

该函数返回系统当前的日期。

**【实例】**使用SYSDATE函数当前系统的日期。

SQL> SELECT SYSDATE AS 系统日期 FROM DUAL;
    系统日期
    -----------
    2017/3/19 1

## 3.2 ADD\_MONTHS(d,i)函数 ##

该函数返回日期d加上i个月之后的结果，其中i为任意整数。

**【实例】**使用ADD\_MONTHS函数在当前日期下加上6个月，并显示其值。

SQL> SELECT ADD_MONTHS(SYSDATE,6) FROM DUAL;
    ADD_MONTHS(SYSDATE,6)
    ---------------------
    2017/9/19 15:57:24

# 4、转换类函数 #

在操作表中的数据时，经常需要将某个数据从一个类型转换为另外一种数据类型，这时就需要转换类型函数。比如常见的，有把具有“特定格式”字符串转换为日期、把数字转换成字符等。常用的转换函数如下：

CHARTORWIDA(S)：该函数将字符串s转换为RWID数据类型。

CONVERT(s,aset\[,bset\])：该函数将字符串s由bset字符集转换为aset字符集。

ROWIDTOCHAR()：该函数将ROWID数据类型转换为CHAR类型。

TO\_CHAR(x\[,format\])：该函数实现将表达式转换为字符串，format表示字符串格式。

TO\_DATE(s\[,format\[lan\]\])：该函数将字符串s转换成date类型，format表示字符串格式，lan表示所使用的语言。

TO\_NUMBER(s\[,format\[lan\]\])：该函数将返回字符串s代表的数字，返回值按照format格式进行显示，format表示字符串格式，lan表示所使用的语言。

## 4.1 TO\_CHAR(x\[,format\])函数 ##

该函数实现将表达式转换为字符串，format表示字符串格式。

**【实例】**使用TO\_CHAR函数转换系统日期为“YYYY-MM-DD”格式。

SQL> SELECT SYSDATE AS 默认格式日期,TO_CHAR(SYSDATE,'YYYY-MM-DD') AS 转换后日期 FROM DUAL;
    默认格式日期  转换后日期
    ------------ ----------
    2017/3/19 16 2017-03-19

## 4.2 TO\_NUMBER(s\[,format\[lan\]\])函数 ##

该函数将返回字符串s代表的数字，返回值按照format格式进行显示，format表示字符串格式，lan表示所使用的语言。

**【实例】**使用TO\_NUMBER函数把十六进制数“18f”转换为十进制数。

SQL> SELECT TO_NUMBER('18','xxx') AS 十进制数 FROM DUAL;
          十进制数
    ----------
            24

# 5、聚合类函数 #

使用聚合类函数可以针对一组数据进行计算，并得到相应的结果。比如常用的操作有计算平均值、统计记录数、计算最大值等。Oracle所提供的主要聚合类函数如下：

AVG(x\[,DISTINCT|ALL\])：计算选择列表项的平均值，列表项目可以是一个列或多个列的表达式。

COUNT(x\[,DISTINCT|ALL\])：返回查询结果中的记录数。

MAX(x\[,DISTINCT|ALL\])：返回选择列表项目中的最大值，列表项目可以是一个列或多个列的表达式。

MIN(x\[,DISTINCT|ALL\])：返回选择列表项目中最小值，列表项目可以是一个列或多个列的表达式。

SUM(x\[,DISTINCT|ALL\])：返回选择列表项目的数值总和，列表项目可以是一个列或多个列的表达式。

VARIANCE(x\[,DISTINCT|ALL\])：返回选择列表项目的统计方差，列表项目可以是一个列或多个列的表达式。