Oracle游标的使用实例详解

什么是游标？

①从表中检索出结果集，从中每次指向⼀条记录进⾏交互的机制。②关系数据库中的操作是在完整的⾏集合上执⾏的。

由 SELECT 语句返回的⾏集合包括满⾜该语句的 WHERE ⼦句所列条件的所有⾏。由该语句返回完整的⾏集合叫做结果集。应⽤程序，尤其是互动和在线应⽤程序，把完整的结果集作为⼀个单元处理并不总是有效的。这些应⽤程序需要⼀种机制来⼀次处理⼀⾏或连续的⼏⾏。⽽游标是对提供这⼀机制的结果集的扩展。

游标是通过游标库来实现的。游标库是常常作为数据库系统或数据访问 API 的⼀部分⽽得以实现的软件，⽤来管理从数据源返回的数据的属性（结果集）。这些属性包括并发管理、在结果集中的位置、返回的⾏数，以及是否能够在结果集中向前和/或向后移动（可滚动性）。

游标跟踪结果集中的位置，并允许对结果集逐⾏执⾏多个操作，在这个过程中可能返回⾄原始表，也可能不返回⾄原始表。换句话说，游标从概念上讲基于数据库的表返回结果集。

由于它指⽰结果集中的当前位置 ，就像计算机屏幕上的光标指⽰当前位置⼀样，“游标”由此得名。游标有什么作⽤？

①指定结果集中特定⾏的位置。

②基于当前的结果集位置检索⼀⾏或连续的⼏⾏。 ③在结果集的当前位置修改⾏中的数据。

④对其他⽤户所做的数据更改定义不同的敏感性级别。⑤可以以编程的⽅式访问数据库。引⾔

本节对Oracle中的游标进⾏详细讲解。本节所举实例来源Oracle中scott⽤户下的emp表dept表：

⼀、游标：1、概念：

游标的本质是⼀个结果集resultset，主要⽤来临时存储从数据库中提取出来的数据块。⼆、游标的分类：1、显式游标：

由⽤户定义，需要的操作：定义游标、打开游标、提取数据、关闭游标，主要⽤于对查询语句的处理。属性：%FOUND %NOTFOUND %ISOPEN %ROWCOUNT

Example:打印emp表的员⼯信息

DECLARE

CURSOR emp_cursor IS SELECT empno,ename,job FROM emp; v_empno emp.empno%TYPE; v_name emp.ename%TYPE; v_job emp.job%TYPE;BEGIN

OPEN emp_cursor; LOOP

FETCH emp_cursor INTO v_empno,v_name,v_job;

DBMS_OUTPUT.PUT_LINE('员⼯号为:'||v_empno||'姓名是'||v_name||'职位：'||v_job); EXIT WHEN emp_cursor%NOTFOUND; END LOOP;

CLOSE emp_cursor;END;

这⾥严格按照显⽰游标的书写规则：DECLARE emp_cursor定义游标OPEN emp_cursor打开游标FETCH emp_cursorINTO...提取数据CLOSE emp_cursor关闭游标,因为提取出来的数据属于多⾏，所以通过loop循环打印即可。Example2:检验游标是否打开,如果打开显⽰提取⾏数

DECLARE

CURSOR emp_cursor IS SELECT empno,ename,job FROM emp; v_empno emp.empno%TYPE; v_name emp.ename%TYPE; v_job emp.job%TYPE;BEGIN

OPEN emp_cursor; LOOP

FETCH emp_cursor INTO v_empno,v_name,v_job; EXIT WHEN emp_cursor%NOTFOUND; END LOOP;

IF emp_cursor%ISOPEN THEN

DBMS_OUTPUT.PUT_LINE('游标已打开');

DBMS_OUTPUT.PUT_LINE('读取了'||emp_cursor%ROWCOUNT||'⾏'); ELSE

DBMS_OUTPUT.PUT_LINE('游标没有打开'); END IF;

CLOSE emp_cursor;END;

通过%ISOPEN属性判断游标是否打开，%ROWCOUNT判断获取⾏数。

2、隐式游标：由系统定义并为它创建⼯作区域，并且隐式的定义打开提取关闭，隐式游标的游标名就是'SQL',属性和显⽰游标相同，主要⽤于对单⾏select语句或dml操作进⾏处理。Example:⼜⽤户输⼊员⼯号修改员⼯⼯资如成功则打印输出成功标志。

为了尽量不改变原表，创建新表emp_new和原表数据相同：

CREATE TABLE emp_newAS

SELECT * FROM emp;

BEGIN

UPDATE emp_new SET sal = sal+500 WHERE empno=&empno; IF SQL%FOUND THEN

DBMS_OUTPUT.PUT_LINE('成功修改'); COMMIT; ELSE

DBMS_OUTPUT.PUT_LINE('修改失败'); ROLLBACK; END IF;END;

这⾥注意增删改以后要对做的操作进⾏commit提交，如果操作失败则rollback回滚刚才的操作。3、参数游标：

在定义游标时加⼊参数的游标，可以配合游标for循环快速找到需要的数据。这⾥先讲⼀下游标for循环A、游标FOR循环：

隐含的执⾏了打开提取关闭数据，代码精简很多。Expression:FOR table_record IN table_cursor LOOP　　STATEMENT;END LOOP;

Example:使⽤游标For循环打印输出员⼯信息：

DECLARE

CURSOR emp_cursor IS SELECT empno,ename,job FROM emp;BEGIN

FOR emp_record IN emp_cursor LOOP

DBMS_OUTPUT.PUT_LINE('员⼯号：'||emp_record.empno||'员⼯姓名'||emp_record.ename||'员⼯职位'||emp_record.job); END LOOP;END;

这⾥游标FOR循环省去了对于取到的数据的变量的命名和赋值，同时如果全部打印则不⽤写循环条件，代码精简了很多。如果想让代码更加精简，则可以去掉对游标的声明引⼊⼦查询即可，操作如下。

BEGIN

FOR emp_record IN (SELECT empno,ename,job FROM emp) LOOP

DBMS_OUTPUT.PUT_LINE('员⼯号：'||emp_record.empno||'员⼯姓名'||emp_record.ename||'员⼯职位'||emp_record.job); END LOOP;END;

代码更加精简，得到的结果相同。和隐式游标是不是有点像，但隐式游标主要⽤于的是单⾏select和dml语句的操作，注意2者⽤法的区别。

下⾯继续参数游标的实例：

Example：输⼊部门号打印员⼯信息：

DECLARE

CURSOR emp_cursor(dno NUMBER)IS SELECT empno,ename,job FROM emp WHERE deptno=dno;BEGIN

FOR emp_record IN emp_cursor(&dno) LOOP

DBMS_OUTPUT.PUT_LINE('员⼯号'||emp_record.empno||'姓名'||emp_record.ename||'职位'||emp_record.job); END LOOP;END;

这⾥既然有参数，那么必然会有对游标的声明，在结合游标FOR循环快速超找所需要的数据。三、使⽤游标修改数据的注意事项

1、使⽤游标修改数据时，为防⽌他⼈在⾃⼰操作数据时对数据进⾏修改，oracle提供for update⼦句进⾏加锁。

同时在你使⽤update或delete时，必须使⽤where current of+name_cursor语句，以及在最后记得提交。如果是级联操作则可以使⽤for update of 来进⾏相关表的加锁。

Example1：对职位是PRESIDENT的员⼯加1000⼯资，MANAGER的⼈加500⼯资

CREATE TABLE emp_newAS

SELECT * FROM emp;

DECLARE

CURSOR empnew_cursor IS SELECT ename,job FROM emp_new FOR UPDATE;BEGIN

FOR empnew_record IN empnew_cursor LOOP

DBMS_OUTPUT.PUT_LINE('姓名'||empnew_record.ename||'职位'||empnew_record.job); IF empnew_record.job='PRESIDENT' THEN

UPDATE emp_new SET sal=sal+1000 WHERE CURRENT OF empnew_cursor; ELSIF empnew_record.job='MANAGER' THEN

UPDATE emp_new SET sal=sal+500 WHERE CURRENT OF empnew_cursor; END IF; END LOOP; COMMIT;END;

可以看到这⾥⼯资有了相应的变化。⾄此，Oracle游标解析完毕，总⽽⾔之，游标只是作为我们从数据库中提取出来的⼀部分数据，我们针对这个结果集做⼀系列的操作。总结

以上就是这篇⽂章的全部内容了，希望本⽂的内容对⼤家的学习或者⼯作具有⼀定的参考学习价值，如果有疑问⼤家可以留⾔交流，谢谢⼤家对的⽀持。