第八章：plsql编程基础(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: integrityconstraint (STUDENT.STU_ZIP_FK) violated- child record found
--ORA-06512:at line5

--因为外键错误没有定义成内置异常，因此是不能使用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_nametable_nameor 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表有两种类型：索引表（或者叫做关联数组，
--associativearray ）和嵌套表（nestedtable）


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

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

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

  --声明索引表集合类型
  type last_name_type is tableof 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 tableof element_type [notnull]; 
  table_name  type_name;
--注意，该声明非常类似于索引表的声明，只是没有INDEXBY  子句。嵌套表的下标类型固定为Integer整型

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

  --声明嵌套表集合类型
  type last_name_type is tableof 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: Referenceto uninitialized collection
--ORA-06512:at line24

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

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

  --声明嵌套表集合类型
  type last_name_type is tableof 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 beyondcount
--ORA-06512:at line24

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

  --声明嵌套表集合类型
  type last_name_type is tableof 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;