SQL 基础之转换函数和条件表达式（八）

liujjun

　　数据类型转换分为：

• 　　隐式数据类型转换
  
• 　　显示数据类型转换
  

　　隐式数据类型转换:
　　在表达式中，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的格式输出
　　select  salary,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 employees  where 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_NAME  JOB_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
　　King  AD_PRES  24000  24000
　　Kochhar   AD_VP   17000  17000
　　De Haan   AD_VP   17000  17000
　　Hunold  IT_PROG   9000   9000
　　Ernst  IT_PROG   6000   6000
　　Austin  IT_PROG   4800   4800
　　Pataballa  IT_PROG   4800   4800
　　Lorentz   IT_PROG   4200   4200
　　Greenberg  FI_MGR  12008  12008
　　Faviet  FI_ACCOUNT   9000   9000
　　Chen  FI_ACCOUNT   8200   8200
　　Sciarra   FI_ACCOUNT   7700   7700
　　Urman  FI_ACCOUNT   7800   7800
　　Popp  FI_ACCOUNT   6900   6900
　　Raphaely  PU_MAN  11000  11000
　　Khoo  PU_CLERK   3100   3100
　　Baida  PU_CLERK   2900   2900
　　Tobias  PU_CLERK   2800   2800
　　Himuro  PU_CLERK   2600   2600
　　Colmenares  PU_CLERK   2500   2500
　　Weiss  ST_MAN   8000   8000
　　Fripp  ST_MAN   8200   8200
　　Kaufling  ST_MAN   7900   7900
　　Vollman   ST_MAN   6500   6500
　　Mourgos   ST_MAN   5800   5800
　　Nayer  ST_CLERK   3200   3200
　　Mikkilineni  ST_CLERK   2700   2700
　　Landry  ST_CLERK   2400   2400
　　Markle  ST_CLERK   2200   2200
　　Bissot  ST_CLERK   3300   3300
　　Atkinson  ST_CLERK   2800   2800
　　Marlow  ST_CLERK   2500   2500
　　Olson  ST_CLERK   2100   2100
　　Mallin  ST_CLERK   3300   3300
　　Rogers  ST_CLERK   2900   2900
　　Gee  ST_CLERK   2400   2400
　　Philtanker  ST_CLERK   2200   2200
　　Ladwig  ST_CLERK   3600   3600
　　Stiles  ST_CLERK   3200   3200
　　Seo  ST_CLERK   2700   2700
　　Patel  ST_CLERK   2500   2500
　　Rajs  ST_CLERK   3500   3500
　　Davies  ST_CLERK   3100   3100
　　Matos  ST_CLERK   2600   2600
　　Vargas  ST_CLERK   2500   2500
　　Russell   SA_MAN  14000  14000
　　Partners  SA_MAN  13500  13500
　　Errazuriz  SA_MAN  12000  12000
　　Cambrault  SA_MAN  11000  11000
　　Zlotkey   SA_MAN  10500  10500
　　Tucker  SA_REP  10000  10000
　　Bernstein  SA_REP   9500   9500
　　Hall  SA_REP   9000   9000
　　Olsen  SA_REP   8000   8000
　　Cambrault  SA_REP   7500   7500
　　Tuvault   SA_REP   7000   7000
　　King  SA_REP  10000  10000
　　Sully  SA_REP   9500   9500
　　McEwen  SA_REP   9000   9000
　　Smith  SA_REP   8000   8000
　　Doran  SA_REP   7500   7500
　　Sewall  SA_REP   7000   7000
　　Vishney   SA_REP  10500  10500
　　Greene  SA_REP   9500   9500
　　Marvins   SA_REP   7200   7200
　　Lee  SA_REP   6800   6800
　　Ande  SA_REP   6400   6400
　　Banda  SA_REP   6200   6200
　　Ozer  SA_REP  11500  11500
　　Bloom  SA_REP  10000  10000
　　Fox  SA_REP   9600   9600
　　Smith  SA_REP   7400   7400
　　Bates  SA_REP   7300   7300
　　Kumar  SA_REP   6100   6100
　　Abel  SA_REP  11000  11000
　　Hutton  SA_REP   8800   8800
　　Taylor  SA_REP   8600   8600
　　Livingston  SA_REP   8400   8400
　　Grant  SA_REP   7000   7000
　　Johnson   SA_REP   6200   6200
　　Taylor  SH_CLERK   3200   3200
　　Fleaur  SH_CLERK   3100   3100
　　Sullivan  SH_CLERK   2500   2500
　　Geoni  SH_CLERK   2800   2800
　　Sarchand  SH_CLERK   4200   4200
　　Bull  SH_CLERK   4100   4100
　　Dellinger  SH_CLERK   3400   3400
　　Cabrio  SH_CLERK   3000   3000
　　Chung  SH_CLERK   3800   3800
　　Dilly  SH_CLERK   3600   3600
　　Gates  SH_CLERK   2900   2900
　　Perkins   SH_CLERK   2500   2500
　　Bell  SH_CLERK   4000   4000
　　Everett   SH_CLERK   3900   3900
　　McCain  SH_CLERK   3200   3200
　　Jones  SH_CLERK   2800   2800
　　Walsh  SH_CLERK   3100   3100
　　Feeney  SH_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_NAME  JOB_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
　　King  AD_PRES  24000  24000
　　Kochhar   AD_VP   17000  17000
　　De Haan   AD_VP   17000  17000
　　Hunold  IT_PROG   9000   9000
　　Ernst  IT_PROG   6000   6000
　　Austin  IT_PROG   4800   4800
　　Pataballa  IT_PROG   4800   4800
　　Lorentz   IT_PROG   4200   4200
　　Greenberg  FI_MGR  12008  12008
　　Faviet  FI_ACCOUNT   9000   9000
　　Chen  FI_ACCOUNT   8200   8200
　　Sciarra   FI_ACCOUNT   7700   7700
　　Urman  FI_ACCOUNT   7800   7800
　　Popp  FI_ACCOUNT   6900   6900
　　Raphaely  PU_MAN  11000  11000
　　Khoo  PU_CLERK   3100   3100
　　Baida  PU_CLERK   2900   2900
　　Tobias  PU_CLERK   2800   2800
　　Himuro  PU_CLERK   2600   2600
　　Colmenares  PU_CLERK   2500   2500
　　Weiss  ST_MAN   8000   8000
　　Fripp  ST_MAN   8200   8200
　　Kaufling  ST_MAN   7900   7900
　　Vollman   ST_MAN   6500   6500
　　Mourgos   ST_MAN   5800   5800
　　Nayer  ST_CLERK   3200   3200
　　Mikkilineni  ST_CLERK   2700   2700
　　Landry  ST_CLERK   2400   2400
　　Markle  ST_CLERK   2200   2200
　　Bissot  ST_CLERK   3300   3300
　　Atkinson  ST_CLERK   2800   2800
　　Marlow  ST_CLERK   2500   2500
　　Olson  ST_CLERK   2100   2100
　　Mallin  ST_CLERK   3300   3300
　　Rogers  ST_CLERK   2900   2900
　　Gee  ST_CLERK   2400   2400
　　Philtanker  ST_CLERK   2200   2200
　　Ladwig  ST_CLERK   3600   3600
　　Stiles  ST_CLERK   3200   3200
　　Seo  ST_CLERK   2700   2700
　　Patel  ST_CLERK   2500   2500
　　Rajs  ST_CLERK   3500   3500
　　Davies  ST_CLERK   3100   3100
　　Matos  ST_CLERK   2600   2600
　　Vargas  ST_CLERK   2500   2500
　　Russell   SA_MAN  14000  14000
　　Partners  SA_MAN  13500  13500
　　Errazuriz  SA_MAN  12000  12000
　　Cambrault  SA_MAN  11000  11000
　　Zlotkey   SA_MAN  10500  10500
　　Tucker  SA_REP  10000  10000
　　Bernstein  SA_REP   9500   9500
　　Hall  SA_REP   9000   9000
　　Olsen  SA_REP   8000   8000
　　Cambrault  SA_REP   7500   7500
　　Tuvault   SA_REP   7000   7000
　　King  SA_REP  10000  10000
　　Sully  SA_REP   9500   9500
　　McEwen  SA_REP   9000   9000
　　Smith  SA_REP   8000   8000
　　Doran  SA_REP   7500   7500
　　Sewall  SA_REP   7000   7000
　　Vishney   SA_REP  10500  10500
　　Greene  SA_REP   9500   9500
　　Marvins   SA_REP   7200   7200
　　Lee  SA_REP   6800   6800
　　Ande  SA_REP   6400   6400
　　Banda  SA_REP   6200   6200
　　Ozer  SA_REP  11500  11500
　　Bloom  SA_REP  10000  10000
　　Fox  SA_REP   9600   9600
　　Smith  SA_REP   7400   7400
　　Bates  SA_REP   7300   7300
　　Kumar  SA_REP   6100   6100
　　Abel  SA_REP  11000  11000
　　Hutton  SA_REP   8800   8800
　　Taylor  SA_REP   8600   8600
　　Livingston  SA_REP   8400   8400
　　Grant  SA_REP   7000   7000
　　Johnson   SA_REP   6200   6200
　　Taylor  SH_CLERK   3200   3200
　　Fleaur  SH_CLERK   3100   3100
　　Sullivan  SH_CLERK   2500   2500
　　Geoni  SH_CLERK   2800   2800
　　Sarchand  SH_CLERK   4200   4200
　　Bull  SH_CLERK   4100   4100
　　Dellinger  SH_CLERK   3400   3400
　　Cabrio  SH_CLERK   3000   3000
　　Chung  SH_CLERK   3800   3800
　　Dilly  SH_CLERK   3600   3600
　　Gates  SH_CLERK   2900   2900
　　Perkins   SH_CLERK   2500   2500
　　Bell  SH_CLERK   4000   4000
　　Everett   SH_CLERK   3900   3900
　　McCain  SH_CLERK   3200   3200
　　Jones  SH_CLERK   2800   2800
　　Walsh  SH_CLERK   3100   3100
　　Feeney  SH_CLERK   3000   3000
　　107 rows selected.
　　2、显示部门为80的每一位员工，适用的税率为：
　　表示如果截取之为0税率为0，1税率为0.09  2税率为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';