存储过程与触发器

4.5 PL/SQL存储过程存储过程是数据库对象，是数据服务器内的一段使用PL/SQL语言编写的程序单元，具有EXECUTE权限的用户可以显式地调用过程。

存储过程保存在数据库服务器上，在完成对数据库的重复操作时很有用，存储过程被显示地调用完成过程定义的计算任务，可以接受各种Oracle定义的参数，用户可以在SQL Developer或者任何可以执行SQL语句的接口处执行PL/SQL过程，一旦过程被创建则在数据字典中记录该数据库对象信息，其数据库对象类型为PROCEDURE。

存储过程具有许多优点。

•加快程序的执行速度。

存储过程在运行之前数据库已对其进行了语法和句法分析，并给出了优化执行方案，这种已经编译好的过程可极大地改善SQL语句的性能，所以存储过程能以极快的速度执行，提高系统的性能；•减少网络的数据流量。

这是一条使用存储过程的非常重要的原因。

存储过程使一个需要许多行SQL代码的操作由一条执行存储过程代码的单独指令就可实现，极大地减轻了网络的负担，提高了系统的响应速度；•提供了一种安全机制。

通过向用户授予对存储过程（而不是基于表）的访问权限，它们可以提供对特定数据的访问；•允许程序模块化设计。

模块化的封装方法使存储过程可独立与程序源代码而单独修改，提高了程序的可用性。

•提高编程的灵活性。

存储过程可以用流控制语句编写，具有很强的灵活性，可以完成复杂的判断和计算。

4.5.1存储过程定义一个存储过程由三部分组成，即声明区、子程序区和异常处理区，如图4-21所示。

图4-21 存储过程的组成图其中，可选关键字OR REPLACE表示如果存储过程已经存在，则用新的存储过程覆盖，通常用于存储过程的重建。

参数部分用于定义多个参数（如果没有参数，就可以省略），参数有三种形式：IN、OUT和IN OUT，如果没有指明参数的形式，则默认为IN。

声明区位于PROCEDURE和BEGIN之间，在声明区用来定义变量，例如：CREATE OR REPLACE PROCEDURE PROTESTISxxx NUMBER;yyy VARCHAR2(20):=’oracle’;--声明REF游标TYPE empcursor ISREFCURSOR;--异常对象read_disk_refused EXCEPTION;--内嵌函数或其他存储过程FUNCTION foo RETURNBooleanIS ……子程序区，即BEGIN…END之间的部分，这部分包括PL/SQL代码逻辑，其中的代码逻辑对于声明区是可见的，这部分是存储过程执行其功能的主体部分，并且在过程的定义中，子程序部分是不可缺少的，要求在BEGIN…END之间至少有一条PL/SQL语句，可以是NULL，例如：CREATE OR REPLACE PROCEDURE PROTESTISBEGIN--此处是PL/SQL代码逻辑logical statement;END;异常处理区处理在子程序执行中发生的异常，例如：……IF age>150THEN RAISE dateexp;EXCEPTIONWHEN dateexp THENhandling dateexp;WHEN OTHERS THENhandling other exps;END;创建存储过程可以使用SQL*Plus工具，也可以使用SQL Developer工具，也可以在Windows的记事本中编辑，当使用记事本编辑存储过程时，需要将它保存为一个.SQL脚本文件，最后使用SQL*Plus或SQL Developer执行该脚本文件。

需要注意的是，用户应当具有创建存储过程的权限。

【例4-16】创建一个存储过程，向STUDENT表中插入记录。

代码如下所示。

CREATE OR REPLACE PROCEDUREInsert_Student(id in char,name varchar2, gender char) /* 创建一个存储过程，其功能为在STUDENT表中插入记录 */ISstudentidchar(12):=id;studentnamevarchar2(20):=name;studentgenderchar(2):=gender;BEGIN/* 根据输入参数在STUDENT表中插入记录 */INSERTINTOSTUDENT(studentid,studentname,studentgender)VALUES(studentid,studentname,studentgender);WHENOTHERSTHEN/* 如果插入语句执行有错则输入error字样 */dbms_output.put_line('errors!');END;在SQL Developer数据库开发管理工具中，通过执行上述语句，其运行结果界面如图4-22所示。

图4-22创建存储过程Insert_Student4.5.1存储过程管理在数据库中创建存储过程后，可根据需要对存储过程对象进行查看、编译、删除等管理操作。

所有这些操作均可通过执行SQL语句方式实现，也可通过SQL Developer工具的GUI界面操作方式实现。

1．存储过程查看在Oracle数据库中，存储过程对象的定义信息是存放在系统数据字典的系统表中。

开发人员可以通过对系统表USER_OBJECTS进行查询访问，可获取所需存储过程的基本信息，通过对系统表USER_SOURCE进行查询访问，可获得所需存储过程的代码信息。

【例4-17】用户希望从系统表USER_OBJECTS获取Insert_Student 存储过程基本信息，并从系统表USER_SOURCE获取Insert_Student 存储过程代码信息。

首先，需要了解系统表USER_OBJECTS的结构，然后使用SELECT语句获取Insert_Student 存储过程信息。

在Oracle数据库中，通过执行DESCRIBE命令可以获取系统表USER_OBJECTS的结构信息，其执行结果见图4-23所示。

图4-23USER_OBJECTS表结构假定用户需要从USER_OBJECTS表中获取Insert_Student 存储过程的名字（OBJECT_NAME）、对象类型（OBJECT_TYPE）、存储过程创建时间（CREATED）、存储过程状态（STATUS）信息，其SELECT语句如下所示。

SELECT Object_Name, Object_Type, Created, StatusFROM USER_OBJECTSWHERE Object_Type = 'PROCEDURE' AND Object_Name = 'DELETE_STUDENT';查询结果见图4-24所示。

图4-24 Insert_Student存储过程基本信息从查询结果可以看到，Insert_Student 存储过程是2016年05月09日创建的，目前状态可用，即可以正常执行。

除了在USER_OBJECTS表中可以查看所创建的存储过程信息，还可以在USER_PROCEDURES表中查看，过程同上所述。

在Oracle数据库中，通过执行DESCRIBE命令可以获取系统表USER_SOURCE的结构信息，其执行结果见图4-25所示。

图4-25USER_SOURCE表结构假定用户需要从USER_SOURCE表中获取Insert_Student 存储过程的源代码信息，其SELECT语句如下所示。

SELECT * FROM USER_SOURCE WHERE NAME = 'INSERT_STUDENT' AND TYPE= 'PROCEDURE';查询结果如图4-26所示。

图4-26 Insert_Student存储过程源代码信息【例4-18】用户希望通过SQL Developer工具的GUI界面获取Insert_Student 存储过程源代码信息。

在SQL Developer工具中，进入TEACHING_DB方案的过程目录，选取INSERT_STUDENT过程，并点取右键菜单“编辑”，打开编辑过程界面，如图4-27所示。

图4-27 Insert_Student存储过程源代码编辑界面在该编辑界面显示了INSERT_STUDENT存储过程的全部源代码，可以在此界面修改INSERT_STUDENT存储过程的源代码并保存编译。

注意右边列表中INSERT_STUDENT存储过程名字旁边显示的是绿色圆点，表示该存储过程目前是有效可用的，如果存储过程编译有错，则过程名字旁边显示的将是红叉圆点，表示该存储过程有错不可用。

2．存储过程编译存储过程创建好后需要编译存储过程，不过Oracle常常在创建存储过程的同时就已经编译，如果存储过程编译有错，则在创建时会提示有编译错误，在SQL*Plus中可以用show error命令可以查看错误信息，使用ALTER PROCEDURE命令可以重新编译存储过程。

【例4-19】使用ALTER PROCEDURE命令重新编译Insert_Student存储过程，如图4-28所示。

图4-28 使用ALTER PROCEDURE命令编译存储过程【例4-20】通过SQL Developer工具的GUI界面重新修改编译Insert_Student存储过程。

按照例4-18的方法在SQL Developer工具中打开Insert_Student存储过程的编辑界面，假设不小心把块头的PROCEDURE关键字写为PROCEDURES，保存存储过程时，数据库会自动编译，并发现编译错误，如图4-29所示。

图4-29 使用GUI界面编译存储过程从图4-29可以看到，存储过程编译出错，则在右下方的窗口会提示错误信息，并且右边列表中INSERT_STUDENT存储过程名字旁边显示的是红叉圆点，表示该存储过程有错不可用。

把INSERT_STUDENT存储过程块头的PROCEDURES写回正确的PROCEDURE，重新保存，存储过程则编译成功。

3．存储过程删除当一个存储过程在数据库中不再被使用后，可以删除该存储过程，其SQL语句格式如下。

DROP PROCEDURE<存储过程名>;【例4-21】在数据库中，删除所创建的Insert_Student存储过程，其存储过程删除的SQL 语句如下：DROPPROCEDUREInsert_Student;当该SQL语句成功执行后，Insert_Student存储过程从数据库中被删除，如图4-30所示。

图4-30 删除Insert_Student存储过程删除后查看数据字典USER_OBJECTS，发现Insert_Student存储过程已被删除，如图4-31所示。