《第8章 存储过程和触发器.doc》由会员分享，可在线阅读，更多相关《第8章 存储过程和触发器.doc（24页珍藏版）》请在金锄头文库上搜索。
1、第8章 存储过程和触发器 学习目标： 了解存储过程和触发器的概念。 了解存储过程和触发器类型。 掌握存储过程和触发器定义方法。 了解存储过程和触发器在程序中的应用。存储过程(过程)和触发器都是PL/SQL语言中的概念，它们都是用户利用PL/SQL语言所提供的结构化编程语言的思想，自己编写、命名的程序块。使用存储过程可以实现过程的抽象和数据的隐藏。用户只需要知道过程的名称、参数、返回值等外部特征，就可以使用参数调用过程，最后利用返回数据即可。触发器是当特殊事件发生时自动执行的代码块，是特殊的存储过程，用户不能直接调用触发器。使用触发器可以大大增强Oracle的功能。本章将分别讨论存储过程和触发器
2、的概念以及其创建与使用方法。8.1 存储过程在Oracle中，可以在数据库中定义的子程序，这种程序块称为存储过程(Procedure)。它的定义存储在在数据字典中，可以在不同用户和应用程序之间共享，并可实现程序的优化和重用。存储过程的主要作用有：(1)过程在服务器端运行，执行速度快。(2)过程执行一次后代码就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译代码执行，提高了系统性能。(3)确保数据库的安全。可以不授权用户直接访问应用程序中的一些表，而是授权用户访问这些表的存储过程。非表的授权用户除非通过存储过程，否则不能访问这些表。(4)自动完成需要预先执行的任务。存储过程
3、可以在系统启动时自动执行，而不必在系统启动后再进行手工操作，大大方便了用户的使用，可以自动完成一些需要预先执行的任务。8.2 创建存储过程用户存储过程只能定义在当前数据库中，可以通过CREATE PROCEDURE命令或OEM创建存储过程，创建存储过程用户要求具有相应的权限。在过程的定义中不能使用下列对象创建语句： CREATE VIEW CREATE DEFAULT CREATE RULE CREATE PROCEDURE CREATE TRIGGER8.2.1 创建和执行存储过程1. 创建存储过程创建存储过程的基本语法如下：CREATE OR REPLACE PROCEDURE schem
4、a.procedure_name /*定义过程名*/(parameter parameter_mode data_type,n)/*定义参数类型及属性*/IS | ASBEGIN sql_statementEND procedure_name;其中： procedure_name：要创建的过程名，要求必须符合标识符规则。关键字OR REPLACE表示在创建过程时，如果已存在同名的过程，则重新创建。 schema：指定过程所属的用户方案。 parameter：过程的参数。参数名必须符合标识符规则，创建过程时，可以声明一个或多个参数，执行过程时应提供相对应的参数。 parameter_mode：表
5、示参数的类型。过程参数和函数参数一样，也有3种类型：分别为IN、OUT和IN OUT，当指定参数数据类型时，不能指定其长度。 sql_statement：表示过程体包含的PL/SQL语句。【例8.1】创建存储过程“print_current_time”，用于打印当前系统的时间。代码如下：CREATE PROCEDURE print_current_time AS curtime VARCHAR2(20);BEGINSELECT TO_CHAR(SYSDATE, yyyy/mm/dd hh24:mi:ss) INTO curtime FROM DUAL;DBMS_OUTPUT.PUT_LINE(
6、当前时间是：|curtime);END print_current_time;2. 执行存储过程创建过程之后即可执行过程。用户既可以从匿名PL/SQL程序块中直接调用，也可以在SQL*Plus中使用EXECUTE()函数来执行。基本语法如下：EXECUTE procedure_name(parameter, n);其中： procedure_name：表示要执行的存储过程的名称。 parameter：表示参数值。【例8.2】使用EXEC函数执行存储过程“print_current_time”，如图8.1所示。注意运行：SET SERVEROUTPUT ON图8.1使用EXEC执行过程“prin
7、t_current_time”【例8.3】使用匿名PL/SQL程序块执行过程“print_current_time”，如图8.2所示。图8.2使用匿名PL/SQL程序块执行“print_current_time”过程8.2.2 使用OEM创建存储过程【例8.4】使用OEM创建存储过程“out_score_sql”，要求该存储过程输出“SQL Server 2005”课程的平均分数。步骤如下：(1)登录OEM，打开“数据库”主页的“主目录”属性页。(2)单击“管理”超链接，打开“管理”属性页。(3)单击“方案”标题下的“过程”超链接，打开“过程”页，如图8.3所示。图8.3“过程”页(4)单击“
8、创建”按钮，进入“创建过程”页面。在“对象名”文本框输入要创建的过程的名字“out_score_sql”；在“方案”文本框中输入“SYSTEM”。然后在“源”代码区域，编辑PL/SQL过程语句块，如图8.4所示。代码如下：ASout_score NUMBERBEGINSELECT AVG(成绩) INTO out_score FROM 成绩表WHERE 课程号=(SELECT 课程号 FROM 课程表 WHERE 课程名= SQL Server 2005);DBMS_OUTPUT.PUT_LINE(SQL Server 2005平均成绩：|out_score);END;图8.4创建“out_s
9、core_sql”过程页(5)代码输入编辑完成后，单击“确定”按钮，过程创建完成。8.3 创建带参数的存储过程Oracle中还可以创建带参数的存储过程，扩展存储过程的功能。存储过程的参数有3种类型：分别为IN、OUT和IN OUT类型。 IN：表示参数是输入给过程的。 OUT：表示参数在过程中将被赋值，可以传给过程体的外部。 IN OUT：表示该类型的参数既可以向过程体传值，也可以在过程体中赋值。【例8.5】在“Student”数据库中，创建存储过程“kc_avg”，并通过一个输出参数，用于返回某课程的平均分数。执行情况如图8.5所示。CREATE OR REPLACE PROCEDURE k
10、c_avg(in_kc IN 课程表.课程名%type,out_score OUT NUMBER)ASBEGINSELECT AVG(成绩) INTO out_scoreFROM 成绩表WHERE 课程号=(SELECT 课程号 FROM 课程表 WHERE 课程名=in_kc);END kc_avg;图8.5创建存储过程“kc_avg”【例8.6】执行存储过程“kc_avg”，输出“SQL Server 2005”课程的平均分数。代码如下：SET SERVEROUTPUT ONDECLAREout_score NUMBER;BEGINkc_avg(SQL Server 2005,out_sc
11、ore); DBMS_OUTPUT.PUT_LINE(课程平均成绩为：|out_score);END;【例8.7】创建存储过程“c_num”，用来统计“学生表”中不同性别的人数。代码如下：CREATE OR REPLACE PROCEDURE c_num(in_sex IN CHAR,out_num OUT NUMBER)ASBEGINIF in_sex =男 THENSELECT COUNT(性别) INTO out_numFROM 学生表WHERE 性别=男;ELSE SELECT COUNT(性别) INTO out_numFROM 学生表WHERE 性别=女;END IF;DBMS_O
12、UTPUT.PUT_LINE(in_sex|生人数为: |out_num);END c_num;执行该过程，运行结果如图8.6所示。图8.6执行过程“c_num”8.4 管理存储过程8.4.1 查看存储过程信息通过数据字典“USER_PROCEDURES”可以查看存储过程的相关信息。通过数据字典“USER_SOURCE”，可以查询当前用户拥有的子程序名称及源代码信息。【例8.8】查看数据字典“USER_PROCEDURES”的结构，如图8.7所示。图8.7“USER_PROCEDURES”的结构【例8.9】通过数据字典“USER_PROCEDURES”查看当前用户拥有的过程及其属性信息，如图8
13、.8所示。图8.8查看当前用户的过程信息8.4.2 修改存储过程若要对已创建的存储过程进行修改，可以使用CREATE OR REPLACE PROCEDURE语句。【例8.10】修改过程“print_current_time”，使其显示的日期的格式为yy_mm_dd，时间格式为12小时制。代码如下：CREATE OR REPLACE PROCEDURE print_current_timeAScurtime VARCHAR2(20);BEGINSELECT TO_CHAR(SYSDATE, yy/mm/dd hh12:mi:ss) INTO curtime FROM DUAL;DBMS_OUT
14、PUT.PUT_LINE(当前时间是：|curtime);END print_current_time;修改后重新执行，运行结果如图8.9所示。图8.9执行过程“print_current_time”8.4.3 删除存储过程删除存储过程的基本语法如下：DROP PROCEDURE schema. PROCEDURE_name;【例8.11】删除过程“print_current_time”。代码如下：DROP PROCEDURE print_current_time;8.4.4 使用OEM管理存储过程 在OEM中查看、编辑、删除过程的步骤如下：(1)登录OEM，打开“数据库”主页的“主目录”属性
15、页。(2)单击“管理”超链接，打开“管理”属性页。(3)单击“方案”标题下的“过程”超链接，打开“过程”页。如图8.10所示。图8.10“过程”页(4)在“方案名”文本框输入方案名“SYSTEM”，在“对象名”文本框中输入过程名“print_current_time”，单击“开始”按钮，可以搜索出相应的存储过程。如图8.11所示。图8.11 “搜索过程”页(5)选中相应的过程，单击“编辑”、“查看”，“删除”按钮，可以分别对过程进行编辑，查看和删除的操作。8.5 触发器8.5.1 触发器概述触发器(Trigger)是特殊的存储过程，其功能是当某种触发事件发生时，触发器被隐式的执行。注意，用户不能直接调用触发器，触发器是特定事件出现的时候，自动执行的代码块。使用触发器可以完成的功能如下： 允许或限制对表的修改； 自动生成派生列； 强制数据一致性；
