Oracle SQL Lesson (4)

隐式转换
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;