Oracle:PL/SQL程序开发

1.环境准备

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

字段名	数据类型	长度	约束	说明
department_id	number	4	primary key	部门编号
department_name	varchar2	30	not null	部门名称
manager_id	number	6		部门管理者编号

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

create table departments(
       department_id number(4) primary key,
       department_name varchar2(30) not null,
       manager_id number(6) 
);

employees表结构如下所示：

字段名	数据类型	长度	约束	说明
employee_id	number	6	primary key	员工编号
employee_name	varchar2	20		员工名
email	varchar2	25	not null	邮箱地址
phone_number	varchar2	20	not null	电话号码
hire_date	Date		not null	入职日期
salary	number	8,2	大于0	工资
commission_pct	number	2,2		奖金（工资百分比）
manager_id	number	6		所属领导编号
department_id	number	4	foreign key	所属部门编号

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

create table employees(
       employee_id number(6) primary key,
       employee_name varchar2(20),
       email varchar2(25) not null,
       phone_number varchar2(20),
       hire_date Date not null,
       salary number(8,2) check(salary>0),
       commission_pct number(2,2),
       manager_id number(6),
       department_id number(4) references departments(department_id)
)

2.存储过程

存储子过程是指被命名的PL/SQL语句块，以编译的形式存储在数据库服务器中，可以在应用程序中进行调用，是PL/SQL程序模块化的一种体现。PL/SQL程序中的存储子程序包括存储过程与函数两种。**通常，存储过程用于执行特定的操作，不需要返回值；而函数则用于返回特定的数据。**在调用时，存储过程可以作为一个独立的表达式被调用，而函数只能作为表达式的一个组成部分被调用。

1.创建存储过程

创建存储过程的基本语法为：

create [or replace] procedure procudure_name(
	parameter1_name [mode] datatype [default|:=value],
	parameter2_name [mode] datatype [default|:=value],
	……
)
As|Is
Declarative section is here

Begin
Executable section is here

Exception
Exception section is here

End[procudure_name]
/* */

2.参数模式

存储过程参数模式mode包括IN,OUT,IN OUT三种。
（1）IN（默认参数模式）表示当存储过程被调用时，实参值被传递给形参；在存储过程内，形参其常量作用，只能读该参数，而不能修改该参数；当存储过程调用结束返回调用环境时，实参值没有被改变。IN模式参数可以是常量或表达式。
（2）OUT表示当存储过程被调用时，实参值被忽略；在存储过程中，形参起未初始化的PL/SQL变量的作用，初始值为NULL，可以进行读写操作；当存储过程调用结束后返回调用环境时，形参值被赋给实参。OUT模式参数只能是变量，不能使常量或表达式。
（3）IN OUT表示当存储过程被调用时，实参值被传递给形参；在存储过程内，形参起已初始化的PL/SQL变量的作用，可读可写；当存储过程调用结束返回调用环境时，形参被赋给实参。IN OUT模式参数只能是变量，不能使常量或表达式。

3.案例分析

1.创建名为proc_show_emp的存储过程，以部门编号为参数，查询并输出该部门的平均工资，以及该部门中比该部门平均工资高的员工信息。

create or replace procedure proc_show_emp(
       p_depno departments.department_id%type
)
AS
       v_sal employees.salary%type;
begin
       select avg(salary) into v_sal from employees
       where department_id=p_depno;
       dbms_output.put_line(p_depno||'average salary is :'||v_sal);
       for v_emp in (
         select * form employees
         where department_id=p_depno and salary>v_sal 
         ) loop
       dbms_output.put_line(v_emp.employee_id||''||v_emp.employee_name);
       end loop
exception
       when no_data_found then
         dbms_output.put_line('The department does not exists!');
end proc_show_emp;

注意： 存储过程不需要返回值，如果需要一个返回值，可以通过函数调用来实现；但是，如果希望返回多个值，则可以使用OUT或IN OUT模式参数来实现。
2.创建名为proc_return_depinfo的存储过程，以部门编号为参数返回该部门的人数与平均工资。

create or replace procedure proc_return_depinfo(
       p_depno departments.department_id%type,
       p_avgsal out employees.salary%type,
       p_count out number
)
AS
Begin
       select count(*),avg(salary) into p_count,p_avgsal from employees
       where department_id=p_depno;
Exception
       when no_data_found then
         dbms_output.put_line('The Department is not exist!');
end proc_return_depinfo;

3.调用存储过程

1.在SQL/PLUS中使用EXECUTE或CALL命令调用存储过程，例如：

execute proc_show_emp(10);

或者

call proc_show_emp(10);

2.在PL/SQL程序中调用存储过程
在PL/SQL程序中，存储过程可以作为一个独立的表达式被调用。

declare
  v_avgsal employees.salary%type;
  v_count number;
begin
  proc_show_emp(20);
  proc_return_depinfo(10,v_avgsal,v_count);
  dbms_output.put_line(v_avgsal||''||v_count);
end;	

3.函数

1.创建函数

函数的创建与存储过程的创建相似，不同之处在于，函数有一个显示的返回值；
创建函数的基本语法为：

create or repace function function_name(
	parameter1_name [mode] datatype [default|:=value],
	parameter2_name [mode] datatype [default|:=value],
	……)
	return return_type
As|Is
Declarative section is here

Begin
Executable section is here

Exception
Exception section is here

End[function_name]

创建函数需要注意下列事项：
（1）在函数定义的头部，参数列表之后，必须包含一个return语句来指明函数返回值的类型，但不能约束返回值的长度，精度，刻度等。如果使用%type，则可以隐含地包括长度精度，刻度等约束信息。
（2）在函数的定义中，必须至少包含一个return语句来指明函数的返回值。也可以有多个return语句，但最终只有一个return语句被执行。

2.案例分析

1.创建一个名为func_dept_maxsal的函数，以部门编号为参数，返回部门最高工资。

create or replace function func_dept_maxsal(
       p_depno departments.department_id%type)
       return employees.salary%type
AS
       v_maxsal employees.salary%type;
begin
       select max(salary) into v_maxsal from employees
       where department_id=p_depno;
       return v_maxsal;
exception
       when no_data_found then 
         dbms_output.put_line('The depno is not exist!');
end func_dept_maxsal;

注意：在创建函数时，函数参数的设置与存储过程参数的设置相同，可以使用IN，OUT，IN OUT模式参数，可以设置参数的默认值，不能设置参数的长度，精度，刻度等。由于一个函数有一个显式的返回值，因此，通常函数参数采用IN模式。如果需要函数返回多个值，也可以使用OUT或IN OUT模式参数。
2.创建一个名为func_dept_info的函数，以部门编号为参数，返回部门名，部门人数以及部门平均工资。

create or replace function func_dept_info(
       p_depno departments.department_id%type,
       p_count out number,
       p_avgsal out employees.salary%type)
       return departments.department_name%type
AS
       v_dname departments.department_name%type;
begin
       select department_name into v_dname from departments
       where department_id=p_depno;
       select count(*),avg(salary) into p_count,p_avgsal from employees
       where department_id=p_depno;
       return v_dname;
exception
       when no_data_found then 
         dbms_output.put_line('The depno is not exist!');
end func_dept_info;

4.包

PL/SQL程序包（Package）用于将相关的PL/SQL语句块或元素（过程，函数，变量，常量，自定义数据类型，游标等）组织在一起，成为一个完整的单元，编译后存储在数据库服务器中，作为一个全局结构，供应用程序调用。
包由包规范和包体两部分组成，在数据库中独立存储。

1.创建包

包的创建包括包规范的创建和包体的创建。

1.创建包规范

包规范提供与应用程序交互的接口，声明了包中所有可共享的元素，如过程，函数，游标，数据类型，异常和变量等，其中过程和函数值包括原型信息，不包括任何实现代码。在包规范中声明的元素不仅可以在包的内部使用，也可以被应用程序调用。
创建包规范的语法如下所示：

create or replace package package_name
AS|IS
	type_definition|variable_declaration|exception_declaration|
	cursor_declaration|procedure_declaration|function_declaration
end package_name;

注意：元素声明的顺序是任意的，但必须是先声明后使用；所有元素都是可选的；过程和函数的声明只包括原型，不包括具体实现。
例：创建一个名为pkg_emp包的包规范，包括2个变量，2个过程和1个异常。

create or replace package pkg_emp
AS
       minsal number;
       maxsal number;
       e_beyondbound exception;
       procedure update_sal(p_empno number,p_sal number);
       procedure add_employee(p_empno number,p_sal number);
end pkg_emp;

2.创建包体

包体中包含在包规范声明的过程和函数的实现代码。此外，包体中还可以包含在包规范中没有声明的变量，游标，类型，异常，过程和函数等，但它们是私有元素，只能由同一个包中的过程或函数使用。
创建包体的语法如下所示：

create or replace package body package_name
IS|AS
	type_definition|variable_declaration|exception_declaration|
	cursor_declaration|procedure_declaration|function_declaration
end package_name;

注意：包体中函数和过程的原型必须与包规范中的声明完全一致；只有在包规范已经创建的条件下，才可以创建包体；如果包规范中不包含任何函数或过程，则可以不创建包体。
例：创建pkg_emp包的包体

create or replace package body pkg_emp
AS
       procedure update_sal(p_empno number,p_sal number)
       AS
       Begin
         select min(salary),max(salary) into minsal,mxasal from employees;
         if p_sal between minsal and maxsal then
           update employees set salary=p_sal where employee_id=p_empno;
           if sql%notfound then 
             raise_application_error(-20000,'The employee does not exist!');
           end if;
         else
           raise e_beyondbound;
         end if;
       exception
          when e_beyondbound then
           dbms_output.put_line('The salary is beyond bound');
       end update_sal;
    
    procedure add_employee(p_empno number,p_sal numnber)
    AS
    Begin
      select min(salary),max(salary) into minsal,mxasal from employees;
      if p_sal between minsal and maxsal then
        insert into employees(employee_id,employee_name,email,phone_number,hire_date,salary)
        values(p_empno,'Nancy','123.com','12138',sysdate,p_sal);
      else
        raise e_beyondbound;
      end if;
    Exception
      when e_beyondbound then 
        dbms_output.put_line('The salary is beyond bound');
    end add_employee;
end pkg_emp;

该包体中的两个存储过程主要是实现的是修改员工工资和插入员工信息;
如果员工编号为p_empno的工资在employees表中的最大工资与最少工资之间，则修改员工的工资为p_sal；
如果员工编号为p_empno的工资在employees表中的最大工资与最少工资之间，则插入新员工的信息。

2.调用包

在包规范中声明的任何元素都是公共的，在包外都是可见的，可以通过package_name.element形式调用，在包体中可以直接通过元素名进行调用。但是，在包体中定义而没有在包规范中声明的元素则是私有的，只能在包体中调用。
例：调用包pkg_emp中的过程update_sal，修改140号员工工资为8000元。调用add_employee添加一个员工号位2011，工资为9000的员工。

begin
  pkg_emp.update_sal(140,8000);
  pkg_emp.add_employee(2011,9000);
end;

5.触发器

1.触发器概述

触发器是一种特殊类型的存储过程，编译后存储在数据库服务器中，当特定事件发生时，由系统自动调用执行，而不能由应用程序显式地调用执行。 此外，触发器不接收任何参数。触发器主要用于维护那些通过创建表时的声明约束不可能实现的复杂的完整性约束，并对数据库特定事件进行监控和响应。
根据触发器作用的对象不同，可以将触发器分为DML触发器，insert of触发器和系统触发器3类。
触发器由触发器头部和触发器体两个部分组成，触发器头部包括：
（1）作用对象：触发器作用的对象包括表，视图，数据库和模式；
（2）触发事件：激发触发器执行的事件；
（3）触发时间：用于指定触发器在触发事件完成之前还是之后执行。如果指定为after，则表示先执行出触发事件，然后再执行触发器；如果指定为before，则表示先执行触发器，然后在执行触发事件。
（4）触发级别：触发级别用于指定触发器响应触发事件的方式。默认为语句级触发器，即触发事件发生后，触发器只执行一次。如果指定为for each row,即为行级触发器，则触发事件每作用于一个记录，触发器就会执行一次。
（5）触发条件：由when子句指定一个逻辑表达式，当触发事件发生，而且when条件为true时，触发器才会执行。

2.创建DML触发器

创建DML触发器的语法为：

create or replace trigger trigger_name
before|after triggering_event [of column_name]
on table_name
[for each row]
[when trigger_conditoin]
Declare
Declarative section is here

Begin
Executable section is here

Exception
Exception section is here
End trigger_name;

1.创建语句级触发器

在默认情况下创建的DML触发器都是语句级触发器，即触发事件发生后，触发器只执行一次。在语句级触发器中不能对列值进行访问和操作，也不能获取当前行的信息。
例:创建名为trg_secure_emp的触发器，保证非工作时间禁止对employees表进行DML操作。(工作时间是周一到周五的08:00到18:00)

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 '18:00'
  or to_char(sysdate,'DY','NLS_DATE_LANGUAGE=AMERICAN') in ('sat','sun')
  then
    raise_application_error(-20005,'只能在正常的时间内进行改变')；
  end if;
end trg_secure_emp;

2.创建行级触发器

行级触发器是指执行DML操作时，每操作一个记录，触发器就执行一次，一个DML操作涉及多少记录，触发器就执行多少次。 在行级触发器中可以使用when条件，进一步控制触发器的执行。在触发器体中，可以对当前操作的记录进行访问和操作。
在行级触发器中引入:old和:new两个标识符，来访问和操作当前被处理记录中的数据。PL/SQL程序将:old和:new作为triggering_table%rowtype类型的两个变量。在不同触发事件中，:old和:new的含义不同。

触发事件	:old	:new
Insert	未定义，所有字段都为NULL	当语句完成后，被插入的记录
update	更新前原始记录	当语句完成时，更新后的记录
delete	记录被删除前的原始值	未定义，所有字段都为NULL

在触发器体内引用这两个标识符时，只能作为当个字段引用而不能作为整个记录引用，方法为:new.filed和:old.filed。
例如为employees表创建一个名为trg_emp_dml_row的触发器，当插入新员工时，显示新员工的员工号，员工名；当更新员工工资时，显示修改前后员工工资；当删除员工时，显示被删除的员工号，员工名。

create or replace trigger trg_emp_dml_row
before insert or update or delete on employees
for each row
begin
  if inserting then
    dbms_output.put_line(:new.employee_id||''||:new.employee_name);
  elsif updating then
    dbms_output.put_line(:old.salary||''||:new.salary);
  else
    dbms_output.put_line(:old.employee_id||''||:old.employee_name);
  end if;
end trg_emp_dml_row;