第13章plsql命名对象13道题

（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）创建一个存储过程，以部门号为参数，输出入职日期最早的10个员工信息。

create or replace procedure pro_showemp(
p_deptno employees.employee_id%type)
as
cursor c_emp is select * from (
select * from employees where department_id=p_deptno
order by hire_date) ds where rownum<=10;
begin
for v_emp in c_emp loop
dbms_output.put_line(v_emp.employee_id||' '||
v_emp.first_name||' '||v_emp.hire_date);
end loop;
end;
begin
pro_showemp(50);
end;
（4）创建一个函数，以员工号为参数，返回该员工的工资。

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;
（5）创建一个函数，以部门号为参数，返回该部门的平均工资；
create or replace function func_retdeptsal(
p_deptno employees.department_id%type)
return number
as
v_sal employees.salary%type;
begin
select nvl(avg(salary),0) into v_sal from employees
where department_id=p_deptno;
return v_sal;
end;
begin
dbms_output.put_line(func_retdeptsal(50));
end;
（6）创建一个函数，以员工号为参数，返回该员工所在部门的平均工资。

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;
dbms_output.put_line(func_retavgsal(10));
end;
（7）创建一个包，包中包含一个函数和一个过程。

函数以部门号为参数，返回该部门员工的最高工资；过程以部门号为参数，输出该部门中工资最高的员工名、员工
号。

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
where department_id=p_deptno and
salary=func_ret_maxsal(p_deptno);
begin
for v_emp in c_emp loop
dbms_output.put_line(v_emp.employee_id||' '||
v_emp.salary);
end loop;
end;
练习：
创建一个存储过程，以员工号和新的职位名称为参数，实现员工职位调动。

创建一个存储过程，以员工号和新的部门名称为参数，实现员工部门调动。

（8）创建一个包，包中包含一个过程和一个游标。

游标返回所有员工的信息；存储过程实现每次输出游标中的5条记录。

CREATE OR REPLACE PACKAGE pkg_showemp
AS
CURSOR c_emp IS SELECT * FROM employees;
PROCEDURE show_fiveemp;
end;
CREATE OR REPLACE PACKAGE BODY pkg_showemp
AS
PROCEDURE show_fiveemp
AS
v_emp c_emp%ROWTYPE;
BEGIN
IF not c_emp%ISOPEN THEN
OPEN c_emp;
END IF;
FOR i in 1..20 LOOP
FETCH c_emp INTO v_emp;
IF c_emp%NOTFOUND THEN
CLOSE c_emp;
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE(v_emp.employee_id||' '||
v_emp.first_name);
END LOOP;
END;
BEGIN
pkg_showemp.show_fiveemp;
END;
（9）在employees表上创建一个触发器，保证每天8：00～17：00之外的时间禁止对该表进行DML操作。

create or replace trigger trg_secure_emp
before insert or update or delete on employees
begin
if to_char(sysdate,'HH24:MI') not between '08:00' and '17:00' then
raise_application_error(-20005,'beyond');
end if;
end;
（10）在employees表上创建一个触发器，当插入、删除或修改员工信息时，统计各个部门的人数及平均工资，并输出。

create or replace trigger trg_10
after insert or update or delete
on employees
declare
cursor c_dept is
select department_id,avg(salary) avgsal,count(*) num
from employees group by department_id;
begin
for v in c_dept loop
dbms_output.put_line(v.department_id||' '||v.avgsal||' '||v.num);
end loop;
end;
（11）在employees表上创建一个触发器，保证修改员工工资时，修改后的工资低于该部门最高工资，同时高于该部门最低工资。

create or replace package pkg_11
as
v_deptno employees.department_id%type;
v_newsal employees.salary%type;
end;
create or replace trigger trg_before
before update of salary
on employees
for each row
begin
pkg_10.v_deptno:=:new.department_id;
pkg_10.v_newsal:=:new.salary;
end;
create or replace trigger trg_after
after update of salary
on employees
declare
v_maxsal employees.salary%type;
v_minsal employees.salary%type;
begin
select max(salary),min(salary) into v_maxsal,v_minsal
from employees where department_id=pkg_10.v_deptno;
dbms_output.put_Line(v_minsal||' '||v_maxsal||pkg_10.v_newsal);
if pkg_10.v_newsal<v_minsal or pkg_10.v_newsal>v_maxsal then
raise_application_error(-20001,'beyond');
dbms_output.put_Line('beyond');
end if;
end;
（12）创建一个存储过程，以一个整数为参数，输入工资最高的前几个（参数指定）员工的信息。

create or replace procedure pro_num(
p_num number
)
as
cursor c_num is select *from employees where rownum<=p_num order by salary desc ;
begin
for v_num in c_num loop
dbms_output.put_line(v_num.employee_id||' '||v_num.first_name||' '||v_num.salary);
end loop;
end;
begin
pro_num(4);
end;
（13）创建一个存储过程，以两个整数为参数，输出工资排序在两个参数之间的员工信息。

create or replace procedure pro_sal(
p_max number,
p_min number
)
as
cursor c_emp is select *from employees where salary>=p_min and salary<=p_max order by salary;
begin
for v_emp in c_emp loop
dbms_output.put_line(v_emp.employee_id||' '||v_emp.first_name||' '||v_emp.salary);
end loop;
end;
begin
pro_sal(9000,3000);
end;。