1.自定义（显式）游标

set serverout on;

declare cursor cu_emp is select empno,ename,sal from emp;

e_no number;

e_name varchar2(10);

e_sal number;

begin

open cu_emp;

fetch cu_emp into e_no,e_name,e_sal;

while cu_emp%found loop  --- 游标存在时

dbms_output.put_line('编号：'||e_no||'，姓名：'||e_name||'，基本薪资：'||e_sal);

fetch cu_emp into e_no,e_name,e_sal;

end loop;

close cu_emp;

end;

2.应用被遍历的的表的列类型

set serverout on;

declare cursor cu_emp is select empno,ename,sal from emp;

e_no emp.empno%type;  -- empno列的类型

e_name emp.ename%type;

e_sal emp.sal%type;

begin

open cu_emp;

fetch cu_emp into e_no,e_name,e_sal;

while cu_emp%found loop

dbms_output.put_line('编号：'||e_no||'，姓名：'||e_name||'，基本薪资：'||e_sal);

fetch cu_emp into e_no,e_name,e_sal;

end loop;

close cu_emp;

end;

3.应用被遍历表的行属性

set serverout on;

declare cursor cu_emp is select * from emp;  -- declare 可以同时声明多个变量，用分号隔开。

e emp%rowtype;

begin

open cu_emp;

fetch cu_emp into e;

while cu_emp%found loop

dbms_output.put_line('编号：'||e.empno||'，姓名：'||e.ename||'，基本薪资：'||e.sal);

fetch cu_emp into e;

end loop;

close cu_emp;

end;

4.游标的遍历可以加入条件

set serverout on;

declare cursor cu_emp is select * from emp where sal>2000 and sal<3000;

e emp%rowtype;

begin

open cu_emp;

fetch cu_emp into e;

while cu_emp%found loop

dbms_output.put_line('编号：'||e.empno||'，姓名：'||e.ename||'，基本薪资：'||e.sal);

fetch cu_emp into e;

end loop;

close cu_emp;

end;

5.隐式游标（系统声明好的游标，取名为sql）

隐式游标的属性 返回值类型 意 义

SQL%ROWCOUNT 整型 代表 DML 语句成功执行的数据行数

SQL%FOUND 布尔型 值为 TRUE 代表插入、删除、更新或单行查询操作成功

SQL%NOTFOUND 布尔型 与 SQL%FOUND 属性返回值相反

SQL%ISOPEN 布尔型 DML 执行过程中为真，结束后为假

begin

if sql%isopen then

dbms_output.put_line('sql游标已打开');

else

dbms_output.put_line('sql游标未打开');

end if;

end;

6.隐式游标的一些方法

declare e_count number;

begin

select count(*) into e_count from emp;

dbms_output.put_line('游标捕获的记录数：'||sql%rowcount);

end;

declare e_count number;

begin

select count(*) into e_count from emp;

dbms_output.put_line('游标捕获的记录数：'||sql%rowcount);

end;

begin

update emp set ename='sb3' where empno=111;

if sql%rowcount=1 then

dbms_output.put_line('已更新');

else

dbms_output.put_line('未更新');

end if;

end;

begin

update emp set ename='sb3' where empno=111;

if sql%found then

dbms_output.put_line('已更新');

else

dbms_output.put_line('未更新');

end if;

end;

 

7.强类型动态游标

declare type emptype is ref cursor return emp%rowtype;

cu_emp emptype;

e_count number;

e emp%rowtype;

begin

select count(*) into e_count from emp where job='PRESIDENT1';

if e_count=0 then

open cu_emp for select * from emp;

else

open cu_emp for select * from emp where job='PRESIDENT';

end if;

fetch cu_emp into e;

while cu_emp%found loop

dbms_output.put_line('编号：'||e.empno||'，姓名：'||e.ename||'，基本薪资：'||e.sal);

fetch cu_emp into e;

end loop;

close cu_emp;

end;

8.弱类型动态游标（更强大）

declare type customType is ref cursor;

e_count number;

e emp%rowtype;

s salgrade%rowType;

cType customType;

begin

select count(*) into e_count from emp where job='PRESIDENT1';

if e_count=0 then

open cType for select * from salgrade;

fetch cType into s;

while cType%found loop

dbms_output.put_line('等级：'||s.grade||'，最低薪资：'||s.losal||'，最高薪资：'||s.hisal);

fetch cType into s;

end loop;

close cType;

else

open cType for select * from emp where job='PRESIDENT';

fetch cType into e;

while cType%found loop

dbms_output.put_line('编号：'||e.empno||'，姓名：'||e.ename||'，基本薪资：'||e.sal);

fetch cType into e;

end loop;

close cType;

end if;

end;