Oracle中的Cursor(游标)

   

一  概念
游标是sql的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。

二  类型
cursor类型包含三种: 隐式cursor，显式cursor和ref cursor（动态cursor）。
1． 隐式cursor:
1).对于select …into…语句，一次只能从数据库中获取到一条数据，对于这种类型的dml sql语句，就是隐式cursor。例如：select /update / insert/delete操作。
2)作用：可以通过隐式cusor的属性来了解操作的状态和结果，从而达到流程的控制。cursor的属性包含：
sql%rowcount 整型 代表dml语句成功执行的数据行数
sql%found  布尔型  值为true代表插入、删除、更新或单行查询操作成功
sql%notfound 布尔型 与sql%found属性返回值相反
sql%isopen 布尔型 dml执行过程中为真，结束后为假
3) 隐式cursor是系统自动打开和关闭cursor.

  set serveroutput on;begin    update t_contract_master set liability_state = 1 where policy_code = '123456789';        if sql%found then       dbms_output.put_line('the policy is updated successfully.');       commit;    else      dbms_output.put_line('the policy is updated failed.');    end if;end;/ 

运行结果:

sql>  the policy is updated failed. pl/sql procedure successfully completed

2． 显式cursor：
（1） 对于从数据库中提取多行数据，就需要使用显式cursor。显式cursor的属性包含：
游标的属性   返回值类型   意    义
%rowcount   整型  获得fetch语句返回的数据行数
%found  布尔型 最近的fetch语句返回一行数据则为真，否则为假
%notfound   布尔型 与%found属性返回值相反
%isopen 布尔型 游标已经打开时值为真，否则为假

（2） 对于显式游标的运用分为四个步骤：
 定义游标---cursor  [cursor name]  is;
 打开游标---open  [cursor name];
 操作数据---fetch  [cursor name]
 关闭游标---close [cursor name],这个step绝对不可以遗漏。
（3）以下是三种常见显式cursor用法。
1）

set serveroutput on;declare     ---define cursor    cursor cur_policy is     select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account     from t_contract_master cm     where cm.liability_state = 2     and cm.policy_type = 1     and cm.policy_cate in ('2','3','4')     and rownum < 5     order by cm.policy_code desc;    curpolicyinfo cur_policy%rowtype;---定义游标变量begin   open cur_policy; ---open cursor   loop      --deal with extraction data from db     fetch cur_policy into curpolicyinfo;     exit when cur_policy%notfound;              dbms_output.put_line(curpolicyinfo.policy_code);   end loop;   exception      when others then         close cur_policy;         dbms_output.put_line(sqlerrm);            if cur_policy%isopen then  	--close cursor       close cur_policy;   end if;end;/

2）

set serveroutput on;declare     cursor cur_policy is     select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account     from t_contract_master cm     where cm.liability_state = 2     and cm.policy_type = 1     and cm.policy_cate in ('2','3','4')     and rownum < 5     order by cm.policy_code desc;     v_policycode t_contract_master.policy_code%type;     v_applicantid t_contract_master.applicant_id%type;     v_periodprem t_contract_master.period_prem%type;     v_bankcode t_contract_master.bank_code%type;     v_bankaccount t_contract_master.bank_account%type;begin   open cur_policy;   loop      fetch cur_policy into v_policycode,                           v_applicantid,                           v_periodprem,                           v_bankcode,                           v_bankaccount;     exit when cur_policy%notfound;              dbms_output.put_line(v_policycode);   end loop;   exception      when others then         close cur_policy;         dbms_output.put_line(sqlerrm);            if cur_policy%isopen then         close cur_policy;   end if;end;/

3）

set serveroutput on;declare     cursor cur_policy is     select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account     from t_contract_master cm     where cm.liability_state = 2     and cm.policy_type = 1     and cm.policy_cate in ('2','3','4')     and rownum < 5     order by cm.policy_code desc;begin   for rec_policy in cur_policy loop       dbms_output.put_line(rec_policy.policy_code);   end loop;   exception      when others then         dbms_output.put_line(sqlerrm);         end;/

运行结果:

sql>  8780203932878020322787802032188771289268 pl/sql procedure successfully completed

3． ref cursor（动态游标）：
1） 与隐式cursor,显式cursor的区别：ref cursor是可以通过在运行期间传递参数来获取数据结果集。而另外两种cursor，是静态的，在编译期间就决定数据结果集。
2） ref cursor的使用:
type [cursor type name] is ref cursor
define 动态的sql语句
open cursor
操作数据---fetch  [cursor name]
close cursor

set serveroutput on;declare    ---define cursor type name    type cur_type is ref cursor;    cur_policy cur_type;    sqlstr varchar2(500);    rec_policy t_contract_master%rowtype;begin   ---define 动态sql   sqlstr := 'select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account from t_contract_master cm     where cm.liability_state = 2      and cm.policy_type = 1      and cm.policy_cate in (2,3,4)      and rownum < 5      order by cm.policy_code desc ';---open cursor  open cur_policy for sqlstr;  loop       fetch cur_policy into rec_policy.policy_code, rec_policy.applicant_id, rec_policy.period_prem,rec_policy.bank_code,rec_policy.bank_account;       exit when cur_policy%notfound;              dbms_output.put_line('policy_code:'||rec_policy.policy_code);    end loop;close cur_policy;    end;/

4.常见exception

1.	错 误 名 称 错误代码    错 误 含 义   2.	cursor_already_open ora_06511   试图打开已经打开的游标   3.	invalid_cursor  ora_01001   试图使用没有打开的游标   4.	dup_val_on_index    ora_00001   保存重复值到惟一索引约束的列中   5.	zero_divide ora_01476   发生除数为零的除法错误   6.	invalid_number  ora_01722   试图对无效字符进行数值转换   7.	rowtype_mismatch    ora_06504   主变量和游标的类型不兼容   8.	value_error ora_06502   转换、截断或算术运算发生错误   9.	too_many_rows   ora_01422   select…into…语句返回多于一行的数据   10.	no_data_found   ora_01403   select…into…语句没有数据返回   11.	timeout_on_resource ora_00051   等待资源时发生超时错误   12.	transaction_backed_out  ora_00060   由于死锁，提交失败   13.	storage_error   ora_06500   发生内存错误   14.	program_error   ora_06501   发生pl/sql内部错误   15.	not_logged_on   ora_01012   试图操作未连接的数据库   16.	login_denied    ora_01017   在连接时提供了无效用户名或口令