Oracle—SQL函数

单⾏函数是指对一⾏数据进⾏操作返回一条数据的函数，其结果集也只有一个数据

一、单行函数—字符函数

1、UPPER，字符串转大写函数

SELECT UPPER ('abcde') FROM dual ; --ABCDE

2、LOWER，字符串转小写函数

SELECT LOWER('ABCDE') FROM dual ; --abcde

3、INITCAP，字符串每个单词的首字母大写

SELECT INITCAP('abc, def') FROM dual; --Abc, Def

4、CONCAT，字符串连接函数

SELECT CONCAT('a', 'b') FROM dual; --ab
SELECT 'a' || 'b' FROM dual; --ab

5、SUBSTR (string str, int a, int b)，字符串截取函数

只传入两个参数时，str 为需要截取的字符串，a为从第a个下标开始截取后面所有的字符串。传入三个参数时，str为需要截取的字符串，a表示字符串截取的位置，b表示截取字符串的长度。

注：当a等于0或1时，都是从第一位开始截取）

SELECT SUBSTR('abcde', 3) FROM dual; --cde
SELECT SUBSTR('abcde', 3, 2) FROM dual; --cd

6、LENGTH，计算字符串长度

SELECT LENGTH('abcde') FROM dual; --5

7、REPLACE，字符串替换函数

SELECT REPLACE('abcde', 'a', 'A') FROM dual; --Abcde

8、INSTR，寻找元素下标

格式一：

instr( string1, string2 )，instr(源字符串, 目标字符串)，表示第一次出现string2，同时第一次出现string2首字母的位置。

格式二：

instr( string1, string2, start_position, nth_appearance) ，instr(源字符串, 目标字符串, 起始位置, 匹配序号)，表示从string1的第start_position开始，寻找第nth_appearance次出现string2的位置序号。

SELECT INSTR('Hello World', 'or') FROM dual; --寻找在'Hello World'中首次出现'or', 且此时'o'的位置序号, 8
SELECT INSTR('helloworld', 'l', 2, 2) FROM dual; --返回结果：4, 在"helloworld"的第2号位置开始查找，查找第二次出现的“l”的位置
SELECT INSTR('helloworld', 'l', 4, 2) FROM dual; --返回结果：9, 在"helloworld"的第4号位置开始，查找第二次出现的“l”的位置

9、lpad( string, padded_length, [ pad_string ] ) ，向左填充字符至指定长度、rpad( string, padded_length, [ pad_string ] ) ，向右填充字符至指定长度

String：准备被填充的字符串；
padded_length：填充完之后的字符串长度，如果该值小于原字符串长度值，则会截取；
pad_string：要用来填充的字符，如果未指定，默认填充空格。

SELECT LPAD('Smith',10,'*') FROM dual; --*****Smith
SELECT RPAD('Smith',10,'*') FROM dual; --Smith*****

10、TRIM ，去掉字符串首尾空格

SELECT TRIM(' Mr Smith ') FROM dual;

二，单行函数—数值函数

1、ROUND (NUMBER, decimals)

NUMBER：待做处理的数值；

decimals：表明保留小数点后面的小数位，四舍五入，可选项，若忽略，则直接去掉所有的小数点位并四舍五入。若该值为负数，则表示从小数点开始左边的位数，相应整数数字用 0 填充，小数被去掉。

注：与TRUNC函数不同的是，ROUND会对处理的数字进行四舍五入。

SELECT ROUND(412.513) FROM dual; --413
SELECT ROUND(412.513, -2) FROM dual; --400
SELECT ROUND(412.513, 2) FROM dual; --412.51

2、MOD (m, n)

MOD 返回m除以n的余数，如果n为0，返回m。

select MOD(23,8), MOD(24,8), MOD(25, 0) from dual; --7, 0, 25

3、TRUNC

格式一：TRUNC(date, [fmt])，日期处理函数，date为必要参数，fmt为可选参数，若为空则表示指定日期的0点。

SELECT TRUNC(sysdate) FROM dual; --2022-01-22, 得到当前时间, 当前日期0点
SELECT TRUNC(sysdate, 'mm') FROM dual; --2022-01-01, 得到当前月份的第一天
SELECT TRUNC(sysdate, 'hh') FROM dual; --2022-01-22 19:00:00, 得到当前时间, 精确到小时

格式二：TRUNC(number, [decimals])，数值处理函数，number为必要参数，decimals为可选参数，若为空则默认截掉小数点后面的值，为正数时表示保存小数点后面几位，为负数时表示小数点左侧几位替换为0，小数点右侧全舍弃。和ROUND函数很像，二者的区别在于TRUNC不会对处理结果进行四舍五入。

SELECT TRUNC(123.567, 2) FROM dual; --123.56
SELECT TRUNC(123.567, -2) FROM dual; --100
SELECT TRUNC(123.567) FROM dual; --123

三、单行函数—日期函数1

1、MONTHS_BETWEEN(m, n))，m与n都为date类型，计算m与n之间相差的月份数，有小数。

SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2000-01-15', 'YYYY.MM.DD')) FROM dual; --264.252886798088410991636798088410991637

2、ADD_MONTHS(date, d)，date与d都是必要参数，对date加上d个月，得到新的时间，d可以为负数。

SELECT SYSDATE FROM dual; --2022-01-22 20:14:47
SELECT ADD_MONTHS(SYSDATE, 1) FROM dual; --2022-02-22 20:14:47
SELECT ADD_MONTHS(SYSDATE, -1) FROM dual; --2021-12-22 20:14:47

3、LAST_DAY(date)，返回指定日期该月份的最后一天。

SELECT SYSDATE FROM dual; --2022-01-22 20:22:29
SELECT LAST_DAY(SYSDATE) FROM dual; --2022-01-31 20:22:29

4、NEXT_DAY(date, d)，返回指定日期的下个星期几，d为必须参数，d用1～7替代，1表示星期日，2代表星期一。。。

SELECT NEXT_DAY(SYSDATE, 2) FROM dual; --2022-01-24 20:26:25, 返回指定日期的下个周一

四、单行函数—转换函数

1、TO_CHAR (str, fmt)，对字符串str按照fmt的格式进行转换。

SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual; --2022
SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd') FROM dual; --2022-01-22
SELECT TO_CHAR('8000.00','L999,999,999') FROM dual; --￥8,000
SELECT TO_CHAR(SYSDATE,'D') FROM dual; --7, 返回指定日期是星期几

2、TO_DATE (str, fmt)，将str转换为fmt的格式

SELECT TO_DATE('20090210', 'yyyyMMdd') FROM dual; --2009-02-10

3、TO_NUMBER，可以将字符串转为number类型，进行加减乘除运算

SELECT TO_NUMBER('13') + TO_NUMBER('14') FROM dual; --27

五，单行函数—通用函数

1、NVL(m, n)，如果 m为空则返回 n——【NVL的参数类型必须要一致】

SELECT NVL('1', 0) FROM dual; --1
SELECT NVL('', 0) FROM dual; --0

2、NULLIF(expr1, expr2)，如果 expr1=expr2，则返回null，若不相等则返回 expr1。第一个参数不可指定为空。对于非数字类型参数，数据类型必须一致。

SELECT NULLIF('1', '1') FROM dual; --null
SELECT NULLIF('1', '0') FROM dual; --1
SELECT NULLIF(1 , '1') FROM dual; --[42000][932] ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 CHAR

3、DECODE(条件, 值1, 返回值1, 值2, 返回值2, ...值n, 返回值n, 缺省值)

该函数的含义如：IF（条件 = 值1）THEN RETURN 返回值1 ELSE IF（条件 = 值2）THEN RETURN 返回值2…ELSE RETURN 缺省值 END IF；

SELECT A00, A0101, A0104 FROM A01;
SELECT A00, A0101, DECODE(A0104, 1, '男', 2, '女', '未知') FROM A01;

4、NVL2(expr1, expr2, expr3)，expr1不为null，返回expr2，否则返回 expr3，如果 expr2与 expr3 类型不同的话，expr3会转化为expr2的类型。

SELECT NVL2('1', '2', 3) FROM dual; --2
SELECT NVL2('', '2', 3) FROM dual; --3
SELECT DUMP(NVL2('', '2', 3)) FROM dual; --Typ=1 Len=1: 51

5、DUMP() 函数可以查看任意值在oracle数据库中的类型，具体类型用Type=t，代码表示。类型与代码值对应关系官方文档。

COALESCE (expr1, expr2, expr3...)，返回第一个不会空的参数，所有参数列表的数据类型必须一致
SELECT COALESCE('', '2', '3') FROM dual; --2
SELECT COALESCE('', '2', 3) FROM dual; --[42000][932] ORA-00932: 数据类型不一致: 应为 CHAR, 但却获得 NUMBER

6、CASE WHEN，分支判断函数，与DECODE函数类似，CASE WHEN 更适用于需要做特殊判断处理的条件中

SELECT A00, A0101, CASE A0104 WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '未知' END FROM A01; --62CB8048-97B9-466E-946E-23E2B86180EA, 吴毓萍, 女

注：所有的函数都可嵌套使用。

六、单行函数练习题

1、找出每个月倒数第三天受雇的员工（如：2021-5-29），hiredate为受雇日期字段

SELECT * FROM EMP WHERE LAST_DAY(HIREDATE)-2 = HIREDATE;

2、找出 25 年前雇的员工

SELECT * FROM EMP WHERE HIREDATE<=ADD_MONTHS(SYSDATE, -25*12); --找出受雇日期早于等于当前时间减25年

3、所有员工名字前加上 Dear ,并且名字首字母大写

SELECT 'DEAR ' || INITCAP(ENAME) FROM EMP;

4、找出姓名为 5 个字母的员工

SELECT * FROM EMP WHERE LENGTH(ENAME) = 5;

5、找出姓名中不带 R 这个字母的员工

SELECT * FROM EMP WHERE ENAME NOT LIKE '%R%';

6、显示所有员工的姓名的第一个字

SELECT SUBSTR(ENAME,0,1) FROM EMP;

7、显示所有员工，按名字降序排列，若相同，则按工资升序排序

SELECT * FROM EMP ORDER BY ENAME desc, SALARY;

8、假设一个月为 30 天，找出所有员工的日薪，不计小数

SELECT ENAME, TRUNC(SALARY/30) FROM EMP;

9、找到 2 月份受雇的员工

SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'FMMM') = '2';

10、列出员工加入公司的月数(四舍五入）

SELECT ENAME, ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE)) FROM EMP;

七、分组函数

1、COUNT，求数据量函数，如果数据库表的没有数据，count(*)返回的不是 null，而是 0

SELECT COUNT(*) FROM AZ09; --0

2、Avg, max, min, sum，求平均值、最大最小、总和。注：分组函数会省略列中的空值，可使用NVL()函数强制分组函数处理空值。

SELECT AVG(NVL(COMM, 0)) FROM EMP;

3、GROUP BY 子句，出现在SELECT列表中的字段或者出现在ORDER BY后面的字段，如果不是包含在分组函数中，那么该字段必须同时在GROUP BY子句中出现。

包含在 GROUP BY子句中的字段则不必须出现在 SELECT列表中，可使用 WHERE子句限定查询条件，可使用ORDER BY子句指定排序方式。

如果没有GROUP BY子句，SELECT列表中不允许出现字段（单行函数）与分组函数混用的情况。

SELECT A0101, ZDYXA01013 FROM A01; --合法
SELECT AVG(ZDYXA01013) FROM A01; --合法
SELECT A0101, INITCAP(A00), AVG(ZDYXA01013) FROM A01; --[42000][937] ORA-00937: 不是单组分组函数

同时也不允许在WHERE子句中使用分组函数。

SELECT A0101, ZDYXA01013 FROM A01 WHERE AVG(ZDYXA01013)>1; --[42000][934] ORA-00934: 此处不允许使用分组函数

4、Having 子句，分组函数中对结果集进行进一步筛选

SELECT A0117 FROM A01 WHERE A0107 < TO_DATE('2000-01-01', 'yyyy-mm-dd') GROUP BY A0117 HAVING A0117='01' ORDER BY A0117;
--[42000][979] ORA-00979: 不是 GROUP BY 表达式
SELECT A0117 FROM A01 WHERE A0107 < TO_DATE('2000-01-01', 'yyyy-mm-dd') GROUP BY A0104 HAVING A0104='01' ORDER BY A0101;

5、分组函数可嵌套使用

SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO;

八、关于ALL，IN，ANY的函数区别

1、in：用于匹配多个定值中的一个

select * from emp where deptno in (10,20);

2、any:表示子查询中有一个值知足条件便可。any和

>any（单列多行子查询）大于最小值的均可以。

例子：查询比10号部门某个员工工资高的员工信息

select  * from emp where sal >any( select sal from emp where deptno = 10);

3、ALL：表示子查询中的全部值都知足条件才能够。

>all（单列多行子查询）大于最大值的均可以（类比any比较学习）

例子：查询比20号部门全部员工工资高的员工信息

select * from emp where sal > all ( select sal from emp where deptno = 20);

4、some：用法和any同样：都是表中其中的一个知足条件便可。

九、日期函数2--last_day()函数和Next_day()

1、last_day 函数

用法：返回指定日期所属月份的最后一天。

语法：last_day(sysdate) 它只有一个日期参数；

例1：判断该日期是否是所属月份的最后一天，即月末。

if(last_day('2020-02-03')='2020-02-03','Y','N')

注：判断该日期是否是所属月份的最后一天，即月末。

例2：找出每个月倒数第三天入职的员工

SELECT * FROM EMP WHERE last_day(hiredate)-2=hiredate;
注：last_day(hiredate) 得到的是每个员工入职当有的最后一天，-2表示倒数第三天，如果与员工入职时间相等，说明这个员工是当月倒数第三天入职的。

例3：获取当前日期、当前日期所在月的最后一天、距离月末还剩多少天

SELECT 
SYSDATE, 
LAST_DAY(SYSDATE) "Last", 
LAST_DAY(SYSDATE) - SYSDATE "Days Left"
FROM DUAL;

2、next_day 函数

定义

NEXT_DAY(date,char)
date参数为日期型，
char:为1~7或Monday/Mon~Sunday/

格式：NEXT_DAY(DATE,WEEKDAY) 即 NEXT_DAY(日期,星期几)

用法：查询出指定时间的下一个星期几（由char指定）所在的日期， char也可用1～7替代，1表示星期日，2代表星期一。 char 还可以是星期一、星期二......星期日。即 monday,thusday.....(看是什么字符集的)。

查询出半年前的时间:

select add_months(sysdate,-6) from dual;

查询出半年后的时间

select add_months(sysdate,6) from dual;

十、日期函数3

https://blog.csdn.net/qq_33459369/article/details/80305175

current_date、current_timestamp、localtimestamp、sessiontimezone、dbtimezone，systimestamp函数

（1）current_timestamp

以 timestamp with time zone 数据类型返回当前会话时区所对应的日期时间。

SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
22-JUL-22 11.19.47.282671 AM +08:00

（2）systimestamp

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
22-JUL-22 11.20.40.282671 AM +08:00

（3）localtimestamp

返回当前会话时区的日期时间

SQL> select localtimestamp from dual;
LOCALTIMESTAMP
---------------------------------------------------------------------------
22-JUL-22 11.20.42.721053 AM

（4）current_date

current_date:9i新增函数，返回当前会话时区所对应的日期时间(date型)

SQL> select current_date from dual;
CURRENT_DATE
--------------------
22-JUL-2022 11:16:19

（5）sessiontimezone

返回会话时区(字符型)

SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00

（6）dbtimezone

返回数据库所在时区

SQL> select dbtimezone from dual;
DBTIME
------
+08:00