oracle中常用的sql、函数

1. TO_DATE，EXISTS，CASE…WHEN 函数
2. LISTAGG 函数与 OVER PARTITION BY 函数
3. Oracle数据库中全库查找某个值是否存在
4. oracle的sql语句规范，创建列、添加列、删除列、函数等
5. 返回指定日期与当前日期的时间差函数

本文涉及到的Oracle函数有：UPPER、LOWER、INITCAP、CONCAT、SUBSTR、LENGTH、REPLACE、INSTR、LPAD、RPAD、TRIM、ROUND、MOD、TRUNC、MONTHS_BETWEEN、ADD_MONTHS、LAST_DAY、NEXT_DAY、TO_CHAR、TO_DATE、TO_NUMBER、NVL、NULLIF、DECODE、NVL2、COALESCE 、CASE WHEN、COUNT、AVG、MAX、MIN、SUM、GROUP BY、Having

一，单行函数—字符函数

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. 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

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

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

5. 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;