Oracle数据库实用教程第三章 PL／SQL程序设计.ppt

1、第三章 PL/SQL程序设计,主要内容,3.1 PL/SQL的优点： 3.2 运行PL/SQL程序 3.3 PL/SQL块结构 3.4 PL/SQL基本语法 3.5 PL/SQL 处理流程 3.6 异常处理 3.7 游标 3.8 存储过程和函数 3.9 触发器,3.1 PL/SQL的优点,有利于客户/服务器环境应用的运行 使用PL/SQL进行编程，将大量数据处理的应用放在服务器端来执行，省去了数据在网上的传输时间。 适合于客户环境 由于PL/SQL分为数据库PL/SQL部分和工具PL/SQL。对于客户端来说，PL/SQL可以嵌套到相应的工具中，客户端程序可以执行本地包含PL/SQL部分，也可以

2、向服务发SQL命令或激活服务器端的PL/SQL程序运行。,3.2 运行PL/SQL程序,PL/SQL程序的运行是通过Oracle中的一个引擎来进行的。这个引擎可能在Oracle的服务器端，也可能在 Oracle 应用开发的客户端。引擎执行PL/SQL中的过程性语句，然后将SQL语句发送给数据库服务器来执行，再将结果返回给执行端。 例如，如果应用程序需要取得学生的成绩，那么可以建立函数实现该项功能。,SQL create function get_grade1(sno char,cno char) 2 return number is 3 V_grade number(3); 4 begin 5

3、 select grade 6 into V_grade 7 from sc 8 where stu_no=sno and cou_no=cno; 9 return V_grade; 10 end; 11 / 函数已创建。 SQL var v_grade number SQL exec :v_grade:=get_grade1(20026101,a02) PL/SQL 过程已成功完成。 SQL print v_grade,3.3 PL/SQL块结构,PL/SQL程序由三个块组成，即 声明部分、执行部分、异常处理部分。PL/SQL块的结构如下： Declare /* 声明部分: 在此 声明PL/

4、SQL用到的变量,类型及光标 */ Begin /* 执行部分: 过程及SQL 语句 , 即程序的主要部分 */ Exception /* 执行异常部分: 错误处理 */ End;,其中 执行部分是必须的。而END则是PL/SQL 块的结束标记。 需要注意的是DECLARE，BEGIN，EXCEPTION后面没有分号（；），而END后则必须要带有分号。 PL/SQL标识符的命名规则： 标识符的最大长度是30个字符，包括字母、数字、$、_、# ；不可包含保留字；要以字来打头；不能和同一块中的表中的字段名一样。,【例3-1】只包含执行部分的PL/SQL块 SQL set serveroutput

5、on SQL begin 2 dbms_output.put_line(Welcome!); 3 end; 4 / Welcome! PL/SQL 过程已成功完成。 注意：当使用dbms_output.包输出数据或消息时，必须要将SQL*Plus的环境变量serveroutput 设置为on.,【例3-2】包含定义部分和执行部分的PL/SQL块 SQL DECLARE 2 v_sname VARCHAR(10); 3 BEGIN 4 select stu_name INTO v_sname FROM student 5 WHERE stu_no= 学生姓名：李勇 PL/SQL 过程已成功完成。

6、 注意：该例中当执行该PL/SQL时，会根据输入的学号显示学生姓名。为了临时存放姓名，就必须定义变量。 常量包括后面的变量名都必须以字母开头，不能有空格，不能超过30个字符长度，同时不能和保留字同名，常（变）量名称不区分大小写，在字母后面可以带数字或特殊字符。括号内的not null为可选参数，若选用，表明该常（变）量不能为空值。,【例3-4】常量定义 SQL declare 2 pi constant number(9):=3.1415926; 3 begin 4 commit; 5 end; 6 / PL/SQL 过程已成功完成。,3.4.2 基本数据类型变量,PL/SQL中常用的基本数据

7、类型,3.4.3 基本数据类型变量的定义方法,变量名 类型标识符 not null:=值; 【例3-5】 程序定义了名为age的数字型变量，长度为3，初始值为26 SQL declare 2 v_age number(3):=26; 3 begin 4 commit; 5 end; 6 / PL/SQL 过程已成功完成。,3.4.4 复合数据类型变量,使用%type定义变量 为了让PL/SQL中变量的类型和数据表中的字段的数据类型一致，Oracle 9i提供了%type定义方法。这样当数据表的字段类型修改后，PL/SQL程序中相应变量的类型也自动修改。,【例3-6】该程序定义了名为 v_sna

8、me的变量，其类型和 student据表中的 stu_name字段类型是一致的。 SQL Declare 2 v_sname student.stu_name%type; 3 begin 4 commit; 5 end; 6 / PL/SQL 过程已成功完成。,自定义记录类型变量 很多结构化程序设计语言都提供了记录类型的数据类型，在PL/SQL中，也支持将多个基本数据类型捆绑在一起的记录数据类型。 【例3-7】程序代码定义了名为 stu_record_type的记录类型，该记录类型由字符型的sno、字符型的name和整型的age基本类型变量组成，stu_record是该类型的变量，引用记录型变

9、量的方法是“记录变量名.基本类型变量名”。,使用%rowtype属性定义记录变量 使用%type可以使变量获得字段的数据类型，使用%rowtype可以使变量获得整个记录的数据类型。该属性可以基于表或视图定义记录变量。为了简化表或视图所有列数据的处理，应该使用该属性定义记录变量。 【例3-8】执行下列PL/SQL程序，程序定义了名为myrecord的复合类型变量，与student表结构相同。,SQL DECLARE 2 myrecord student%rowtype; 3 BEGIN 4 select * 5 into myrecord 6 from student 7 where stu_n

10、o= 16 /,3.4.5 PL/SQL集合类型,索引表(PL/SQL表) PL/SQL表与其他过程化语言(如C语言)的一维数组类似。需要注意的是，高级语言数组的下标不能为负，但PL/SQL 表的下标可以为负值；高级语言数组的元素个数有限制，而PL/SQL 表的元素个数没有限制，并且其下标没有上下限。现PL/SQL表需要创建一个数据类型并另外进行变量说明。表类型变量和数据表是有区别的，定义表类型变量的语法如下： Type Is Table Of Index by 索引表元素下标的数据类型;,【例3-9】 在索引表中使用数据类型Binary_integer SQL SET SERVEROUTPU

11、T ON SQL Declare 2 Type Array_type is 3 Table Of Number 4 Index by Binary_Integer; 5 My_Array Array_type; 6 Begin 7 For I In 1.10 Loop 8 My_Array(I) := I*2; 9 End Loop; 10 For I In 1.10 Loop 11 Dbms_Output.Put_line(To_char(My_Array(I); 12 End Loop; 13 End; 14 /,嵌套表 嵌套表是嵌在一张表中记录的表。对保存嵌套表的表中的每一列都可以创建一

12、张存储表。嵌套表的每一行都存储在主表外的存储表中。其格式： type 嵌套表名 is table of 元素类型 not null; 嵌套表（Nested Table）类似于高级语言中的数组。需要注意的是，高级语言数组和嵌套表的下标都不能为负值，高级语言数组的元素个数有限制，而嵌套表的元素个数没有限制。 当在表列中使用嵌套表时，必须首先使用CREATE TYPE语句建立嵌套表类型。该嵌套表类型被存储在数据字典中(user_type)。,【例3-11】为雇员信息建立对象类型emp_obj，而emp_array是基于emp_obj的嵌套表类型，它可以用于存储多个雇员信息。 SQL create o

13、r replace type emp_obj as object ( 2 name varchar2(10), 3 salary number(6,2), 4 hiredate date ); 5 / 类型已创建。 SQL create or replace type emp_array is table of emp_obj; 2 / 类型已创建。,SQL create table department ( 2 depno number(2), 3 dname varchar2(10), 4 employee emp_array 5 ) nested table employee store

14、 as employee; 表已创建。 create table 语句中包含有nested table子句，指明将用来存放嵌套表行的存储表的名字为employee。而且，对此存储表不能直接进行访问，必须通过主表才能访问引存储表中的数据. 存储表是系统生成的表，它用来存储嵌套表中的实际数据，这些数据不是和表中其他列的数据共同存储的，而是被单独存放的。,变长数组(VARRAY) VARRAY也是一种用于处理PL/SQL 数组的数据类型，客观存在也可以作为表列的数据类型使用。该数据类型与高级语言数组非常类似，其元素下标以1开始，并且元素的最大个数是有限制的。定义变长数组的格式： type 类型名 i

15、s varry（最大尺寸) of 元素类型 not null; 当在PL/SQL 块中使用varray变量时，必须首先使用其构造方法来初始化varray变量，然后才能在PL/SQL块内引用varray元素。下面举例说明使用VARRAY的方法：,SQL declare 2 type sname_table_type is varray(10) of student.stu_name%TYPE; 3 sname_table sname_table_type:=sname_table_type(lin); 4 begin 5 select stu_name into sname_table(1) f

16、rom student 6 where stu_no= 学生姓名:刘晨 PL/SQL 过程已成功完成。,3.5 PL/SQL 处理流程,在PL/SQL程序中，要使程序能按照逻辑进行处理，除了有些语句是SQL语句外，还必须有能进行逻辑控制的语句。PL/SQL 也不例外，它不仅可以嵌入SQL语句，而且还支持条件分支语句（IF，CASE）、循环语句（LOOP）。,格式： IF THEN PL/SQL 和 SQL语句; ELSE 其它语句; ELSIF THEN 其它语句; END IF;,3.5.1 条件分支语句,【例 3-12】判断两个整数变量的大小，输出不同的结果。 SQL set server

17、output on SQL declare 2 number1 integer:=80; 3 number2 integer:=90; 4 begin 5 if number1=number2 then 6 if number1=number2 then 7 dbms_output.put_line(number1等于number2); 8 else 9 dbms_output.put_line(number1小于number2); 10 end if; 11 else 12 dbms_output.put_line(number1大于number2); 13 end if; 14 end;

18、15 /,从Oracle9i开始，不仅可以使用IF语句，也可以使用CASE语句来执行多重条件分支操作。使用CASE语句更加简捷，而且执行效率也更好。 在CASE语句中使用单一选择符进行等值比较 格式： CASE WHEN THEN 语句1； WHEN THEN 语句1； WHEN THEN 语句1； ELSE 语句n+1; END CASE;,3.5.2 CASE语句,在CASE语句中使用多种条件比较 格式： CASE WHEN THEN 语句1； WHEN THEN 语句1； WHEN THEN 语句1； ELSE 语句n+1; END CASE;,基本循环 Loop 要执行的语句; exi

19、t when condition; end loop; 当使用基本循环时，无论是否满足条件，语句至少会被执行一次。当condition为TURE时，会退出循环，并执行END LOOP后的相应操作。,3.5.3 循环语句,【例 3-15】 为stu2 表插入5条数据(2004610120046105)。 SQL create table stu2(sno int); 表已创建。 SQL declare 2 i int:=20048101; 3 begin 4 loop 5 insert into stu2 values(i); 6 exit when i=20048105; 7 i:=i+1;

20、8 end loop; 9 end; 10 / PL/SQL 过程已成功完成。,WHILE 循环 格式： while loop 要执行的语句; end loop; 只有条件为真时，才会执行循环体内的语句。,FOR循环 格式： FOR 循环控制变量 IN REVERSE 下界值 上界值 LOOP statement1; statement2; END LOOP; 当使用FOR循环时，每次循环时循环控制变量会自动增一；如果指定REVERSE选项，那么每次循环时循环控制变量会自动减一。,3.6 异常处理,一个优秀的程序都应该能够正确处理各种出错情况，并尽可能从错误中恢复。Oracle 提供异常情况(

21、EXCEPTION)和异常处理(EXCEPTION HANDLER)来实现错误处理。 虽然在PL/SQL编程中，异常处理不是必须的，但建议编程人员要养成在PL/SQL编程中指定相应的异常。,异常处理是用来处理正常执行过程中未预料的事件，异常处理包括预定义的错误和自定义错误。PL/SQL程序块一旦产生异常而没有指出如何处理时,程序就会自动终止整个程序运行。 EXCEPTION when exception1 then statement1; when exception2 then statement2; . when others then statement; END; 其中：异常处理可以按

22、任意次序排列,但 Others 必须放在最后。,3.6.1 异常处理概念,两种类型的异常：用户定义(user_define) 异常和预定义 ( predefined )异常。 当使用预定义异常处理时，应该了解PL/SQL 块的常见运行错误，并掌握与之相关的预定义异常处理。,3.6.2预定义的异常处理,可以使用RAISE_APPLICATION_ERROR 创建自己的错误处理。其语法如下： RAISE_APPLICATION_ERROR(error_number,error_message,keep_errors ) ; 其中： error_number 是从 20,000 到 20,999 之

23、间的参数， error_message 是相应的提示信息( 2048 字节)， keep_errors 为可选，如果keep_errors =TRUE ,则新错误将被添加到已经引发的错误列表中。如果keep_errors=FALSE(缺省),则新错误将替换当前的错误列表。,3.6.3 用户定义的异常处理,3.7游标,在PL/SQL 中处理查询语句和数据操纵语句时，Oracle 必须分配一片叫上下文( context area )的区域来处理所必需的信息，其中包括： 要处理的行的数目； 一个指针，指向语句被分析以后的表示形式； 查询的活动集(active set)。 游标是一个指向上下文的句柄(

24、 handle)或指针。通过游标，PL/SQL 可以控制上下文区和处理语句时上下文区会发生的事情。,在PL/SQL程序中定义的游标称作显式游标。显式游标包括: 定义游标 打开游标 提取数据（也称为推进游标） 关闭游标,3.7.1 显式游标,定义游标 cursor 游标名（参数名1 数据类型,参数2 数据类型） is 查询语句; 其中：游标名是一个没有定义的PL/SQL 变量，用户不能给游标名赋值。 参数名是一个在SELECT语句的WHERE条件中要用到的参数。 SELECT语句是一个不含INTO子句的SELECT语句，允许带WHERE、ORDER BY、GROUP BY等子句。,打开游标 打开

25、游标是在SELECT语句的所有输入参数都接受值之后，执行游标所对应的SELECT语句，将其查询结果放入工作区，指针指向工作区的首部。假如在打开游标时查询没有返回行，PL/SQL 没有触发错误，可以在取数据（FETCH）之后检测游标的状态。 open 游标名称（实际参数值1实际参数值2）;,提取数据 在打开游标之后，SELECT语句的结果被临时存放到游标结果集中，为了处理结果的数据，需要使用FETCH语句提取游标数据。 格式一： fetch 游标名称 into 变量列表; 格式二： fetch 游标名称 bulk collect into 集合变量表LIMT rows; 关闭游标 close 游

26、标名称;,【例 3-16】 显示信息专业的所有学生学号及姓名。（使用FETCH.INTO语句） SQL set serveroutput on SQL declare 2 cursor stu_cursor is 3 select stu_no, stu_name from student where stu_dept like 信息; 4 v_sname varchar2(10); 5 v_sno char(8); 6 begin 7 if not stu_cursor%ISOPEN then 8 open stu_cursor; 9 end if; 10 loop 11 fetch stu

27、_cursor into v_sno, v_sname; 12 exit when stu_cursor%NOTFOUND ; 13 dbms_output.put_line(v_sno|,|v_sname ); 14 end loop; 15 close stu_cursor; 16 end; 17 /,所有的SQL 语句在上下文区内部都是可执行的，因此都有一个游标指向上下文区，此游标就是所谓的SQL游标(SQL cursor)，即隐式游标。与显式游标不同，SQL 游标不被程序打开和关闭。 当一个DML语句执行时，PL/SQL内部打开一个游标，语句的结果被保存在4个游标属性中。 SQL%FO

28、UND SQL%NOTFOUND SQL%ROWCOUNT SQL%ISOPEN 游标是一块包含有查询信息的内存空间。在执行DML语句，游标被打开；当语句完成时，游标被关闭。,3.7.2 隐式游标,【例 3-20】 更新学生表，如果找到学号为20028104的学生更新学生的年龄，否则往学生表中插入该学生的记录。 SQL BEGIN 2 UPDATE student 3 SET stu_age=19 4 WHERE stu_no=20028104; 5 - 如果更新没有匹配则插入一新行 6 IF SQL%NOTFOUND THEN 7 INSERT into Student VALUES(200

29、28104,李滨,19,男,信息); 8 END IF; 9 commit; 10 END; 11 /,游标FOR循环是在PL/SQL 块中使用游标最简单的方式，简化了对游标的处理。当使用游标FOR循环时，Oracle 会隐含地打开游标、提取游标数据并关闭游标。语法如下： FOR record_name IN cursor_name LOOP Ststement1; Ststement2; END LOOP; 其中：record_name是Oracle 隐含定义的记录变量名。 当使用游标开发PL/SQL 应用程序时，为了简化程序代码，建议大家使用游标FOR循环。,3.7.3 游标FOR循环,【

30、例 3-21】给课程名为数据库原理的所有学生的成绩加5分。 SQL declare 2 cursor sc1_cursor 3 is 4 select * 5 from sc for update; 6 begin 7 dbms_output.put_line(课程号 学号 成绩); 8 for sc_rec in sc1_cursor loop 9 if sc_rec.cou_no=a01 then 10 dbms_output.put_line(sc_rec.cou_no| |sc_rec.stu_no| |sc_rec.grade); 11 update sc 12 set grade=

31、grade+2 13 WHERE CURRENT OF sc1_cursor; 14 end if; 15 end loop; 16 end; 17 /,ORACLE编写的程序一般分为两类： 存储过程：是可以完成一定功能的程序叫存储过程； 函数：是在使用时给出一个或多个值，处理完后返回一个或多个结果的程序叫函数； 这两类程序都存放在Oracle数据库字典中。,3.8存储过程和函数,与其它的数据库系统一样，Oracle的存储过程是用PL/SQL语言编写的能完成一定处理功能的存储在数据库字典中的程序。 创建过程语法: CREATE OR REPLACE PROCEDURE 过程名 (参数名 IN

32、| IN OUT 数据类型.) IS | AS PL/SQL块,3.8.1 存储过程,建立无参数的存储过程 【例 22】以下过程用于输出当前系统日期和时间。 SQL CREATE OR REPLACE PROCEDURE data_time 2 IS 3 BEGIN 4 dbms_output.put_line(systimestamp); 5 END; 6 / 过程已创建。,建立了存储过程data_time之后，就可以调用该过程。在SQL*Plus环境中调用过程有两种方法： 方法一：使用execute命令调用过程 SQL set serveroutput on; SQL exec data_

33、time; 19-7月 -05 09.08.36.312000000 下午 +08:00 PL/SQL 过程已成功完成。 方法二：使用call命令调用过程 SQL call data_time(); 20-7月 -05 002000000 上午 +08:00 调用完成。,建立带有IN参数的存储过程 建立存储过程时，可以通过使用输入参数，将应用程序的数据传递到过程中， 如果不指定参数模式，则默认是输入参数， 可以使用IN关键字显示地定义输入参数。 下面以建立为选课表SC插入数据的存储过程add_sc为例，说明建立带有输入参数的过程的方法。,【例 3-23】建立为选课表SC插入数

34、据的存储过程add_scv。 SQL CREATE OR REPLACE PROCEDURE add_scv 2 (v_sno sc.stu_no%TYPE, v_cno sc.cou_no%TYPE, v_grade sc.grade%TYPE) 3 IS 4 e_integrity EXCEPTION; 5 pragma exception_init(e_integrity,-2291); 6 BEGIN 7 insert into sc(stu_no,cou_no,grade) 8 values(v_sno,v_cno,v_grade); 9 EXCEPTION 10 WHEN dup_

35、val_on_index THEN 11 RAISE_APPLICATION_ERROR(-20000,学号与课程号不能重复); 12 WHEN e_integrity THEN 13 RAISE_APPLICATION_ERROR(-20001,学号或课程号不存在); 14 END; 15 /,建立OUT参数的存储过程 过程不仅可以用来执行特定操作，而且也可以用于输出数据，在过程中输出数据是使用OUT或IN OUT 参数来完成的，当定义输出参数时，必须提供OUT关键字。,【例 3-24】 建立用于输出某学生某门课的成绩的存储过程sc_grade SQL CREATE OR REPLACE P

36、ROCEDURE sc_grade 2 ( v_sno IN sc.stu_no%TYPE, 3 v_cno IN sc.cou_no%TYPE, 4 v_grade OUT sc.grade%TYPE ) 5 IS 6 BEGIN 7 select grade into v_grade 8 from sc 9 where stu_no=v_sno and cou_no=v_cno; 10 EXCEPTION 11 WHEN no_data_found THEN 12 RAISE_APPLICATION_ERROR(-20000,学号或课程号不存在); 13 END; 14 /,建立带IN O

37、UT参数的存储过程 定义过程时，不仅可以指定IN和OUT参数，也可以指定IN OUT参数。 IN OUT参数也称为输入输出参数，当使用这种参数时，在调用过程之前需要通过变量给该参数传递数据。在调用结束后，Oracle 会通过该变量将过程结果传递给应用程序。,【例 3-25】 将一个7位数字的电话号码（如2217889转换成8 位数字的电话号码。转换规则：第一个数字为2，前面加上5 ，其余的加上6。 SQL CREATE OR REPLACE PROCEDURE telephone 2 (v_phone_no IN OUT varchar2) 3 IS 4 BEGIN 5 if substr(v

38、_phone_no,1,1)=2 then 6 v_phone_no:=5|v_phone_no; 7 else 8 v_phone_no:=6|v_phone_no; 9 end if; 10 END;,Oracle的函数是一个独有的对象，它也是由PL/SQL语句编写而成。与存储过程不同的是函数必须返回某些值，而存储过程可以不返回任何值。创建函数的语法如下： CREATE OR REPLACE FUNCTION 函数名 (argment in| in out TYPE, argment in | out | in out TYPE,. RETURN datatype IS | AS PL/S

39、QL 块；,3.8.2 函数,建立无参数的函数 当建立函数时，函数可以带有参数，也可以不带参数。 【例 3-26】建立用于显示当前数据库用户的函数。（不带任何参数） SQL CREATE OR REPLACE FUNCTION get_user 2 return varchar2 3 AS 4 v_user varchar2(100); 5 BEGIN 6 select username into v_user from user_users; 7 return v_user; 8 END; 9 / 函数已创建。,建立带IN参数的函数 建立函数时，通过使用输入参数，可以将应用程序的数据传递到函

40、数中，最终通过执行函数可以将结果返回到应用程序中。 【例 3-27】创建函数get_grade，实现输入学生的学号及课程号返回该门课的成绩。如果学号及课程号存在，显示该信息。（带有IN参数）,SQL CREATE OR REPLACE FUNCTION get_grade 2 (v_sno IN varchar2, v_cno IN varchar2) 3 return number 4 AS 5 v_grade sc.grade%TYPE; 6 EGIN 7 select grade into v_grade 8 from sc 9 where stu_no=v_sno and cou_no

41、=v_cno; 10 return v_grade; 11 EXCEPTION 12 WHEN no_data_found THEN 13 RAISE_APPLICATION_ERROR(-20000,学号或课程号不存在); 14 END;,建立带OUT参数的函数 一般情况下，函数只有一个返回值，如果希望使用函数同时返回多个值，则需要使用输出参数OUT。 【例 28】 输入学生的学号，建立用于返回学生的姓名及所在的专业的函数get_dept。（带有OUT参数）,SQL CREATE OR REPLACE FUNCTION get_dept 2 (v_sno IN varchar2, v_nam

42、e OUT varchar2) 3 return varchar2 4 AS 5 v_sdept student.stu_dept%TYPE; 6 BEGIN 7 select stu_name, stu_dept into v_name,v_sdept 8 from student 9 where stu_no=v_sno; 10 return v_sdept; 11 EXCEPTION 12 WHEN no_data_found THEN 13 RAISE_APPLICATION_ERROR(-20000,学号不存在); 14 END;,存储过程、函数是存储在数据字典中的对象，它们是属于某

43、一数据库用户的。用户对其所拥有的对象可以进行任何操作，其他用户在被授予了合适的权限以后也可以访问这些对象。 如果调试正确的存储过程没有进行授权，那就只有建立者本人才可以运行。所以作为应用系统的一部分的存储过程也必须进行授权才能达到要求。 可以用GRANT命令来进行存储过程的运行授权。 GRANT语法： GRANT system_privilege | role TO user | role | PUBLIC WITH ADMIN OPTION GRANT object_privilege | ALL column ON schema.object FROM user | role | PUBL

44、IC WITH GRANT OPTION,3.8.3 过程和函数的安全性,【例 29】假定表student是用户scott的私有表，用户personal是开发者，最终用户是green。现要求green只能通过personal创建的过程add_stu存取scott的student表。该过程插入学生的记录。如何实现？ （1）首先在scott用户环境下，为用户personal授于对student表操作所需的相应权限。 SQL grant select,insert,update,delete ON student to personal; 授权成功。,注意：如果某个用户没有权限来创建存储过程，则需要

45、DBA将创建过程的权限授予某用户。由于personal用户没有创建存储过程的权限，必须以DBA的身份为personal用户建立创建存储过程的权限。否则personal用户没法创建存储过程的权限。,（2）personal拥有这些权限后，就可以建立存储过程。 SQL CREATE OR REPLACE PROCEDURE add_stu 2 (v_sno scott.student.stu_no%TYPE, v_sname scott.student.stu_name%TYPE) 3 IS 4 e_integrity EXCEPTION; 5 pragma exception_init(e_int

46、egrity,-2291); 6 BEGIN 7 insert into scott.student(stu_no,stu_name) 8 values(v_sno,v_sname); 9 EXCEPTION 10 WHEN dup_val_on_index THEN 11 RAISE_APPLICATION_ERROR(-20000,学号不能重复); 12 WHEN e_integrity THEN 13 RAISE_APPLICATION_ERROR(-20001,学号不存在); 14 END; 15 /,（3）进行授权 SQL grant execute on add_stu to gr

47、een; 授权成功。 （4）Green用户就可以对personal用户所建的存储过程调用了。示例如下： SQL exec personal.add_stu(20026121,李琳); PL/SQL 过程已成功完成。,提高数据的安全性与完整性 利用安全性的权限来控制那些没有足够权限用户对数据库的间接访问。通过把相关联的表的操作集中到一起，保证对这些相关联的表执行一致的操作，或者任何操作都不做。 改善操作性能 多个用户使用同一个SQL语句时，只需做一次语法分析，只在编译时进行语法分析，运行时不再重做，可以直接调用缓存中的编译代码。,3.8.4 过程和函数的优点,39触发器,触发器是许多关系数据库系

48、统都提供的一项技术。在oracle系统里，触发器类似过程和函数，都有声明，执行和异常处理过程的PL/SQL块。 触发器在数据库里以独立的对象存储，它与存储过程不同的是存储过程通过其它程序来启动运行或直接启动运行，而触发器是由一个事件来启动运行，触发器是当某个事件发生时自动地隐式运行，并且触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。 在Oracle里，触发器事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。,DML 触发器 Oracle可以在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行

49、触发。 替代触发器 在Oracle里，不能直接对由两个以上的表建立的视图进行操作，所以给出了替代触发器。 系统触发器 系统触发器是在Oracle数据库系统的事件中进行触发，如Oracle系统的启动与关闭等。 管理触发器 Oracle 提供了显示触发器信息、禁止触发器 和激活触发器等功能。,3.9.1 触发器类型,创建触发器的一般语法是： CREATE OR REPLACETRIGGER 触发器名 BEFORE|AFTER event ON table_reference FOR EACH ROW WHEN trigger_condition trigger_body;,3.9.2DML触发器,

50、建立BEFORE语句触发器 【例3- 30】 建立一个行级触发器，当选课表被删除一条记录时，把被删除记录写到选课表删除日志表中去。,（1）首先创建一个日志表sc.his SQL create table SC_his( 2 Sno char(8) , 3 Cno char(3), 4 Grade number(3); 表已创建。 （2）创建一个行级触发器 。 SQL create or replace trigger scott.del_SC 2 before delete on scott.SC for each row 3 begin 4 - 将修改前数据插入到日志记录表 del_emp,

51、 以供监督使用。 5 insert into sc_his( sno,cno,grade) 6 values( :old.stu_no, :old.cou_no, :old.grade); 6 end; 8 /,（3）测试： SQL delete sc 2 where stu_no=20026102 and cou_no=a03; 已删除 1 行。 SQL select * from sc_his; SNO CNO GRADE - - - 20026102 a03 89 在行级触发器中，在列名前加上:old标识符标识该列变化前的值，加上:new标识符标识变化后的值。,使用条件谓词 ORACLE

52、 提供三个参数 INSERTING，UPDATEING，DELETING 用于判断触发了哪些操作。谓词行为如下： INSERTING：如果触发语句是 INSERT 语句，则为TRUE,否则为FALSE UPDATING：如果触发语句是 UPDATE语句，则为TRUE,否则为FALSE DELETING：如果触发语句是 DELETE 语句，则为TRUE,否则为FALSE 【例3- 31】 创建一个包含多个触发器事件的触发器，禁止工作人员在星期六及星期日插入、删除或更改雇员的信息。,SQL create or replace trigger tri_emp 2 before insert or update or delete on emp 3 begin 4 if to_char(sysdate,DY,nls_date_language=AMERICAN) IN (SAT,SUN) then 5 case 6 when inserting then 7 raise_application_error(-