Oracle学习笔记2——SQL 单行函数

单行函数

• 操作数据对象
• 接受参数返回一个结果
• 只对一行进行变换
• 每行返回一个结果
• 可以转换数据类型
• 可以嵌套
• 参数可以是一列或一个值
• 分类：字符、数字、日期、转换、通用函数

字符函数

大小写控制函数

这类函数改变字符的大小写。

INITCAP将每个单词的第一个字母大写,其它字母变为小写返回。单词由空格、控制字符、标点符号等非字母符号限制。

字符控制函数

这类函数控制字符。

字符串下角标从1开始。

SUBSTR(取字符串开始的下角标，取字符串的长度)

INSTR用于求某字符串在指定字符串中（首次）出现的位置。如果不存在返回0。

LPAD表示用x位来显示a数据，如果a数据不足x位，用指定字符进行左填充。

trim一般都是用在删除字符串首尾的空格。也可以用来删除字符串首尾的指定字符。并且trim指定删除的字符串只能是单个字符。如 trim('字符1' from '字符串2') ，字符1只能是单个字符。

数字函数

个位数的下标为0。

ROUND: 四舍五入

ROUND(45.926)  = ROUND(45.926，0)                   46
ROUND(45.926, 2)                                  45.93

TRUNC: 截断

TRUNC(45.926)  =TRUNC(45.926，0)                    46
TRUNC(45.926, 2)                                  45.92
TRUNC(45.926, -2)                                   0
TRUNC(45.926, -1)                        40（保留十位，后面截去，补0）

MOD: 求余

MOD(1600, 300)                                     100

日期函数

Oracle 中的日期型数据实际含有两个值: 日期和时间。

函数SYSDATE返回: 日期、时间。

在日期上加上或减去一个数字结果仍为日期。

两个日期相减返回日期之间相差的天数。 （日期不允许做加法运算，无意义）

可以用数字除24来向日期中加上或减去天数。

表达式	含义
yyyy	年
mm	月
dd	日
day	星期
hh	小时
mi	分钟
ss	秒

ROUND四舍五入年份按1-6月和7-12月四舍五入到最近的 几几年1月

ROUND四舍五入月份按1-15日和16-30日四舍五入到最近的 几月1日

ROUND四舍五入天数按**周一至周三 和 周四至周日 **四舍五入到最近的 周日

转换函数

隐式： Oracle 自动完成下列转换

显式：

TO_CHAR

TO_CHAR (date, ‘format_model’)

SELECT employee_id,hire_date
FROM employees
WHERE TO_CHAR(hire_date,'yyyy/mm/dd')='1994/06/07';

TO_CHAR(number, ‘format_model’)

SELECT TO_CHAR(12345678.99,'L999,999,999.99') FROM dual;

在TO_CHAR 函数中经常使用的几种格式

符号	含义
9	数字
0	零
$	美元符
L	本地货币符号
.	小数点
,	千位符

TO_DATE

TO_DATE(char[ , ‘format_model’ ])

SELECT employee_id,hire_date
FROM employees
WHERE TO_DATE('1994/06/07','yyyy/mm/dd')=hire_date;

TO_NUMBER

TO_NUMBER(char[ , ‘format_model’ ])

TO_NUMBER('￥1,234,567,890.00','L999,999,999,999.99')
FROM dual

通用函数

这些函数适用于任何数据类型，同时也适用于空值：

NVL (expr1, expr2)

将空值转换成一个已知的值，可以使用的数据类型有日期、字符、数字。

SELECT last_name,NVL(TO_CHAR(department_id),'没有部门')
FROM employees;

NVL2 (expr1, expr2, expr3)

expr1不为NULL，返回expr2；为NULL，返回expr3。

SELECT employee_id,NVL2(commission_pct,commission_pct+0.015,0.01)
FROM employees;

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 (expr1, expr2, …, exprn)

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

SELECT last_name,
	   COALESCE(commission_pct,salary,10) comm
FROM employees
ORDER BY commission_pct;

条件表达式

在 SQL 语句中使用IF-THEN-ELSE 逻辑有两种方法：CASE 表达式；DECODE 函数。

CASE表达式

CASE expr WHEN comparison_expr1 THEN return_expr1
		 [WHEN comparison_expr2 THEN return_expr2
		  WHEN comparison_exprn THEN return_exprn
		  ELSE else_expr]
END

SELECT last_name,job_id,salary,
	CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
				WHEN 'ST_CLERK' THEN 1.15*salary
				WHEN 'SA_REP' THEN 1.20*salary
				ELSE salary
	END		"REVISED_SALARY"
FROM employees;

DECODE 函数

若expression=search1，则结果为 result1

DECODE (col|expression,search1,result1,
					 [,search2,result2,...,]
					 [,default])

SELECT last_name,job_id,salary,
	   DECODE(job_id,'IT_PROG',1.10*salary,
	   				 'ST_CLERK',1.15*salary,
	   				 'SA_REP',1.20*salary,
	   				 		   salary)
	   REVISED_SALARY
FROM employees;

嵌套函数

单行函数可以嵌套。

嵌套函数的执行顺序是由内到外。