隐式转换

select * from emp where empno='7788'

字符(char,varchar2)转换为数字(number)或日期(date)

数字或日期转换为字符

显式转换

字符转换为数字或日期(TO_NUMBER, TO_DATE)

数字或日期转换为字符(TO_CHAR)

TO_CHAR(date, 'format_model')

select sysdate, to_char(sysdate,'yyyy') yyyy, to_char(sysdate,'year') year,

from dual;

select sysdate, to_char(sysdate,'mm') mm, to_char(sysdate,'mon') mon, to_char(sysdate, 'month') month

from dual;

select sysdate, to_char(sysdate,'dd') dd, to_char(sysdate,'dy') dy, to_char(sysdate, 'day') day

from dual;

select sysdate, to_char(sysdate, 'HH24:MI:SS AM') from dual;

select sysdate, to_char(sysdate, 'DD "of" MONTH') from dual;

select sysdate, to_char(sysdate, 'ddspth') from dual;

Has an fm element to remove padded blanks or suppress leading zeros

SELECT last_name, TO_CHAR(hire_date, 'fmDD Month YYYY') AS HIREDATE

FROM employees;

TO_CHAR(number, 'format_model')

select sal, to_char(sal, '9999') from emp;

select sal, to_char(sal, '09999') from emp;

select sal, to_char(sal, '09999$') from emp;

select sal, to_char(sal, '$9999') from emp;

select sal, to_char(sal, '09999L') from emp;(从windows连接到linux oracle server)

select sal, to_char(sal, '9999.99') from emp;

select sal, to_char(sal, '9,999,999') from emp;

TO_NUMBER(char[, 'format_model'])

TO_DATE(char[, 'format_model'])

SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY')

FROM employees

WHERE hire_date < TO_DATE('01-Jan-90','DD-Mon-RR');

嵌套函数

SELECT last_name, UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 8), '_US'))

FROM employees

WHERE department_id = 60;

SELECT TO_CHAR(ROUND((salary/7), 2),'99G999D99',

'NLS_NUMERIC_CHARACTERS = '',.'' ')

"Formatted Salary"

FROM employees;

NVL 函数

select commission_pct, nvl(commission_pct,0) from employees;

select hire_date, NVL(hire_date,'01-JAN-97') from employees;

select job_id, NVL(job_id,'No Job Yet') from employees;

SELECT last_name, salary, NVL(commission_pct, 0), (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL

FROM employees;

NVL2 函数

SELECT last_name, salary, commission_pct,

NVL2(commission_pct, 'SAL+COMM', 'SAL') income

FROM employees

WHERE department_id IN (50, 80);

NULLIF(exp1,exp2) 函数

select ename, nullif(ename,'KING') from emp;

相同则返回空

COALESCE (expr1, expr2, ..., exprn)

返回第一个不为空的表达式的值

SELECT last_name, employee_id,

COALESCE(TO_CHAR(commission_pct),TO_CHAR(manager_id),'No commission and no manager')

FROM employees;

CASE 表达式

plsql中才可以使用IF-THEN-ELSE

DECODE仅能用于Oracle数据库，只能实现等值判断

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;

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;

select ename, sal, case when sal<=800 then 'low'

case when sal < 2000 then 'mid'

else 'high'

end "salary grade"

from emp;