Oracle数据库实用教程第三章 PL／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语句发送给数据库服务器来执行，再将结果返回给执行端。例如，如果应用程序需要取得学生的成绩，那么可以建立函数实现该项功能。SQLcreatefunctionget_grade1(snochar,cnochar)2returnnumberis3V_gradenumber(3);4begin5selectgrade6intoV_grade7fromsc8wherestu_no=snoandcou_no=cno;9returnV_grade;10end;11/函数已创建。SQLvarv_gradenumberSQLexec:v_grade:=get_grade1('20026101','a02')PL/SQL过程已成功完成。SQLprintv_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块SQLsetserveroutputonSQLbegin2dbms_output.put_line('Welcome!');3end;4/Welcome!PL/SQL过程已成功完成。注意：当使用dbms_output.包输出数据或消息时，必须要将SQL*Plus的环境变量serveroutput设置为on.【例3-2】包含定义部分和执行部分的PL/SQL块SQLDECLARE2v_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】常量定义SQLdeclare2piconstantnumber(9):=3.1415926;3begin4commit;5end;6/PL/SQL过程已成功完成。3.4.2基本数据类型变量PL/SQL中常用的基本数据类型3.4.3基本数据类型变量的定义方法变量名类型标识符[notnull]:=值;【例3-5】程序定义了名为age的数字型变量，长度为3，初始值为26SQLdeclare2v_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字段类型是一致的。SQLDeclare2v_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表结构相同。SQLDECLARE2myrecordstudent%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_integerSQLSETSERVEROUTPUTONSQLDeclare2TypeArray_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的嵌套表类型，它可以用于存储多个雇员信息。SQLcreateorreplacetypeemp_objasobject(2namevarchar2(10),3salarynumber(6,2),4hiredatedate);5/类型已创建。SQLcreateorreplacetypeemp_arrayistableofemp_obj;2/类型已创建。SQLcreatetabledepartment(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的方法：SQLdeclare2typesname_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其它语句;ENDI