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

第三章PL/SQL程序设计主要内容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的优点有利于客户/服务器环境应用的运行

使用PL/SQL进行编程，将大量数据处理的应用放在服务器端来执行，省去了数据在网上的传输时间。适合于客户环境由于PL/SQL分为数据库PL/SQL部分和工具PL/SQL。对于客户端来说，PL/SQL可以嵌套到相应的工具中，客户端程序可以执行本地包含PL/SQL部分，也可以向服务发SQL命令或激活服务器端的PL/SQL程序运行。3.2运行PL/SQL程序PL/SQL程序的运行是通过Oracle中的一个引擎来进行的。这个引擎可能在Oracle的服务器端，也可能在Oracle应用开发的客户端。引擎执行PL/SQL中的过程性语句，然后将SQL语句发送给数据库服务器来执行，再将结果返回给执行端。例如，如果应用程序需要取得学生的成绩，那么可以建立函数实现该项功能。SQL>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_grade3.3PL/SQL块结构PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。PL/SQL块的结构如下：Declare

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

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

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

注意：当使用dbms_output.包输出数据或消息时，必须要将SQL*Plus的环境变量serveroutput设置为on.【例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.4PL/SQL基本语法3.4.1常常量与变变量定义常量量的语法法格式：：常量名constant类类型标标识符[notnull]:=值;常量包括括后面的的变量名名都必须须以字母母开头，，不能有有空格，，不能超超过30个字符符长度，，同时不不能和保保留字同同名，常常（变））量名称称不区分分大小写写，在字字母后面面可以带带数字或或特殊字字符。括括号内的的notnull为为可选参参数，若若选用，，表明该该常（变变）量不不能为空空值。【例3-4】常常量定义义SQL>declare2piconstantnumber(9):=3.1415926;3begin4commit;5end;6/PL/SQL过过程已已成功完完成。3.4.2基本数据据类型变变量PL/SQL中中常用的的基本数数据类型型3.4.3基基本数数据类型型变量的的定义方方法变量名类类型标标识符[notnull]:=值;【例3-5】程程序定定义了名名为age的数数字型变变量，长长度为3，初始始值为26SQL>declare2v_agenumber(3):=26;3begin4commit;5end;6/PL/SQL过程程已成功完完成。3.4.4复合合数据类型型变量使用%type定义义变量为了让PL/SQL中变量的的类型和数数据表中的的字段的数数据类型一一致，Oracle9i提提供了%type定定义方法。。这样当数数据表的字字段类型修修改后，PL/SQL程序中中相应变量量的类型也也自动修改改。【例3-6】该程序序定义了名名为v_sname的变量量，其类型型和student据表中中的stu_name字段段类型是一一致的。SQL>Declare2v_snamestudent.stu_name%type;3begin4commit;5end;6/PL/SQL过程程已成功完完成。自定义记录录类型变量量很多结构化化程序设计计语言都提提供了记录录类型的数数据类型，，在PL/SQL中中，也支持持将多个基基本数据类类型捆绑在在一起的记记录数据类类型。【例3-7】程序代代码定义了了名为stu_record_type的记记录类型，，该记录类类型由字符符型的sno、字符符型的name和整整型的age基本类类型变量组组成，stu_record是该类型型的变量，，引用记录录型变量的的方法是““记录变量量名.基本本类型变量量名”。使用%rowtype属性定定义记录变变量使用%type可以以使变量获获得字段的的数据类型型，使用%rowtype可可以使变量量获得整个个记录的数数据类型。。该属性可可以基于表表或视图定定义记录变变量。为了了简化表或或视图所有有列数据的的处理，应应该使用该该属性定义义记录变量量。【例3-8】执行下下列PL/SQL程程序，程序序定义了名名为myrecord的复合合类型变量量，与student表结构构相同。SQL>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/3.4.5PL/SQL集合类型型索引表(PL/SQL表)PL/SQL表与其其他过程化化语言(如如C语言)的一维数数组类似。。需要注意意的是，高高级语言数数组的下标标不能为负负，但PL/SQL表的下下标可以为为负值；高高级语言数数组的元素素个数有限限制，而PL/SQL表的的元素个数数没有限制制，并且其其下标没有有上下限。。现PL/SQL表表需要创建建一个数据据类型并另另外进行变变量说明。。表类型变变量和数据据表是有区区别的，定定义表类型型变量的语语法如下：：Type<用户户自定义的的类型名称称>IsTableOf<索引引表元素数数据类型>Indexby<索引引表元素下下标的数据据类型;【例3-9】在索索引表中使使用数据类类型Binary_integerSQL>SETSERVEROUTPUTONSQL>Declare2TypeArray_typeis3TableOfNumber4IndexbyBinary_Integer;5My_ArrayArray_type;6Begin7ForIIn1..10Loop8My_Array(I):=I*2;9EndLoop;10ForIIn1..10Loop11Dbms_Output.Put_line(To_char(My_Array(I)));12EndLoop;13End;14/嵌套表嵌套表是嵌嵌在一张表表中记录的的表。对保保存嵌套表表的表中的的每一列都都可以创建建一张存储储表。嵌套套表的每一一行都存储储在主表外外的存储表表中。其格格式：type嵌嵌套表表名istableof元元素类型[notnull];嵌套表（NestedTable））类似于高高级语言中中的数组。。需要注意意的是，高高级语言数数组和嵌套套表的下标标都不能为为负值，高高级语言数数组的元素素个数有限限制，而嵌嵌套表的元元素个数没没有限制。。当在表列中中使用嵌套套表时，必必须首先使使用CREATETYPE语句建立立嵌套表类类型。该嵌嵌套表类型型被存储在在数据字典典中(user_type)。【例3-11】为雇雇员信息建建立对象类类型emp_obj，而emp_array是是基于emp_obj的嵌套套表类型，，它可以用用于存储多多个雇员信信息。SQL>createorreplacetypeemp_objasobject(2namevarchar2(10),3salarynumber(6,2),4hiredatedate);5/类型已创建建。SQL>createorreplacetypeemp_arrayistableofemp_obj;2/类型已创建建。SQL>createtabledepartment(2depnonumber(2),3dnamevarchar2(10),4employeeemp_array5)nestedtableemployeestoreasemployee;表已创创建。。createtable语语句句中包包含有有nestedtable子句句，指指明将将用来来存放放嵌套套表行行的存存储表表的名名字为为employee。而而且，，对此此存储储表不不能直直接进进行访访问，，必须须通过过主表表才能能访问问引存存储表表中的的数据据.存存储储表是是系统统生成成的表表，它它用来来存储储嵌套套表中中的实实际数数据，，这些些数据据不是是和表表中其其他列列的数数据共共同存存储的的，而而是被被单独独存放放的。。变长数数组(VARRAY)VARRAY也也是一一种用用于处处理PL/SQL数数组组的数数据类类型，，客观观存在在也可可以作作为表表列的的数据据类型型使用用。该该数据据类型型与高高级语语言数数组非非常类类似，，其元元素下下标以以1开开始，，并且且元素素的最最大个个数是是有限限制的的。定定义变变长数数组的的格式式：type类类型名名isvarry（（最大大尺寸寸)of元元素素类型型[notnull];当在PL/SQL块块中中使用用varray变量量时，，必须须首先先使用用其构构造方方法来来初始始化varray变变量，，然后后才能能在PL/SQL块块内引引用varray元元素。。下面面举例例说明明使用用VARRAY的方方法：：SQL>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过过程程已成成功完完成。。3.5PL/SQL处处理理流程程在PL/SQL程序序中，，要使使程序序能按按照逻逻辑进进行处处理，，除了了有些些语句句是SQL语句句外，，还必必须有有能进进行逻逻辑控控制的的语句句。PL/SQL也也不不例外外，它它不仅仅可以以嵌入入SQL语语句，，而且且还支支持条条件分分支语语句（（IF，CASE））、循循环语语句（（LOOP）。。格式：IF<布尔尔表达式式>THENPL/SQL和和SQL语语句;[ELSE其它语句句;][ELSIF<其其它布尔尔表达式式>THEN其它语句句;ENDIF;3.5.1条条件分支支语句【例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/从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语句句在CASE语句句中使用用多种条条件比较较格式：CASEWHEN<条件件表达式式1>THEN语语句句1；WHEN<条件件表达式式2>THEN语语句句1；……WHEN<条件件表达式式n>THEN语语句1；；[ELSE语语句n+1;]ENDCASE;基本循环Loop要执行的语句句;exit[whencondition];endloop;当使用基本循循环时，无论论是否满足条条件，语句至至少会被执行行一次。当condition为TURE时，，会退出循环环，并执行ENDLOOP后的相相应操作。3.5.3循循环语句【例3-15】为stu2表表插入5条数数据(20046101…20046105)。SQL>createtablestu2(snoint);表已创建。SQL>declare2iint:=20048101;3begin4loop5insertintostu2values(i);6exitwheni=20048105;7i:=i+1;8endloop;9end;10/PL/SQL过程已成成功完成。WHILE循循环格式：while<布尔表表达式>loop要执行的语句句;endloop;只有条件为真真时，才会执执行循环体内内的语句。FOR循环格式：FOR循循环控制变量量IN[REVERSE]下界值……上界值LOOPstatement1;statement2;ENDLOOP;当使用FOR循环时，每每次循环时循循环控制变量量会自动增一一；如果指定定REVERSE选项，，那么每次循循环时循环控控制变量会自自动减一。3.6异异常处理一个优秀的程程序都应该能能够正确处理理各种出错情情况，并尽可可能从错误中中恢复。Oracle提提供异常情情况(EXCEPTION)和异常处理(EXCEPTIONHANDLER)来实现错误处处理。虽然在PL/SQL编程程中，异常处处理不是必须须的，但建议议编程人员要要养成在PL/SQL编编程中指定相相应的异常。。异常处理是用用来处理正常常执行过程中中未预料的事事件，异常处处理包括预定定义的错误和和自定义错误误。PL/SQL程序块块一旦产生异异常而没有指指出如何处理理时,程序就就会自动终止止整个程序运运行。EXCEPTIONwhenexception1thenstatement1;whenexception2thenstatement2;……..whenothersthenstatement;END;其中：异常处处理可以按任任意次序排列列,但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之间间的参数，error_message是相应应的提示信信息(<2048字节)，keep_errors为为可选，如如果keep_errors=TRUE,则新错误误将被添加加到已经引引发的错误误列表中。。如果keep_errors=FALSE(缺省),则新错误误将替换当当前的错误误列表。3.6.3用户定定义的异常常处理3.7游游标在PL/SQL中中处理查询询语句和数数据操纵语语句时，Oracle必须须分配一片片叫上下文文(contextarea)的区域来来处理所必必需的信息息，其中包包括：要处理的行行的数目；；一个指针，，指向语句句被分析以以后的表示示形式；查询的活动动集(activeset)。游标是一个个指向上下下文的句柄柄(handle)或指针针。通过游游标，PL/SQL可以控控制上下文文区和处理理语句时上上下文区会会发生的事事情。在PL/SQL程序序中定义的的游标称作作显式游标标。显式游游标包括:定义游标打开游标提取数据（（也称为推推进游标））关闭游标3.7.1显式游游标定义游标cursor游游标名[（（参数名1数据类类型[,参参数2数数据类类型…]））]is查查询语句;其中：游标标名是一个个没有定义义的PL/SQL变变量，用用户不能给给游标名赋赋值。参数名是一一个在SELECT语句的WHERE条件中要要用到的参参数。SELECT语句是是一个不含含INTO子句的SELECT语句，，允许带WHERE、ORDERBY、GROUPBY等子子句。打开游标标打开游标标是在SELECT语语句的所所有输入入参数都都接受值值之后，，执行游游标所对对应的SELECT语语句，将将其查询询结果放放入工作作区，指指针指向向工作区区的首部部。假如如在打开开游标时时查询没没有返回回行，PL/SQL没没有触触发错误误，可以以在取数数据（FETCH）之之后检测测游标的的状态。。open游游标名称称[（实实际参数数值1[实际参参数值2…]））];提取数据在打开游标之之后，SELECT语句句的结果被临临时存放到游游标结果集中中，为了处理理结果的数据据，需要使用用FETCH语句提取游游标数据。格式一：fetch游游标名称称into变量量列表;格式二：fetch游游标名称称bulkcollectinto集合变变量表[LIMTrows];关闭游标close游游标名称称;【例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/所有的的SQL语语句句在上上下文文区内内部都都是可可执行行的，，因此此都有有一个个游标标指向向上下下文区区，此此游标标就是是所谓谓的SQL游标标(SQLcursor)，即即隐式式游标标。与与显式式游标标不同同，SQL游游标不不被程程序打打开和和关闭闭。当一个个DML语语句执执行时时，PL/SQL内内部打打开一一个游游标，，语句句的结结果被被保存存在4个游游标属属性中中。SQL%FOUNDSQL%NOTFOUNDSQL%ROWCOUNTSQL%ISOPEN游标是是一块块包含含有查查询信信息的的内存存空间间。在在执行行DML语语句，，游标标被打打开；；当语语句完完成时时，游游标被被关闭闭。3.7.2隐隐式游游标【例3-20】更更新新学生生表，，如果果找到到学号号为’’20028104’的的学生生更新新学生生的年年龄，，否则则往学学生表表中插插入该该学生生的记记录。。SQL>BEGIN2UPDATEstudent3SETstu_age=194WHEREstu_no='20028104';5--如果果更新没有有匹配则插插入一新行行6IFSQL%NOTFOUNDTHEN7INSERTintoStudentVALUES('20028104','李滨滨',19,'男','信息息');8ENDIF;9commit;10END;11/游标FOR循环是在在PL/SQL块块中使用游游标最简单单的方式，，简化了对对游标的处处理。当使使用游标FOR循环环时，Oracle会隐含含地打开游游标、提取取游标数据据并关闭游游标。语法法如下：FORrecord_nameINcursor_nameLOOPStstement1;Ststement2;ENDLOOP;其中：record_name是Oracle隐含定定义的记录录变量名。。当使用游标标开发PL/SQL应用程程序时，为为了简化程程序代码，，建议大家家使用游标标FOR循循环。3.7.3游标FOR循环环【例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/ORACLE编写的的程序一般般分为两类类：存储过程：：是可以完完成一定功功能的程序序叫存储过过程；函数：是在在使用时给给出一个或或多个值，，处理完后后返回一个个或多个结结果的程序序叫函数；；这两类程序序都存放在在Oracle数据据库字典中中。3.8存储储过程和函函数与其它的数数据库系统统一样，Oracle的存储储过程是用用PL/SQL语言言编写的能能完成一定定处理功能能的存储在在数据库字字典中的程程序。创建过程语语法:CREATE[ORREPLACE]PROCEDURE过程程名[(参数数名[{IN|INOUT}]数数据类类型….)]{IS|AS}PL/SQL块3.8.1存储储过程建立无参数数的存储过过程【例22】以下过过程用于输输出当前系系统日期和和时间。SQL>CREATEORREPLACEPROCEDUREdata_time2IS3BEGIN4dbms_output.put_line(systimestamp);5END;6/过程已创建建。建立了存储储过程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调用完成。。建立带有IN参数的的存储过程程建立存储过过程时，可可以通过使使用输入参参数，将应应用程序的的数据传递递到过程中中，如果不指定定参数模式式，则默认认是输入参参数，可以使用IN关键字字显示地定定义输入参参数。下面以建立立为选课表表SC插入入数据的存存储过程add_sc为例，，说明建立立带有输入入参数的过过程的方法法。【例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/建立OUT参数的存存储过程过程不仅可可以用来执执行特定操操作，而且且也可以用用于输出数数据，在过过程中输出出数据是使使用OUT或INOUT参参数来完完成的，当当定义输出出参数时，，必须提供供OUT关关键字。【例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/建立带INOUT参数的存存储过程定义过程时时，不仅可可以指定IN和OUT参数，，也可以指指定INOUT参参数。INOUT参数也也称为输入入输出参数数，当使用用这种参数数时，在调调用过程之之前需要通通过变量给给该参数传传递数据。。在调用结结束后，Oracle会通通过该变量量将过程结结果传递给给应用程序序。【例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;Oracle的函数数是一个独独有的对象象，它也是是由PL/SQL语语句编写而而成。与存存储过程不不同的是函函数必须返返回某些值值，而存储储过程可以以不返回任任何值。创创建函数的的语法如下下：CREATE[ORREPLACE]FUNCTION函函数名名[(argment[{in|inout}]TYPE,argment[{in|out|inout}]TYPE,…….]RETURNdatatype{IS|AS}PL/SQL块；；3.8.2函数建立无参数数的函数当建立函数数时，函数数可以带有有参数，也也可以不带带参数。【例3-26】建建立用于显显示当前数数据库用户户的函数。。（不带任任何参数））SQL>CREATEORREPLACEFUNCTIONget_user2returnvarchar23AS4v_uservarchar2(100);5BEGIN6selectusernameintov_userfromuser_users;7returnv_user;8END;9/函数已创建建。建立带IN参参数的函数建立函数时，，通过使用输输入参数，可可以将应用程程序的数据传传递到函数中中，最终通过过执行函数可可以将结果返返回到应用程程序中。【例3-27】创建函函数get_grade，实现输入入学生的学号号及课程号返返回该门课的的成绩。如果果学号及课程程号存在，显显示该信息。。（带有IN参数）SQL>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;建立带OUT参数的函数数一般情况下，，函数只有一一个返回值，，如果希望使使用函数同时时返回多个值值，则需要使使用输出参数数OUT。【例28】】输入学生生的学号，建建立用于返回回学生的姓名名及所在的专专业的函数get_dept。（带带有OUT参参数）SQL>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;存储过程、函函数是存储在在数据字典中中的对象，它它们是属于某某一数据库用用户的。用户户对其所拥有有的对象可以以进行任何操操作，其他用用户在被授予予了合适的权权限以后也可可以访问这些些对象。如果调试正确确的存储过程程没有进行授授权，那就只只有建立者本本人才可以运运行。所以作作为应用系统统的一部分的的存储过程也也必须进行授授权才能达到到要求。可可以用GRANT命令来来进行存储过过程的运行授授权。GRANT语语法：GRANTsystem_privilege|roleTOuser|role|PUBLIC[WITHADMINOPTION]GRANTobject_privilege|ALLcolumnONschema.objectFROMuser|role|PUBLICWITHGRANTOPTION3.8.3过过程和函函数的安全性性【例29】】假定表student是用户scott的私私有表，用户户personal是开开发者，最终终用户是green。现现要求green只能通通过personal创创建的过程add_stu存取scott的student表。该过过程插入学生生的记录。如如何实现？（1）首先在在scott用户环境下下，为用户personal授于对对student表操作作所需的相应应权限。SQL>grantselect,insert,update,deleteONstudenttopersonal;授权成功。注意：如果某某个用户没有有权限来创建建存储过程，，则需要DBA将创建过过程的权限授授予某用户。。由于personal用户没有创创建存储过程程的权限，必必须以DBA的身份为personal用户建建立创建存储储过程的权限限。否则personal用户没法法创建存储过过程的权限。。（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/（3）进行授授权SQL>grantexecuteonadd_stutogreen;授权成功。（4）Green用户就就可以对personal用户所建建的存储过程程调用了。示示例如下：SQL>execpersonal.add_stu('20026121','李琳琳');PL/SQL过程已成成功完成。提高数据的安安全性与完整整性利用安全性的的权限来控制制那些没有足足够权限用户户对数据库的的间接访问。。通过把相关关联的表的操操作集中到一一起，保证对对这些相关联联的表执行一一致的操作，，或者任何操操作都不做。。改善操作性能能多个用户使用用同一个SQL语句时，，只需做一次次语法分析，，只在编译时时进行语法分分析，运行时时不再重做，，可以直接调调用缓存中的的编译代码。。3.8.4过过程和函函数的优点3．9触发发器触发器是许多多关系数据库库系统都提供供的一项技术术。在oracle系统统里，触发器器类似过程和和函数，都有有声明，执行行和异常处理理过程的PL/SQL块块。触发器在数据据库里以独立立的对象存储储，它与存储储过程不同的的是存储过程程通过其它程程序来启动运运行或直接启启动运行，而而触发器是由由一个事件来来启动运行，，触发器是当当某个事件发发生时自动地地隐式运行，，并且触发器器不能接收参参数。所以运运行触发器就就叫触发或点点火（firing）。。在Oracle里，，触发发器事事件指指的是是对数数据库库的表表进行行的INSERT、、UPDATE及DELETE操作或或对视视图进进行类类似的的操作作。DML触触发器器Oracle可可以在在DML语语句进进行触触发，，可以以在DML操作作前或或操作作后进进行触触发，，并且且可以以对每每个行行或语语句操操作上上进行行触发发。替代触触发器器在Oracle里，，不能能直接接对由由两个个以上上的表表建立立的视视图进进行操操作，，所以以给出出了替替代触触发器器。系统触触发器器系统触触发器器是在在Oracle数据据库系系统的的事件件中进进行触触发，，如Oracle系系统的的启动动与关关闭等等。管理触触发器器Oracle提提供供了显显示触触发器器信息息、禁禁止触触发器器和和激活活触发发器等等功能能。3.9.1触触发发器类类型创建触触发器器的一一般语语法是是：CREATE[ORREPLACE]TRIGGER触触发发器名名[BEFORE|AFTER]eventONtable_reference[FOREACHROW[WHENtrigger_condition]]trigger_body;3.9.2DML触发发器建立BEFORE语语句触触发器器【例3-30】建建立立一个个行级级触发发器，，当选选课表表被删删除一一条记记录时时，把把被删删除记记录写写到选选课表表删除除日志志表中中去。。（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/（3）测试试：SQL>deletesc2wherestu_no='20026102'andcou_no='a03';已删除1行。SQL>select*fromsc_his;SNOCNOGRADE---------------------------------20026102a0389在行级触发发器中，在在列名前加加上:old标识符符标识该列列变化前的的值，加上上:new标识符标标识变化后后的值。使用条件谓谓词ORACLE提供供三个参数数INSERTING，UPDATEING，DELETING用于于判断触发发了哪些操操作。谓词词行为如下下：INSERTING：如果触触发语句是是INSERT语语句，则则为TRUE,否则则为FALSEUPDATING：：如果触发发语句是UPDATE语句句，则为TRUE,否则为FALSEDELETING：：如果触发发语句是DELETE语语句，则为为TRUE,否则为为FALSE【例3-31】创创建一个个包含多个个触发器事事件的触发发器，禁止止工作人员员在星期六六及星期日日插入、删删除或更改改雇员的信信息。SQL>createorreplacetriggertri_emp2beforeinsertorupdateordeleteonemp3begin4ifto_char(sysdate,'DY','nls_date_language=AMERICAN')IN('SAT','SUN')then5case6wheninsertingthen7raise_application_error(-20001,'不能在休休息日增加雇雇员');8whenupdatingthen9raise_application_error(-20001,'不