Oracle函数学习笔记

1. 单行函数

1.1 字符函数

LOWER 使字符串小写；
select LOWER('HeLp') from dual     --->help

UPPER 使字符串大写；
select UPPER('HeLp') from dual     --->HELP

INITCAP 使字符串的第一个字母大写，其它为小写
select INITCAP('hELp') from dual   --->Help

LENGTH 返回表达式中的字符串长度
select LENGTH('hELP') from dual   ---->4

CONCAT 将值连接到一起(，对于此函数，只能使用2个参数)
select CONCAT('Hello', 'World') from dual   --->HelloWorld

SUBSTR　抽取确定长度的字符串
select SUBSTR('HelloWorld',1,5) from dual   ---->Hello

INSTR 查找指定字符的数字位置
select INSTR('HelloWorld', 'W') from dual  --->6

LPAD 按右对齐填充字符串
select LPAD(salary,10,'*') from dual    --->*****24000

RPAD 按左对齐填充字符串
select RPAD(salary, 10, '*') from dual  --->24000*****

TRIM(leading|trailing|both,trim_character FROM trim_source) 从字符串中截去头部或者尾部的字符(或者头尾都截掉)(如果trim_character或trim_source是一个字符型文字值，则必须将它包含在单引号之内。)
select TRIM('H' FROM 'HelloWorld') from dual  --->elloWorld

REPLACE(text,search_string,replacement_string) 搜索字符串的文本表达式，如果找到，用指定的替代字符串替换它。
select REPLACE('HelloWorld','Hello','hi') from dual  --->hiWorld

1.2数字函数

ROUND 将值四舍五入到指定的位数
ROUND(45.926, 2) ---->45.93

TRUNC 将值截断到指定的小数位(不舍入)
TRUNC(45.926, 2)  ---->45.92

MOD 返回除法运算的余数
MOD(1600, 300) --->100

1.3 日期函数

在oracle中，日期是以数字的形式存储的，故可以直接对日期类型的数据进行+-*/等运算。

MONTHS_BETWEEN 两个日期之间的月数，结果以月为单位
MONTHS_BETWEEN(date1, date2)

ADD_MONTHS 将日历月份添加到日期中
ADD_MONTHS(date, n) n为月数

NEXT_DAY 指定日期的下一个月
NEXT_DAY(date, 'char') char可以是星期，月数

LAST_DAY 月份的最后一天
LAST_DAY(date)

ROUND 四舍五入日期
ROUND(date[, 'fmt'])

设置日期格式：alter session set nls_date_format='yyyy-mm-dd:hh24:mi:ss'

TRUNC 截断日期
TRUNC(date[, 'fmt'])

1.4 转换函数

TO_CHAR(number|date,'[fmt]',[nlsparams])将数字和日期值转换为格式样式为fmt的varchar2字符串。
nlsparams参数指定返回的月份名称、日期名称以及缩写所用的语言。

TO_NUMBER(char,'[fmt]',[nlsparams])将包含数字的字符串转换为格式样式为fmt指定的格式表示的数字。
nlsparams参数与TO_CHAR()相同，是进行数字转换。

TO_DATE(char,'[fmt]',[nlsparams])将代表日期的字符串按照指定的fmt转换为日期值。如果省略了fmt，则格式为DD-MON-YY。
nslparams参数在此函数中的用途是进行日期转换。

1.5嵌套函数

NVL (expr1, expr2) 将空值转换为实际的值。

NVL2 (expr1, expr2, expr3) 如果expr1为非空，返回expr2。如果expr1为空值，将返回expr3。expr1可以是任何数据类型。

NULLIF (expr1, expr2) 比较2个值，如相等，返回空值，否则返回第一个表达式。

COALESCE (expr1, expr2, ..., exprn) 返回表达式中第一个非空值。

1.6 条件表达式

CASE表达式(符合ANSISQL)
通过执行IF-THEN-ELSE语句的任务，可以简化条件查询
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END

DECODE函数(Oracle专用语法)
通过执行IF-THEN-ELSE语句的任务，可以简化条件查询
DECODE(col|expression, search1, result1
[, search2, result2,...,]
[, default])


2、分组函数

使用分组函数的准则：
DISTINCT 使函数只考虑非重复值；ALL使之考虑包括重复值在内的所有值。默认值为ALL，因此它不需
要专门指定。
带有expr参数的函数的数据类型可以是CHAR、VARCHAR2、NUMBER或者DATE。
所有分组函数都忽略空值。要用一个值代替空值，可以使用NVL、NVL2或者COALESCE函数。
在使用GROUP BY 字句时，Oracle服务器隐式地按照升序对结构集进行排序。要改写此默认顺序，可以
在ORDER BY 字句中使用DESC。

2.1 AVG([DISTINCT|ALL]n)   n的平均值，忽略空值

2.2 COUNT({*|[DISTINCT|ALL]expr}) 行数，其中expr用来判断非空值(使用*计算所有选定行，包括重
复行和带有空值的行)

2.3 MAX([DISTINCT|ALL]expr)  expr的最大值，忽略空值

2.4 MIN([DISTINCT|ALL]expr) expr的最小值，忽略空值
注：可以对任何数据类型使用MIN 和 MAX 函数

2.5 STDDEV([DISTINCT|ALL]x)  n的标准偏差，忽略空值

2.6 SUM([DISTINCT|ALL]n)    n的总计值，忽略空值

2.7 VARIANCE([DISTINCT|ALL]x)  n的方差，忽略空值
例1：
SELECT AVG(salary), MAX(salary),
MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
例2：
SELECT MIN(hire_date), MAX(hire_date)
FROM employees;
例3：
SELECT COUNT(*)
FROM employees
WHERE department_id = 50;

2.8 GROUP BY 字句
语法：GROUP BY group_by_expression    group_by_expression－－－>指定列，这些列的值是对行进行分组的基础。
作用：通过使用GROUP BY字句将表中的行分成更小的组。也可以对多个列使用GROUP BY子句。
例4：
SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;
注意：
A、 SELECT 列表中不是聚合函数的任何列或表达式都必须在GROUP BY子句中。
B、 不能使用 WHERE 子句来限制组；
C、 可以使用HAVING子句来限制组；
D、 不能在 WHERE 子句中使用分组函数。

2.9 嵌套分组函数
分组函数可以嵌套两层。
例5：
SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;