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

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进行编程，将大量数据处理的应用放在进行编程，将大量数据处理的应用放在服务器端来执行，省去了数据在网上的传输时间。服务器端来执行，省

2、去了数据在网上的传输时间。 适合于客户环境适合于客户环境由于由于PL/SQL分为数据库分为数据库PL/SQL部分和工具部分和工具PL/SQL。对于客户端来说，对于客户端来说，PL/SQL可以嵌套到相应的工具中，可以嵌套到相应的工具中，客户端程序可以执行本地包含客户端程序可以执行本地包含PL/SQL部分，也可以向部分，也可以向服务发服务发SQL命令或激活服务器端的命令或激活服务器端的PL/SQL程序运行。程序运行。3.2 运行运行PL/SQL程序程序 PL/SQL程序的运行是通过程序的运行是通过Oracle中的一个引擎中的一个引擎来进行的。这个引擎可能在来进行的。这个引擎可能在Oracle的服务

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

4、5 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 numberSQL exec :v_grade:=get_grade1(20026101,a02)PL/SQL 过程已成功完成。过程已成功完成。SQL print v_grade3.3 PL/SQL块结构块结构 PL/SQL程序由三个块组成，即程序由三个块组成，即 声明部分、执行部分、异常声明部分、执行部分、异常处理部分。处理部分。

5、PL/SQL块的结构如下：块的结构如下：Declare /* 声明部分声明部分: 在此在此 声明声明PL/SQL用到的变量用到的变量,类型及光标类型及光标 */Begin /* 执行部分执行部分: 过程及过程及SQL 语句语句 , 即程序的主要部分即程序的主要部分 */Exception /* 执行异常部分执行异常部分: 错误处理错误处理 */End;其中其中 执行部分是必须的。而执行部分是必须的。而END则是则是PL/SQL 块的块的结束标记。结束标记。需要注意的是需要注意的是DECLARE，BEGIN，EXCEPTION后后面没有分号（；），而面没有分号（；），而END后则必须要带有分号。

6、后则必须要带有分号。PL/SQL标识符的命名规则：标识符的命名规则： 标识符的最大长度是标识符的最大长度是30个字符，包括字母、数字、个字符，包括字母、数字、$、_、# ；不可包含；不可包含保留字；要以字来打头；不能和同一块中的表中的保留字；要以字来打头；不能和同一块中的表中的字段名一样。字段名一样。【例【例3-13-1】只包含执行部分的只包含执行部分的PL/SQLPL/SQL块块SQL set SQL set serveroutputserveroutput on onSQL beginSQL begin 2 2 dbmsdbms_output.put_line(Welcome!);_out

7、put.put_line(Welcome!); 3 end; 3 end; 4 / 4 /Welcome!Welcome!PL/SQL PL/SQL 过程已成功完成。过程已成功完成。 注意：当使用注意：当使用dbmsdbms_output._output.包输出数据或消息时，必须包输出数据或消息时，必须要将要将SQLSQL* *PlusPlus的环境变量的环境变量serveroutputserveroutput 设置为设置为on.on.【例【例3-2】包含定义部分和执行部分的包含定义部分和执行部分的PL/SQL块块SQL DECLARE 2 v_sname VARCHAR(10); 3 BEG

8、IN 4 select stu_name INTO v_sname FROM student 5 WHERE stu_no=&no; 6 dbms_output.put_line(学生姓名：学生姓名：|v_sname); 7 END; 8 /输入输入 no 的值的值: 20026101原值原值 5: WHERE stu_no=&no;新值新值 5: WHERE stu_no=20026101;学生姓名：李勇学生姓名：李勇PL/SQL 过程已成功完成。过程已成功完成。注意：该例中当执行该注意：该例中当执行该PL/SQL时，会根据输入的学号显时，会根据输入的学号显示学生姓名。为了临

9、时存放姓名，就必须定义变量。示学生姓名。为了临时存放姓名，就必须定义变量。 &no为为SQL*Plus的替代变量。的替代变量。3.4 PL/SQL基本语法基本语法3.4.1 常量与变量常量与变量 定义常量的语法格式：定义常量的语法格式：常量名常量名 constant 类型标识符类型标识符 not null:=值值; 常量包括后面的变量名都必须以字母开头，不能常量包括后面的变量名都必须以字母开头，不能有空格，不能超过有空格，不能超过30个字符长度，同时不能和保个字符长度，同时不能和保留字同名，常（变）量名称不区分大小写，在字留字同名，常（变）量名称不区分大小写，在字母后面可以带数字或特殊

10、字符。括号内的母后面可以带数字或特殊字符。括号内的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中常用的基本数据类型中常用的基本数据类型3.4.3 基本数据类型变量的定义方法基本数据类型变量的定义方法 变量名变量名 类型标识符类型标识符 not

11、 null:=值值;【例【例3-5】 程序定义了名为程序定义了名为age的数字型变量，长度为的数字型变量，长度为3，初始值为初始值为26SQL 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定义方法。这样当定义方法。这样当数据表的字段类型修改后，数据

12、表的字段类型修改后，PL/SQL程序中相应变量的程序中相应变量的类型也自动修改。类型也自动修改。【例【例3-6】该程序定义了名为该程序定义了名为 v_sname的变量，其类型的变量，其类型和和 student据表中的据表中的 stu_name字段类型是一致的。字段类型是一致的。SQL Declare 2 v_sname student.stu_name%type; 3 begin 4 commit; 5 end; 6 /PL/SQL 过程已成功完成。过程已成功完成。 自定义记录类型变量自定义记录类型变量很多结构化程序设计语言都提供了记录类型的数据类很多结构化程序设计语言都提供了记录类型的数据类

13、型，型，在在PL/SQL中，也支持将多个基本数据类型捆绑在中，也支持将多个基本数据类型捆绑在一起的记录数据类型。一起的记录数据类型。【例例3-7】程序代码定义了名为程序代码定义了名为 stu_record_type的记的记录类型，该记录类型由字符型的录类型，该记录类型由字符型的sno、字符型的字符型的name和整型的和整型的age基本类型变量组成，基本类型变量组成，stu_record是该类是该类型的变量，引用记录型变量的方法是型的变量，引用记录型变量的方法是“记录变量名记录变量名.基基本类型变量名本类型变量名”。使用使用%rowtype属性定义记录变量属性定义记录变量使用使用%type可以使

14、变量获得字段的数据类型，使用可以使变量获得字段的数据类型，使用%rowtype可以使变量获得整个记录的数据类型。该属可以使变量获得整个记录的数据类型。该属性可以基于表或视图定义记录变量。为了简化表或视图性可以基于表或视图定义记录变量。为了简化表或视图所有列数据的处理，应该使用该属性定义记录变量。所有列数据的处理，应该使用该属性定义记录变量。【例【例3-8】执行下列执行下列PL/SQL程序，程序定义了名为程序，程序定义了名为myrecord的复合类型变量，与的复合类型变量，与student表结构相同。表结构相同。SQL DECLARE 2 myrecord student%rowtype; 3

15、BEGIN 4 select * 5 into myrecord 6 from student 7 where stu_no=&no; 8 dbms_output.put_line(姓名姓名:|myrecord.stu_name); 9 dbms_output.put_line(年龄年龄:|myrecord.stu_age); 10 dbms_output.put_line(性别性别:|myrecord.stu_sex); 11 dbms_output.put_line(专业专业:|myrecord.stu_dept); 12 EXCEPTION 13 WHEN NO_DATA_FOU

16、ND THEN 14 dbms_output.put_line(请输入正确的学号请输入正确的学号!); 15 END; 16 /3.4.5 PL/SQL集合类型集合类型 索引表索引表(PL/SQL表表)PL/SQL表与其他过程化语言表与其他过程化语言(如如C语言语言)的一维数组类似。的一维数组类似。需要注意的是，高级语言数组的下标不能为负，但需要注意的是，高级语言数组的下标不能为负，但PL/SQL 表的下标可以为负值；高级语言数组的元素个表的下标可以为负值；高级语言数组的元素个数有限制，而数有限制，而PL/SQL 表的元素个数没有限制，并且其表的元素个数没有限制，并且其下标没有上下限。现下标没

17、有上下限。现PL/SQL表需要创建一个数据类型并表需要创建一个数据类型并另外进行变量说明。表类型变量和数据表是有区别的，另外进行变量说明。表类型变量和数据表是有区别的，定义表类型变量的语法如下：定义表类型变量的语法如下：Type Is Table Of Index by SET SERVEROUTPUT ONSQL 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) :

18、= 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 / 嵌套表嵌套表嵌套表是嵌在一张表中记录的表。对保存嵌套表的表中的嵌套表是嵌在一张表中记录的表。对保存嵌套表的表中的每一列都可以创建一张存储表。嵌套表的每一行都存储在每一列都可以创建一张存储表。嵌套表的每一行都存储在主表外的存储表中。其格式：主表外的存储表中。其格式： type 嵌套表名嵌套表名 is table of 元素类型元素类型 not null;嵌套表（嵌套表（Nes

19、ted Table）类似于高级语言中的数组。需要类似于高级语言中的数组。需要注意的是，高级语言数组和嵌套表的下标都不能为负值，注意的是，高级语言数组和嵌套表的下标都不能为负值，高级语言数组的元素个数有限制，而嵌套表的元素个数没高级语言数组的元素个数有限制，而嵌套表的元素个数没有限制。有限制。当在表列中使用嵌套表时，必须首先使用当在表列中使用嵌套表时，必须首先使用CREATE TYPE语句建立嵌套表类型。该嵌套表类型被存储在数据字典中语句建立嵌套表类型。该嵌套表类型被存储在数据字典中(user_type)。【例【例3-11】为雇员信息建立对象类型为雇员信息建立对象类型emp_obj，而而emp_

20、array是基于是基于emp_obj的嵌套表类型，它可以用于的嵌套表类型，它可以用于存储多个雇员信息。存储多个雇员信息。SQL create or 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 numbe

21、r(2), 3 dname varchar2(10), 4 employee emp_array 5 ) nested table employee store as employee;表已创建。表已创建。create table 语句中包含有语句中包含有nested table子句，指明将用子句，指明将用来存放嵌套表行的存储表的名字为来存放嵌套表行的存储表的名字为employee。而且，对而且，对此存储表不能直接进行访问，必须通过主表才能访问引存此存储表不能直接进行访问，必须通过主表才能访问引存储表中的数据储表中的数据. 存储表是系统生成的表，它用来存储嵌套存储表是系统生成的表，它用来存储嵌

22、套表中的实际数据，这些数据不是和表中其他列的数据共同表中的实际数据，这些数据不是和表中其他列的数据共同存储的，而是被单独存放的。存储的，而是被单独存放的。 变长数组变长数组(VARRAY)VARRAY也是一种用于处理也是一种用于处理PL/SQL 数组的数据类型，客数组的数据类型，客观存在也可以作为表列的数据类型使用。该数据类型与高观存在也可以作为表列的数据类型使用。该数据类型与高级语言数组非常类似，其元素下标以级语言数组非常类似，其元素下标以1开始，并且元素的开始，并且元素的最大个数是有限制的。定义变长数组的格式：最大个数是有限制的。定义变长数组的格式： type 类型名类型名 is varr

23、y（最大尺寸最大尺寸) 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(

24、lin); 4 begin 5 select stu_name into sname_table(1) from student 6 where stu_no=&no; 7 dbms_output.put_line(学生姓名学生姓名:|sname_table(1); 8 end; 9 /输入输入 no 的值的值: 20026102原值原值 6: where stu_no=&no;新值新值 6: where stu_no=20026102;学生姓名学生姓名:刘晨刘晨PL/SQL 过程已成功完成。过程已成功完成。3.5 PL/SQL 处理流程处理流程 在在PL/SQL程序中，要使程

25、序能按照逻辑进行处程序中，要使程序能按照逻辑进行处理，除了有些语句是理，除了有些语句是SQL语句外，还必须有能进语句外，还必须有能进行逻辑控制的语句。行逻辑控制的语句。PL/SQL 也不例外，它不仅也不例外，它不仅可以嵌入可以嵌入SQL语句，而且还支持条件分支语句语句，而且还支持条件分支语句（IF，CASE）、）、循环语句（循环语句（LOOP）。）。 格式：格式：IF THENPL/SQL 和和 SQL语句语句;ELSE其它语句其它语句;ELSIF THEN其它语句其它语句;END IF;3.5.1 条件分支语句条件分支语句【例【例 3-12】判断两个整数变量的大小，输出不同的结果。判断两个整

26、数变量的大小，输出不同的结果。SQL set serveroutput onSQL 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(number

27、1大于大于number2); 13 end if; 14 end; 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语句语句在在CAS

28、E语句中使用多种条件比较语句中使用多种条件比较 格式：格式： CASE WHEN THEN 语句语句1；WHEN THEN 语句语句1；WHEN THEN 语句语句1；ELSE 语句语句n+1; END CASE; 基本循环基本循环Loop要执行的语句要执行的语句;exit when condition;end loop;当使用基本循环时，无论是否满足条件，语句至少会当使用基本循环时，无论是否满足条件，语句至少会被执行一次。当被执行一次。当condition为为TURE时，会退出循环，时，会退出循环，并执行并执行END LOOP后的相应操作。后的相应操作。3.5.3 循环语句循环语句【例例 3

29、-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; 8 end loop; 9 end; 10 /PL/SQL 过程已成功完成。过程已成功完成。 WHILE 循环循环格式：格式：while loop 要执行的语句要执行的语句;end loop;只有条件为真时，

30、才会执行循环体内的语句。只有条件为真时，才会执行循环体内的语句。 FOR循环循环 格式：格式： FOR 循环控制变量循环控制变量 IN REVERSE 下界值下界值 上界值上界值 LOOP statement1; statement2; END LOOP; 当使用当使用FOR循环时，每次循环时循环控制变量会自循环时，每次循环时循环控制变量会自动增一；如果指定动增一；如果指定REVERSE选项，那么每次循环时选项，那么每次循环时循环控制变量会自动减一。循环控制变量会自动减一。3.6 异常处理异常处理 一个优秀的程序都应该能够正确处理各种出错情一个优秀的程序都应该能够正确处理各种出错情况，并尽可能

31、从错误中恢复。况，并尽可能从错误中恢复。Oracle 提供异常情提供异常情况况(EXCEPTION)和异常处理和异常处理(EXCEPTION HANDLER)来实来实现错误处理。现错误处理。 虽然在虽然在PL/SQL编程中，异常处理不是必须的，编程中，异常处理不是必须的，但建议编程人员要养成在但建议编程人员要养成在PL/SQL编程中指定相编程中指定相应的异常。应的异常。 异常处理是用来处理正常执行过程中未预料的事异常处理是用来处理正常执行过程中未预料的事件，异常处理包括预定义的错误和自定义错误。件，异常处理包括预定义的错误和自定义错误。PL/SQL程序块一旦产生异常而没有指出如何处程序块一旦产

32、生异常而没有指出如何处理时理时,程序就会自动终止整个程序运行。程序就会自动终止整个程序运行。EXCEPTION when exception1 then statement1;when exception2 then statement2; .when others then statement;END;其中：异常处理可以按任意次序排列其中：异常处理可以按任意次序排列,但但 Others 必须必须放在最后。放在最后。3.6.1 异常处理概念异常处理概念 两种类型的异常：用户定义两种类型的异常：用户定义(user_define) 异常和异常和预定义预定义 ( predefined )异常异常。

33、当使用预定义异常处理时，应该了解当使用预定义异常处理时，应该了解PL/SQL 块块的常见运行错误，并掌握与之相关的预定义异常的常见运行错误，并掌握与之相关的预定义异常处理。处理。3.6.2预定义的异常处理预定义的异常处理 可以使用可以使用RAISE_APPLICATION_ERROR 创建自己的错误创建自己的错误处理。其语法如下：处理。其语法如下：RAISE_APPLICATION_ERROR(error_number,error_message,keep_errors ) ;其中：其中：error_number 是从是从 20,000 到到 20,999 之间的参数，之间的参数， error

34、_message 是相应的提示信息是相应的提示信息( set serveroutput onSQL 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_cursor into v_sno, v_sn

35、ame; 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 游标不被程序打开和关闭。游标不被程序打开和关闭。当一个当一个

36、DML语句执行时，语句执行时，PL/SQL内部打开一个游标，语内部打开一个游标，语句的结果被保存在句的结果被保存在4个游标属性中。个游标属性中。SQL%FOUNDSQL%NOTFOUNDSQL%ROWCOUNTSQL%ISOPEN游标是一块包含有查询信息的内存空间。在执行游标是一块包含有查询信息的内存空间。在执行DML语语句，游标被打开；当语句完成时，游标被关闭。句，游标被打开；当语句完成时，游标被关闭。3.7.2 隐式游标隐式游标【例例 3-20】 更新学生表，如果找到学号为更新学生表，如果找到学号为20028104的学生更新学生的年龄，否则往学生表中插入该学生的的学生更新学生的年龄，否则往

37、学生表中插入该学生的记录。记录。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(20028104,李滨李滨,19,男男,信息信息); 8 END IF; 9 commit; 10 END; 11 / 游标游标FOR循环是在循环是在PL/SQL 块中使用游标最简单的方式，块中使用游标最简单的方式，简化了对游标的处理。当使用游标简化了对游标的

38、处理。当使用游标FOR循环时，循环时，Oracle 会会隐含地打开游标、提取游标数据并关闭游标。语法如下：隐含地打开游标、提取游标数据并关闭游标。语法如下： FOR record_name IN cursor_name LOOP Ststement1; Ststement2; END LOOP;其中：其中：record_name是是Oracle 隐含定义的记录变量名。隐含定义的记录变量名。当使用游标开发当使用游标开发PL/SQL 应用程序时，为了简化程序代应用程序时，为了简化程序代码，建议大家使用游标码，建议大家使用游标FOR循环。循环。3.7.3 游标游标FOR循环循环【例例 3-21】给课

39、程名为给课程名为数据库原理数据库原理的所有学生的成绩加的所有学生的成绩加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 upda

40、te sc 12 set grade=grade+2 13 WHERE CURRENT OF sc1_cursor; 14 end if; 15 end loop; 16 end; 17 / ORACLE编写的程序一般分为两类：编写的程序一般分为两类：存储过程：是可以完成一定功能的程序叫存储过程；存储过程：是可以完成一定功能的程序叫存储过程；函数：是在使用时给出一个或多个值，处理完后返回一个函数：是在使用时给出一个或多个值，处理完后返回一个或多个结果的程序叫函数；或多个结果的程序叫函数；这两类程序都存放在这两类程序都存放在Oracle数据库字典中。数据库字典中。3.8存储过程和函数存储过程和函

41、数 与其它的数据库系统一样，与其它的数据库系统一样，Oracle的存储过程是的存储过程是用用PL/SQL语言编写的能完成一定处理功能的存储语言编写的能完成一定处理功能的存储在数据库字典中的程序。在数据库字典中的程序。创建过程语法创建过程语法: CREATE OR REPLACE PROCEDURE 过程名过程名 (参数名参数名 IN | IN OUT 数据类型数据类型.) IS | AS PL/SQL块块3.8.1 存储过程存储过程 建立无参数的存储过程建立无参数的存储过程【例例 22】以下过程用于输出当前系统日期和时间。】以下过程用于输出当前系统日期和时间。SQL CREATE OR REP

42、LACE 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_time;19-7月月 -05 09.08.36.312000000 下午下午 +08:00PL/S

43、QL 过程已成功完成。过程已成功完成。方法二：使用方法二：使用call命令调用过程命令调用过程SQL call data_time();20-7月月 -05 09.24.59.902000000 上午上午 +08:00调用完成。调用完成。 建立带有建立带有IN参数的存储过程参数的存储过程建立存储过程时，可以通过使用输入参数，将应用程序建立存储过程时，可以通过使用输入参数，将应用程序的数据传递到过程中，的数据传递到过程中，如果不指定参数模式，则默认是输入参数，如果不指定参数模式，则默认是输入参数，可以使用可以使用IN关键字显示地定义输入参数。关键字显示地定义输入参数。下面以建立为选课表下面以建立

44、为选课表SC插入数据的存储过程插入数据的存储过程add_sc为为例，说明建立带有输入参数的过程的方法。例，说明建立带有输入参数的过程的方法。【例例 3-23】建立为选课表建立为选课表SC插入数据的存储过程插入数据的存储过程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,-229

45、1); 6 BEGIN 7 insert into sc(stu_no,cou_no,grade) 8 values(v_sno,v_cno,v_grade); 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 / 建立建立OUT参数的存储过程参数的存储过程过程不仅可以用

46、来执行特定操作，而且也可以用于输出过程不仅可以用来执行特定操作，而且也可以用于输出数据，在过程中输出数据是使用数据，在过程中输出数据是使用OUT或或IN OUT 参数来参数来完成的，当定义输出参数时，必须提供完成的，当定义输出参数时，必须提供OUT关键字。关键字。【例例 3-24】 建立用于输出某学生某门课的成绩的存储过建立用于输出某学生某门课的成绩的存储过程程sc_gradeSQL CREATE OR REPLACE PROCEDURE sc_grade 2 ( v_sno IN sc.stu_no%TYPE, 3 v_cno IN sc.cou_no%TYPE, 4 v_grade OUT

47、 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 OUT参数的存储过程参数的存储过程定义过程时，不仅可以指定定义过程时，不仅可以指定IN和和OUT参数，也可以指定参数，也可以指定IN OUT参数。参数。

48、IN OUT参数也称为输入输出参数，当使用这种参数时，参数也称为输入输出参数，当使用这种参数时，在调用过程之前需要通过变量给该参数传递数据。在调在调用过程之前需要通过变量给该参数传递数据。在调用结束后，用结束后，Oracle 会通过该变量将过程结果传递给应会通过该变量将过程结果传递给应用程序。用程序。【例例 3-25】 将一个将一个7位数字的电话号码（如位数字的电话号码（如2217889转转换成换成8 位数字的电话号码。转换规则：第一个数字为位数字的电话号码。转换规则：第一个数字为2，前面加上前面加上5 ，其余的加上，其余的加上6。SQL CREATE OR REPLACE PROCEDURE

49、 telephone 2 (v_phone_no IN OUT varchar2) 3 IS 4 BEGIN 5 if substr(v_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语句编语句编写而成。与存储过程不同的是函数必须返回某些值，而存写而成。与存储过程不同的是函数必须返回某些值，而存储过程可以不返回任何值。创建函数的语法如下：储过程可以不返

50、回任何值。创建函数的语法如下：CREATE OR REPLACE FUNCTION 函数名函数名 (argment in| in out TYPE, argment in | out | in out TYPE,. RETURN datatype IS | AS PL/SQL 块；块；3.8.2 函数函数 建立无参数的函数建立无参数的函数当建立函数时，函数可以带有参数，也可以不带参数。当建立函数时，函数可以带有参数，也可以不带参数。【例例 3-26】建立用于显示当前数据库用户的函数。（不带】建立用于显示当前数据库用户的函数。（不带任何参数）任何参数） SQL CREATE OR REPLACE

51、 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参数的函数参数的函数建立函数时，通过使用输入参数，可以将应用程序的数建立函数时，通过使用输入参数，可以将应用程序的数据传递到函数中，最终通过执行函数可以将结果返回到据传递到函数中，最终通过执行函数可以将结果返回到应用程序中。应用程序中。【例例 3-27】创建函数

52、】创建函数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

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

54、名及所在的专业的函数get_dept。（。（带有带有OUT参数）参数）SQL CREATE OR REPLACE FUNCTION get_dept 2 (v_sno IN varchar2, v_name 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 1

55、2 WHEN no_data_found THEN 13 RAISE_APPLICATION_ERROR(-20000,学号不存在学号不存在); 14 END; 存储过程、函数是存储在数据字典中的对象，它们是属于某存储过程、函数是存储在数据字典中的对象，它们是属于某一数据库用户的。用户对其所拥有的对象可以进行任何操作，一数据库用户的。用户对其所拥有的对象可以进行任何操作，其他用户在被授予了合适的权限以后也可以访问这些对象。其他用户在被授予了合适的权限以后也可以访问这些对象。 如果调试正确的存储过程没有进行授权，那就只有建立者本如果调试正确的存储过程没有进行授权，那就只有建立者本人才可以运行。所

56、以作为应用系统的一部分的存储过程也必人才可以运行。所以作为应用系统的一部分的存储过程也必须进行授权才能达到要求。须进行授权才能达到要求。 可以用可以用GRANT命令来进行存储命令来进行存储过程的运行授权。过程的运行授权。 GRANT语法：语法：GRANT system_privilege | role TO user | role | PUBLICWITH ADMIN OPTIONGRANT object_privilege | ALL column ON schema.objectFROM user | role | PUBLIC WITH GRANT OPTION3.8.3 过程和函数的安

57、全性过程和函数的安全性 【例例 29】假定表】假定表student是用户是用户scott的私有表，用户的私有表，用户personal是开发者，最终用户是是开发者，最终用户是green。现要求现要求green只能通过只能通过personal创建的过程创建的过程add_stu存取存取scott的的student表。该过程表。该过程插入学生的记录。如何实现？插入学生的记录。如何实现？（1）首先）首先在在scott用户环境下，为用户用户环境下，为用户personal授于对授于对student表操作所需的相应权限。表操作所需的相应权限。SQL grant select,insert,update,del

58、ete ON student to personal;授权成功。授权成功。注意：如果某个用户没有权限来创建存储过程，则需要注意：如果某个用户没有权限来创建存储过程，则需要DBA将创建过程的权限授予某用户。由于将创建过程的权限授予某用户。由于personal用户用户没有创建存储过程的权限，必须没有创建存储过程的权限，必须以以DBA的身份为的身份为personal用户建立创建存储过程的权限。否则用户建立创建存储过程的权限。否则personal用户没法创建存储过程的权限。用户没法创建存储过程的权限。（2）personal拥有这些权限后，就可以建立存储过程。拥有这些权限后，就可以建立存储过程。SQL

59、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_integrity,-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 TH

60、EN 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 green;授权成功。授权成功。（4）Green用户就可以对用户就可以对personal用户所建的存储过用户所建的存储过程调用了。示例如下：程调用了。示例如下：SQL exec personal.add_stu(20026121,李琳李琳);PL/SQL 过程已成功完成。过程已成功完