单行函数
-
操作数据对象
-
接受参数返回一个结果
-
只对一行进行变换
-
每行返回一个结果
-
可以转换数据类型
-
可以嵌套
-
参数可以是一列或一个值
大小写控制函数
| 函数 | 结果 |
|---|---|
| LOWER****('SQL Course') | sql course |
| UPPER****('SQL Course') | SQL COURSE |
| INITCAP('SQL Course') | Sql Course |
大小写控制函数
显示员工 Higgins的信息:
SELECT employee_id, last_name, department_id
FROM EMPLOYEES
WHERE lower(last_name) = 'higgins';
字符控制函数
| 函数 | 结果 |
|---|---|
| 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 |
SELECT ROUND(45.923,2), ROUND(45.923,0),
ROUND(45.923,-1)
FROM DUAL;
SELECT TRUNC(45.923,2), TRUNC(45.923),
TRUNC(45.923,-2)
FROM DUAL;
日 期
- Oracle 中的日期型数据实际含有两个值: 日期和时间。
SELECT last_name, hire_date
FROM employees
WHERE last_name like 'G%';
函数SYSDATE 返回: 日期 时间
日期的数学运算
-
在日期上加上或减去一个数字结果仍为日期。
-
两个日期相减返回日期之间相差的天数。
-
日期不允许做加法运算，无意义。
-
可以用数字除24来向日期中加上或减去小时数。
-
可以用数字向日期加上或减去天数。
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_id = 90;
日期函数
| 函数 | 描述 |
|---|---|
| MONTHS_BETWEEN | 两个日期相差的月数 |
| ADD_MONTHS | 向指定日期中加上若干月数 |
| NEXT_DAY | 指定日期的下一个星期 * 对应的日期 |
| LAST_DAY | 本月的最后一天 |
| ROUND | 日期四舍五入 |
| TRUNC | 日期截断 |
隐式数据类型转换
| 源数据类型 | 目标数据类型 |
|---|---|
| VARCHAR2 or CHAR | NUMBER |
| VARCHAR2 or CHAR | DATE |
| NUMBER | VARCHAR2 |
| DATE | VARCHAR2 |
TO_CHAR函数对日期的转换
格式:
-
必须包含在单引号中而且大小写敏感。
-
可以包含任意的有效的日期格式。
-
日期之间用逗号隔开。
SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') FROM dual;
TO_CHAR 函数对日期的转换
SELECT last_name,
TO_CHAR(hire_date, 'DD Month YYYY')
AS HIREDATE
FROM employees;
TO_DATE 函数对字符的转换
select TO_DATE('2012年10月29日 08:10:21','yyyy"年"mm"月"dd"日"hh24:mi:ss') time
From dual;
通用函数
这些函数适用于任何数据类型，同时也适用于空值：
-
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')
条件表达式
-
在 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 函数
在需要使用 IF-THEN-ELSE 逻辑时:
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;
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;
嵌套函数
-
单行函数可以嵌套。
-
嵌套函数的执行顺序是由内到外。
SELECT last_name,
NVL(TO_CHAR(manager_id), 'No Manager')
FROM employees
WHERE manager_id IS NULL;