1.环境准备

下面所有关于PL/SQL的程序都是建立在employees表和departments表上，具体表结构如下所示：
departments表结构如下所示：

字段名	数据类型	长度	约束	说明
department_id	number	4	primary key	部门编号
dep_name	varchar2	30	not null	部门名称
manager_name	varchar	20		部门管理者姓名

创建departments表的sql语句如下所示

create table departments(
       department_id number(4) primary key,
       dep_name varchar2(30) not null,
       manager_name varchar2(20)
)

employees表结构如下所示：

字段名	数据类型	长度	约束	说明
emp_id	number	6	primary key	员工编号
emp_name	varchar2	20		员工名
sex	number	1		员工性别
birthday	Date			员工生日
salary	number	8,2	大于0	工资
department_id	number	4	foreign key	所属部门编号

对应employees的建表sql语句如下所示：

create table employees(
       emp_id number(6) primary key,
       emp_name varchar2(20),
       sex number(1),
       birthday Date,
       salary number(8,2) check(salary>0),
       department_id number(4) references departments(department_id)
)

2.例题解析

下面例题中主要包括存储过程，函数，包以及触发器的创建，具体如下所示
1、创建一个函数，以员工号为参数，返回该员工所在部门的平均工资。

create or replace function func_id_salary(
       empno employees.emp_id%type)
       return employees.salary%type
AS
       depno departments.department_id%type;
       avg_sal employees.salary%type;
Begin
       select department_id into depno from employees
       where emp_id=empno;
       select avg(salary) into avg_sal from employees
       where department_id=depno;
       return avg_sal;
Exception
       when no_data_found then 
         dbms_output.put_line('The empno is not exist');
End func_id_salary;
       

2、创建一个存储过程，以员工号为参数，修改该员工的工资。若该员工属于 10 号部门， 则工资增加 140 元；若属于 20 号部门，则工资增加 200 元；若属于 30 号部门，则工资增加250 元；若属于其他部门，则工资增长 300 元。

create or replace procedure proc_id_salary(
       empno employees.emp_id%type)
AS
       depno departments.department_id%type;
       increment number(4);
Begin
       select department_id into depno from employees;
       if depno=10 then increment:=140;
       elsif depno=20 then increment:=200;
       elsif depno=30 then increment:=250;
       else increment:=300;
       end if;
       update employees set salary=salary+increment
       where emp_id=empno;
Exception 
       when no_data_found then
         dbms_output.put_line('The empno is not exist');
End proc_id_salary;

3、创建一个包，包中包含一个函数和一个过程。函数以部门号为参数，返回该部门员工 的最高工资；过程以部门号为参数，输出该部门中工资最高的员工名、员工号。

#创建包规范
create or replace package pack_func_proc
AS
       function func_dept_maxsalary(depno departments.department_id%type) 
       return employees.salary%type;
       procedure proc_dept_info(depno departments.department_id%type);
End pack_func_proc;
       
#创建包体
create or replace package body pack_func_proc
AS
       function func_dept_maxsalary(depno departments.department_id%type)
       return employees.salary%type 
       As
           max_sal employees.salary%type;
       Begin
           select max(salary) into max_sal from employees
           where department_id=depno;
           return max_sal;
       Exception
           when no_data_found then 
             dbms_output.put_line('The depno is not exist');
       End func_dept_maxsalary;
       
       procedure proc_dept_info(depno departments.department_id%type)
       AS
         maxsalary employees.salary%type;
         empno employees.emp_id%type;
         empname employees.emp_name%type;
       Begin
         select max(salary) into maxsalary from employees
         where department_id=depno;
         select emp_id,emp_name into empno,empname from employees
         where salary=maxsalary;
         dbms_output.put_line(empno||''||empname);
       Exception
         when no_data_found then 
           dbms_output.put_line('The depno is not exist!');
       End proc_dept_info;
End pack_func_proc;

4、在 employees 表上创建一个触发器，保证每天 8：00～17：00 之外的时间禁止对该表进行 DML 操作。

create or replace trigger trig_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,'只能在正常的工作时间进行改变');
   end if;
end trig_secure_emp;

5、在 employees 表上创建一个触发器，保证修改员工工资时，修改后的工资低于部门最
高工资，同时高于部门的最低工资。

create or replace trigger update_salary
after update of salary on employees
for each row
declare
    maxsal employees.salary%type;
    minsal employees.salary%type;
begin
    select max(salary),min(salary) into maxsal,minsal from employees
    where department_id=:new.department_id;
    if :new.salary not between minsal and maxsal then
      raise_application_error(-20000,'The salary is not between'|| minsal||'And'|| maxsal);
    end if;
end update_salary;

注意：这里raise_application_error是用来抛出异常的，里面的错误码和错误内容用户可以自定义，不过错误码只能在-20000到-20999之间取值，防止与 ORACLE 的任何错误代码发生冲突。
6、在 employees 表上创建一个触发器，当插入新员工时显示新员工的员工号、员工名和部门名。当删除员工时显示被删除员工的员工号、员工名。

create or replace trigger trg_emp_dml_row
after insert or delete on employees
for each row
declare
depname departments.dep_name%type;
begin
  if inserting then
    select dep_name into  depname from departments
    where department_id=:new.department_id;
    dbms_output.put_line(:new.emp_id||''||:new.emp_name||''||depname);
  elsif deleting then
    dbms_output.put_line(:old.emp_id||''||:old.emp_name);
  end if;
end trg_emp_dml_row;