7654MARTIN ￥1,250.00
7698BLAKE ￥2,850.00
7782CLARK ￥2,450.00
B 把这个字符￥1,250.00,转成数字.....
select to_number('￥1,250.00','L9,999.99') from dual ;
TO_NUMBER('￥1,250.00','L9,999.99')
-----------------------------------
1250
6 通用函数
A 给员工涨工资:总裁 1000 经理:800 其他涨500,
===============================================
分析：前后工资给列出来
if (job == 'PRESIDENT')
SAL+1000
else if (job == 'MANAGER')
SAL+800
else
SAL+500
================================================
使用的知识点：
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THENreturn_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
===============================================
转化：
CASE job when 'PRESIDENT' then sal+1000
when'MANAGER' then sal+800
elsesal + 500
END
=============================================
select ename, job, sal 涨前工资,
(
CASE job when 'PRESIDENT' then sal+1000
when'MANAGER' then sal+800
elsesal + 500
END
)
涨后工资
from emp
-----------------------------------------------------------------------------------------------------
结果：
ENAME JOB 涨前工资 涨后工资
------------------- ---------- ----------
SMITH CLERK 800 1300
ALLEN SALESMAN 1600 2100
WARD SALESMAN 1250 1750
JONES MANAGER 2975 3775
MARTIN SALESMAN 1250 1750
BLAKE MANAGER 2850 3650
CLARK MANAGER 2450 3250
SCOTT ANALYST 3000 3500
KING PRESIDENT 5000 6000
TURNER SALESMAN 1500 2000
ADAMS CLERK 1100 1600
JAMES CLERK 950 1450
FORD ANALYST 3000 3500
MILLER CLERK 1300 1800
已选择14行。
B 第二种方法
===================================================
语法：
DECODE(col|expression, search1, result1
[, search2, result2,...,]
[, default])
==================================================
转化：
decode(job, 'PRESIDENT', sal+10000, 'MANAGER', sal+800, sal+500)
===================================================
select ename, job, sal 涨前工资,
(
decode(job, 'PRESIDENT', sal+10000,'MANAGER', sal+800, sal+500)
)
涨后工资
from emp;