Oracle数据库之单行函数（一）字符、数字、日期、转换、通用、嵌套函数

单行函数

字符函数

大小写控制函数

函数	结果
LOWER(‘SQL Server’)	sql server
UPPER(‘SQL Server’)	SQL SERVER
INITCAP(SQL Server)	Sql Server

eg:

SELECT lower('XXX')  FROM dual;

output:

xxx

注：下面的函数都可通过虚表测试

字符控制函数

函数	结果	注释
CONCAT(‘Hello’，‘World’)	HelloWorld	连接字符串
SUBSTR(‘HelloWorld’,1,5)	Hello	截取字符串
LENGTH(‘HelloWorld’)	10	字符串长度
INSTR(‘HelloWorld’,‘W’)	6

数字函数

函数	结果	注释
round(23.356,2)	23.36	四舍五入
trunc(45.356,2)	45.92	截断
mod(1600,300)	100	求余

日期函数

默认的日期格式是：DD-MON-RR day-month-year

函数	结果	注释
sysdate	2019/9/9 15:01:46	系统时间
months_between(‘22-JAN-2019’,‘2-FEB-2018’)	11	两个日期相差的月份
add_months(‘9-sep-2019’,3)	2019/12/9	指定日期添加月份显示日期
next_day(‘9-sep-2019’,‘FRI’)	2019/9/13	指定日期的下一个日期（通过星期确定）
last_day(‘9-sep-2019’)	2019/9/30	本月的最后一天
trunc(sysdate,‘month’)	2019/9/1	系统日期的截断（month条件下后面day为1，year条件后面month、day都为1，day条件正常）

数据类型转换函数

隐式数据类型转换

通过表达式计算可以得出：

原数据类型	目标数据类型	实现方式
VARCHAR2/CHAR	NUMBER	select '2'+2 from dual
VARCHAR2/CHAR	DATE	select '10-九月-2019'+1 from dual

显示数据类型转换

原数据类型	目标数据类型	实现方式	结果
VARCHAR2/CHAR	NUMBER	select to_number('555')from dual	555
VARCHAR2/CHAR	DATE	SELECT to_date('01/2/90','DD-MM-RR')FROM dual	1990/2/1
NUMBER	varchar2/char	SELECT to_char(2600.00,'999,999.99L')salary FROM dual	2,600.00￥

嵌套函数

嵌套函数执行顺序是由里到外

通用函数

NVL函数

• 使用：NVL( string1, replace_with)
• 功能：如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。
• 实现：SELECT last_name,NVL( '', '222')name1 FROM employees
• 

NVL2函数

• 使用：NVL2(expr1,expr2,expr3)
• 功能：如果参数表达式expr1值为NULL，则NVL2()函数返回参数表达式expr3的值；如果参数表达式expr1值不为NULL，则NVL2()函数返回参数表达式expr2的值。
• 实现：SELECT last_name,NVL2('2','aa','bb')name2 FROM employees
• 结果：

NULLIF函数

• 形式：NULLIF(exp1,exp2)
• 功能如果exp1=exp2,返回空值，如果exp1!=exp2,返回exp1
• 例如：select nullif(1,2)from dual
• 结果：1

COALESCE函数

• 形式coalesce(exp1,exp2,…expn)
• 功能：返回参数中第一个非空的参数
• 例如：SELECT COALESCE(NULL,3,NULL,5)FROM dual
• 结果：3

CASE…ELSE…END

• 形式：

case...
	when ...then...
	when...then...
	.
	.
	.
	when...then...
else...
end 别名

• 举例

select last_name,job_id,salary,
case job_id
	when 'IT_PROG' then 1.10*salary
	when 'SE_CLERK' then 1.15*salary
	when 'SA_REP' THEN 1.20*salary
else salary
end revised_salary
from employees

• 结果
  

decode函数

• 形式

decode(表达式，
	值1，翻译值1，
	值2，翻译值2，
		.
		.
		.
	值n, 翻译值n,
	default值)别名

• 举例

SELECT last_name, salary, 
       DECODE (TRUNC(salary/2000, 0), 
       0, 0.00, 
       1, 0.09,
       2, 0.20, 
       3, 0.30, 
       4, 0.40, 
       5, 0.42, 
       6, 0.44, 
       0.45) TAX_RATE
FROM   employees WHERE  department_id = 80;

• 解释

if(表达式==值1)
return 翻译值1
if(表达式==值2)
return 翻译值2  
	.
	.
	.
default)别名