SQL 基础之转换函数和条件表达式（八）
数据类型转换分为：[*] 隐式数据类型转换
[*] 显示数据类型转换
隐式数据类型转换:
在表达式中，Oracle服务器自动完成下列转换
FromTo VARCHAR2 or CHARNUMBERVARCHAR2 or CHARDATE NUMBERVARCHAR2 or CHARDATEVARCHAR2 or CHAR 显示数据类型转换
1、使用 TO_CHAR函数对日期的转换
格式：TO_CHAR (date, 'format_model')
[*] 必须包含在单引号中
[*] 大小写敏感。
[*] 可以包含任意的有效的日期格式。
[*] 可以使用 fm 去掉多余的空格或者前导零。
[*] 与日期值用逗号隔开。
日期格式的组成
元素结果YYYY完整的年份YEAR年(英文) MM双位数字月份 MONTH完整的月份名称MON月份的三个字母缩写DY星期的三个字母的缩写DAY完整的星期名称DD月份的数字天
1、查找员工入职日期，并按照 日 月 年方式显示
select last_name,to_char(hire_date,'fmDD Month YYYY') as hiredate from employees;
2、查询员工入职日期，按照英文的星期、月份、英文年数
select last_name,to_char(hire_date,'fmDAY Month YEAR') as hiredate from employees;
3、查询员工入职日期，按照数字 日、月、年方式
select last_name,to_char(hire_date,'fmDD MM YYYY') as hiredate from employees;
4、查询员工入职日期，按照年、月、日方式
select last_name,to_char(hire_date,'YYYY,MM,fmDD') as hiredate from employees;
5、创建一张报表，现在员工入职日期当天为周六，并显示FIRST_NAME和LAST_NAME列的信息和一个表达式，这个表达式的别名为START_DATE。
select first_name,last_name,to_char(hire_date,'fmDAY Month YYYY') start_date from employees where to_char(hire_date,'fmDAY')='SATURDAY';
也可以这样显示
select first_name,last_name,to_char(hire_date,'fmDAY,"the "ddth "of"Month, Yyyysp.') start_date from employees where to_char(hire_date,'fmDAY')='SATURDAY';
使用 TO_CHAR 函数对数字的转换
元素结果9代表一个数字0强制显示0$放置一个浮动的美元符号L采用浮动本地货币符号.打印小数点,打印一个逗号作为千位标示符 1、查找名字为Ernst 员工的工资，并按$99,999.0的格式输出
selectsalary,to_char(salary,'$99,999.00') salary from employees where last_name='Ernst';
2、查找名字为Zlotkey 员工的工资，并按照的$99,999.0的格式输出
select salary,to_char(salary,'L0,0000.000') salary from employees where last_name='Zlotkey';
使用 TO_NUMBER 和 和 TO_DATE 函数
使用 TO_NUMBER 函数将字符转换为数字格式:
TO_NUMBER(char[, 'format_model'])
使用 TO_DATE 函数将字符串转换为日期格式：
TO_DATE(char[, 'format_model'])
这个函数有一个fx 修饰符， 这个修饰符指定TO_DATE 中字符参数和格式精确匹配.
使用RR日期格式，在员工表中查找1990年之前入职的员工，在1999年执行查询或者现在执行，产生的结果是否相同
1、查找05年1月1号以后入职的员工
select last_name,to_char(hire_date,'DD-Mon-YYYY') from employees where hire_date > to_date('01-Jan-05','DD-Mon-RR');
嵌套函数
[*] 单行函数可以嵌套到任意层级
[*] 嵌套函数的执行顺序是由内到外
1、查找60部门的员工last_name并将名字变成大写，与US拼接在一起
select last_name,upper(concat(substr(last_name,1,8),'_US')) con_last_name from employees where department_id=60;
通用函数：这些函数适用于任何数据类型，同时也适用于空值：
NVL (expr1, expr2) : 如果expr1位空，则返回expr2
NVL2 (expr1, expr2, expr3) : 如果参数表达式expr1值为NULL，则NVL2()函数返回参数表达式expr3的值；如果参数表达式expr1值不为NULL，则NVL2()函数返回参数表达式expr2的值。
NULLIF (expr1, expr2) ： 如果两个指定的表达式相等，则返回空值。不相等返回expr1
COALESCE (expr1, expr2, ..., exprn)：依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值
NVL函数
将空值转换成一个已知的值：
可以使用的数据类型有数字、日期、字符。
数据类型必须匹配：
– NVL(commission_pct,0)
– NVL(hire_date,'01-JAN-97')
– NVL(job_id,'No Job Yet')
1、计算员工年薪，按照last_name, 提成，和12*salary 显示
select last_name,salary,nvl(commission_pct,0) comm,(salary*12) +(salary*12*NVL(commission_pct,0)) AN_SAL from employees;
2、查询50和80部门员工的工资并现在三段字符如果commission_pct 为空就显示SAL，如果不为空就显示SAL+COMM
select last_name,salary,commission_pct, nvl2(commission_pct, 'SAL+COMM', 'SAL') income from employees where department_id in (50,60);
3、查找既没有提成也没有领导id的员工，按照last_name、employee_id显示
select last_name,employee_id,coalesce(to_char(commission_pct),to_char(manager_id),'No commission and no manager') from employees;
4、创建一张报表，要求检索EMPLOYEES表中DEPARTMENT_ID为100的行集合。这个集合包含
FIRST_NAME和LAST_NAME,以及别名为NAME_LENGTHS的表达式。如果FIRST_NAME和
LAST_NAME长度不相等，该表达式返回字符串‘Different Length’，否则返回字符串为‘Same
Length’
select last_name,first_name,nvl2(nullif(length(last_name),length(first_name)),'Different Length','Same Length') name_lengths from employeeswhere department_id=100;
条件表达式
在 SQL语句中使用 IF-THEN-ELSE 逻辑。
使用两种方法：
– CASE 表达式
– DECODE 函数
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
使用case表达式，实现IF-THEN-ELSE 的示例：
1、把job_id 为it_prog、st_clerk、sa_rep等职位的员工分别按照1.1/1.15/1.2的比例涨工资，除了这3个部门以外的职位都按照正常的显示。
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;
LAST_NAMEJOB_ID SALARY revised_salary
------------------------- ---------- ---------- --------------
OConnellSH_CLERK 2600 2600
GrantSH_CLERK 2600 2600
WhalenAD_ASST 4400 4400
HartsteinMK_MAN1300013000
FayMK_REP 6000 6000
MavrisHR_REP 6500 6500
BaerPR_REP1000010000
Higgins AC_MGR1200812008
GietzAC_ACCOUNT 8300 8300
KingAD_PRES2400024000
Kochhar AD_VP 1700017000
De Haan AD_VP 1700017000
HunoldIT_PROG 9000 9000
ErnstIT_PROG 6000 6000
AustinIT_PROG 4800 4800
PataballaIT_PROG 4800 4800
Lorentz IT_PROG 4200 4200
GreenbergFI_MGR1200812008
FavietFI_ACCOUNT 9000 9000
ChenFI_ACCOUNT 8200 8200
Sciarra FI_ACCOUNT 7700 7700
UrmanFI_ACCOUNT 7800 7800
PoppFI_ACCOUNT 6900 6900
RaphaelyPU_MAN1100011000
KhooPU_CLERK 3100 3100
BaidaPU_CLERK 2900 2900
TobiasPU_CLERK 2800 2800
HimuroPU_CLERK 2600 2600
ColmenaresPU_CLERK 2500 2500
WeissST_MAN 8000 8000
FrippST_MAN 8200 8200
KauflingST_MAN 7900 7900
Vollman ST_MAN 6500 6500
Mourgos ST_MAN 5800 5800
NayerST_CLERK 3200 3200
MikkilineniST_CLERK 2700 2700
LandryST_CLERK 2400 2400
MarkleST_CLERK 2200 2200
BissotST_CLERK 3300 3300
AtkinsonST_CLERK 2800 2800
MarlowST_CLERK 2500 2500
OlsonST_CLERK 2100 2100
MallinST_CLERK 3300 3300
RogersST_CLERK 2900 2900
GeeST_CLERK 2400 2400
PhiltankerST_CLERK 2200 2200
LadwigST_CLERK 3600 3600
StilesST_CLERK 3200 3200
SeoST_CLERK 2700 2700
PatelST_CLERK 2500 2500
RajsST_CLERK 3500 3500
DaviesST_CLERK 3100 3100
MatosST_CLERK 2600 2600
VargasST_CLERK 2500 2500
Russell SA_MAN1400014000
PartnersSA_MAN1350013500
ErrazurizSA_MAN1200012000
CambraultSA_MAN1100011000
Zlotkey SA_MAN1050010500
TuckerSA_REP1000010000
BernsteinSA_REP 9500 9500
HallSA_REP 9000 9000
OlsenSA_REP 8000 8000
CambraultSA_REP 7500 7500
Tuvault SA_REP 7000 7000
KingSA_REP1000010000
SullySA_REP 9500 9500
McEwenSA_REP 9000 9000
SmithSA_REP 8000 8000
DoranSA_REP 7500 7500
SewallSA_REP 7000 7000
Vishney SA_REP1050010500
GreeneSA_REP 9500 9500
Marvins SA_REP 7200 7200
LeeSA_REP 6800 6800
AndeSA_REP 6400 6400
BandaSA_REP 6200 6200
OzerSA_REP1150011500
BloomSA_REP1000010000
FoxSA_REP 9600 9600
SmithSA_REP 7400 7400
BatesSA_REP 7300 7300
KumarSA_REP 6100 6100
AbelSA_REP1100011000
HuttonSA_REP 8800 8800
TaylorSA_REP 8600 8600
LivingstonSA_REP 8400 8400
GrantSA_REP 7000 7000
Johnson SA_REP 6200 6200
TaylorSH_CLERK 3200 3200
FleaurSH_CLERK 3100 3100
SullivanSH_CLERK 2500 2500
GeoniSH_CLERK 2800 2800
SarchandSH_CLERK 4200 4200
BullSH_CLERK 4100 4100
DellingerSH_CLERK 3400 3400
CabrioSH_CLERK 3000 3000
ChungSH_CLERK 3800 3800
DillySH_CLERK 3600 3600
GatesSH_CLERK 2900 2900
Perkins SH_CLERK 2500 2500
BellSH_CLERK 4000 4000
Everett SH_CLERK 3900 3900
McCainSH_CLERK 3200 3200
JonesSH_CLERK 2800 2800
WalshSH_CLERK 3100 3100
FeeneySH_CLERK 3000 3000
107 rows selected.
DECODE函数
在需要使用 IF-THEN-ELSE 逻辑时：
DECODE(col|expression, search1, result1
[, search2, result2,...,]
[, default])
同上面的例子一样还是it_prog、st_clerk、sa_rep 三个职位的员工相应的涨钱，其它职位按照默认计算
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;
LAST_NAMEJOB_ID SALARY REVISED_SALARY
------------------------- ---------- ---------- --------------
OConnell SH_CLERK 2600 2600
Grant SH_CLERK 2600 2600
Whalen AD_ASST 4400 4400
Hartstein MK_MAN 13000 13000
Fay MK_REP 6000 6000
Mavris HR_REP 6500 6500
Baer PR_REP 10000 10000
Higgins AC_MGR 12008 12008
Gietz AC_ACCOUNT 8300 8300
KingAD_PRES2400024000
Kochhar AD_VP 1700017000
De Haan AD_VP 1700017000
HunoldIT_PROG 9000 9000
ErnstIT_PROG 6000 6000
AustinIT_PROG 4800 4800
PataballaIT_PROG 4800 4800
Lorentz IT_PROG 4200 4200
GreenbergFI_MGR1200812008
FavietFI_ACCOUNT 9000 9000
ChenFI_ACCOUNT 8200 8200
Sciarra FI_ACCOUNT 7700 7700
UrmanFI_ACCOUNT 7800 7800
PoppFI_ACCOUNT 6900 6900
RaphaelyPU_MAN1100011000
KhooPU_CLERK 3100 3100
BaidaPU_CLERK 2900 2900
TobiasPU_CLERK 2800 2800
HimuroPU_CLERK 2600 2600
ColmenaresPU_CLERK 2500 2500
WeissST_MAN 8000 8000
FrippST_MAN 8200 8200
KauflingST_MAN 7900 7900
Vollman ST_MAN 6500 6500
Mourgos ST_MAN 5800 5800
NayerST_CLERK 3200 3200
MikkilineniST_CLERK 2700 2700
LandryST_CLERK 2400 2400
MarkleST_CLERK 2200 2200
BissotST_CLERK 3300 3300
AtkinsonST_CLERK 2800 2800
MarlowST_CLERK 2500 2500
OlsonST_CLERK 2100 2100
MallinST_CLERK 3300 3300
RogersST_CLERK 2900 2900
GeeST_CLERK 2400 2400
PhiltankerST_CLERK 2200 2200
LadwigST_CLERK 3600 3600
StilesST_CLERK 3200 3200
SeoST_CLERK 2700 2700
PatelST_CLERK 2500 2500
RajsST_CLERK 3500 3500
DaviesST_CLERK 3100 3100
MatosST_CLERK 2600 2600
VargasST_CLERK 2500 2500
Russell SA_MAN1400014000
PartnersSA_MAN1350013500
ErrazurizSA_MAN1200012000
CambraultSA_MAN1100011000
Zlotkey SA_MAN1050010500
TuckerSA_REP1000010000
BernsteinSA_REP 9500 9500
HallSA_REP 9000 9000
OlsenSA_REP 8000 8000
CambraultSA_REP 7500 7500
Tuvault SA_REP 7000 7000
KingSA_REP1000010000
SullySA_REP 9500 9500
McEwenSA_REP 9000 9000
SmithSA_REP 8000 8000
DoranSA_REP 7500 7500
SewallSA_REP 7000 7000
Vishney SA_REP1050010500
GreeneSA_REP 9500 9500
Marvins SA_REP 7200 7200
LeeSA_REP 6800 6800
AndeSA_REP 6400 6400
BandaSA_REP 6200 6200
OzerSA_REP1150011500
BloomSA_REP1000010000
FoxSA_REP 9600 9600
SmithSA_REP 7400 7400
BatesSA_REP 7300 7300
KumarSA_REP 6100 6100
AbelSA_REP1100011000
HuttonSA_REP 8800 8800
TaylorSA_REP 8600 8600
LivingstonSA_REP 8400 8400
GrantSA_REP 7000 7000
Johnson SA_REP 6200 6200
TaylorSH_CLERK 3200 3200
FleaurSH_CLERK 3100 3100
SullivanSH_CLERK 2500 2500
GeoniSH_CLERK 2800 2800
SarchandSH_CLERK 4200 4200
BullSH_CLERK 4100 4100
DellingerSH_CLERK 3400 3400
CabrioSH_CLERK 3000 3000
ChungSH_CLERK 3800 3800
DillySH_CLERK 3600 3600
GatesSH_CLERK 2900 2900
Perkins SH_CLERK 2500 2500
BellSH_CLERK 4000 4000
Everett SH_CLERK 3900 3900
McCainSH_CLERK 3200 3200
JonesSH_CLERK 2800 2800
WalshSH_CLERK 3100 3100
FeeneySH_CLERK 3000 3000
107 rows selected.
2、显示部门为80的每一位员工，适用的税率为：
表示如果截取之为0税率为0，1税率为0.092税率为0.2 后面以此类推
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;
LAST_NAME SALARY TAX_RATE
------------------------- ---------- ----------
Russell 14000 .45
Partners 13500 .44
Errazuriz 12000 .44
Cambrault 11000 .42
Zlotkey 10500 .42
Tucker 10000 .42
Bernstein9500 .4
Hall9000 .4
Olsen8000 .4
Cambrault7500 .3
Tuvault 7000 .3
King 10000 .42
Sully9500 .4
McEwen9000 .4
Smith8000 .4
Doran7500 .3
Sewall7000 .3
Vishney 10500 .42
Greene9500 .4
Marvins 7200 .3
Lee6800 .3
Ande6400 .3
Banda6200 .3
Ozer 11500 .42
Bloom 10000 .42
Fox9600 .4
Smith7400 .3
Bates7300 .3
Kumar6100 .3
Abel 11000 .42
Hutton8800 .4
Taylor8600 .4
Livingston8400 .4
Johnson 6200 .3
34 rows selected.
3、查询LOCALTIONS表中的行，这些行的COUNTRY_ID列的值为US，请使用别名为LOCALTION_INFO
的表达式来计算STATE_PROVINCE列值，并返回不同的信息。结果如下图：
STATE_PROVINCE返回值Washington字符串’Headquarters’Texas 字符串’Oil Wells’CaliforniaCITY值New JerseySTREET _ADDRESS列值 select decode(state_province,'Washington','Headquarters','Texas','Oil Wells','California',city,'New Jersey',
street_address) location_info,state_province,city,street_address,country_id from locations where country_id='US';
页:
[1]