Oracle基操笔记（三）：常用函数

梦里梦外; 2022-03-31 11:08 424阅读 0赞

**本文包含大量实际操作，功能可通过索引找到**

**目录**

三五七小节为常用，其余小节可跳，大致了解即可

一、概论

二、数学函数

三、字符串函数

四、日期和时间函数

五、转换函数（常用）

六、系统信息函数

七、综合案例

--------------------

# #

## 一、概论 ##

函数的本质就是输入参数，得到返回值，不再赘述。Oracle提供大量丰富的函数库，本文以Oracle12c为例，分类介绍各常用函数用法。

IDE：PL/SQL Developer或Oracle SQL Developer

## 二、数学函数 ##

主要用来处理数值数据，大致包含：绝对值函数、三角函数、对数函数、随机函数等等。若有错误产生，则返回NULL。

以下代码在“命令窗口”执行。

### **1.绝对值函数示例 ABS(n)** ###

SQL> SELECT ABS(2), ABS(-3.3), ABS(-33) FROM dual;

结果为

ABS(2)  ABS(-3.3)   ABS(-33)  
\---------- -  ---------   ----------  
2         3.3         33

### **2.平方根 SQRT(n) 与求余 MOD(x,y)** ###

MOD(x,y)返回x被y除后的余数

SQL> SELECT MOD(31,8),MOD(234, 10),MOD(45.5,6) FROM dual;

MOD(31,8)  MOD(234,10)  MOD(45.5,6)  
\----------         -----------             -----------  
7                     4                             3.5

### **3.取整CEIL(n) 与 FLOOR(n)** ###

SQL> SELECT  CEIL(-3.35), CEIL (3.35) FROM dual;

CEIL(-3.35) CEIL(3.35)  
\-----------   ----------  
\-3             4

SQL> SELECT FLOOR(-3.35), FLOOR(3.35) FROM dual;

FLOOR(-3.35)  FLOOR(3.35)  
\------------       -----------      
\-4               3

Ceil往大的取

Floor往小的取（地板）

### **4.获取随机数DBMS\_RANDOM.RANOM 与 DBMS\_RANDOM.VALUE(x,y)** ###

使用DBMS\_RANDOM.VALUE(x，y)函数产生1~20之间随机数

SQL> SELECT DBMS_RANDOM.VALUE(1,20),DBMS_RANDOM.VALUE(1,20) FROM dual;

DBMS\_RANDOM.VALUE(1,20) DBMS\_RANDOM.VALUE(1,20)  
\-----------------------             -----------------------  
1.871131765                    4.727097533

### **5.四舍五入 ROUND(n),  ROUND(x,y)  与截取TRUNC(x,y) 常用** ###

（1）  ROUND(x,y)返回最接近于参数x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位。

SQL> SELECT ROUND(-1.14),ROUND(-1.67), ROUND(1.14),ROUND(1.66) FROM dual;

ROUND(-1.14) ROUND(-1.67)   ROUND(1.14)  ROUND(1.66)  
\------------       ------------      -----------        -----------  
\-1                       -2               1                 2

（2）TRUNCATE(x,y)返回被舍去至小数点后y位的数字x。若y的值为 0，则结果不带有小数点或不带有小数部分。

若y设为负数,则截去（归零）x小数点左起第y位开始后面所有低位的值。

SQL>SELECT TRUNC(1.31,1), TRUNC (1.99,1), TRUNC (1.99,0), TRUNC (19.99,-1) FROM dual;

TRUNC(1.31,1)  TRUNC(1.99,1)  TRUNC(1.99,0)  TRUNC(19.99,-1)  
   -------------       -------------       -------------        ---------------  
        1.3                1.9                   1                    10

TRUNC (1.31,1)和TRUNC (1.99,1)都保留小数点后1位数字，返回值分别为1.3和1.9；

TRUNC (1.99,0)返回整数部分值1；

TRUNC (19.99,-1)截去小数点左边第1位后面的值，并将整数部分的1位数字置0，结果为10。

ROUND(x,y)函数在截取值的时候会四舍五入，而TRUNC(x,y)直接截取值，并不进行四舍五入。

### 6.乘方POWER(x,y)、EXP(x)等 ###

1.  POWER(x,y)函数返回x的y次乘方的结果值。
2.  EXP(x)返回e的x乘方后的值。
3.  还有LN(x) 返回x的自然对数
4.  正弦函数SIN(x)和反正弦函数ASIN(x)
5.  余弦函数COS(x)和反余弦函数ACOS(x)
6.  TAN(x)返回x的正切，ATAN(x)返回x的反正切，

## 三、字符串函数 ##

1.  LENGTH(str)返回值为字符串的字节长度
2.  CONCAT(s1,s2)返回结果为连接参数产生的字符串。
3.  INSTR(s,x)返回x字符在字符串s的位置。
4.  LOWER (str)可以将字符串str中的字母字符全部转换成小写字母。
5.  UPPER(str)则变成大写
6.  SUBSTR(s,m,n)函数获取指定的字符串。其中参数s代表字符串，m代表截取的位置，n代表截取长度。
7.  REPLACE (s1,s2,s3)是一个替换字符串的函数。其中参数s1表示搜索的目标字符串；S2表示在目标字符串中要搜索的字符串；s3是可选参数，用它替换被搜索到的字符串，如果该参数不用，表示从s1字符串中删除搜索到的字符串。

### 1.TRIM函数 将删除指定的前缀或者后缀的字符，默认删除空格。 ###

TRIM（\[LEADING/TRAILING/BOTH\]  \[trim\_character FROM\]  trim\_source）  
其中 LEADING 指删除 trim\_source 的前缀字符；TRAILING删除trim\_source的后缀字符；BOTH删除trim\_source的前缀和后缀字符；trim\_character指删除的指定字符，默认删除空格；trim\_source指被操作的源字符串。

SQL> SELECT TRIM( BOTH 'x' FROM 'xyxbxykyx'), TRIM('     xyxyxy     ') FROM dual;

TRIM(BOTH'X'FROM'XYXBXYKYX')    TRIM('XYXYXY')  
   ----------------------------                        --------------  
         yxbxyky                                         xyxyxy

删除字符串“xyxbxykyx”两端的重复字符 “x”，而中间的“x”并不删除，结果为“yxbxyky”。

### 2.LTRIM(s,n)函数将删除指定的左侧字符。【R就是右侧】 ###

其中s是目标字符串，n是需要查找的字符。如果n不指定，则表示删除左侧的空格。

SQL>SELECT LTRIM ('this is a dog', 'this') , LTRIM ('   this is a dog') FROM dual;

LTRIM('THISISADOG','THIS')   LTRIM('THISISADOG')  
     --------------------------               -------------------  
             is a dog                        this is a dog

### **3.使用INITCAP函数将字符串中首字母转换成大写** ###

SQL> SELECT INITCAP ('hello beautiful word ') FROM dual;

INITCAP('HELLOBEAUTIFULWORD')  
\-----------------------------  
Hello Beautiful Word

**假设有一张员工表tb\_emp**

**--选取id为1024与1026的员工，拼接两个字段且首字母大写**

![20190104143255206.png][]

SELECT M_ID,CONCAT(INITCAP(M_FN),INITCAP(M_LN)) Fullname
      FROM member
     WHERE M_ID IN (1024,1026);

查询结果为

![20190104143530518.png][]

## 四、日期和时间函数 ##

1.  SYSDATE()函数获取当前系统日期。
2.  SYSTIMESTAMP()函数获取当前系统时间，该时间包含时区信息，精确到微秒。
3.  DBTIMEZONE函数获取数据库所在的时区
4.  LAST\_DAY(date)函数。该函数返回参数指定日期对应月份的最后一天。
5.  NEXT\_DAY(date,char)函数获取当前日期向后的一周对应日期，char表示是星期几，全称和缩写都允许。

### 1.EXTRACT(datetime)从指定的时间中提取特定部分。 ###

### 例如提取年份、月份或者时等。 ###

SQL> SELECT EXTRACT (YEAR FROM SYSDATE), EXTRACT (MINUTE  FROM TIMESTAMP '1985-10-8  12:23:40')   FROM dual;

EXTRACT(YEARFROMSYSDATE)    EXTRACT(MINUTEFROMTIMESTAMP'1985-10-812:23:40')  
  ------------------------                                 -----------------------------------------------  
  2019                                                      23

### 2.MONTHS\_BETWEEN函数获取两个日期之间的月份数。 ###

SQL>SELECT 
    MONTHS_BETWEEN(TO_DATE('1985-10-8','YYYY-MM-DD'),TO_DATE('1985-8-8','YYYY-MM-DD') one,
    MONTHS_BETWEEN(TO_DATE('1985-05-8','YYYY-MM-DD'),TO_DATE('1985-07-8','YYYY-MM-DD') )TWO FROM dual;

ONE        TWO  
\----------    ----------  
  2             -2

## 五、转换函数（常用） ##

1.  ASCIISTR(char)函数将任意字符串转换为数据库字符集对应的ASCII字符串。char为字符类型。
2.  BIN\_TO\_NUM()函数实现将二进制转换成对应的十进制。
3.  CAST函数把数字与字符串之间进行转换操作二进制转为十进制类型。

### 1.TO\_CHAR函数将一个数值型参数转换成字符型数据。（常用） ###

TO\_CHAR(n，\[ fmt \[nlsparam\] \] )

其中参数 n 代表数值型数据；参数 ftm 代表要转换成字符的格式；

nlsparam参数代表指定fmt的特征，包括小数点字符、组分隔符和本地钱币符号。

SQL> SELECT TO_CHAR (10.13245, '99.999'), TO_CHAR (10.13245) FROM dual;

TO\_CHAR(10.13245,'99.999')     TO\_CHAR(10.13245)  
\--------------------------                         -----------------  
            10.132                                10.13245

**TO\_CHAR函数还可以将日期类型转换为字符串类型。**

SQL> SELECT TO_CHAR (SYSDATE, 'YYYY-MM-DD'), TO_CHAR (SYSDATE, 'HH24-MI-SS') FROM dual;

TO\_CHAR(SYSDATE,'YYYY-MM-DD')   TO\_CHAR(SYSDATE,'HH24-MI-SS')  
\-----------------------------                               -----------------------------  
2019-01-02                                            15-06-49

### 2.TO\_DATE函数将一个字符型数据转换成日期型数据。（常用） ###

TO\_DATE( char \[,fmt \[,nlsparam\] \] )

其中参数char代表需要转换的字符串。参数ftm代表要转换成字符的格式；nlsparam参数控制格式化时使用的语言类型。

SQL> SELECT TO_CHAR(TO_DATE ('2019-10-16', 'YYYY-MM-DD'),'MONTH') FROM dual;

TO\_CHAR(TO\_DATE('2019-10-16','YYYY-MM-DD'),'MONTH')  
\---------------------------------------------------  
10月

**取系统时间前2个月的数据**

...WHERE MONTH = TO_CHAR(ADD_MONTHS(SYSDATE, -2), 'YYYYMM');

**获取系统时间前1个月的数据**

WHERE MONTH =
           TO_CHAR(ADD_MONTHS(TO_DATE(&当前月, 'YYYYMM'), -1), 'YYYYMM');

## 六、系统信息函数 ##

1.  USER函数返回当前会话的登录名。
2.  USERENV函数返回当前会话的信息

## 七、综合案例 ##

oracle的[sql语句][sql]中没有limit，limit是mysql中特有的，在oracle中可用rownum来表示，用于查询结果中的前N行数据。

如要查询emp表中的前5行数据，可用如下语句：

select \* from emp where rownum<=5;

**如何选择列表中第一个不为空的表达式？**

COALESCE(expr)函数返回列表中第一个不为null的表达式。如果全部为null，则返回一个null。

SQL> SELECT COALESCE (1-2,NULL ,9-8,NULL) FROM dual;

COALESCE(NULL,9-8,NULL)  
\-----------------------  
\-1

[20190104143255206.png]: /images/20220331/82d907ec6cb24e59af2f123344f01d90.png
[20190104143530518.png]: /images/20220331/618004b393b94428bf71bbc2a6e9e04a.png
[sql]: https://www.baidu.com/s?wd=sql%E8%AF%AD%E5%8F%A5&tn=SE_PcZhidaonwhc_ngpagmjz&rsv_dl=gh_pc_zhidao