游标基本概念和使用实例

游标基本概念

为了处理select语句只能怪返回多行数据的情况，在oracle 11g中恶意使用游标处理多行数据。
也可以使用select ...bulk collect into 语句处理多行数据。
显式游标：由用户定义、操作，用户处理返回多行数据的select 查询。
隐式游标：由系统自动进行操作，用于DML语句和返回单行数据的select 查询。

定义游标 > 打开游标 > 检索游标 > 是否为空 > 关闭游标

简单游标的使用：

create or replace produre emp_list
as
cursor emp_cursor is select empno,empname from emp;
begin
for emp_record in emp_cursor loop
dbms_output.put_line(emp_record.empno||emp_record.empname);
end loop;
end;
/

CREATE OR REPLACE PROCEDURE EMP_LIST   
        AS  
         CURSOR emp_cursor IS    
        SELECT empno,ename FROM emp;   
        BEGIN  
FOR Emp_record IN emp_cursor LOOP      
    DBMS_OUTPUT.PUT_LINE(Emp_record.empno||Emp_record.ename);   
        END LOOP;   
        EMP_COUNT;   
        END;

create or replace procedure test_pro1(
empno in number)
is
a number; 
ab boolean;
begin
select count(*) into a from test1;
dbms_output.put_line('total '||a||' row');
end;
/

create or replace procedure test_pro2
is
f1 number;
begin
select count(*) into f1 from a;
insert into b values(f1);
dbms_output.put_line('first test');
end;
/

一、游标：

参数化显示游标：
参数化显示游标是指定义游标的时候使用参数，当使用不同参数打开游标的时候，可以生产不同的结果集，参数化显示游标的语法如下：

cursor cursor_name(var datatype,...) is select ...;

注意：
定义游标时，只能指定参数的类型不能指定参数的长度、精度、刻度。
打开带参数的游标的时候，参数的个数和数据类型等必须与定义时的游标的参数的个数和数据类型一致。

declare
cursor c_emp(p_deptno emp.deptno%type) is
select * from emp where deptno=p_deptno;
v_emp c_emp%rowtype;
begin
open c_emp(20);
loop
 fetch c_emp into v_emp;
 exit when c_emp%NOTFOUND;
dbms_output.put_line(v_emp.empno||v_emp.ename||v_emp.sal);
end loop;
close c_emp;
open c_emp(30);
loop
 fetch c_emp into v_emp;
 exit when c_emp%NOTFOUND;
dbms_output.put_line(v_emp.empno||v_emp.ename||v_emp.sal);
end loop;
end;
/

显示游标的特性：
无论显示游标还是隐式游标都有下面的这些特性：
%ISOPEN ：布尔型 用于检查游标是否已经打开，如果游标已经打开，则返回true，否者返回false.
%FOUND: 布尔型  判断最近一次使用fetch语句时是否从缓冲区 中检索到数据，如果由返回true，否者返回false.
%NOTFOUND:布尔型  返回值和found相反
%ROWCOUNT:数值型，返回到目前为止从游标缓冲区中检索的记录的个数。
%BULK_ROWCOUNT(i) :数值型，用于取得forall语句执行批量绑定操作时第I个元素所影响的行数。

1、%ROWCOUNT 的使用实例：
declare
cursor test IS select * from emp where deptno=20;
v_cus test%ROWTYPE;
v_count int;
begin
 open test;
 loop
 fetch test into v_cus;
 exit when test%NOTFOUND;
 dbms_output.put_line(v_cus.empno||v_cus.ename||v_cus.sal);
 v_count:=test%ROWCOUNT;
 dbms_output.put_line(v_count);
 end loop;
close test;
end;
/

使用简单检索游标
使用while循环检索游标

declare
cursor test IS select * from emp where deptno=20;
v_cus test%ROWTYPE;
begin
 open test;
 fetch test into v_cus;
 while test%FOUND loop
 dbms_output.put_line(v_cus.empno||v_cus.ename||v_cus.sal);
 fetch test into v_cus;
 end loop;
close test;
end;
/

使用for循环检索游标：
注意：使用for循环检索游标时，系统会自动的打开、检索和关闭游标，用户只需要考虑如何处理从游标缓冲区中检索出来的数据即可。

declare
cursor test IS select * from emp where deptno=20;
begin
for v_cus in test loop
   dbms_output.put_line(v_cus.empno||v_cus.ename||v_cus.sal);
end loop;
end;
/

利用for循环时，系统会先隐式的定义一个数据类型为cursor_name%ROWTYPE类型的循环变量loop_var,然后自动打开游标，从游标中提取数据并放入到loop_var变量中，同事进行%FOUND属性检查以确定是否检索到数据，当所有数据检索完毕之后循环会自动中端，系统自动关闭游标。

二、利用游标更新或删除数据。
利用显示游标，不仅可以处理select语句返回的多个记录，还可以在处理游标中当前数据的同时，修改或者删除数据库中的数据。

（1）、利用游标更新或者删除数据的游标定义方法。
如果要利用 游标删除或者更新数据库中的数据，则需要在游标定义中使用FOR UPDATE字句，对要修改或者删除的数据加行锁。
语法：
cursor cursor_name is
select * from emp for update [of cloumn_reference]
[NOWAIT]

说明：
1、打开游标时会在表上添加锁，（通常select语句操作不会在数据上设置任何锁） 其他用户不能对该表进行DML操作。
2、若数据对象已经被其他回话加锁，则当前会话挂起等待（默认等待），若指定了NOWAIT字句，那么则不会等待，直接返回错误。
3、对于多表查时，可以通过OF字句指定某个要加锁的表的列的形式，对特定的表加锁，而其他表不加锁，否者所有表都加锁，如：
对emp表加锁，对dept表不加锁：

cursor c is select ename,..  from emp e,dept d;
where e.deptno=d.deptno for update of salary;

4、当用户执行commit或者rollback操作时，数据上的锁会自动释放。