Oracle 转换函数-- TO_CHAR(date|number {,fmt} {,nlsparams}) fmt:格式内容，返回的字符串是什么格式的，在此处指定；nlsparams：指定国家语言设置参数，设置一些数值中的字符，不同国家不同，默认按当前会话的设置 
-- 将日期转换成字符串-- fm:压缩因转换后字符串中多填充的空格或者数字前面填充的0select employee_id,to_char(hire_date,'fmDD MONTH YYYY') from employees;select employee_id,to_char(hire_date,'DD-MM-YYYY') from employees;-- 特殊字符必须将特殊字符使用英文的双引号，引起来select employee_id,to_char(hire_date,'YYYY"年"MM"月"DD"日"') from employees;
-- 将数字转换成字符串select employee_id,salary,to_char(salary,'$999,999.00'),to_char(salary,'L999,999.00') from employees;
-- TO_DATE(char {,fmt} {,nlsparams}) 将字符串转换成日期类型-- fx：精确匹配，要转换的字符串要严格的按照右边fmt的格式，否则就会报错select last_name,hire_datefrom employeeswhere hire_date>to_date('2007-09-10','fxYYYY-MM-DD');-- TO_NUMBER(char {,fmt} {,nlsparams})将字符串转换成数值类型select to_number('12,345.05','999,999.00') from dual;-- 函数嵌套-- oracle 可以达到任意深度的函数嵌套，嵌套的计算是从内到外select last_name,upper(concat(substr(last_name,1,3),'_ZH'))from employees;-- 通用函数 -- NVL(column|expression1,expression2) 将空值转换成目标值 expression2：如果expression1为空则返回expression2-- 注：expression1和expression2的类型一定要一致select last_name,salary,COMMISSION_PCT,(salary*12)+(salary*12*NVL(COMMISSION_PCT,0)) as "All_Salary"from employees;-- NVL2(expr1,expr2,expr3) 将空格转换为已指定的值 ，如果expr1不为空则返回 expr2,如果expr1为空则返回expr3-- 注：expr1可以是任意类型,expr2和expr3可以是除LONG以外的任意类型select last_name,salary,commission_pct,NVL2(COMMISSION_PCT,'SAL+COMM','SAL') as "InCome"from employeeswhere DEPARTMENT_ID in (50,80);-- NULLIF(expr1,expr2) 判断expr1和expr2是否相等，如果相等则返回NULL，如果不等则返回expr1-- 注：expr1和expr2不能直接传入NULLselect LENGTH(first_name) as "expr1",LENGTH(last_name) as "expr2",NULLIF(LENGTH(first_name),LENGTH(last_name)) as "result"from employees;-- COALESCE(expr1,expr2,expr3,...exprn) 返回表达式中第一个不为空的表达式，如果expr1为空则判断expr2是否为空，如果不为空则返回expr2，否则较验expr3...-- 如果所有表达式都为空，则返回空；所有表达式都要是统一的数据类型select last_name,COALESCE(to_char(COMMISSION_PCT),to_char(MANAGER_ID),'No commission and no manager')from employees;-- CASE expr WHEN expr1 THEN return1-- [WHEN expr2 THEN return2-- ...-- WHEN exprn THEN returnn]-- ELSE else_expr ENDselect 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 AS "New Salary"from employees;select last_name,salary,(case when salary<5000 then 'Low' when salary<10000 then 'Medinum' when salary<20000 then 'good' ELSE 'Excellent' END) as "Salary Level"from employees;-- DECODE(expr,expr1,return1-- expr2,return2-- expr3,return3-- ...-- 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) AS "New Salary"from employees;