oracle11g第10章

1、2第10章 存储过程、函数、触发器和包4本章要点：掌握存储过程的创建。熟练掌握带参数的存储过程的使用。掌握存储过程的管理。掌握函数的创建与使用。了解触发器的类型。理解触发器的作用。熟练掌握各种类型的触发器。了解程序包的创建与使用。310.1 存 储 过 程4存储过程是一组为了完成特定功能的SQL语句集，它大大提高了SQL语句的功能和灵活性。4存储过程经编译后存储在数据库中，所以执行存储过程要比执行存储过程中封装的SQL语句更有效率。410.1.1 创建与调用存储过程4创建存储过程需要使用CREATE PROCEDURE语句，其语法如下：CREATE OR REPLACE PROCEDURE p

2、rocedure_name ( parameter IN | OUT | IN OUT data_type ) , . IS | AS declaration_section ; BEGIN procedure_body ;END procedure_name ;510.1.1 创建与调用存储过程4【例【例10.1】创建一个简单的存储过程update_emp，该过程用于将emp表中empno为6500的员工的姓名修改为CANDY，如下：SQL CREATE PROCEDURE update_emp AS 2 BEGIN 3 UPDATE emp SET ename = CANDY WHERE

3、empno = 6500 ; 4 END update_emp ;SQL /过程已创建。4【例【例10.2】使用EXECUTE语句与CALL语句调用存储过程update_emp，分别如下：SQL EXECUTE update_emp ;PL/SQL 过程已成功完成。610.1.2 带参数的存储过程41IN参数的使用IN参数是指输入参数，由存储过程的调用者为其赋值(也可以使用默认值)。如果不为参数指定模式，则其模式默认为IN。在调用上述存储过程update_emp2时，就需要为该过程的两个输入参数赋值，赋值的形式主要有如下两种。|(1)不指定参数名不指定参数名|(2)指定参数名指定参数名 42O

4、UT参数的使用OUT参数是指输出参数，由存储过程中的语句为其赋值，并返回给用户。使用这种模式的参数，必须在参数后面添加OUT关键字。43IN OUT参数的使用IN OUT参数同时拥有IN与OUT参数的特性，它既接受用户的传值，又允许在过程体中修改其值，并可以将值返回。使用这种模式的参数需要在参数后面添加IN OUT关键字。不过，IN OUT参数不接受常量值，只能使用变量为其传值。710.1.2 带参数的存储过程4【例【例10.3】创建带IN参数的存储过程update_emp2，为该过程设置两个IN参数，分别用于接受用户提供的empno与ename值，如下：SQL CREATE PROCEDUR

5、E update_emp2 2 ( emp_num IN NUMBER , emp_name IN VARCHAR2 ) AS 3 BEGIN 4 UPDATE emp SET ename = emp_name 5 WHERE empno = emp_num ; 6 END update_emp2 ; 7 /过程已创建。810.1.2 带参数的存储过程4【例【例10.4】调用update_emp2过程，通过该过程将empno为6500的员工的ename修改为XIAOQI，如下：SQL EXEC update_emp2 (6500 , XIAOQI) ;PL/SQL 过程已成功完成。4【例【例1

6、0.5】使用指定参数名的形式调用update_emp2过程，如下：SQL EXEC update_emp2 (emp_name = XIAOQI , emp_num = 6500) ;PL/SQL 过程已成功完成。910.1.2 带参数的存储过程4【例【例10.6】创建存储过程select_emp，为该过程设置一个IN参数和一个OUT参数，其中IN参数接受用户提供的empno值，然后在过程体中将该empno对应的ename值传递给OUT参数，如下：SQL CREATE PROCEDURE select_emp 2 ( emp_num IN NUMBER , emp_name OUT VARCH

7、AR2 ) AS 3 BEGIN 4 SELECT ename INTO emp_name 5 FROM emp WHERE empno = emp_num ; 6 END select_emp ; 7 /过程已创建。1010.1.2 带参数的存储过程4【例【例10.7】调用存储过程select_emp，为其IN参数赋值为6500，并声明变量employee_ name接受与输出其OUT参数的返回值，如下：|SQL VARIABLE employee_name VARCHAR2(10) ;|SQL EXEC select_emp (6500 , :employee_name) ;|PL/SQL

8、 过程已成功完成。过程已成功完成。然后，需要使用PRINT命令查看变量employee_name中的值，如下：|SQL PRINT employee_name ;|EMPLOYEE_NAME|-|XIAOQI也可以使用SELECT语句查看变量employee_name中的值，语句如下：|SQL SELECT :employee_name FROM dual ;1110.1.2 带参数的存储过程4【例【例10.8】创建存储过程exchange_value，通过该过程交换两个变量中的值，过程创建如下：参见教材P227|调用调用exchange_value过程，调用前过程，调用前声明为声明为IN O

9、UT参数赋值的变量，调用参数赋值的变量，调用后使用后使用SELECT语句输出交换值后的结语句输出交换值后的结果，如下：果，如下：参见教材P2271210.1.3 修改与删除存储过程4修改存储过程是在CREATE PROCEDURE语句中添加OR REPLACE关键字，其他内容与创建存储过程一样，其实质是删除原有过程，然后创建一个全新的过程，只不过前后两个过程的名称相同而已。4删除存储过程需要使用DROP PROCEDURE语句，其语法形式如下：DROP PROCEDURE procedure_name ;1310.1.4 查询存储过程的定义信息4对于创建好的存储过程，如果想要了解其定义信息，可

10、以查询数据字典user_source。4【例【例10.9】通过数据字典user_source查询存储过程select_emp的定义信息，如下：参见教材P2284其中，name表示对象名称；type表示对象类型；line表示定义信息中文本所在的行数；text表示对应行的文本信息。1410.2 函 数4创建函数需要使用CREATE FUNCTION语句，其语法如下：CREATE OR REPLACE FUNCTION function_name ( parameter IN | OUT | IN OUT data_type ) , . RETURN data_type IS | AS declar

11、ation_section ; BEGIN function_body ;END function_name ; 1510.3 实验指导使用存储过程与函数查询图书信息4实验指导10-1：使用存储过程和函数查询图书信息1创建函数get_prompt|首先创建函数首先创建函数get_prompt，如下：，如下：v参见教材参见教材P2302创建存储过程get_book_information3调用过程|存储过程和函数都已经创建好了，需要查询存储过程和函数都已经创建好了，需要查询某图书的信息时就可以直接调用某图书的信息时就可以直接调用get_book_information存储过程。例如获取存储过程。

12、例如获取bookid为为2的图书的信息，如下：的图书的信息，如下：v参见教材参见教材P2311610.4 触 发 器4触发器是一种特殊的存储过程，它在发生某种数据库事件时由Oracle系统自动触发。4触发器通常用于加强数据的完整性约束和业务规则等，对于表来说，触发器可以实现比CHECK约束更为复杂的约束。1710.4.1 触发器的类型4DML触发器：DML触发器由DML语句触发，例如INSERT、UPDATE和DELETE语句。 4INSTEAD OF触发器：INSTEAD OF触发器又称替代触发器，用于执行一个替代操作来代替触发事件的操作。 4系统事件触发器：系统事件触发器在发生如数据库启动

13、或关闭等系统事件时触发。4DDL触发器：DDL触发器由DDL语句触发，例如CREATE、ALTER和DROP语句。DDL触发器同样可以分为BEFORE触发器与AFTER触发器。1810.4.2 创建触发器4创建触发器需要使用CREATE TRIGGER语句，其语法如下：CREATE OR REPLACE TRIGGER trigger_name BEFORE | AFTER | INSTEAD OF trigger_event ON table_name | view_name | DATABASE FOR EACH ROW ENABLE | DISABLE WHEN trigger_cond

14、ition DECLARE declaration_statements ; BEGIN trigger_body ;END trigger_name ;1910.4.3 DML触发器4DML触发器由DML语句触发，其对应的trigger_event具体内容如下： INSERT | DELETE | UPDATE OF column , . 4关于DML触发器的说明如下：DML操作主要包括INSERT、DELETE和UPDATE操作，通常根据触发器所针对的具体事件将DML触发器分为INSERT触发器、UPDATE触发器和DELETE触发器。可以将DML操作细化到列，即针对某列进行DML操作时激

15、活触发器。任何DML触发器都可以按触发时间分为BEFORE触发器与AFTER触发器。在行级触发器中，为了获取某列在DML操作前后的数据，Oracle提供了两种特殊的标识符:OLD和:NEW，通过:OLD.column_name的形式可以获取该列的旧数据，而通过:NEW.column_name则可以获取该列的新数据。 2010.4.3 DML触发器4【例【例10.11】为了演示触发器的效果，下面首先创建两个简单的示例表：student(学生表)和record(记录表)，并向student表中添加几条记录，如下：参见教材P2334创建AFTER UPDATE触发器，要求在修改student表中的某

16、行数据后，在record表中记录修改操作，并保存修改前的行数据。创建触发器的语句如下：参见教材P2342110.4.4 INSTEAD OF触发器4INSTEAD OF触发器用于执行一个替代操作来代替触发事件的操作，而触发事件本身最终不会被执行。 4如果是DML触发器，则无论是BEFORE触发器还是AFTER触发器，触发事件最终都会被执行。4不过，Oracle中的INSTEAD OF触发器不能针对表，而只能针对视图。 2210.4.4 INSTEAD OF触发器4【例【例10.12】首先基于student表创建视图student_view，该视图检索student表中的所有数据，但将stude

17、nt表中的sage列加1。视图创建如下：SQL CREATE VIEW student_view 2 AS 3 SELECT sid , sname , sage + 1 new_age 4 FROM student 5 WITH CHECK OPTION ;视图已创建。2310.4.5 系统事件触发器4系统事件触发器是指由数据库系统事件触发的触发器，其所支持的系统事件如表10-1所示。系统事件说 明LOGOFF用户从数据库注销LOGON用户登录数据库SERVERERROR服务器发生错误SHUTDOWN关闭数据库实例STARTUP打开数据库实例2410.4.5 系统事件触发器4【例【例10.1

18、3】在system用户下创建一个系统事件触发器，该触发器由LOGON事件触发，记录登录用户的用户名(USER)与登录时间，如下：SQL CONNECT system/admin已连接。SQL CREATE TRIGGER logon_trigger 2 AFTER LOGON 3 ON DATABASE 4 BEGIN 5 INSERT INTO logon_log VALUES ( USER , SYSDATE ) ; 6 END logon_trigger ; 7 /触发器已创建2510.4.6 DDL触发器4DDL触发器由DDL语句触发，按触发时间可以分为BEFORE触发器与AFTER触

19、发器，其所针对的事件包括CREATE、ALTER、DROP、ANALYZE、GRANT、COMMENT、REVOKE、RENAME、TRUNCATE、AUDIT、NOTAUDIT、ASSOCIATE STATISTICS和DISASSOCIATE STATISTICS。4创建DDL触发器需要用户具有DBA权限。2610.4.7 禁用与启用触发器4在创建触发器时，可以使用ENABLE与DISABLE关键字指定触发器的初始状态为启用或禁用，默认情况下为ENABLE。4在需要的时候，也可以使用ALTER TRIGGER语句修改触发器的状态，其语法如下：ALTER TRIGGER trigger_na

20、me ENABLE | DISABLE ;4如果需要修改某个表上的所有触发器的状态，还可以使用如下形式：ALTER TABLE table_name ENABLE | DISABLE ALL TRIGGERS ;2710.4.8 修改与删除触发器4修改触发器只需要在CREATE TRIGGER语句中添加OR REPLACE关键字。4删除触发器需要使用DROP TRIGGER语句，其语法如下：DROP TRIGGER trigger_name ;2810.5 程 序 包4使用程序包主要是为了实现程序模块化，程序包可以将相关的存储过程、函数、变量、常量和游标等PL/SQL程序组合在一起，通过这种方

21、式可以构建供程序人员重用的代码库。4另外，当首次调用程序包中的存储过程或函数等元素时，Oracle会将整个程序包调入内存，在下次调用包中的元素时，Oracle就可以直接从内存中读取，从而提高程序的运行效率。2910.5.1 创建程序包41创建包规范创建包规范需要使用CREATE PACKAGE语句，其简要语法如下：|CREATE OR REPLACE PACKAGE package_name| IS | AS |package_specification ;|END package_name ;语法说明如下。|package_name：创建的包名。：创建的包名。|package_specifi

22、cation：用于列出用户可以使用的公共存：用于列出用户可以使用的公共存储过程、函数、类型和对象。储过程、函数、类型和对象。3010.5.1 创建程序包42创建包体创建包体需要使用CREATE PACKAGE BODY语句，并且在创建时需要指定已创建的包，其简要语法如下：|CREATE OR REPLACE PACKAGE BODY package_name| IS | AS |package_body ;|END package_name ;3110.5.2 调用程序包中的元素4DBMS_OUTPUT是系统定义的包，而PUT_LINE是该包中的存储过程。可见调用程序包中的元素时，是使用如下形式：package_name. element_name ;4其