3.某公司要根据雇员的职位来加薪,公司决定按下列加薪结构处理:
job_id 加薪
-----------------------
Clerk 500
Salesman 1000
Analyst 1500
Otherwise 2000
编写一个程序块,接受一个雇员名,从employees表中实现上述加薪处理.
DECLARE
v_ename hr.employees.last_name%type:='&v_ename';
v_job hr.employees.job_id%type;
v_addSal hr.employees.salary%type;--额外增加的工资
v_sal hr.employees.salary%type;
BEGIN
select job_id into v_job from hr.employees where last_name = v_ename;
select salary into v_sal from hr.employees where last_name = v_ename;
IF v_job = 'SH_CLERK' then
v_addSal := 500;
elsif v_job = 'SA_MAN' then
v_addSal := 1000;
elsif v_job = 'AD_SST' then
v_addSal := 1500;
else
v_addSal := 2000;
END IF;
v_addSal := v_addSal + v_sal;
UPDATE hr.employees SET salary = v_addSal where last_name = v_ename;
END;
运行结果为