Oracle常用函数详解

Oracle常用函数详解

1.大小写控制函数

函数	结果
LOWER (‘SQL Course’) ：字符小写	sql course
UPPER(‘SQL Course’) ：字符大写	SQL COURSE
INITCAP(‘SQL Course’) ：首字母大写	Sql Course

2.字符控制函数

函数	结果
CONCAT(‘Hello’,‘World’) ：连接	HelloWorld
SUBSTR(‘HelloWorld’,1,5) ：截断	Hello
LENGTH(‘HelloWorld’) : 长度统计	10
INSTR(‘HelloWorld’,‘w’) : 字符位置	6
LPAD(‘24000’,10,’*’) : 符号填充（左）	*****24000
RPAD(‘24000’,10,’*’) ：符号填充（右）	24000*****
TRIM(‘H’ from ‘HelloWorld’) : 删除字符串两边的指定字符/去除空格	elloWorld
REPLACE(‘ABCD’,‘B’,‘M’) : 替换	AMCD

3.数字函数

函数	结果
ROUND(45.927,2) : 四舍五入（保留两位）	45.93
TRUNC(45.927,2) : 截断	45.92
MOD(1600,300) :求余	100

4.日期函数

日期函数描述

函数	描述
MONTHS_BETWEEN	两个日期相差的月数
ADD_MONTHS	向指定日期中加上若干月数
NEXT_DAY	指定日期下一个星期*对应的日期
LAST_DAY	本月的最后一天
ROUND	日期四舍五入
TRUNC	日期截断

日期函数案例

函数	结果
MONTHS_BETWEEN（‘01-SEP-95’,‘11-JAN-94’）	19.6774194
ADD_MONTHS(’‘11-JAN-94’,6)	‘11-JUL-94’
NEXT_DAY (‘01-SEP-95’,‘FRIDAY’)	‘08-SEP-95’
LAST_DAY (‘01-FEP-95’)	‘28-FEP-95’
ROUND(‘25-JUL-95’,‘MONTH’)	‘01-AUG-95’
ROUND(‘25-JUL-95’,‘YEAR’)	01-JAN-96
TRUNC(‘25-JUL-95’,‘MONTH’)	‘01-JUL-95’
TRUNC(‘25-JUL-95’,'YEAR)	‘01-JAN-95’

5.数据类型转换

转换关系图

TO_CHAR函数对日期的转换

SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') FROM dual;
结果：2021-06-10 11:41:51

TO_DATE函数对字符的转换

SELECT TO_DATE('2021-06-10 11:41:51','yyyy-mm-dd hh:mi:ss') FROM DUAL;
结果：2021/6/10 11:41:51

TO_NUMBER函数对字符的转换

SELECT TO_NUMBER('￥1,234,567,890.00','L999,999,999,999.99') FROM DUAL;
结果：1234567890

6.NVL函数

NVL函数，将空值转换成一个已知的值

测试表user1原始数据

描述：查询user1表中的数据，如果地址（ADDRESS）为空，将返回结果为“未知”，不为空，返回原始数据，执行下面SQL，结果如下所示。

SELECT ID,NAME,AGE,NVL(ADDRESS,'未知'),PROFESSIONAL FROM USER1;

NVL2函数，NVL2(value1,value2,value3):如果value1不为null，返回value2；为null，返回value3。

描述：查询user1表中的数据，如果地址（ADDRESS）为空，返回“不晓得”，如果地址（ADDRESS）不为空，返回“王者峡谷市”，执行下面SQL，结果如下所示。

SELECT ID,NAME,AGE,NVL2(ADDRESS,'不晓得','王者峡谷市'),PROFESSIONAL FROM USER1;

NULLIF函数—NULLIF(value1,value2):如果value1等于value2，返回null，否则返回value1.

描述：查询表user1数据，如果名称（NAME）和职业（PROFESSIONAL）的字符长度相等，返回一个字段result的值为空，否则返回名称（NAME）的长度，执行下面SQL，结果如下所示。

SELECT ID,NAME,LENGTH(NAME) NAME_LENGTH,AGE,PROFESSIONAL,
LENGTH(PROFESSIONAL) PROFESSIONAL_LENGTH,
NULLIF(LENGTH(NAME),LENGTH(PROFESSIONAL)) RESULT
 FROM USER1;

COALESCE函数

COALESC函数和NVL函数相比的优点在于COALESCE函数可以同时处理交替的多个值。
如果第一个表达式为空，则返回下一个表达式，对其他的参数进行COALESCE。

表user1原始数据

描述：查询表user1，如果年龄（AGE）为空，返回一个字段result的值为地址（ADDRESS）,不为空则返回年龄；如果地址也为空，返回“年龄和地址都为空”，不为空，则返回地址（ADDRESS）,执行下面SQL，返回结果如下所示。

 SELECT NAME,AGE,ADDRESS,PROFESSIONAL,
 COALESCE(AGE,ADDRESS,'年龄和地址都为空') RESULT 
 FROM USER1;

7.条件表达式

7.1CASE

表user1原始数据

描述：对表中数据的年龄进行年龄段划分，
0-6岁为幼儿；7-12岁为少儿；13-17岁为青少年；18-45岁为青年。
46-69岁为中年；大于69岁为老年。执行下面SQL，返回结果如下。

 SELECT NAME,AGE,ADDRESS,PROFESSIONAL,
 CASE  WHEN AGE BETWEEN 0 AND 6 THEN '幼儿'
          WHEN AGE BETWEEN 7 AND 12 THEN '少儿'
          WHEN AGE BETWEEN 13 AND 17 THEN '青少年'
          WHEN AGE BETWEEN 18 AND 45 THEN '青年'
          WHEN AGE BETWEEN 46 AND 69 THEN '中年'
      ELSE '老年' END  "phase"
  FROM USER1;

7.2 DECODE

函数格式：DECODE(value,条件1,value1，条件2,value2,条件3，value3，…)
描述：如果取到的值value满足条件1，返回value1的值，满足条件2，返回value2的值，满足条件3，返回value3，以此类推。

表user1原始数据

描述：查询表user1的数据，将职业（PROFESSIONAL）输出为对应的职业。（1：辅助；2：射手；3：法师；4：上单。）执行下面SQL，结果返回如下所示。

  SELECT NAME,AGE,ADDRESS,PROFESSIONAL,
  DECODE(PROFESSIONAL,'1','辅助','2','射手','3','法师','4','上单')   PROFESSIONAL_NAME 
  FROM USER1;

本文内容如有错误，请指出，谢谢~参考来源于尚硅谷Oracle视频教学，尚硅谷视频地址