游标使用2-常用属性及引用游标

隐式游标
四个常用的属性
Sql%FOUND
SQL%NOTFOUND
SQL%ISOPEN
SQL%ROWCOUNT

Declare
Dept_no_number(4) :=50;
Begin
Delete from dept_temp where deptno=dept_no;
If sql%found then
Insert into dept_temp values(50,’database’,’beijing’);
end if;
commit;
end;
/

显示游标
通常使用在plsql中，有我们显示的控制open,fetch,close,有下面4个最常用的属性
Cursorname%found
Cursorname%notfound
Cursorname%isopen
Cursorname%rowcount

Declare
Cursor c1 is select ename,sal from emp where rownum<11;
My_ename emp.ename%type;
My_salary emp.sal%typ;
Begin
Open c1;
Loop
Fetch c1 into my_ename,my_salary;
If c1%found then
Dbms_output.put_line(‘name=’||my_ename||’,salary=’||my_salary);
Else
Exit;
End if;
End loop;
Close c1;
End;
/
标准游标的使用方法

Create or replace procedure p_demo_explicit_cursor_std is
Cursor c1 is select * from emp where rownum<11;
Emp_rec emp%rowtype;
Begin
Open c1;
Fetch c1 into emp_rec;
While(c1%found) loop
Dbms_output.put_line(‘name=’||emp_rec.ename||’,salary=’||emp_rec.sal);
Fetch c1 into emp_rec;
End loop;
Close c1;
Exception
When others then
….
Rollback;
Return;
End p_demo_explicti_curosor_std;

参考游标，参考游标与显示游标很像也是在存储过程中使用，常用的属性有下面四个
与显示游标的是一样的。
主要有下面三个特性：
1定义方式灵活
2可以不与某个固定的sql绑定，可以随时open,并且每次open所对应的sql语句都可以是不一样的。
3可以作为存储过程的输入输出
引用游标范例：
Create package pck_refcursor_open_demo as
Type gencurtyp is ref cursor;
Procedure open_cv(generic_cv in out gencurtyp,choice int);
End pck_refcursor_open_demo;
/

Create package body pck_refcursor_oepn_demo as
Procedure open_cv(generic_cv in out gencurtyp,choice int) is
Begin
If choice=1 then
Open generic_cv for select * from emp;
Elsif choice=2 then
Open generic_cv for select * from dept;
End if;
End;
End pck_refcursor_open_demo;
/

引用游标中的批量取值
下面是一行一行取
Declare
Type empcurtyp is ref cursor return emp%rowtype;
Emp_cv empcurtyp;
Emp_rec emp%rowtype;
Begin
Open emp_cv for select * from emp where rownum<11;
Loop
Fetch emp_cv into emp_rec;
Exit when emp_cv%notfound;
Dbms_output.put_line(‘name=’||emp_rec.ename);
End loop;
Close emp_cv;
End;
/

下面是批量取值
Declare
Type empcurtyp is ref cursor;
Type namelist is table of emp.ename%type;
Emp_cv empcurtyp;
Names namelist;
Begin
Open emp_cv for select ename from emp where rownum<11;
Fetch emp_cv bulk collect into names;
Close emp_cv;
For I in names.first .. names.last
Loop
Dbms_output.put_line(‘name=’||names(i));
End loop;
End;
/