如何在Oracle数据库中使用游标

48次阅读

没有评论

共计 3958 个字符，预计需要花费 10 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章将为大家详细讲解有关如何在 Oracle 数据库中使用游标，文章内容质量较高，因此丸趣 TV 小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

什么是游标？

①从表中检索出结果集，从中每次指向一条记录进行交互的机制。

②关系数据库中的操作是在完整的行集合上执行的。

由 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_cursor INTO… 提取数据 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_new
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_new
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;