pl/sql语句中正确使用静态游标和动态游标

1 什么是游标?

当在PL/SQL块中执行DML(增删改查)时，Oracle会为其分配上下文区(Context Area)，游标是指向上下文区的指针

2 游标分类?

显示游标和隐式游标

3 游标的属性

• %FOUND ---在结果集中找到了记录

• %NOTFOUND ---在结果集中没有找到记录

• %ROWCOUNT ---当前已经从游标中获取的记录数量

• %ISOPEN ---是否打开

• %BULK_ROWCOUNT ---在forall操作中，Sql%rowcount表示commit之前被影响的记录总行数，而sql%bulk_rowcount(i)表示每一次的操作所影响的行数，该值是存在一个集合里，forall中的第n条dml语句处理的行数存储在该集合的第n个元素中

• %BULK_EXCEPTION

a 隐式游标

对于数据库操纵语句和单行SELECT INTO语句来说，Oracle会为它们分配隐含游标。

DECLARE

r NUMBER;

BEGIN

UPDATE emp e SET e.sal=4000 WHERE e.deptno=20;

r := SQL%ROWCOUNT; --隐式游标

IF(SQL%FOUND)THEN --隐式游标

dbms_output.put_line(r);

END IF;

END;

b 显示游标

显示游标操作步骤

• 解析：确保SQL语句有效，然后才决定执行计划。

• 绑定：将PL/SQL中变量与绑定变量关联。

• 打开：打开游标。

• 执行：执行游标中的SELECT语句。

• 提取：提取游标的当前行后指针移到下一行。

• 关闭：关闭游标。

实例:显示大于3000元工资的员工姓名

第一种写法

declare

cursor emp_cur is select * from emp wheresal>300000;

emp_row emp%rowtype;

begin

for x in emp_cur

loop

dbms_output.put_line('姓名为:'||x.ename);

end loop;

end;

第二种写法

declare

--定义游标cur_emp

cursor cur_emp is select * from emp where sal>3000;

--定义一个人员表的变量

v_emp emp%rowtype;

begin

--打开游标

open cur_emp;

loop

fetch cur_emp into v_emp ;--从游标中获取数据

exit when cur_emp%notfound ;--当游标执行完成时,退出

dbms_output.put_line(v_emp.ename||'薪酬是:'||v_emp.sal);

end loop;

close cur_emp ;--关闭

end ;

游标还可以带参数

--用户输入一个部门编号.

--要求将该部门下的员工的工资全部增加200,并输出增加后的工资和姓名

declare

v_depno dept.deptno%type;

cursor cur_emp(v_dep number) is

select * from emp e where e.deptno=v_dep;

v_emp emp%rowtype;

begin

v_depno:=&输入部门ID;

open cur_emp(v_depno) ;

loop

fetch cur_emp into v_emp;

exit when cur_emp%notfound ;

update emp e set e.sal=e.sal+300 where e.empno=v_emp.empno;

dbms_output.put_line(v_emp.ename||' '||(v_emp.sal+300));

end loop;

close cur_emp;

commit;

end ;

以上为简单使用静态游标（强类型）.下面演示动态游标（弱类型）的用法

--定义一个动态游标,pl/sql中分别打开两个不同的表

declare

cur_temp SYS_REFCURSOR; --定义动态游标

v_emp emp%rowtype;

v_dept dept%rowtype;

begin

--员工游标

open cur_temp for select * from emp;

loop

fetch cur_temp into v_emp;

exit when cur_temp%notfound ;

dbms_output.put_line(v_emp.ename);

end loop;

close cur_temp;

--部门游标

open cur_temp for select * from dept;

loop

fetch cur_temp into v_dept;

exit when cur_temp%notfound ;

dbms_output.put_line(v_dept.dname);

end loop;

close cur_temp;

end;

以上语句有oracle客户端的网友，使用scott账号登录之后，可以直接测试。希望对大家有所帮助。

游标的分类