Oracle --单行函数的总结

开发工具与关键技术：工具：Oracle
技术：Oracle
作者：吴东梅 ；撰写时间：2019 年 3 月 16 日
单行函数
字符函数
这类函数改变字符的大小写。

函数	结果
LOWER(‘SQL Course’)	sql course
UPPER(‘SQL Course’)	SQL COURSE
INITCAP(‘SQL Course’)	Sql Course

字符控制函数

函数	结果
CONCAT( ‘Hello’ , ‘World’ )	HelloWorld
SUBSTR( ‘HelloWorld’ ,1,5)	Hello
LENGTH(‘HelloWorld’)	10
INSTR(‘HelloWorld’, ‘W’)	6
LPAD(salary,10,’*’)	*****24000
RPAD(salary,10,’*’)	24000*****
TRIM(‘H’ FROM ‘HelloWorld’)	elloWorld
REPLACE(‘abcd’,‘b’,‘m’)	amcd

–字符控制函数
–1、字符连接控制函数 CONCAT
select first_name,last_name,concat(first_name,last_name)
from employees; --把括号里面的字符用 concat 拼接到一起
–2、字符串截取函数 SUBSTR
select SUBSTR(‘HelloWorld’,5,2) from employees; --从第五个字符开始截取两个长度的字符串。可以从0开始
–把 HelloWorld 从 (‘目标字符串’，‘截取的起始位置’,‘截取的长度 ‘) 。从1算起
–3、字符串判断长度函数 LENGTH
select LENGTH(‘HelloWorld’) from employees; --把括号里面的目标字符串用 LENGTH 计算出有多少个字符。（空格也算一个字符）
–4、返回字符 (串) 的索引位置函数 计算字符（串）在目标字符串里面排第几个
–它是来返回一个字符串或者一个字符在某一个字符串或某一个字符之中的索引位置
select INSTR(‘HelloWorld’, ‘W’) from employees; --计算w 在HelloWorld 中处于第几个位置。
–5、REPLACE 替换函数
select REPLACE(‘abcd’,‘b’,‘m’) from employees; --把 目标函数 abcd 里面的原字符 (串) b 替换 m (或其他字符(串))
–6、TRIM 可以把一个字符串的开头和结尾指定的字符(串)去掉
select TRIM(‘H’ FROM ‘HelloWorld’) from employees; --用 TRIM 把H 在目标函数中去掉。(在开头和结尾都可以去掉)
select TRIM(’ HelloWorld ‘) from employees; --常见的用法就是把字符串前后的空格去掉。
–7、LPAD 左对齐
select LPAD(salary,10,’’) from employees; --把 salary 全部用 10 位数显示，不够10位数的就用 * （也可以用其他字符代替）在前面插入代替，全部左对齐
–8、RPAD 右对齐
select RPAD(salary,10,’’) from employees; --把 salary 全部用 10 位数显示，不够10位数的就用 * （也可以用其他字符代替）在后面插入代替，全部右对齐
–不管左对齐还是右对齐，如果补齐的数字比原有的查出来的数据的位数少，那么将会自动截断为补齐的那个数 。

数字函数

函数	结果
ROUND:四舍五入 ROUND(45.926,2)	45.93
TRUNC:截断 TRUNC(45.926,2)	45.92
MOD:求余 MOD(1600,300)	100

–数字函数
–1、 ROUND: 四舍五入
select ROUND(45.926, 2) from employees; - -(目标数，四舍五入保留多少位小数) 把 45.926 保留两位小数，结果就是45.93
–2、 TRUNC: 截断
select TRUNC(45.926, 2) from employees; --(目标数，直接保留多少位小数) 把 45.926 直接截断保留两位小数，结果就是 45.92
–3、 MOD: 求余
select MOD(1600, 300) from employees; --(目标数，目标数需除于的数) 把 1600 除去 300 还剩余多少，结果是100 。

日期的数学运算
•在日期上加上或减去一个数字结果仍为日期。
•两个日期相减返回日期之间相差的天数。
–日期不允许做加法运算，无意义
•可以用数字除24来向日期中加上或减去天数。
1、select sysdate from employees; --查询出运行这行代码执行的系统时间。
2、SELECT last_name,hire_date, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_id = 90; --当前SYSDATE 系统时间 减去 hire_date 除于 7 （一个星期） 等于有多少个星期。

日期函数

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

1、select MONTHS_BETWEEN (‘01-1月-95’,‘11-9月-94’) from employees; --两个日期相差的月数
2、select ADD_MONTHS (‘11-1月-94’,6) from employees; --向指定日期中加上若干月数 1994/7/11
3、select NEXT_DAY (to_date(‘02-2月-19’),‘星期一’) from employees; --指定02-2月-19 的下一个星期一对应的日期是多少。
4、select LAST_DAY(‘03-3月-19’) from employees; --查出 03-3月-19 的这个月的最后一天的日期。
5、select ROUND(SYSDATE,‘MONTH’) FROM employees; --日期的四舍五入，日期最近的月份 MONTH 月
6、select TRUNC(SYSDATE ,‘YEAR’) from employees; --日期截断 日期的年的第一个月份的第一天

转换函数
TO_CHAR函数对日期的转换
格式:
•必须包含在单引号中而且大小写敏感。
•可以包含任意的有效的日期格式。
•日期之间用逗号隔开。
1、SELECT last_name, TO_CHAR(hire_date, ‘DD Month YYYY’) AS HIREDATE
FROM employees; --用TO_CHAR 把hire_date的日期格式改为’DD Month YYYY’显示出来。

TO_DATE 函数对字符的转换
select TO_DATE(‘2012年10月29日 08:10:21’,‘yyyy"年"mm"月"dd"日"hh:mi:ss’) From employees; --将字符转换成数字，把年月日去掉。

TO_NUMBER 函数对字符的转换
select TO_NUMBER(‘￥1,234,567,890.00’,‘L999,999,999,999.99’)
from employees;

通用函数
这些函数适用于任何数据类型，同时也适用于空值：
•NVL (expr1, expr2)
•NVL2 (expr1, expr2, expr3)
•NULLIF (expr1, expr2)
•COALESCE (expr1, expr2, …, exprn)
NVL 函数
将空值转换成一个已知的值：
•可以使用的数据类型有日期、字符、数字。
•函数的一般形式:
–NVL(commission_pct,0)
–NVL(hire_date,‘01-JAN-97’)
–NVL(job_id,‘No Job Yet’)
– NVL 函数 将空值转换成一个已知的值： NVL (expr1, expr2) 当 expr1 为空的时候，输出expr2 。
– 第一个数据是什么数据类型，第二个也要是相同的数据类型。
1、select nvl(to_char(commission_pct),‘没有奖金’) FROM employees;

使用 NVL2 函数
NVL2 (expr1, expr2, expr3) : expr1不为 NULL，返回 expr2 ；为 NULL，返回 expr3 。
SELECT last_name, salary, commission_pct,
NVL2(commission_pct,
‘SAL+COMM’, ‘SAL’) income
FROM employees WHERE department_id IN (50, 80);

使用 NULLIF 函数
NULLIF (expr1, expr2) : 相等返回 NULL，不等返回 expr1
SELECT first_name, LENGTH(first_name) “expr1”,
last_name, LENGTH(last_name) “expr2”,
NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM employees;

使用 COALESCE 函数
•COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。
•如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE 。
– 如果 commission_pct 不为空就显示本身，为空就显示第二个salary，第二个为空就显示第三个 ，以此类推。