《Oracle Database 11g应用与开发教程 教学课件 ppt 作者 978-7-302-31490-5 第11章 存储过程、函数、触发器和程序包》由会员分享，可在线阅读，更多相关《Oracle Database 11g应用与开发教程 教学课件 ppt 作者 978-7-302-31490-5 第11章 存储过程、函数、触发器和程序包（120页珍藏版）》请在金锄头文库上搜索。
1、第1页,授课教师：姚瑶 职务：讲师,Oracle 11g数据库应用教程,第2页,在此之前，我们所创建的PL/SQL程序都是匿名的。这些匿名的程序块没有被存储在数据库中，每次执行的时候都需要被重新编译。很多时候，我们都需要保存PL/SQL程序块，以便随后可以调用。这意味着，程序块需要一个名称，这样在调用或引用它时，系统就可以找到这个特定的程序块。创建并命名的程序块称为“命名程序单元”或“子程序”。由于存储代码的位置、代码所执行的任务类型不同以及调用方式的不同，命名PL/SQL程序单元分为存储过程、函数、触发器和程序包四种。,第十一章 存储过程、函数、触发器和程序包,第3页,本章的学习目标： 了解
2、Oracle匿名程序块 熟练掌握Oracle存储过程的创建、调用 理解存储过程中各种形式的参数 了解为过程添加局部变量和子过程 熟练掌握Oracle函数的创建、调用 了解触发器类型 掌握创建多种类型触发器的方法 熟悉程序包及创建程序包的公有成员和私有成员 掌握创建包规范和包体的方法 掌握调用包中元素的方法,第4页,11.1 存储过程,存储过程是Oracle开发者在数据转换或查询报表时最经常使用的方式之一。存储过程是一种命名PL/SQL程序块，它将一些相关的SQL语句，流程控制语句组合在一起，用于执行某些特定的操作或者任务，可以将经常需要执行的特定的操作写成过程。通过过程名，就可以多次调用过程，
3、从而实现程序的模块化设计。这种方式极大地节省了用户的时间，也提高了程序的效率。,第5页,在Oracle中，可以在数据库中定义子程序，在子程序中将一些固定的操作集中起来，由Oracle数据库服务器完成，以完成某个特定的功能。这种子程序称为存储过程（Proce-Dure）。存储过程可以通俗地理解为是存储在数据库服务器中的封装了一段或多段SQL语句的PL/SQL代码块。在数据库中有一些是系统默认的存储过程，那么可以直接通过存储过程的名称进行调用。另外，存储过程还可以在编程语言中调用，如Java、C#等等。,11.1.1 存储过程概述,使用存储过程具有如下的优点： （1）存储过程在服务器端运行，执行速
4、度快。,第6页,（2）存储过程执行一次后驻留在Oracle数据库服务器的高速Cache中，以后再次执行存储过程时，只需从高速Cache中调用已经编译好的代码即可，从而提高了系统性能。 （3）存储过程确保了数据库的安全。使用存储过程，可以在禁止用户直接访问应用程序中的某些数据表的情况下，授权执行访问这些数据表的存储过程。 （4）自动完成需要预先执行的任务。存储过程可以设置为系统启动时自动执行，而不必在系统启动后再进行手动操作，从而方便了用户的使用，可以自动完成一些需要预先执行的任务。,11.1.2 创建和执行存储过程,1. 创建存储过程,第7页,创建存储过程之前，先来看一下创建存储过程的语法结构
5、： CREATE OR REPLACE PROCEDURE procedure_name Parameter IN |OUT |IN OUT data_type1, Parameter IN |OUT |IN OUT data _type2, IS|AS 声明部分 BEGIN 执行部分 EXCEPTION 异常处理部分 END procedure_name;,第8页,在上面语法结构中的各选项说明： OR REPLACE：表示如果指定的过程已存在，则覆盖同名的存储过程。 Procedure_name：表示存储过程的名称。 Parameter：表示存储过程中的参数。 IN：表示向存储过程传递参数。
6、 OUT：表示从存储过程返回参数。 Date_type：表示参数类型，在指定其长度时，不能指定其长度。 AS或IS后声明的变量主要用于过程体内，且不能加DECLARE语句。,第9页,2. 执行存储过程 存储过程创建以后，只要通过授权，用户就可以在SQL Plus、ORACLE开发工具或第三方开发工具中来调用执行了。在SQL Plus中调用存储过程的命令是EXECUTE，具体的语法格式如下： DECLARE Parameter1 data_type1 , BEGIN EXECUTE Procedure_name(parameter1,parameter2,); END;,第10页,【例11-1】
7、创建一个简单的存储过程Insert_student，该过程可以用于向学生表添加数据。 SQL CREATE OR REPLACE PROCEDURE Insert_student IS 2 BEGIN 3 INSERT INTO Students 4 VALUES(20110810199,李明,男,to_date(1993-05-24,YYYY-MM-DD),汉族,081,08101); 5 EXCEPTION 6 WHEN DUP_VAL_ON_INDEX THEN 7 DBMS_OUTPUT.PUT_LINE(重复的学生ID); 8 WHEN OTHERS THEN 9 DBMS_OUTP
8、UT.PUT_LINE(发生其他错误！); 10 END Insert_student; 过程已创建。,第11页,上面创建的简单的存储过程，它包括匿名块中的异常处理部分和可执行部分。它只是展示了创建了存储过程的基本结构。下面执行该存储过程： 用户也可以通过PL/SQL匿名程序块中执行，代码和执行结果如下： SQL SET SERVEROUTPUT ON -保证了DBMS_OUTPUT.PUT_LINE成功输出 2 BEGIN 3 Inseret_student; 4 END; 重复的学生ID PL/SQL过程已成功完成。 由于输入的学生是Students表已经存在的数据，所以执行结果输出“重复
9、的学生ID”。 用户也可以通过在关键词EXEC后面加上存储过程名来执行已经存在的存储过程。,第12页,3. 查看存储过程 存储过程一旦被创建就会存储到数据库服务器上，Oracle通过使用视图USER_SOURCE查看已经存在的存储过程脚本。 【例11-2】查看存储过程Insert_student的脚本，代码如下，执行结果如图11-1所示。 SQL SELECT * FROM USER_SOURCE WHERE NAME=Insert_student ORDER BY LINE;,图11-1 查看存储过程,第13页,从USER_PROCEDURE视图中可以获得有关过程的信息。表11-1给出了US
10、ER_PROCEDURE视图中部分列的说明。,查看该视图的例子请读者自行练习。,第14页,11.1.3 带参数的存储过程 存储过程允许带有参数，参数的使用将增加存储过程的灵活性，给数据库编程带来极大的方便。本小节将介绍如何使用输入类型参数、输出类型参数和输入输出类型参数。 存储过程中如果使用了参数，在执行存储过程时必须为其指定参数。总的来说，参数可以是常量、变量和表达式等。ORACLE有三种参数模式：IN，OUT和IN OUT。 1. IN参数 该类参数值由调用者传入，并且只能够被存储过程读取。这种模式的参数是最常用的，也是默认的参数模式。 【例11-3】创建一个带有输入参数的存储过程：当使用
11、Inseret_student添加学生信息时，用户只需要向该存储过程传入参数值，然后由存储过程从中读取数据。,第15页,SQLCREATE OR REPLACE PROCEDURE Insert_student 2 （P_ID IN VARCHAR2, 3 P_NAME IN VARCHAR2, 4 P_SEX IN VARCHAR2, 5 P_BIRTHDAY IN DATE, 6 P_NAT IN VARCHAR2, 7 P_DID IN VARCHAR2, 8 P_CID IN VARCHAR2） 9 IS 10 BEGIN 11 INSERT INTO Students 12 VALU
12、ES(P_ID, P_NAME, P_SEX, P_BIRTHDAY, P_NAT, P_DID, P_CID); 13 EXCEPTION 14 WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE(重复的学生ID); 15 WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(发生其他错误！); 16 END Insert_student; 过程已创建。,第16页,调用该过程时，用户需要传递7个参数，向学生信息表中插入一条记录。对于参数的传递也有三种方法：按名称传递，按位置传递，组合传递。 名称传递： EXECUTE Inse
13、rt_student(P_ID=20110810199, P_NAME=李明, P_SEX=男, P_BIRTHDAY= to_date(1993-05-24,YYYY-MM-DD), P_NAT=汉族, P_DID=081, P_CID=08101); 位置传递： EXECUTE Insert_student( 20110810199, 李明, 男, to_date(1993-05-24,YYYY-MM-DD), 汉族, 081, 08101); 组合传递： EXECUTE Insert_student(20110810199, 李明, 男, to_date(1993-05-24,YYYY-
14、MM-DD), P_NAT=汉族, P_DID=081, P_CID=08101);,第17页,1. OUT参数 该类参数值由存储过程写入。 【例11-4】创建一个带有输出参数的存储过程：该存储过程根据学生的姓名返回学生该生选修的所有课程的课程名。 (1)使用SQL Developer工具创建存储过程 CREATE OR REPLACE PROCEDURE P_COURSENAME(P_SNAME IN VARCHAR2, COURSENAME OUT VARCHAR2) IS V_COURSENAME VARCHAR2(50); BEGIN,第18页,FOR STUDENT IN (SELE
15、CT C.CNAME FROM STUDENTS S, SC , COURSES C WHERE S.STUDENT_ID = SC.STUDENT_ID AND SC.COURSE_ID = C.COURSE_ID AND S.SNAME =P_SNAME) LOOP V_COURSENAME := V_COURSENAME | STUDENT.CNAME ; END LOOP; COURSENAME := V_COURSENAME; END P_COURSENAME; 在SQL Developer中的SQL编辑器中输入以上PL/SQL语句块后，按F5键编译存储过程。,第19页,(2)执行存
16、储过程 因为这个过程要通过OUT参数返回值，所以在运行它时必须首先定义一个能够接收返回值的变量。 VARIABLE C_NAME VARCHAR2(20) EXEC P_COURSENAME(王传璐,:C_NAME); PRINT C_NAME; 运行结果如图11-2所示：,图11-2 SQL Developer执行存储过程,若在SQL Plus 中调用该过程时，也需要使用VARIABLE命令绑定参数值。 SQLvariable objname varchar2(100); SQLExec p_coursename(王传璐,: objname);,第20页,在这里首先在SQL*PLUS中定义了一个变量，然后以这个变量为输出参数调用过程p_coursename，然后执行PRINT命令可以查看结果，如图所1
