函 数(图解)
1单行函数
| 单行函数: |
|---|
| 操作数据对象 |
| 接受参数返回一个结果 |
| 只对一行进行变换 |
| 每行返回一个结果 |
| 可以转换数据类型 |
| 可以嵌套 |
| 参数可以是一列或一个值function_name [(arg1, arg2,…)] |
 |
1.1字符函数
举例大小写控制函数
LOWER('SQL Course')--sql course
UPPER('SQL Course')--SQL COURSE
INITCAP('SQL Course')--Sql Course
实际应用 last_name 名字可能驼峰（Higgins）匹配不到数据
SELECT employee_id, last_name, department_id
FROM employees
WHERE last_name = 'higgins';
no rows selected
解决：转小写后匹配
SQL> SELECT employee_id, last_name, department_id
2 FROM employees
3 WHERE LOWER(last_name) = 'higgins';
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID
----------- ------------------------- -------------
205 Higgins 110
1 row selected
字符控制函数
| 函数 | 结果 | 作用 |
|---|---|---|
| CONCAT(‘Hello’,‘World’) | HelloWoeld | 字符串拼接 |
| SUBSTR(‘HelloWorld’,1,5) | Hello | 字符串截取（这里是指从第一位开始截取5个字符长度） |
| LENGTH(‘HelloWorld’) | 10 | 字符串长度 |
| INSTR（‘HelloWorld’,‘W’） | 6 | instr(源字符串, 目标字符串, 起始位置, 匹配序号) |
| LPAD(salary,10,‘*’) | *****10000 | 这里指（工资10000）不足左边补* |
| RPAD(salary,10,‘*’) | 10000***** | 这里指（工资10000）不足右边补* |
| TRIM('H" FROM ‘HelloWorld’) | elloWorld | 这里指去两端的H |
| REPLACE(‘abcd’,‘b’,‘e’) | aecd | 替换abcd的b为e |
实战举例
SELECT employee_id, CONCAT(first_name, last_name) NAME,
job_id, LENGTH (last_name),
INSTR(last_name, 'a') "Contains 'a'?"
FROM employees
WHERE SUBSTR(job_id, 4) = 'REP';
EMPLOYEE_ID NAME JOB_ID LENGTH(LAST_NAME) Contains 'a'?
----------- --------------------------------------------- ---------- ----------------- -------------
174 EllenAbel SA_REP 4 0
176 JonathonTaylor SA_REP 6 2
178 KimberelyGrant SA_REP 5 3
1.2 数字函数
| 函数 | 作用 | 举例 | 结果 |
|---|---|---|---|
| ROUND: | 四舍五入 | ROUND(45.926, 2) | 45.93 |
| TRUNC | 截断 | TRUNC(45.926, 2) | 45.92 |
| MOD | 求余 | MOD(1600, 300) | 100 |
举例ROUND
SQL> SELECT ROUND(45.923,2), ROUND(45.923,0),
2 ROUND(45.923,-1)
3 FROM DUAL;
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
--------------- --------------- ----------------
45.92 46 50
1 row selected
举例TRUNC
SQL> SELECT TRUNC(45.923,2), TRUNC(45.923),
2 TRUNC(45.923,-2)
3 FROM DUAL;
TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-2)
--------------- ------------- ----------------
45.92 45 0
1 row selected
举例MOD
SQL> SELECT last_name, salary, MOD(salary, 5000)
2 FROM employees
3 WHERE job_id = 'SA_REP';
LAST_NAME SALARY MOD(SALARY,5000)
------------------------- ---------- ----------------
Abel 11000.00 1000
Taylor 8600.00 3600
Grant 7000.00 2000
1.3 Oracle 中的日期型数据实际含有两个值: 日期和时间。
SQL> SELECT last_name, hire_date
2 FROM employees
3 WHERE last_name like 'G%';
LAST_NAME HIRE_DATE
------------------------- -----------
Grant 1999/5/24
Gietz 1994/6/7
Greenberg 1994/8/17
函数SYSDATE 返回:日期 时间
| 日期运算 |
|---|
| 在日期上加上或减去一个数字结果仍为日期。 |
| 两个日期相减返回日期之间相差的天数。 |
| 日期不允许做加法运算，无意义 |
举例日期（可以用数字除24来向日期中加上或减去天数）
SQL> SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
2 FROM employees
3 WHERE department_id = 90;
LAST_NAME WEEKS
------------------------- ----------
King 1787.52749
Kochhar 1669.38463
De Haan 1496.52749
3 rows selected
日期函数
| 函数 | 描述 |
|---|---|
| MONTHS_BETWEEN | 返回日期相差月数 |
| ADD_MONTHS | 向指定日期中加上若干月数 |
| NEXT_DAY | 指定日期的下一个星期*对应的日期 |
| LAST_DAY | 本月最后一天 |
| ROUND | 日期四舍五入 |
| TRUNC | 日期截断 |
举例MONTHS_BETWEEN
SQL> SELECT MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') FROM DUAL;
MONTHS_BETWEEN('01-SEP-95','11-JAN-94')
---------------------------------------
19.6774193548387
1 row selected
举例ADD_MONTHS
SQL> SELECT ADD_MONTHS('18,SEP,21',2) FROM DUAL;
ADD_MONTHS('18,SEP,21',2)
-------------------------
2021/11/18
1 row selected
举例NEXT_DAY
SQL> SELECT NEXT_DAY ('01-SEP-21','FRIDAY') FROM DUAL;
NEXT_DAY('01-SEP-21','FRIDAY')
------------------------------
2021/9/3
1 row selected
举例LAST_DAY
SQL> SELECT LAST_DAY('10-SEP-21') FROM DUAL;
LAST_DAY('10-SEP-21')
---------------------
2021/9/30
1 row selected
举例ROUND 和 TRUNC
Assume SYSDATE = '18-SEP-21'):
SQL> SELECT ROUND(SYSDATE,'MONTH') FROM DUAL;
ROUND(SYSDATE,'MONTH')
----------------------
2021/10/1
1 row selected
SQL> SELECT ROUND(SYSDATE ,'YEAR') FROM DUAL;
ROUND(SYSDATE,'YEAR')
---------------------
2022/1/1
1 row selected
SQL> SELECT TRUNC(SYSDATE ,'MONTH') FROM DUAL;
TRUNC(SYSDATE,'MONTH')
----------------------
2021/9/1
1 row selected
SQL> SELECT TRUNC(SYSDATE ,'YEAR') FROM DUAL;
TRUNC(SYSDATE,'YEAR')
---------------------
2021/1/1
1.4 数据类型转换
隐性数据类型转换（自动转换）
| 源数据类型 | 目标数据类型 |
|---|---|
| VARCHAR2 or Char | NUMBER |
| VARCHAR2 or Char | DATE |
| NUMBER | VARCHAR2 |
| DATE | VARCAHR2 |
| 总结 | DATE<->VARCAHR2<->NUMBER |
显示数据类型转换
TO_CHAR函数对日期的转换
TO_CHAR(date, 'format_model')
//格式:
//必须包含在单引号中而且大小写敏感。
//可以包含任意的有效的日期格式。
//日期之间用逗号隔开。
SELECT TO_CHAR(sysdate,‘yyyy-mm-dd hh:mi:ss’) FROM dual;
| 日期格式元素 | 举例 |
|---|---|
| YYYY | 2004 |
| YEAR | TWO HUOUSAND FOUR |
| MM | 02 |
| MONTH | JULY |
| MON | JUL |
| DY | MON |
| DAY | MONDAY |
| DD | 02 |
日期格式的元素
HH24:MI:SS AM //15:45:32 PM
DD "of" MONTH //12 of OCTOBER
SQL> SELECT last_name,
2 TO_CHAR(hire_date, 'DD Month YYYY')
3 AS HIREDATE
4 FROM employees;
LAST_NAME HIREDATE
------------------------- ---------------------
King 17 June 1987
Kochhar 21 September 1989
De Haan 13 January 1993
SQL> select employee_id,last_name,hire_date
2 from employees
3 where to_char(hire_date,'yyyy-mm-dd')='1987-09-17';
EMPLOYEE_ID LAST_NAME HIRE_DATE
----------- ------------------------- -----------
200 Whalen 1987/9/17
1 row selected
TO_DATE 函数对字符的转换
TO_DATE(char[, 'format_model'])
TO_DATE('2012年10月29日 08:10:21','yyyy"年"mm"月"dd"日" hh:mi:ss')
From dual;
对数字类型的转换
TO_CHAR(number, 'format_model')
SQL> SELECT TO_CHAR(salary, '$99,999.00') SALARY
2 FROM employees
3 WHERE last_name = 'Ernst';
SALARY
-----------
$6,000.00
1 row selected
TO_NUMBER(char[, 'format_model'])
TO_NUMBER('￥1,234,567,890.00','L999,999,999,999.99')
----------------------------------------------------
1234567890
1 row selected
1.5 通用函数（这些函数适用与任何数据类型，同时也适用与空值）
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
SQL> SELECT last_name, salary, NVL(commission_pct, 0),
2 (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
3 FROM employees;
LAST_NAME SALARY NVL(COMMISSION_PCT,0) AN_SAL
------------------------- ---------- --------------------- ----------
Vargas 2500.00 0 30000
Zlotkey 10500.00 0.2 151200
Abel 11000.00 0.3 171600
举例NVL2
SQL> SELECT last_name, salary, commission_pct,
2 NVL2(commission_pct,
3 'SAL*10', 'SAL') income
4 FROM employees WHERE department_id IN (50, 80)
LAST_NAME SALARY COMMISSION_PCT INCOME
------------------------- ---------- -------------- ------
Vargas 2500.00 SAL
Zlotkey 10500.00 0.20 SAL*10
Abel 11000.00 0.30 SAL*10
举例NULLIF (expr1, expr2) : 相等返回NULL，不等返回expr1
SQL> SELECT first_name, LENGTH(first_name) "expr1",
2 last_name, LENGTH(last_name) "expr2",
3 NULLIF(LENGTH(first_name), LENGTH(last_name)) result
4 FROM employees;
FIRST_NAME expr1 LAST_NAME expr2 RESULT
-------------------- ---------- ------------------------- ---------- ----------
Steven 6 King 4 6
Alexander 9 Hunold 6 9
Bruce 5 Ernst 5
COALESCE 函数
COALESCE是一个函数， (expression_1, expression_2, …,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。
COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。
如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE 。
SQL> SELECT last_name,
2 COALESCE(commission_pct, salary, 10) comm
3 FROM employees
4 ORDER BY commission_pct;
LAST_NAME COMM
------------------------- ----------
Banda 10
Greene 0.15
King 24000
1.6 条件表达式
在 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, 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;
1.7 嵌套函数
单行函数可以嵌套。
嵌套函数的执行顺序是由内到外。
SQL> SELECT last_name,
2 NVL(TO_CHAR(manager_id), 'No Manager')
3 FROM employees
4 WHERE manager_id IS NULL;
LAST_NAME NVL(TO_CHAR(MANAGER_ID),'NOMANAGER')
------------------------- ----------------------------------------
King No Manager
总结
使用函数对数据进行计算
使用函数修改数据
使用函数控制一组数据的输出格式
使用函数改变日期的显示格式
使用函数改变数据类型
使用 NVL 函数
使用IF-THEN-ELSE 逻辑
123
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
