转换函数

to_char

to_number

to_date

隐式转换 fm 去掉前导0

显式转换 fx 完全精确匹配才会成功，否则报错

NVL(A,B) 若A为NULL，则输出B

select last_name,salary,NVL(commission_pct,0),(salary12)+(salary12*NVL(commission_pct,0)) AN_SAL from employees;

NVL2(A,B,C) 若A为NULL，则输出C，若A不为NULL，则输出B

select last_name,salary,commission_pct,NVL2(commission_pct,'SAL+COM','SAL') incomefrom employee where department_id IN (50,80);

NULLIF(A,B) 若A=B，则为NULL，若A<>B，则输出A

select first_name,LENGTH(first_name) "expr1",last_name,LENGTH(last_name) "expr2",NULLIF(LENGTH(first_name),LENGTH(last_name)) resultfrom employees;

COALESCE(A,B,C,.......Z) 从A开始往后判断是否为NULL，找到不为NULL时则输出值，单最后Z不能设定为NULL

select last_name,salary,commission_pct,COALESCE((salary+(commission_pct*salary)),salary+2000) "new Salary"

from employees;

条件表达式

CASEselect last_name,job_id,salary,case job_id when 'IT_PROG' THEN 1.10salary

when 'ST_CLERK' THEN 1.15salarywhen 'SA_REP' THEN 1.20*salaryelse salaryend "REVISED_SALARY"from employees;

DECODE

select last_name,job_id,salary,DECODE(job_id,'IT_PROG',1.10salary,

'ST_CLERK',1.15salary,'SA_REP' ,1.20*salary,salary)REVISED_SALARYfrom employees;