ORACLE函数

以下sql用scott用户进行测试

---

一、字符函数

大小写转换函数

LOWER(参数)

把【参数】全部变成小写字母

SELECT *
FROM EMP
WHERE LOWER(ENAME)='smith';

UPPER(参数)

把【参数】全部变成大写字母

SELECT *
FROM EMP
WHERE ENAME=UPPER('smith');

INITCAP(参数)

把【参数】第一个字母大写，其余全部小写

SELECT INITCAP(ENAME)
FROM EMP
WHERE ENAME='SMITH';

字符处理函数

CONCAT(参数1, 参数2)

把【参数1】和【参数2】连接起来

SELECT CONCAT(EMPNO,ENAME)
FROM EMP;
/*
	也可以直接用 EMPNO || ENAME 代替
*/

SUBSTR(参数1, 参数2, [参数3])

在【参数1】里，从【参数2】位置开始截取【参数3】的字符，【参数2】是从1开始，不指定【参数3】则全部截止

SELECT SUBSTR(ENAME,1,3)
FROM EMP
WHERE ENAME='SMITH';

LENGTH(参数)

获取【参数】的长度

SELECT LENGTH(ENAME)
FROM EMP;

LPAD(参数1,参数2,参数3)

把【参数1】补齐【参数2】位，如果不够位数则用【参数3】在左边填充

SELECT SAL,LPAD(SAL,10,'#')
FROM EMP;

RPAD(参数1,参数2,参数3)

把【参数1】补齐【参数2】位，如果不够位数则用【参数3】在右边填充

SELECT SAL,RPAD(SAL,10,'#')
FROM EMP;

REPLACE(参数1,参数2,[参数3])

在【参数1】里面找到【参数2】，用【参数3】替换掉，不指定【参数3】默认删除，不是换成空格

SELECT ENAME,REPLACE(ENAME,'A','#')
FROM EMP
WHERE ENAME='ALLEN';

---

二、数字函数

ROUND(参数1,参数2)

【参数1】四舍五入精确到【参数2】位数

SELECT ROUND(45.9,0)
FROM DUAL;
/*
	0代表四舍五入精确个位，这个输出结果就是46
	-1代表十位
	1代表小数点后一位
*/

TRUNC(参数1,参数2)

以【参数2】为位数截取【参数1】

SELECT TRUNC(45.943,2)
FROM DUAL;
/*
	2代表小数点后两位，结果为45.94
*/

---

三、日期函数

日期不能相加，日期也不能乘除，能相减，得到天数（数值）

SYSDATE

获取系统时间，没有（），格式为1999/11/18 11:01:59

SELECT SYSDATE
FROM DUAL;
/*
	两个时间相减得到天数（数值），除365截取或四舍五入都可以得到年数
*/

MONTHS_BETWEEN(日期参数1,日期参数2)

得到【参数1】和【参数2】的相差月数数值（数值可以参加运行，时间不行），【参数1】大

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

ADD_MONTHS(时间参数1，数值参数2)

在时间【时间参数1】基础上增加【数值参数2】个月，得到一个新的日期

SELECT ADD_MONTHS(HIREDATE,3)
FROM EMP;

NEXT_DAY(时间参数)

计算返回当前【时间参数】那个月的最后一天的日期

SELECT LAST_DAY(HIREDATE)
FROM EMP;

---

四、转换函数

字符型要一定格式才能转化为日期型

TO_CHAR(参数1,模板参数2)

把时间【参数1】按【模板参数2】格式输出，可以把时间和数值转化为字符

SELECT TO_CHAR(HIREDATE,'YYYY-MM-DD')
FROM EMP;

TO_NUMBER(字符参数1，模板参数2)

把【字符参数1】按【模板参数2】转化为一个数值，字符参数1中有的特殊符号，模板参数也要有

SELECT TO_NUMBER('$800.00','$999,999.00')
FROM DUAL;
/*
	结果：800
*/

TO_DATE(字符参数1，模板参数2)

把【字符参数1】按照【模板参数2】转化为日期，oracle日期都是 / 去隔离年月日

SELECT TO_DATE('2001)11)22','YYYY-MM-DD')
FROM DUAL;
/*
	结果：2001/11/22
*/

SELECT TO_DATE('20011122','YYYYMMDD')
from dual;
/*
	结果也是：2001/11/22
*/

---

五、空值处理函数

NVL(参数1，参数2)

如果【参数1】为空值，则返回【参数2】，【参数1】【参数2】必须要类型相同

SELECT NVL(HIREDATE,'1-1月-2009')
FROM EMP;
/*
	'1-1月-2009'可以被识别成日期，可以理解为自动装箱
*/

NVL2(参数1，参数2，参数3)

如果【参数1】不为空值，则返回【参数2】，否则返回【参数3】

SELECT NVL2(ENAME,'有名字','没名字')
FROM EMP;
/*
	'1-1月-2009'可以被识别成日期，可以理解为自动装箱
*/

---

六、分支逻辑函数

CASE

CASE 匹配参数
	WHEN 参数1 THEN 表达式1
	[WHEN 参数2 THEN 表达式2]
	[WHEN 参数3 THEN 表达式3]
	[ELSE 表达式4]
END "别名"

如果【匹配参数】匹配上【参数1】，则执行【表达式1】，返回结果给别名列，如果都匹配不上执行表达式4，同样返回结果给别名列。

SELECT EMPNO,SAL,
	  CASE EMPNO
      	WHEN 1357 THEN SAL*2
      	WHEN 7654 THEN SAL*2
      	WHEN 7900 THEN SAL*2
      	ELSE SAL
      END "这3人工资提高"
FROM EMP;

DECODE

DECODE(匹配参数,参数1,表达式1,[参数2],[表达式2],[默认表达式]) AS "别名"

如果【匹配参数】匹配上了【参数】，则执行表达式1，以此类推，都匹配不上则执行默认表达式

SELECT ENAME,SAL,DECODE(EMPNO,1357,SAL*2,7654,SAL*2,SAL) AS "两人提高工资"
FROM EMP;

---

七、组函数

# 下面函数都会忽略null
# sum求和，求全部人的工资总和，值的和
SELECT SUM(salary) FROM emp;
# avg平均值，求人均工资，切记要先用NVL函数去处理空值
SELECT AVG(salary) FROM emp;
# max最大值，求最大工资的人
SELECT MAX(salary) FROM emp;
# min最小值，求最小工资的人
SELECT MIN(salary) FROM emp;
# count计算个数，求获得工资的人数，行的和(不是总行数和，如果有人工资为null是不会被计算的)
SELECT COUNT(salary) FROM emp;