oracle 11g 007 sql 第4章 函数2 笔记
conversion funstions
1.to_char、to_date、to_number
2.nesting fustions(函数的嵌套)
3.data type (数据类型)
数据类型的转换分为两类：1)是隐式的转换（Implicit data type） 2)是显式的转换（explicit data type），个人建议用显式的转换
1).Implicit data type分为：varchar2 or char
char 长度是NULL的，比如共有10个字符，你输入1个字符也等于10个字符。
varchar2 var是可变的长度，比如你共有10个字符，你给它赋予1个字符那就等于1个字符，这样不会浪费资源。
Implicit data type例子：
SQL> select '923.14'+25.05 from dual;
'923.14'+25.05
--------------
948.19
注意：Implicit data type会自动转换数值。
2).explicit data type 分为：number、character、date
可以吧date转换成字符串如下例子；
SQL> select employee_id,to_char(hire_date,'mm/yy') month_hired
2 from employees
3 where last_name='Higgins';
EMPLOYEE_ID MONTH_HIRED
----------- -----------
205 06/94
如果在日期前有前导零"0"的话就在formant_model前加fm。
to_char的使用$格式化例子：
SQL> select to_char(salary,'$99,999.00') salary
2 from employees
3 where last_name='Ernst';
SALARY
-----------
$6,000.00
to_number的使用例子：
SQL> select to_number('970.03') from dual;
TO_NUMBER('970.03')
-------------------
970.03
以上是将字符串转换为数字，来个复杂点的加上$的格式化例子如下：
SQL> select to_number('$12,346.67','$99,999.00') from dual;
TO_NUMBER('$12,346.67','$99,99
------------------------------
12346.67
to_date的使用例子：
SQL> select to_date('1999/2/13','yyyy/mm/dd') from dual;
TO_DATE('1999/2/13','YYYY/MM/D
------------------------------
1999/2/13
来点复杂的to_date的语句：
SQL> select last_name,to_char(hire_date,'yyyy/mm/dd')
2 from employees
3 where hire_date < to_date('1997/2/17','yyyy/mm/dd');
LAST_NAME TO_CHAR(HIRE_DATE,'YYYY/MM/DD'
------------------------- ------------------------------
Whalen 1987/09/17
Hartstein 1996/02/17
Mavris 1994/06/07
Baer 1994/06/07
Higgins 1994/06/07
Gietz 1994/06/07
King 1987/06/17
Kochhar 1989/09/21
De Haan 1993/01/13
Hunold 1990/01/03
Ernst 1991/05/21
Greenberg 1994/08/17
Faviet 1994/08/16
Raphaely 1994/12/07
Khoo 1995/05/18
Weiss 1996/07/18
Kaufling 1995/05/01
Marlow 1997/02/16
Mallin 1996/06/14
Ladwig 1995/07/14
以上是列出在employees（雇员表）中大于1997/2/17的值全部列出。
2.nesting fustions(函数的嵌套)
嵌套函数分为：NVL 、NVL2 、 NULLIF
1)NVL(commission_pct，0) ----表示第1个参数是NULL则为0,如果不是NULL则是commission_pct值；
SQL> select last_name,salary,nvl(commission_pct,0),
2 (salary*12)+(salary*12*nvl(commission_pct,0)) as AN_SAL
3 from employees;
LAST_NAME SALARY NVL(COMMISSION_PCT,0) AN_SAL
------------------------- ---------- --------------------- ----------
Olson 2100.00 0 25200
Mallin 3300.00 0 39600
Rogers 2900.00 0 34800
Gee 2400.00 0 28800
Philtanker 2200.00 0 26400
Ladwig 3600.00 0 43200
Stiles 3200.00 0 38400
Seo 2700.00 0 32400
Patel 2500.00 0 30000
Rajs 3500.00 0 42000
Davies 3100.00 0 37200
Matos 2600.00 0 31200
Vargas 2500.00 0 30000
Russell 14000.00 0.4 235200
Partners 13500.00 0.3 210600
Errazuriz 12000.00 0.3 187200
Cambrault 11000.00 0.3 171600
Zlotkey 10500.00 0.2 151200
Tucker 10000.00 0.3 156000
Bernstein 9500.00 0.25 142500
Hall 9000.00 0.25 135000
NVL2函数意思例子如下：
nvl2(commission_pct,'sal+comm','sal')
如果第1个参数（commission_pct）是非NULL则返回第2个参数（'sal+comm')的值,
如果第1个参数是NULL则返回第3个参数（'sal'）的值；
NULLIF函数意思和例子如下：
nullif((LENGTH(first_name),LENGTH(last_name))
如果两个参数相等则返回NULL，两个参数不相等返回第1个参数的值；
Coalesce 函数
SQL> select last_name,employee_id,
2 coalesce (to_char(commission_pct),to_char(manager_id),'no commission and no manager')
3 from employees;
LAST_NAME EMPLOYEE_ID COALESCE(TO_CHAR(COMMISSION_PC
------------------------- ----------- ----------------------------------------
OConnell 198 124
Grant 199 124
Whalen 200 101
Hartstein 201 100
Fay 202 201
Mavris 203 101
Baer 204 101
Higgins 205 101
Gietz 206 205
King 100 no commission and no manager
Kochhar 101 100
De Haan 102 100
Hunold 103 102
Ernst 104 103
Austin 105 103
Pataballa 106 103
Lorentz 107 103
Greenberg 108 101
Faviet 109 108
Chen 110 108
Provide the use of the IF-THEN-ELSE logic within a SQL statement
Use two methods:
1.CASE expression(函数)：
范例：
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
例子如下：
SQL> SELECT last_name, job_id, salary,
2 CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
3 WHEN 'ST_CLERK' THEN 1.15*salary
4 WHEN 'SA_REP' THEN 1.20*salary
5 ELSE salary END "REVISED_SALARY"
6 FROM employees;
LAST_NAME JOB_ID SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
OConnell SH_CLERK 2600.00 2600
Grant SH_CLERK 2600.00 2600
Whalen AD_ASST 4400.00 4400
Hartstein MK_MAN 13000.00 13000
Fay MK_REP 6000.00 6000
Mavris HR_REP 6500.00 6500
Baer PR_REP 10000.00 10000
Higgins AC_MGR 12000.00 12000
Gietz AC_ACCOUNT 8300.00 8300
King AD_PRES 24000.00 24000
Kochhar AD_VP 17000.00 17000
De Haan AD_VP 17000.00 17000
Hunold IT_PROG 9000.00 9900
Ernst IT_PROG 6000.00 6600
Austin IT_PROG 4800.00 5280
Pataballa IT_PROG 4800.00 5280
Lorentz IT_PROG 4200.00 4620
Greenberg FI_MGR 12000.00 12000
Faviet FI_ACCOUNT 9000.00 9000
Chen FI_ACCOUNT 8200.00 8200
DECODE function(函数)：
范例：
DECODE(col|expression, search1, result1
[, search2, result2,...,]
[, default])
例子如下：
SQL> SELECT last_name, job_id, salary,
2 DECODE(job_id, 'IT_PROG', 1.10*salary,
3 'ST_CLERK', 1.15*salary,
4 'SA_REP', 1.20*salary,
5 salary)
6 REVISED_SALARY
7 FROM employees;
LAST_NAME JOB_ID SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
OConnell SH_CLERK 2600.00 2600
Grant SH_CLERK 2600.00 2600
Whalen AD_ASST 4400.00 4400
Hartstein MK_MAN 13000.00 13000
Fay MK_REP 6000.00 6000
Mavris HR_REP 6500.00 6500
Baer PR_REP 10000.00 10000
Higgins AC_MGR 12000.00 12000
Gietz AC_ACCOUNT 8300.00 8300
King AD_PRES 24000.00 24000
Kochhar AD_VP 17000.00 17000
De Haan AD_VP 17000.00 17000
Hunold IT_PROG 9000.00 9900
Ernst IT_PROG 6000.00 6600
Austin IT_PROG 4800.00 5280
Pataballa IT_PROG 4800.00 5280
Lorentz IT_PROG 4200.00 4620
Greenberg FI_MGR 12000.00 12000
Faviet FI_ACCOUNT 9000.00 9000
Chen FI_ACCOUNT 8200.00 8200
