PLSQL程序设计经典教程课件

第三章PL/SQL程序设计第三章PL/SQL程序设计1主要内容3.1PL/SQL的优点：3.2运行PL/SQL程序3.3PL/SQL块结构3.4PL/SQL基本语法3.5PL/SQL处理流程3.6异常处理3.7游标3.8存储过程和函数3.9触发器主要内容3.1PL/SQL的优点：2

3.1PL/SQL的优点有利于客户/服务器环境应用的运行

使用PL/SQL进行编程，将大量数据处理的应用放在服务器端来执行，省去了数据在网上的传输时间。适合于客户环境由于PL/SQL分为数据库PL/SQL部分和工具PL/SQL。对于客户端来说，PL/SQL可以嵌套到相应的工具中，客户端程序可以执行本地包含PL/SQL部分，也可以向服务发SQL命令或激活服务器端的PL/SQL程序运行。3.1PL/SQL的优点有利于客户/服务器环境应用的运33.2运行PL/SQL程序PL/SQL程序的运行是通过Oracle中的一个引擎来进行的。这个引擎可能在Oracle的服务器端，也可能在Oracle应用开发的客户端。引擎执行PL/SQL中的过程性语句，然后将SQL语句发送给数据库服务器来执行，再将结果返回给执行端。例如，如果应用程序需要取得学生的成绩，那么可以建立函数实现该项功能。3.2运行PL/SQL程序PL/SQL程序的运行是通过Or4SQL>createfunctionget_grade1(snochar,cnochar)2returnnumberis3V_gradenumber(3);4begin5selectgrade6intoV_grade7fromsc8wherestu_no=snoandcou_no=cno;9returnV_grade;10end;11/函数已创建。SQL>varv_gradenumberSQL>exec:v_grade:=get_grade1('20026101','a02')PL/SQL过程已成功完成。SQL>printv_gradeSQL>createfunctionget_gra53.3PL/SQL块结构PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。PL/SQL块的结构如下：Declare

/*声明部分:在此声明PL/SQL用到的变量,类型及光标*/Begin

/*执行部分:过程及SQL语句,即程序的主要部分*/Exception

/*执行异常部分:错误处理*/End;3.3PL/SQL块结构PL/SQL程序由三个块组成，即6其中执行部分是必须的。而END则是PL/SQL块的结束标记。需要注意的是DECLARE，BEGIN，EXCEPTION后面没有分号（；），而END后则必须要带有分号。PL/SQL标识符的命名规则：标识符的最大长度是30个字符，包括字母、数字、$、_、#；不可包含保留字；要以字来打头；不能和同一块中的表中的字段名一样。其中执行部分是必须的。而END则是PL/SQL块的结束标7【例3-1】只包含执行部分的PL/SQL块SQL>setserveroutputonSQL>begin2dbms_output.put_line('Welcome!');3end;4/Welcome!PL/SQL过程已成功完成。

注意：当使用dbms_output.包输出数据或消息时，必须要将SQL*Plus的环境变量serveroutput设置为on.【例3-1】只包含执行部分的PL/SQL块8【例3-2】包含定义部分和执行部分的PL/SQL块SQL>DECLARE2v_snameVARCHAR(10);3BEGIN4selectstu_nameINTOv_snameFROMstudent5WHEREstu_no=&no;6dbms_output.put_line('学生姓名：'||v_sname);7END;8/输入no的值:20026101原值5:WHEREstu_no=&no;新值5:WHEREstu_no=20026101;学生姓名：李勇PL/SQL过程已成功完成。注意：该例中当执行该PL/SQL时，会根据输入的学号显示学生姓名。为了临时存放姓名，就必须定义变量。&no为SQL*Plus的替代变量。【例3-2】包含定义部分和执行部分的PL/SQL块93.4PL/SQL基本语法3.4PL/SQL基本语法103.4.1常量与变量定义常量的语法格式：常量名constant类型标识符[notnull]:=值;常量包括后面的变量名都必须以字母开头，不能有空格，不能超过30个字符长度，同时不能和保留字同名，常（变）量名称不区分大小写，在字母后面可以带数字或特殊字符。括号内的notnull为可选参数，若选用，表明该常（变）量不能为空值。3.4.1常量与变量定义常量的语法格式：11【例3-4】常量定义SQL>declare2piconstantnumber(9):=3.1415926;3begin4commit;5end;6/PL/SQL过程已成功完成。【例3-4】常量定义123.4.2基本数据类型变量PL/SQL中常用的基本数据类型3.4.2基本数据类型变量PL/SQL中常用的基本数据类133.4.3基本数据类型变量的定义方法变量名类型标识符[notnull]:=值;【例3-5】程序定义了名为age的数字型变量，长度为3，初始值为26SQL>declare2v_agenumber(3):=26;3begin4commit;5end;6/PL/SQL过程已成功完成。3.4.3基本数据类型变量的定义方法变量名类型标识符143.4.4复合数据类型变量使用%type定义变量为了让PL/SQL中变量的类型和数据表中的字段的数据类型一致，Oracle9i提供了%type定义方法。这样当数据表的字段类型修改后，PL/SQL程序中相应变量的类型也自动修改。3.4.4复合数据类型变量使用%type定义变量15【例3-6】该程序定义了名为v_sname的变量，其类型和student据表中的stu_name字段类型是一致的。SQL>Declare2v_snamestudent.stu_name%type;3begin4commit;5end;6/PL/SQL过程已成功完成。【例3-6】该程序定义了名为v_sname的变量，其类型和16自定义记录类型变量很多结构化程序设计语言都提供了记录类型的数据类型，在PL/SQL中，也支持将多个基本数据类型捆绑在一起的记录数据类型。【例3-7】程序代码定义了名为stu_record_type的记录类型，该记录类型由字符型的sno、字符型的name和整型的age基本类型变量组成，stu_record是该类型的变量，引用记录型变量的方法是“记录变量名.基本类型变量名”。自定义记录类型变量17使用%rowtype属性定义记录变量使用%type可以使变量获得字段的数据类型，使用%rowtype可以使变量获得整个记录的数据类型。该属性可以基于表或视图定义记录变量。为了简化表或视图所有列数据的处理，应该使用该属性定义记录变量。【例3-8】执行下列PL/SQL程序，程序定义了名为myrecord的复合类型变量，与student表结构相同。使用%rowtype属性定义记录变量18SQL>DECLARE2myrecordstudent%rowtype;3BEGIN4select*5intomyrecord6fromstudent7wherestu_no=&no;8dbms_output.put_line('姓名:'||myrecord.stu_name);9dbms_output.put_line('年龄:'||myrecord.stu_age);10dbms_output.put_line('性别:'||myrecord.stu_sex);11dbms_output.put_line('专业:'||myrecord.stu_dept);12EXCEPTION13WHENNO_DATA_FOUNDTHEN14dbms_output.put_line('请输入正确的学号!');15END;16/SQL>DECLARE193.4.5PL/SQL集合类型索引表(PL/SQL表)PL/SQL表与其他过程化语言(如C语言)的一维数组类似。需要注意的是，高级语言数组的下标不能为负，但PL/SQL表的下标可以为负值；高级语言数组的元素个数有限制，而PL/SQL表的元素个数没有限制，并且其下标没有上下限。现PL/SQL表需要创建一个数据类型并另外进行变量说明。表类型变量和数据表是有区别的，定义表类型变量的语法如下：Type<用户自定义的类型名称>IsTableOf<索引表元素数据类型>Indexby<索引表元素下标的数据类型;3.4.5PL/SQL集合类型索引表(PL/SQL表)20【例3-9】在索引表中使用数据类型Binary_integerSQL>SETSERVEROUTPUTONSQL>Declare2 TypeArray_typeis3 TableOfNumber4 IndexbyBinary_Integer;5 My_ArrayArray_type;6Begin7ForIIn1..10Loop8My_Array(I):=I*2;9EndLoop;10ForIIn1..10Loop11Dbms_Output.Put_line(To_char(My_Array(I)));12EndLoop;13End;14/【例3-9】在索引表中使用数据类型Binary_integ21嵌套表嵌套表是嵌在一张表中记录的表。对保存嵌套表的表中的每一列都可以创建一张存储表。嵌套表的每一行都存储在主表外的存储表中。其格式： type嵌套表名istableof元素类型[notnull];嵌套表（NestedTable）类似于高级语言中的数组。需要注意的是，高级语言数组和嵌套表的下标都不能为负值，高级语言数组的元素个数有限制，而嵌套表的元素个数没有限制。当在表列中使用嵌套表时，必须首先使用CREATETYPE语句建立嵌套表类型。该嵌套表类型被存储在数据字典中(user_type)。嵌套表22【例3-11】为雇员信息建立对象类型emp_obj，而emp_array是基于emp_obj的嵌套表类型，它可以用于存储多个雇员信息。SQL>createorreplacetypeemp_objasobject(2namevarchar2(10),3salarynumber(6,2),4hiredatedate);5/类型已创建。SQL>createorreplacetypeemp_arrayistableofemp_obj;2/类型已创建。【例3-11】为雇员信息建立对象类型emp_obj，而emp23SQL>createtabledepartment(2depnonumber(2),3dnamevarchar2(10),4employeeemp_array5)nestedtableemployeestoreasemployee;表已创建。createtable语句中包含有nestedtable子句，指明将用来存放嵌套表行的存储表的名字为employee。而且，对此存储表不能直接进行访问，必须通过主表才能访问引存储表中的数据.存储表是系统生成的表，它用来存储嵌套表中的实际数据，这些数据不是和表中其他列的数据共同存储的，而是被单独存放的。SQL>createtabledepartment(24变长数组(VARRAY)VARRAY也是一种用于处理PL/SQL数组的数据类型，客观存在也可以作为表列的数据类型使用。该数据类型与高级语言数组非常类似，其元素下标以1开始，并且元素的最大个数是有限制的。定义变长数组的格式：

type类型名isvarry（最大尺寸)of元素类型[notnull];

当在PL/SQL块中使用varray变量时，必须首先使用其构造方法来初始化varray变量，然后才能在PL/SQL块内引用varray元素。下面举例说明使用VARRAY的方法：变长数组(VARRAY)25SQL>declare2typesname_table_typeisvarray(10)ofstudent.stu_name%TYPE;3sname_tablesname_table_type:=sname_table_type('lin');4begin5selectstu_nameintosname_table(1)fromstudent6wherestu_no=&no;7dbms_output.put_line('学生姓名:'||sname_table(1));8end;9/输入no的值:20026102原值6:wherestu_no=&no;新值6:wherestu_no=20026102;学生姓名:刘晨PL/SQL过程已成功完成。PLSQL程序设计经典教程课件263.5PL/SQL处理流程在PL/SQL程序中，要使程序能按照逻辑进行处理，除了有些语句是SQL语句外，还必须有能进行逻辑控制的语句。PL/SQL也不例外，它不仅可以嵌入SQL语句，而且还支持条件分支语句（IF，CASE）、循环语句（LOOP）。3.5PL/SQL处理流程在PL/SQL程序中，要使程序27格式：IF<布尔表达式>THEN PL/SQL和SQL语句;[ELSE 其它语句;][ELSIF<其它布尔表达式>THEN 其它语句;ENDIF;3.5.1条件分支语句格式：3.5.1条件分支语句28【例3-12】判断两个整数变量的大小，输出不同的结果。SQL>setserveroutputonSQL>declare2number1integer:=80;3number2integer:=90;4begin5ifnumber1<=number2then6ifnumber1=number2then7dbms_output.put_line('number1等于number2');8else9dbms_output.put_line('number1小于number2');10endif;11else12dbms_output.put_line('number1大于number2');13endif;14end;15/PLSQL程序设计经典教程课件29从Oracle9i开始，不仅可以使用IF语句，也可以使用CASE语句来执行多重条件分支操作。使用CASE语句更加简捷，而且执行效率也更好。在CASE语句中使用单一选择符进行等值比较格式：CASE<条件选择符> WHEN<条件值的表达式1>THEN语句1； WHEN<条件值的表达式2>THEN语句1； …… WHEN<条件值的表达式n>THEN语句1； [ELSE语句n+1;]ENDCASE;3.5.2CASE语句从Oracle9i开始，不仅可以使用IF语句，也可以使用CA30在CASE语句中使用多种条件比较格式：CASE WHEN<条件表达式1>THEN语句1； WHEN<条件表达式2>THEN语句1； …… WHEN<条件表达式n>THEN语句1； [ELSE语句n+1;]ENDCASE;在CASE语句中使用多种条件比较31基本循环Loop 要执行的语句; exit[whencondition];endloop;当使用基本循环时，无论是否满足条件，语句至少会被执行一次。当condition为TURE时，会退出循环，并执行ENDLOOP后的相应操作。3.5.3循环语句基本循环3.5.3循环语句32【例3-15】为stu2表插入5条数据(20046101…20046105)。SQL>createtablestu2(snoint);表已创建。SQL>declare2iint:=20048101;3begin4loop5insertintostu2values(i);6exitwheni=20048105;7i:=i+1;8endloop;9end;10/PL/SQL过程已成功完成。【例3-15】为stu2表插入5条数据(200461033WHILE循环格式：while<布尔表达式>loop要执行的语句;endloop;只有条件为真时，才会执行循环体内的语句。

WHILE循环34FOR循环

格式：

FOR循环控制变量IN[REVERSE]下界值…上界值LOOP

statement1;statement2;ENDLOOP;

当使用FOR循环时，每次循环时循环控制变量会自动增一；如果指定REVERSE选项，那么每次循环时循环控制变量会自动减一。FOR循环353.6异常处理一个优秀的程序都应该能够正确处理各种出错情况，并尽可能从错误中恢复。Oracle提供异常情况(EXCEPTION)和异常处理(EXCEPTIONHANDLER)来实现错误处理。虽然在PL/SQL编程中，异常处理不是必须的，但建议编程人员要养成在PL/SQL编程中指定相应的异常。3.6异常处理一个优秀的程序都应该能够正确处理各种出错情36异常处理是用来处理正常执行过程中未预料的事件，异常处理包括预定义的错误和自定义错误。PL/SQL程序块一旦产生异常而没有指出如何处理时,程序就会自动终止整个程序运行。EXCEPTIONwhenexception1thenstatement1;whenexception2thenstatement2;……..whenothersthenstatement;END;其中：异常处理可以按任意次序排列,但Others必须放在最后。3.6.1异常处理概念异常处理是用来处理正常执行过程中未预料的事件，异常处理包括预37两种类型的异常：用户定义(user_define)异常和预定义(predefined)异常。当使用预定义异常处理时，应该了解PL/SQL块的常见运行错误，并掌握与之相关的预定义异常处理。3.6.2预定义的异常处理两种类型的异常：用户定义(user_define)异常和预38可以使用RAISE_APPLICATION_ERROR创建自己的错误处理。其语法如下：RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors]);其中：error_number是从–20,000到–20,999之间的参数，error_message是相应的提示信息(<2048字节)，keep_errors为可选，如果keep_errors=TRUE,则新错误将被添加到已经引发的错误列表中。如果keep_errors=FALSE(缺省),则新错误将替换当前的错误列表。3.6.3用户定义的异常处理可以使用RAISE_APPLICATION_ERROR创建393.7游标在PL/SQL中处理查询语句和数据操纵语句时，Oracle必须分配一片叫上下文(contextarea)的区域来处理所必需的信息，其中包括：要处理的行的数目；一个指针，指向语句被分析以后的表示形式；查询的活动集(activeset)。游标是一个指向上下文的句柄(handle)或指针。通过游标，PL/SQL可以控制上下文区和处理语句时上下文区会发生的事情。3.7游标在PL/SQL中处理查询语句和数据操纵语句时，40在PL/SQL程序中定义的游标称作显式游标。显式游标包括:定义游标打开游标提取数据（也称为推进游标）关闭游标3.7.1显式游标在PL/SQL程序中定义的游标称作显式游标。显式游标包括:341定义游标cursor游标名[（参数名1数据类型[,参数2数据类型…]）]is查询语句;其中：游标名是一个没有定义的PL/SQL变量，用户不能给游标名赋值。参数名是一个在SELECT语句的WHERE条件中要用到的参数。SELECT语句是一个不含INTO子句的SELECT语句，允许带WHERE、ORDERBY、GROUPBY等子句。定义游标42打开游标打开游标是在SELECT语句的所有输入参数都接受值之后，执行游标所对应的SELECT语句，将其查询结果放入工作区，指针指向工作区的首部。假如在打开游标时查询没有返回行，PL/SQL没有触发错误，可以在取数据（FETCH）之后检测游标的状态。open游标名称[（实际参数值1[实际参数值2…]）];打开游标43提取数据在打开游标之后，SELECT语句的结果被临时存放到游标结果集中，为了处理结果的数据，需要使用FETCH语句提取游标数据。格式一：fetch游标名称into变量列表;格式二：

fetch游标名称bulkcollectinto集合变量表[LIMTrows];关闭游标close游标名称;提取数据44【例3-16】显示信息专业的所有学生学号及姓名。（使用FETCH….INTO语句）SQL>setserveroutputonSQL>declare2cursorstu_cursoris3selectstu_no,stu_namefromstudentwherestu_deptlike'信息';4v_snamevarchar2(10);5v_snochar(8);6begin7ifnotstu_cursor%ISOPENthen8openstu_cursor;9endif;10loop11fetchstu_cursorintov_sno,v_sname;12exitwhenstu_cursor%NOTFOUND;13dbms_output.put_line(v_sno||','||v_sname);14endloop;15closestu_cursor;16end;17/【例3-16】显示信息专业的所有学生学号及姓名。（使用F45所有的SQL语句在上下文区内部都是可执行的，因此都有一个游标指向上下文区，此游标就是所谓的SQL游标(SQLcursor)，即隐式游标。与显式游标不同，SQL游标不被程序打开和关闭。当一个DML语句执行时，PL/SQL内部打开一个游标，语句的结果被保存在4个游标属性中。SQL%FOUNDSQL%NOTFOUNDSQL%ROWCOUNTSQL%ISOPEN游标是一块包含有查询信息的内存空间。在执行DML语句，游标被打开；当语句完成时，游标被关闭。3.7.2隐式游标所有的SQL语句在上下文区内部都是可执行的，因此都有一个游46【例3-20】更新学生表，如果找到学号为’20028104’的学生更新学生的年龄，否则往学生表中插入该学生的记录。SQL>BEGIN2UPDATEstudent3SETstu_age=194WHEREstu_no='20028104';5--如果更新没有匹配则插入一新行6IFSQL%NOTFOUNDTHEN7INSERTintoStudentVALUES('20028104','李滨',19,'男','信息');8ENDIF;9commit;10END;11/【例3-20】更新学生表，如果找到学号为’200281047游标FOR循环是在PL/SQL块中使用游标最简单的方式，简化了对游标的处理。当使用游标FOR循环时，Oracle会隐含地打开游标、提取游标数据并关闭游标。语法如下：

FORrecord_nameINcursor_nameLOOPStstement1;Ststement2;ENDLOOP;其中：record_name是Oracle隐含定义的记录变量名。当使用游标开发PL/SQL应用程序时，为了简化程序代码，建议大家使用游标FOR循环。3.7.3游标FOR循环游标FOR循环是在PL/SQL块中使用游标最简单的方式，简48【例3-21】给课程名为‘数据库原理’的所有学生的成绩加5分。SQL>declare2cursorsc1_cursor3is4select*5fromscforupdate;6begin7dbms_output.put_line('课程号学号成绩');8forsc_recinsc1_cursorloop9ifsc_rec.cou_no='a01'then10dbms_output.put_line(sc_rec.cou_no||''||sc_rec.stu_no||''||sc_rec.grade);11updatesc12setgrade=grade+213WHERECURRENTOFsc1_cursor;14endif;15endloop;16end;17/【例3-21】给课程名为‘数据库原理’的所有学生的成绩加549ORACLE编写的程序一般分为两类：存储过程：是可以完成一定功能的程序叫存储过程；函数：是在使用时给出一个或多个值，处理完后返回一个或多个结果的程序叫函数；这两类程序都存放在Oracle数据库字典中。3.8存储过程和函数ORACLE编写的程序一般分为两类：3.8存储过程和函数50与其它的数据库系统一样，Oracle的存储过程是用PL/SQL语言编写的能完成一定处理功能的存储在数据库字典中的程序。创建过程语法:

CREATE[ORREPLACE]PROCEDURE过程名[(参数名[{IN|INOUT}]数据类型….)]{IS|AS}PL/SQL块3.8.1存储过程与其它的数据库系统一样，Oracle的存储过程是用PL/SQ51建立无参数的存储过程【例22】以下过程用于输出当前系统日期和时间。SQL>CREATEORREPLACEPROCEDUREdata_time2IS3BEGIN4dbms_output.put_line(systimestamp);5END;6/过程已创建。建立无参数的存储过程52建立了存储过程data_time之后，就可以调用该过程。在SQL*Plus环境中调用过程有两种方法：方法一：使用execute命令调用过程SQL>setserveroutputon;SQL>execdata_time;19-7月-0509.08.36.312000000下午+08:00PL/SQL过程已成功完成。方法二：使用call命令调用过程SQL>calldata_time();20-7月-05002000000上午+08:00调用完成。建立了存储过程data_time之后，就可以调用该过程。在S53建立带有IN参数的存储过程建立存储过程时，可以通过使用输入参数，将应用程序的数据传递到过程中，如果不指定参数模式，则默认是输入参数，可以使用IN关键字显示地定义输入参数。下面以建立为选课表SC插入数据的存储过程add_sc为例，说明建立带有输入参数的过程的方法。建立带有IN参数的存储过程54【例3-23】建立为选课表SC插入数据的存储过程add_scv。

SQL>CREATEORREPLACEPROCEDUREadd_scv2(v_snosc.stu_no%TYPE,v_cnosc.cou_no%TYPE,v_gradesc.grade%TYPE)3IS4e_integrityEXCEPTION;5pragmaexception_init(e_integrity,-2291);6BEGIN7insertintosc(stu_no,cou_no,grade)8values(v_sno,v_cno,v_grade);9EXCEPTION10WHENdup_val_on_indexTHEN11RAISE_APPLICATION_ERROR(-20000,'学号与课程号不能重复');12WHENe_integrityTHEN13RAISE_APPLICATION_ERROR(-20001,'学号或课程号不存在');14END;15/【例3-23】建立为选课表SC插入数据的存储过程add_s55建立OUT参数的存储过程过程不仅可以用来执行特定操作，而且也可以用于输出数据，在过程中输出数据是使用OUT或INOUT参数来完成的，当定义输出参数时，必须提供OUT关键字。建立OUT参数的存储过程56【例3-24】建立用于输出某学生某门课的成绩的存储过程sc_gradeSQL>CREATEORREPLACEPROCEDUREsc_grade2(v_snoINsc.stu_no%TYPE,3v_cnoINsc.cou_no%TYPE,4v_gradeOUTsc.grade%TYPE)5IS6BEGIN7selectgradeintov_grade8fromsc9wherestu_no=v_snoandcou_no=v_cno;10EXCEPTION11WHENno_data_foundTHEN12RAISE_APPLICATION_ERROR(-20000,'学号或课程号不存在');13END;14/【例3-24】建立用于输出某学生某门课的成绩的存储过程s57建立带INOUT参数的存储过程定义过程时，不仅可以指定IN和OUT参数，也可以指定INOUT参数。INOUT参数也称为输入输出参数，当使用这种参数时，在调用过程之前需要通过变量给该参数传递数据。在调用结束后，Oracle会通过该变量将过程结果传递给应用程序。建立带INOUT参数的存储过程58【例3-25】将一个7位数字的电话号码（如2217889转换成8位数字的电话号码。转换规则：第一个数字为2，前面加上5，其余的加上6。SQL>CREATEORREPLACEPROCEDUREtelephone2(v_phone_noINOUTvarchar2)3IS4BEGIN5ifsubstr(v_phone_no,1,1)='2'then6v_phone_no:='5'||v_phone_no;7else8v_phone_no:='6'||v_phone_no;9endif;10END;【例3-25】将一个7位数字的电话号码（如221788959Oracle的函数是一个独有的对象，它也是由PL/SQL语句编写而成。与存储过程不同的是函数必须返回某些值，而存储过程可以不返回任何值。创建函数的语法如下：CREATE[ORREPLACE]FUNCTION函数名[(argment[{in|inout}]TYPE,argment[{in|out|inout}]TYPE,….]RETURNdatatype{IS|AS}PL/SQL块；3.8.2函数Oracle的函数是一个独有的对象，它也是由PL/SQL语句60建立无参数的函数当建立函数时，函数可以带有参数，也可以不带参数。【例3-26】建立用于显示当前数据库用户的函数。（不带任何参数）

SQL>CREATEORREPLACEFUNCTIONget_user2returnvarchar23AS4v_uservarchar2(100);5BEGIN6selectusernameintov_userfromuser_users;7returnv_user;8END;9/函数已创建。建立无参数的函数61建立带IN参数的函数建立函数时，通过使用输入参数，可以将应用程序的数据传递到函数中，最终通过执行函数可以将结果返回到应用程序中。【例3-27】创建函数get_grade，实现输入学生的学号及课程号返回该门课的成绩。如果学号及课程号存在，显示该信息。（带有IN参数）建立带IN参数的函数62SQL>CREATEORREPLACEFUNCTIONget_grade2(v_snoINvarchar2,v_cnoINvarchar2)3returnnumber4AS5v_gradesc.grade%TYPE;6EGIN7selectgradeintov_grade8fromsc9wherestu_no=v_snoandcou_no=v_cno;10returnv_grade;11EXCEPTION12WHENno_data_foundTHEN13RAISE_APPLICATION_ERROR(-20000,'学号或课程号不存在');14END;SQL>CREATEORREPLACEFUNCTIO63建立带OUT参数的函数一般情况下，函数只有一个返回值，如果希望使用函数同时返回多个值，则需要使用输出参数OUT。【例28】输入学生的学号，建立用于返回学生的姓名及所在的专业的函数get_dept。（带有OUT参数）建立带OUT参数的函数64SQL>CREATEORREPLACEFUNCTIONget_dept2(v_snoINvarchar2,v_nameOUTvarchar2)3returnvarchar24AS5v_sdeptstudent.stu_dept%TYPE;6BEGIN7selectstu_name,stu_deptintov_name,v_sdept8fromstudent9wherestu_no=v_sno;10returnv_sdept;11EXCEPTION12WHENno_data_foundTHEN13RAISE_APPLICATION_ERROR(-20000,'学号不存在');14END;PLSQL程序设计经典教程课件65存储过程、函数是存储在数据字典中的对象，它们是属于某一数据库用户的。用户对其所拥有的对象可以进行任何操作，其他用户在被授予了合适的权限以后也可以访问这些对象。如果调试正确的存储过程没有进行授权，那就只有建立者本人才可以运行。所以作为应用系统的一部分的存储过程也必须进行授权才能达到要求。可以用GRANT命令来进行存储过程的运行授权。GRANT语法：GRANTsystem_privilege|roleTOuser|role|PUBLIC[WITHADMINOPTION]GRANTobject_privilege|ALLcolumnONschema.objectFROMuser|role|PUBLICWITHGRANTOPTION3.8.3过程和函数的安全性存储过程、函数是存储在数据字典中的对象，它们是属于某一数据库66【例29】假定表student是用户scott的私有表，用户personal是开发者，最终用户是green。现要求green只能通过personal创建的过程add_stu存取scott的student表。该过程插入学生的记录。如何实现？（1）首先在scott用户环境下，为用户personal授于对student表操作所需的相应权限。SQL>grantselect,insert,update,deleteONstudenttopersonal;授权成功。【例29】假定表student是用户scott的私有表，用67注意：如果某个用户没有权限来创建存储过程，则需要DBA将创建过程的权限授予某用户。由于personal用户没有创建存储过程的权限，必须以DBA的身份为personal用户建立创建存储过程的权限。否则personal用户没法创建存储过程的权限。注意：如果某个用户没有权限来创建存储过程，则需要DBA将创建68（2）personal拥有这些权限后，就可以建立存储过程。SQL>CREATEORREPLACEPROCEDUREadd_stu2(v_snoscott.student.stu_no%TYPE,v_snamescott.student.stu_name%TYPE)3IS4e_integrityEXCEPTION;5pragmaexception_init(e_integrity,-2291);6BEGIN7insertintoscott.student(stu_no,stu_name)8values(v_sno,v_sname);9EXCEPTION10WHENdup_val_on_indexTHEN11RAISE_APPLICATION_ERROR(-20000,'学号不能重复');12WHENe_integrityTHEN13RAISE_APPLICATION_ERROR(-20001,'学号不存在');14END;15/（2）personal拥有这些权限后，就可以建立存储过程。69（3）进行授权SQL>grantexecuteonadd_stutogreen;授权成功。（4）Green用户就可以对personal用户所建的存储过程调用了。示例如下：SQL>execpersonal.add_stu('20026121','李琳');PL/SQL过程已成功完成。（3）进行授权70提高数据的安全性与完整性利用安全性的权限来控制那些没有足够权限用户对数据库的间接访问。通过把相关联的表的操作集中到一起，保证对这些相关联的表执行一致的操作，或者任何操作都不做。改善操作性能多个用户使用同一个SQL语句时，只需做一次语法分析，只在编译时进行语法分析，运行时不再重做，可以直接调用缓存中的编译代码。3.8.4过程和函数的优点提高数据的安全性与完整性3.8.4过程和函数的优点713．9触发器触发器是许多关系数据库系统都提供的一项技术。在oracle系统里，触发器类似过程和函数，都有声明，执行和异常处理过程的PL/SQL块。触发器在数据库里以独立的对象存储，它与存储过程不同的是存储过程通过其它程序来启动运行或直接启动运行，而触发器是由一个事件来启动运行，触发器是当某个事件发生时自动地隐式运行，并且触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。在Oracle里，触发器事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。3．9触发器触发器是许多关系数据库系统都提供的一项技术。在72DML触发器Oracle可以在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。替代触发器在Oracle里，不能直接对由两个以上的表建立的视图进行操作，所以给出了替代触发器。系统触发器系统触发器是在Oracle数据库系统的事件中进行触发，如Oracle系统的启动与关闭等。管理触发器Oracle提供了显示触发器信息、禁止触发器和激活触发器等功能。3.9.1触发器类型DML触发器3.9.1触发器类型73创建触发器的一般语法是：CREATE[ORREPLACE]TRIGGER触发器名[BEFORE|AFTER]eventONtable_reference[FOREACHROW[WHENtrigger_condition]]trigger_body;3.9.2DML触发器创建触发器的一般语法是：3.9.2DML触发器74建立BEFORE语句触发器【例3-30】建立一个行级触发器，当选课表被删除一条记录时，把被删除记录写到选课表删除日志表中去。建立BEFORE语句触发器75（1）首先创建一个日志表sc.hisSQL>createtableSC_his(2Snochar(8),3Cnochar(3),4Gradenumber(3));表已创建。（2）创建一个行级触发器。SQL>createorreplacetriggerscott.del_SC2beforedeleteonscott.SCforeachrow3begin4--将修改前数据插入到日志记录表del_emp,以供监督使用。5insertintosc_his(sno,cno,grade)6values(:old.stu_no,:old.cou_no,:old.grade);6end;8/（1）首先创建一个日志表sc.his76（3）测试：SQL>deletesc2wherestu_no='20026102'andcou_no='a03';已删除1行。SQL>select*fromsc_his;SNOCNOGRADE---------------------------------20026102a0389在行级触发器中，在列名前加上:old标识符标识该列变化前的值，加上:new标识符标识变化后的值。（3）测试：77使用条件谓词ORACLE提供三个参数INSERTING，UPDATEING，DELETING用于判断触发了哪些操作。谓词行为如下：INSERTING：如果触发语句是INSERT语句，则为TRUE,否则为FALSEUPDATING：如果触发语句是UPDATE语句，则为TRUE,否则为FALSEDELETING：如果触发语句是DELETE语句，则为TRUE,否则为FALSE【例3-31】创建一个包含多个触发器事件的触发器，禁止工作人员在星期六及星期日插入、删除或更改雇员的信息。使用条件谓词78SQL>createorreplacetriggertri_emp2beforeinsertorupdateordeleteonemp3begin4ifto_char(sysdate,'DY','nls_date_language=AMERICAN')IN('SAT','SUN')then5case6wh