《Oracle 11g管理与编程基础 教学课件 ppt 作者 王鹏杰 王存睿 郑海旭 Chapt12 PLSQL高级》由会员分享，可在线阅读，更多相关《Oracle 11g管理与编程基础 教学课件 ppt 作者 王鹏杰 王存睿 郑海旭 Chapt12 PLSQL高级（46页珍藏版）》请在金锄头文库上搜索。
1、任课：* Email: *,大型数据库技术（Oracle11g）,欢迎全国读者加入教材/课程讨论QQ群：249449451 问题探讨、授课咨询请加作者QQ：535413184,复合数据类型 游标 异常处理 函数、过程、包等,PL/SQL高级,复合数据类型,记录数据类型 集合数据类型：主要是index-by表类型、数组类型。,记录数据类型,type 类型名 is record ( 字段1 字段1类型 not null:=表达式1; 字段2 字段2类型 not null :=表达式2; ),记录数据类型（续1）,type t_EmpRecord is record( empno number(4)
2、, ename varchar2(10), job varchar2(9) ); V_EmpInfo t_EmpRecord; 当引用记录中的一个字段时，用点标记，例如： V_EmpInfo.ename,记录之间相互赋值需要类型完全相同,记录数据类型（续2）,type t_StuRecord is record( id number(4), name varchar2(20), major varchar2(30) ); type t_StuRecord2 is record( id number(4), name varchar2(20), major varchar2(30) ); v_s
3、tudents t_stuRecord; v_students_copy t_stuRecord2; begin v_students.id:=1000; v_students.name:=赵伟; v_students.major:=计算机; v_students_copy:=v_students;,PL/SQL中的集合类似于其他3GL中的数组。“集合”有两种： index-by表（index-by table）。 变长数组（varrays）。,集合数据类型,为什么需要集合类型,Declare v_s# varchar2(4); v_sname varchar2(20); Begin sele
4、ct s#, sname into v_s#, v_sname from S; End; 如果查询语句返回值有多个，则会出现错误“exact fetch returns more than requested numbers of rows”。因为into子句中的变量不能处理多行数据。,index-by表,类似于普通程序设计语言中的数组概念。 声明index-by表的方法如下： Type tabletype IS TABLE OF type INDEX BY BINARY_INTEGER; 其中type可以是内置类型、用户定义的对象类型或者使用rowtype的表达式等,声明了类型和变量后，就可
5、以用以下语句使用index-by表中的单个元素： v_Name(index) 其中index是指表中的第几个元素。,declare TYPE Country_Name IS TABLE OF COUNTRMS.COUNTRIES_NAMETYPE INDEX BY BINARY_INTEGER；,下面是一个定义index-by表的代码实例：,V_Name Country_Name；,元素赋值,我们可以使用以下语句给表中的元素赋值：,BEGIN v_Name(1):=China ; v_Name(2):=USA ; END；,从语法上讲PL/SQL表与数组的处理方式类似。但是，表 的实现机制不同
6、于数组，一个PL/SQL表类似一个数据库表 有两列KEY和VALUE。KEY就是BINARY_INTEGER,BEGIN v_Name(1):=China; v _Name(-2):= USA; v_Name(5):=Italy ; END；,因为这个表中的每个元素都是一个记录，我们可以使用下面的语法引用这些字段： table(Index).field,DECLARE TYPE t_StudentTable IS TABLE OF studentsROWTYPE INDEX BY BINARY_INTEGER v_Students t_StudentTable； BEGIN SELECT *
7、INTO v_ Students（10001) FROM students WHERE Id10001; END；,V_Students(10001).first-name:=Larry； DBMS-OUTPUT.PUT-LINE（v_ Students(10001).first-name）,例子,Index by表的属性函数,变长数组（varrays）,可变数组的声明如下,TYPE type-name IS VARRAY|VARYING ARRAY (maximum-size) OF element_typeNOT Null;,type-name是可变数组的类型名 maximum-size是
8、指可变数组元素个数的最大值 Element_type是指数组元素的类型,数组元素的个数可以在maximum-size这个上限内变化，当元素个数超出了最大上限后，系统就会提示出错。,变长数组（续）,可变数组的存储机制与C或者Pascal的是完全一样的。 1.限制最大长度 2.是顺序存储的,游标,为了分次处理SQL语句返回的结果集，Oracle必须分配一片内存区域，这就是上下文区(context area) 。 游标（cursor）是一个指向上下文区的句柄（handle）或指针。,游标（续）,DECLARE v_FustName VARCHAR2(20）； v_LastName VARCHAR2(
9、20); CURSOR c _Students IS SELECT first name, last name、 FROM students; BEGIN OPEN c_Students; LOOP FETCH c_Students INTO v_FirstName, v_LastNarne; EXIT WHEN c_StudentsNOTFOUND; END LOOP; CLOSE c_Students; END;,游标的类型,显式游标和隐式游标。显式游标要声明(Declare)，在使用前要打开(Open)使用完毕要关闭（Close) 与循环结构结合的显式游标处理返回多于一行的SELECT语
10、句。如果SELECT语句只返回一行，那么可以用隐式游标。,declare v_id student.id%type; v_birthday student.birthday%type; cursor c_student is select id, birthday from student; begin open c_student; fetch c_student into v_id, v_birthday; while c_student%found loop if v_birthday is null then insert into temtable values(v_id,no bi
11、rthday); else update student set age=sysdate-v_birthday where id=v_id; end if; fetch c_student into v_id, v_birthday; end loop; close c_student; end;,游标的属性,% found和%notfound检验游标检索数据是否成功。 %rowcount返回目前位置由游标返回的行的数目。 %isopen用来判断相关的游标是否被打开了。,游标的属性（续2）,1：游标还没有打开，没有任何活动集合与它关联 2：尽管已经打开，但是还是没有执行提取操作 3：已经有一次
12、提取操作 4：已经完成第二次提取操作 5：前一个提取语句没有返回任何数据，因为活动行中的所有 的行都已经被检索出来 6：游标已经关闭，所有关于活动集的信息都被清除,隐式游标,游标for循环,declare frame varchar2(10); lname varchar2(30); ssec_num varchar2(8); region_number_in person.region_number%type:=000356; cursor region_cur is select first_name,last_name,ssn from person where region_numbe
13、rregion_number_in; begin for region_rec in region_cur loop if ssec_num is null then insert into e_mg values(Pin_in,no ssnum); else insert into e_tab values(pin_in,sysdate); end if; end loop; end;,open,fetch,check,close,异常处理,什么是异常情态 异常处理处理的是运行时错误，异常分为预定义异常和用户自定义异常。 声明异常情态 异常情态在块的声明部分进行声明，在块的执行部分进行触发，
14、在块的异常部分进行处理。 用户定义类型异常情态 例： Declare e_toomanystudents exception;,异常处理（预定义的异常）,异常处理（预定义的异常续）,SQL declare 2 my_comm m%type; 3 begin 4 select comm into my_comm from emp; 5 exception 6 when no_data_found then 7 dbms_output.put_line(no data!); 8 when too_many_rows then 9 dbms_output.put_line(too many rows
15、!); -触发了too_many_rows异常 10 when others then 11 null; 12 end; 13 / too many rows! PL/SQL procedure successfully completed.,异常处理（自定义异常）,用户定义异常 my_exception exception; 在程序体中触发异常 raise my_exception； 在异常处理部分处理异常 when my_exception then .,SQL declare 2 e_toomuchComm exception; -声明自定义异常 3 v_sal emp.sal%type; 4 v_comm m%type; 5 begin 6 select sal, comm into 7 v_sal,v_comm 8 from emp 9 where empno=7654; 10 if v_comm v_sal then 11 raise e_toomuchComm; -抛出自定义异常 12 end if; 13 exception 14 when no_
