第八章：exception&游标&rowtype&集合/数组



- -异常的作用域
- -结论：如果在语句块中定义一个异常，该异常是本语句
- -块专用的。但是，在语句块嵌套的情况下，外部语句块中
- -所定义的任何异常都适用于内部语句块



- -用户定义异常
- -作用：用来表示违反业务规则的情况。

- - -要使用用户定义异常，必须首先进行声明。
- -用户定义异常在PL / SQL语句块的声明部分进行声明: 
DECLARE 
   exception_name   EXCEPTION;


- -而用户定义异常必须显式抛出。也就是说，
- -需要自己在程序中抛出异常
DECLARE
      exception_name EXCEPTION;
BEGIN
      ...
     IF  condition   THEN
        RAISE   exception_name;
      ELSE
      ...
      END IF;
EXCEPTION
       WHEN  exception_name   THEN
          error -processing statements;
END;

- -总结： 3步：声明 - - -抛出 - - -捕获

- -示例 3：
- -使用用户定义异常处理输入的学生编号为负的错误情况
DECLARE
   v_student_id student.student_id% type : = &sv_student_id;
   v_total_courses NUMBER;
    - - 1、声明异常对象。代表生编号为负的错误情况
   e_invalid_id   EXCEPTION;
BEGIN
    - -检查学生编号是否违反业务规则
   if v_student_id < 0 then
      - - 2、抛出异常，表示违法了业务规则
     raise e_invalid_id;
    else  
      - -正常的业务处理
      select count( *)
        into v_total_courses
        from enrollment
        where student_id =v_student_id;

    dbms_output.put_line(v_total_courses);   
    end if;
exception - - 3、捕获异常
   when e_invalid_id then
    dbms_output.put_line( '学生编号不能为负的');   
end;


- -异常传播
- -研究在plsql块的 3个部分产生异常对象时，该对象
- -如何向外传播并得到处理。

- -第一种情况：在 begin... end部分抛出异常
- -该异常首先由本块的 exception部分来处理。如果本块
- -的 exception部分不处理，那么由外部块的 exception
- -部分来处理；如果外部块也不处理，那么最终由系统
- -来处理：将程序终止，然后打印系统错误消息。


- -第二种情况：在 declare部分抛出异常
- -本块的 exception部分不能处理该异常。只能由外部块
- -的 exception部分来处理；如果外部块也不处理，
- -那么最终由系统来处理
- -示例 6：
DECLARE
   v_test_var CHAR( 3): = 'ABCDE';
BEGIN
   DBMS_OUTPUT.PUT_LINE ( 'This is a test');
EXCEPTION
    WHEN INVALID_NUMBER OR VALUE_ERROR THEN
      DBMS_OUTPUT.PUT_LINE ( 'An error has occurred');
END;


- -改进上例，抓住异常
begin
   DECLARE
    v_test_var CHAR( 3) : = 'ABCDE';
   BEGIN
    DBMS_OUTPUT.PUT_LINE( 'This is a test');
   EXCEPTION
     WHEN INVALID_NUMBER OR VALUE_ERROR THEN
      DBMS_OUTPUT.PUT_LINE( 'An error has occurred');
   END;

EXCEPTION
   WHEN INVALID_NUMBER OR VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE( '外部块抓住了');
end;


- -第三种情况：在 exception部分抛出异常
- -本块的 exception部分不能处理该异常。只能由外部块
- -的 exception部分来处理；如果外部块也不处理，
- -那么最终由系统来处理

- -示例 8：
DECLARE
   v_test_var CHAR( 3) : = 'ABC';
BEGIN
   v_test_var : = '1234';
   DBMS_OUTPUT.PUT_LINE ( 'v_test_var: '||v_test_var);
EXCEPTION
    WHEN INVALID_NUMBER OR VALUE_ERROR THEN
      - -如何处理该异常？
      v_test_var : = 'ABCD'; 
      DBMS_OUTPUT.PUT_LINE ( 'An error has occurred');
END;


- -改进上例，抓住异常
begin
   DECLARE
    v_test_var CHAR( 3) : = 'ABC';
   BEGIN
    v_test_var : = '1234';
    DBMS_OUTPUT.PUT_LINE( 'v_test_var: ' || v_test_var);
   EXCEPTION
     WHEN INVALID_NUMBER OR VALUE_ERROR THEN
       - -如何处理该异常？
      v_test_var : = 'ABCD';
      DBMS_OUTPUT.PUT_LINE( 'An error has occurred');
   END;

exception
   WHEN INVALID_NUMBER OR VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE( '外部块抓住了');
end;


- -异常高级概念
- - 1、使用raise_application_error过程处理用户定义异常

- -RAISE_APPLICATION_ERROR(error_number, error_message);
- -参数：error_number：错误编号，取值范围： - 20999到 - 20000
- -error_message：错误消息，最长 2048字节。

- -该过程直接在控制台打印错误编号和错误消息

- -使用该过程处理学生编号为负的异常情况
DECLARE
   v_student_id student.student_id% type : = &sv_student_id;
   v_total_courses NUMBER;
    - - 1、声明异常对象。代表生编号为负的错误情况
    - -e_invalid_id   EXCEPTION;
BEGIN
    - -检查学生编号是否违反业务规则
   if v_student_id < 0 then
      - - 2、抛出异常，表示违法了业务规则
     - - raise e_invalid_id
    raise_application_error( - 20000, '学生编号不能为负的');
    else  
      - -正常的业务处理
      select count( *)
        into v_total_courses
        from enrollment
        where student_id =v_student_id;

    dbms_output.put_line(v_total_courses);   
    end if;
- - exception - - 3、捕获异常
- - when e_invalid_id then
   - -  dbms_output.put_line( '学生编号不能为负的');   
end;

- -本例中不包含异常的名称、RAISE语句和
- -错误处理部分

- -raise_application_error过程的问题：
- - 1、错误消息的显示格式和系统错误消息的显示格式一样，
- -可能用户不能接受
- - 2、需要专人维护错误编号和错误消息之间的对应关系


- -一个编译指令是针对PL / SQL编译器的特殊指令。
- -当程序编译时处理编译指令
- - 2、exception_init编译指令
- -通过使用EXCEPTION_INIT编译指令，你可以把某个
- -Oracle错误编号和某个用户定义异常的名称建立关联。
- -在建立关联之后，就可以引用这个错误，
- -并为它编写处理程序

- -EXCEPTION_INIT编译指令出现在语句块的声明部分，
- -如下所示: 
DECLARE 
  exception_name   EXCEPTION; 
  PRAGMA  EXCEPTION_INIT(exception_name,error_code);


- -示例 5：根据用户提供的邮编从ZIPCODE表中删除相应的
- -记录，并在屏幕上显示邮编已被删除的消息
- -使用 06870作为v_zip的值运行这个范例
DECLARE
   v_zip ZIPCODE.ZIP% TYPE : = '&sv_zip';
BEGIN
    DELETE FROM zipcode
     WHERE zip = v_zip;
    DBMS_OUTPUT.PUT_LINE ( 'Zip '||v_zip|| ' has been deleted');
    COMMIT;
END;

- -运行结果：
- -ORA - 02292: integrity constraint (STUDENT.STU_ZIP_FK) violated - child record found
- -ORA - 06512: at line 5

- -因为外键错误没有定义成内置异常，因此是不能使用 exception
- -来捕获的。
- -要处理外键错误，必须使用编译指令
DECLARE
   v_zip ZIPCODE.ZIP% TYPE : = '&sv_zip';
   e_child_exists exception;
   pragma exception_init(e_child_exists, - 2292);
BEGIN
    DELETE FROM zipcode
     WHERE zip = v_zip;
    DBMS_OUTPUT.PUT_LINE ( 'Zip '||v_zip|| ' has been deleted');
    COMMIT;
exception
   when e_child_exists then
    dbms_output.put_line( '先删除一些学生');   
END;


- - 3、两个函数
- -为了改进OTHERS异常处理程序，Oracle提供了两个内置函数： SQLCODE和SQLERRM
- - SQLCODE函数：返回Oracle错误编号
- -SQLERRM函数：返回错误消息。返回消息的最大长度是 512字节

- - 07458作为邮政编码的值
DECLARE
   v_zip      VARCHAR2( 5) : = '&sv_zip';
   v_city     VARCHAR2( 15);
   v_state     CHAR( 2);
   v_err_code NUMBER;
   v_err_msg  VARCHAR2( 200);
BEGIN
    SELECT city, state
      INTO v_city, v_state
      FROM zipcode
     WHERE zip = v_zip;

    DBMS_OUTPUT.PUT_LINE (v_city|| ',  '||v_state);

EXCEPTION
    WHEN  OTHERS THEN
     v_err_code : = SQLCODE;
     v_err_msg  : = SUBSTR(SQLERRM, 1, 200);
     DBMS_OUTPUT.PUT_LINE ( 'Error code: '||v_err_code);
     DBMS_OUTPUT.PUT_LINE ( 'Error message: '||v_err_msg);
END;



- -游标基础

- -记录类型
- -它是一个复合数据类型，其内部由多个字段（元素）构成。
- -复合数据类型的特点是：该类型的一个变量就可以保存
- -多个值。

- -记录类型专门设计用来保存表或者游标中的一行。

- -PL / SQL支持三种类型的记录：
- -基于表的( table -based)、基于游标的( cursor -based)
- -以及程序员定义的(programmer - defined)

- -基于表的记录就是该记录的结构来自于数据库表中所有的列
- -基于游标的记录就是记录的结构来自于定义游标的 select列表。

- -为创建一个基于表或者基于游标的记录，
- -可以使用%ROWTYPE属性。语法: 
- - record_name table_name or cursor_name%ROWTYPE

- -示例 1：使用记录保存student表中的一行
declare
   - -声明基于student表的记录类型以及记录变量
  r_student student%rowtype;
begin
   - -将学生 156保存到记录中
   select *
     into r_student
     from student
     where student_id = 156;

   - -取出记录中各个字段的值。语法：记录变量名.字段名    
  dbms_output.put_line(r_student.first_name);
  dbms_output.put_line(r_student.last_name);
  dbms_output.put_line(r_student.phone);
end;

- -处理显式游标
- - 4个步骤：声明、打开、检索和关闭

- -示例 4：声明一个游标，保存 5个学生的姓名。检索游标，
- -将 5个学生的姓名打印到屏幕上
declare
   - - 1、声明
   cursor c_student_name is
     select first_name,last_name
       from student
       where rownum < = 5;

   - -声明基于游标的记录，以用于游标检索
  r_student c_student_name%rowtype;
begin
   - - 2、打开
   open c_student_name;  

  loop
     - - 3、检索。将拿到的行存到记录中
     fetch c_student_name into r_student; 

     - -循环退出条件
    exit when c_student_name%notfound;

     - -对拿到的行进行处理
    dbms_output.put_line(r_student.first_name||
       ' '||r_student.last_name);
   end loop; 

   - - 4、关闭
   close c_student_name; 
end;

- -用户定义记录
- -用户定义记录是基于程序员定义的记录类型的。
- -首先，声明一种记录类型，然后基于所定义的记录类型
- -来声明记录变量。语法如下: 
- - type  type_name   is record 
( field_name1  datatype1 ,
  field_name2  datatype2, 
  ……
  field_namen  datatypen) ;

 record_name  type_name;

- -示例 6：使用用户定义记录保存 102号老师的姓名和
- -所教班级的数量，然后打印记录的内容
declare
   - -声明记录类型
   type instructor_info_type is record(
    fname instructor.first_name% type,
    lname instructor.last_name% type,
    sections number
  );

- -声明记录变量
r_instructor instructor_info_type;
begin
   - -将 102号老师的姓名和所教班级的数量保存到记录中
   select first_name,last_name, count( *)
     into r_instructor
     from instructor i, section s
     where i.instructor_id = s.instructor_id
       and i.instructor_id = 102
       group by first_name,last_name;

  dbms_output.put_line(r_instructor.fname|| ' '||
     r_instructor.lname|| '教了'||r_instructor.sections|| '班');

end; 



- -使用游标属性: 4个
- - 1）%notfound: 如果前面的 fetch操作没有拿到行，则为 true，否则为 false
- - 2)% found: 如果前面的 fetch操作拿到了行，则为 true，否则为 false
- - 3)%rowcount: 取值 > = 0。累计 fetch操作一共拿到的行的数量
- - 4)%isopen: 如果游标是打开的，则为 true，否则为 false

- -常见使用方法：
- - 1）%notfound主要用来退出游标检索循环
  - -   exit when c_student%notfound; 
   - - end  loop; 

- - 2）%isopen主要用在 exception中，判断游标是否关闭，
- -如果没有则关掉它
- exception 
        when others   then 
     if c_student%isopen    then 
            close c_student; 
      end if; 
end;

- - 3）%rowcount主要用来判断是否从游标中拿到了行或者
- -统计拿到的行的数量

- -注意：
- -通过 SQL前缀，就可以操作隐式游标的游标属性，
- -如 SQL%ROWCOUNT

- -对于隐式游标，其%isopen属性永远为 false

- -示例 9：使用% found属性退出游标检索循环。
- -使用%rowcount属性统计 fetch操作拿到的行的数量
declare
  v_sid student.student_id% type;

   cursor c_student is
     select student_id
       from student
       where student_id < 110;
begin
   open c_student;
  loop
     fetch c_student into v_sid;
     - -判断是否从游标中拿到行
    if c_student% found then
      dbms_output.put_line(v_sid);
      dbms_output.put_line( 'fetch拿到了'||c_student%rowcount|| '行');
     else
      exit;
     end if;  
   end loop;

   close c_student;
exception
   when others then
    if c_student%isopen then
       close c_student;
     end if;    
end;

- -使用游标 for循环处理游标
- -它简化了游标的处理过程。
- -借助于游标 FOR循环，游标打开、检索和关闭的过程
- -被隐含地自动实现。这使得语句块代码更容易编写和维护
- -语法：
for  record_variable_name in   cursor_name   loop
       statement;
end loop;

- -该循环的每一次迭代，都会自动地从游标 cursor_name 
- -中检索出一行记录，并把它存放到记录变量
- - record_variable_name中。在循环体的语句中，
- -使用句点(.)的语法，可通过变
- -量 record_variable_name来得到该行记录每列的值

- -示例 11：
create   table  table_log
               (description  varchar2( 250));

declare
   cursor c_student is
     select student_id,last_name
       from student
       where student_id < 110;
begin
   - -处理游标
   for r_student in c_student loop
     insert into table_log
       values(r_student.last_name);
   end loop;
end;


- -使用嵌套游标
- -实际上就是游标 for循环的嵌套使用

- -示例 13：
declare
  v_zip  zipcode.zip% TYPE;
  v_student_flag   CHAR;

   - -父游标.保存的是CT州的所有邮编
   cursor c_zip is
     select zip,city, state
       from zipcode
       where state = 'CT';    

   - -子游标。保存特定邮编区域的学生姓名.
   - -注意：子游标的 where条件中有个变量v_zip
   cursor c_student is
     select first_name,last_name
       from student
       where zip =v_zip;  
begin
   - -遍历父游标
   for r_zip in c_zip loop
     - -值为N表示该邮编区域没有学生
    v_student_flag : = 'N';

     - -给v_zip初始化以便打开子游标
    v_zip : = r_zip.zip; 

     - -输出一个空白行：
    dbms_output.put_line(chr( 10)); 
    dbms_output.put_line( '学生住在：'||r_zip.city); 

     - -遍历子游标，输出学生的姓名
     for r_student in c_student loop
      dbms_output.put_line(r_student.last_name);

      v_student_flag : = 'Y';
     end loop;

     - -如果该邮编区域没有学生
    if v_student_flag = 'N' then
      dbms_output.put_line( '没有学生');
     end if;

   end loop;
end;



- -使用集合（数组）
- -集合是相同类型元素的组合。在集合中，
- -使用唯一的下标来标识每个元素

- -一共有 3种类型的集合：
- -索引表、嵌套表和变长数组

- -PL / SQL表有两种类型：索引表（或者叫做关联数组，
- -associative array ）和嵌套表（nested table）


- -使用集合，主要为了使用一个或者几个变量就可以
- -保存大量的数据

- - 1、使用索引表（相关数组）集合

- -示例 1：分别声明一个游标和一个索引表类型，
- -游标从student表中检索出前 10个学生的姓名。
- -遍历游标，将每个学生的姓名保存到一个索引表类型的
- -集合中，然后从集合中取出每个学生的姓名打印到屏幕上
declare
   - -声明游标
   cursor c_student is
     select last_name
       from student
       where rownum < = 10;

   - -声明索引表集合类型
   type last_name_type is table of student.last_name% type 
     index by pls_integer;

   - -声明集合变量
  last_name_tab last_name_type;

  v_index pls_integer : = 0;
begin
   - -遍历游标，将学生姓名保存到集合中
   for r_student in c_student loop
      - -下标加 1
     v_index : = v_index + 1;

      - -保存
     last_name_tab(v_index) : = r_student.last_name;
   end loop;

   - -遍历集合
   for i in 1.. 10 loop
     dbms_output.put_line(last_name_tab(i));
   end loop;
end;

- - 2、使用嵌套表集合
- -创建嵌套表的语法如下所示（方括号中的内容可选的）： 
   type type_name is table of element_type [ not null]; 
   table_name  type_name;
- -注意，该声明非常类似于索引表的声明，只是没有 INDEX BY  子句。嵌套表的下标类型固定为 Integer整型

- -修改示例 1，将集合类型改为嵌套表类型
declare
   - -声明游标
   cursor c_student is
     select last_name
       from student
       where rownum < = 10;

   - -声明嵌套表集合类型
   type last_name_type is table of student.last_name% type 
    ;

   - -声明集合变量
  last_name_tab last_name_type;

  v_index pls_integer : = 0;
begin
   - -遍历游标，将学生姓名保存到集合中
   for r_student in c_student loop
      - -下标加 1
     v_index : = v_index + 1;

      - -保存
     last_name_tab(v_index) : = r_student.last_name;
   end loop;

   - -遍历集合
   for i in 1.. 10 loop
     dbms_output.put_line(last_name_tab(i));
   end loop;
end;  

- -执行以上代码，抛出以下异常：
- -ORA - 06531: Reference to uninitialized collection
- -ORA - 06512: at line 24

- -必须使用嵌套表的构造器函数对嵌套表集合变量初始化。

- -改写代码：
declare
   - -声明游标
   cursor c_student is
     select last_name
       from student
       where rownum < = 10;

   - -声明嵌套表集合类型
   type last_name_type is table of student.last_name% type 
    ;

   - -声明集合变量。必须使用构造器函数初始化
  last_name_tab last_name_type : = last_name_type();

  v_index pls_integer : = 0;
begin
   - -遍历游标，将学生姓名保存到集合中
   for r_student in c_student loop
      - -下标加 1
     v_index : = v_index + 1;

      - -保存
     last_name_tab(v_index) : = r_student.last_name;
   end loop;

   - -遍历集合
   for i in 1.. 10 loop
     dbms_output.put_line(last_name_tab(i));
   end loop;
end;  


- -执行以上代码，抛出以下异常：
- -ORA - 06533: Subscript beyond count
- -ORA - 06512: at line 24

- -为了把元素放到嵌套表集合中，必须首先调用
- -集合的 extend方法给集合添加存储空间
- -改写代码：
declare
   - -声明游标
   cursor c_student is
     select last_name
       from student
       where rownum < = 10;

   - -声明嵌套表集合类型
   type last_name_type is table of student.last_name% type 
    ;

   - -声明集合变量。必须使用构造器函数初始化
  last_name_tab last_name_type : = last_name_type();

  v_index pls_integer : = 0;
begin
   - -遍历游标，将学生姓名保存到集合中
   for r_student in c_student loop
      - -下标加 1
     v_index : = v_index + 1;

      - -首先给集合添加存储空间
     last_name_tab. extend;

      - -保存
     last_name_tab(v_index) : = r_student.last_name;
   end loop;

   - -遍历集合
   for i in 1.. 10 loop
     dbms_output.put_line(last_name_tab(i));
   end loop;
end;  

- -集合方法：
- - count、 first、 last、 prior、 next、 delete、 trim、 exists


- - 3、使用变长数组集合类型
- -创建变长数组的语法如下所示 
TYPE  type_name IS  VARRAY(siz e_limit)   OF  element_type; 
varray_name  TYPE_NAME;

- -变长数组有最大元素数量限制。
- -他在使用上和嵌套表的语法限制是完全一样的：
- - 1）必须对变长数组进行初始化
- - 2）在放元素到变长数组集合中之前，必须调用 extend方法
- -添加存储空间
declare
   - -声明游标
   cursor c_student is
     select last_name
       from student
       where rownum < = 10;

   - -声明变长数组集合类型
   type last_name_type is varray( 10) of student.last_name% type 
    ;

   - -声明集合变量。必须使用构造器函数初始化
  last_name_tab last_name_type : = last_name_type();

  v_index pls_integer : = 0;
begin
   - -遍历游标，将学生姓名保存到集合中
   for r_student in c_student loop
      - -下标加 1
     v_index : = v_index + 1;

      - -首先给集合添加存储空间
     last_name_tab. extend;

      - -保存
     last_name_tab(v_index) : = r_student.last_name;
   end loop;

   - -遍历集合
   for i in 1.. 10 loop
     dbms_output.put_line(last_name_tab(i));
   end loop;
end;  

- -注意：不能对变长数组使用 delete方法来删除其元素，
- -使用 delete方法会导致错误：


- -多层集合
- -如果一个集合的元素又是一个集合，那么
- -该集合就叫做多层集合。

- -多层集合对应于其它语言中的二维数组

- -所谓二维数组，就是有一个一维数组，其中的
- -每个元素又是一个一维数组。

- -要取出二维数组中的每个元素，必须使用两个下标。

declare
   type varray_type1 is varray( 4) of pls_integer;
   type varray_type2 is varray( 3) of varray_type1;

  varray1 varray_type1 : = varray_type1( 2, 4, 6, 8);

   - -二维数组
  varray2 varray_type2 : = varray_type2(varray1);
begin
  varray2. extend;

   - -给varray2再添加一个元素
  varray2( 2) : = varray_type1( 1, 3, 5, 7);  

   - -遍历varray2数组
   for i in 1.. 2 loop
     for j in 1.. 4 loop
      dbms_output.put_line(varray2(i)(j));   
     end loop;
   end loop;

   - -灵活的写法
   for i in varray2. first..varray2. last loop
     for j in 1..varray2(i). count loop
      dbms_output.put_line(varray2(i)(j));   
     end loop;
   end loop;
end;