再认PL/SQL

在上一篇文章中，我们已经认识了PL/SQL中支持的基本数据类型，但是没有哪种数据类型是可以接收一个集合的，例如这种的查询结果：

select customerno,submitdate into ???  from t_credit_agreement ;

所以在此引出光标的概念，也可以称为游标，就是一个结果集。

一、光标

--先声明类型是光标，再为光标命名。

CURSOR 光标名 （参数名 参数类型，参数名 参数类型...）

IS SELECT 语句;

例如：

cursor ca select ename from emp;

光标的属性：1、光标名%found  

                   2、光标名%notfound   

                   3、光标名%isopen      结果是true/false 

                   4、光标名%rowcount  影响的行数，而不是光标结果集中的总行数。

例如：

declare
  --定义一个光标
  cursor c1 is select customerno,submitdate from t_credit_agreement;
  --为光标定义对应的变量
  customer_no t_credit_agreement.customerno%type;
  submit_date t_credit_agreement.submitdate%type;
begin
   
  --打开光标
  open c1;
  
  loop 
    --从光标中取一条记录
    fetch c1 into customer_no,submit_date;
    exit when c1%notfound;
    dbms_output.put_line(customer_no||'  '||submit_date);
  end loop;
  
  --关闭光标
  close c1;
 

end;

其中fetch的作用：

如果对数据进行了更改，一定要在PL/SQL程序中提交操作：commit

光标数的限制：默认情况下，oracle数据库只允许在同一个会话中打开300个光标，对于大型系统，若是300个光标不够用，可修改默认配置：

alter system set open_cursors = 400 scope =both;

带参数的光标：

declare
  --定义一个带参数的光标
  cursor c1(state_2 number) is select customerno,submitdate from t_credit_agreement where state = state_2;
  --为光标定义对应的变量
  customer_no t_credit_agreement.customerno%type;
  submit_date t_credit_agreement.submitdate%type;
begin
   
  --打开光标并给光标传递实参
  open c1(2);
  
  loop 
    --从光标中取一条记录
    fetch c1 into customer_no,submit_date;
    exit when c1%notfound;
    dbms_output.put_line(customer_no||'  '||submit_date);
  end loop;
  
  --关闭光标
  close c1;
 

end;

二、例外

例外是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性。其实跟Java中的异常差不多。

常见的系统例外有：

No_data_found  (没有找到数据)

Too_many_rows  (select...into语句匹配到太多行)

Zero_divide  (被零除)

等等.

例如：

declare
  customer_no t_credit_agreement.customerno%type;
  submit_date t_credit_agreement.submitdate%type;
begin
   
 select customerno,submitdate into customer_no,submit_date from t_credit_agreement where state =110;
 
 exception
   when no_data_found then dbms_output.put_line('没有找到数据');
   --捕获所有其他的异常，否则可能会导致数据库出错
   when others then dbms_output.put_line('捕获其他异常');
 

end;

自定义例外

声明一个例外（一个例外类型的变量）

no_data exception; -- 例外名称 类型

例如：

declare
  cursor c1 is  select customerno,submitdate from t_credit_agreement where state =110;
  customer_no t_credit_agreement.customerno%type;
  submit_date t_credit_agreement.submitdate%type;
  --定义一个自定义例外
  no_data_exception exception;
begin
   
   open c1;
   
   fetch c1 into customer_no,submit_date;
   
   if c1%notfound then 
      raise no_data_exception;--使用raise抛出例外
   end if;
   
   close c1;
   
   exception
     --捕获自定义例外
     when no_data_exception then dbms_output.put_line('没有找到数据');
     --捕获所有其他的异常，否则可能会导致数据库出错
     when others then dbms_output.put_line('捕获其他异常');
     
end;

注：在没有数据时，fetch并不会自动抛出No_data_found系统例外，而select...into 会。

一般遇到例外时，可以向日志表中插入一条数据，方便后续问题的跟踪。