WHEN v_emp.job_id='PU_MAN' OR v_emp.job_id='PU_CLERK' THEN v_increment:=500;
WHEN v_emp.job_id='ST_MAN' OR v_emp.job_id='ST_CLERK' OR v_emp.job_id='SH_CLERK' THEN v_increment:=400; WHEN v_emp.job_id='IT_PROG' OR v_emp.job_id='MK_MAN' OR v_emp.job_id='MK_REP' THEN v_increment:=300; ELSE v_increment:=200; end case;
update employees set salary=salary+v_increment where employee_id=v_emp.employee_id; end loop; end;
（6） declare
v_lowsal jobs.min_salary%type; v_highsal jobs.max_salary%type; e exception; begin
update employees set salary=8000 where employee_id=201; select min_salary,max_salary into v_lowsal,v_highsal
from jobs where job_id=(select job_id from employees where employee_id=201);
if 8000 not between v_lowsal and v_highsal then raise e; end if; exception when e then
raise_application_error(-20001,'beyond limit'); rollback; end;
第10章 PL/SQL程序设计
（1）创建一个存储过程，以员工号为参数，输出该员工的工资。 create or replace procedure pro_showsal( p_empno employees.employee_id%type) as
v_sal employees.salary%type; begin
select salary into v_sal from employees where employee_id=p_empno; dbms_output.put_line(v_sal); exception
when no_data_found then
dbms_output.put_line('there is not such an employees'); end; begin
pro_showsal(100); end;
（2）创建一个存储过程，以员工号为参数，修改该员工的工资。若该员工属于10号部门，则工资增加140元；若属于20号部门，则工资增加200元；若属于30号部门，则工资增加250元；若属于其他部门，则工资增长300元。 create or replace procedure pro_updatesal( p_empno employees.employee_id%type) as
v_deptno employees.department_id%type; v_inc number; begin
select department_id into v_deptno from employees where employee_id=p_empno; case v_deptno
when 10 then v_inc:=140;
when 20 then v_inc:=200; when 30 then v_inc:=250; else v_inc:=300; end case;
update employees set salary=salary+v_inc where employee_id=p_empno; exception
when no_data_found then
dbms_output.put_line('there is not such an employees');
end;
（3）创建一个函数，以员工号为参数，返回该员工的工资。 create or replace function func_retsal( p_empno employees.employee_id%type) return employees.salary%type as
v_sal employees.salary%type; begin
select salary into v_sal from employees where employee_id=p_empno; return v_sal; exception
when no_data_found then
dbms_output.put_line('there is not such an employees'); end; begin
dbms_output.put_line(func_retsal(100)); end;
（4） 创建一个函数，以员工号为参数，返回该员工所在部门的平均工资。 create or replace function func_retavgsal( p_empno employees.employee_id%type) return employees.salary%type as
v_deptno employees.department_id%type; v_avgsal employees.salary%type;
begin
select department_id into v_deptno from employees where employee_id=p_empno;
select avg(salary) into v_avgsal from employees where department_id=v_deptno; return v_avgsal; exception
when no_data_found then
dbms_output.put_line('there is not such an employees'); end;
（5）创建一个包，包中包含一个函数和一个过程。函数以部门号为参数，返回该部门员工的最高工资；过程以部门号为参数，输出该部门中工资最高的员工名、员工号。 create or replace package pkg_emp as
function func_ret_maxsal(p_deptno number) return number; procedure pro_showemp(p_deptno number); end;
create or replace package body pkg_emp as
function func_ret_maxsal(p_deptno number) return number as
v_maxsal number; begin
select max(salary) into v_maxsal from employees where department_id=p_deptno; return v_maxsal; end;
procedure pro_showemp(p_deptno number) as
cursor c_emp is select * from employees
