Oracle游标 CURSOR实例详解

游标 CURSOR

一、游标概述

游标（cursor）是数据库系统在内存中开设的一个数据缓冲区，存放SQL语句的执行结果。

每个游标都有一个名字，用户可以用SQL语句逐一从游标中获取记录，并赋给变量做进一步处理。

作用：用于定位结果集的行 和 遍历结果集。

游标产生时间：当执行DML SQL语句时；

游标用于存放：结果集

游标有名字吗：有，SQL或用户给他取名

游标如何操作：用FETCH语句逐一从游标中提取记录，并赋给变量进一步处理

同时可打开几个游标：多个，具体数量由数据库初始化参数OPEN_CURSOR决定。

二、游标分类

游标分为：静态游标（隐式和显式）和REF游标（+游标变量）

1. 游标的属性

%FOUND SQL语句影响了一行或多行时为true；

%NOTFOUND SQL语句没有影响任何行时为true(常用，没找到为T，就退出)

%ROWCOUNT SQL语句影响的行数；

%ISOPEN 游标是否打开，始终为false。

2. 静态游标

静态游标是指结果集已经确实（静态定义）的游标。

静态游标分：隐式游标 和 显示游标。

2.1 隐式游标

在PL/SQL中隐式游标在执行DML SQL(Insert/Delete/Update/Select)语句时自动创建、自动声明、打开和关闭，其名SQL（注：所有的隐式游标名都叫“SQL”）；

示例：

使用游标的属性。

BEGIN

UPDATE emp SET sal=5000 WHERE empno=7369;

IF SQL%FOUND THEN

DBMS_OUTPUT.PUT_LINE( ‘表已更新’ );

END IF;

END;

提示：

在Java中，如对表进行了增删改操作，结果会返回n，表示在数据库中多少行受影响。

我们通过 n>0 或 n=0 来判断SQL代码是否执行成功。

这个n 即SQL%ROWCOUNT属性。

2.2 显示游标

显示游标用于处理SELECT时返回多行的查询；

增删改时不会用显示游标；

显示游标需要手动的去做声明、打开、提取、关闭操作。

示例：

l 显示游标操作

DECLARE

--声明游标：划分存储区域，注意此时并没有执行Select语句。

CURSOR c_name IS

SELECT sal FROM emp WHERE empno=7369;

my_sal emp.sal%TYPE;

BEGIN

--打开游标：执行select语句，获得结果集存到游标中，此时游标指向结果集头，而不是第一条记录。

OPEN c_name;

--提取记录：移动游标取一条记录

FETCH c_name INTO my_sal;

DBMS_OUTPUT.PUT_LINE(my_sal);

--关闭游标：将游标放入缓冲池中，没有完全释放资源，可重新打开。

CLOSE c_name;

END;

l 带参数的显示游标

DECLARE

CURSOR c_name( dno NUMBER ) IS

SELECT * FROM emp WHERE deptno=dno;

my_a emp%ROWTYPE;

BEGIN

OPEN c_name(10); --打开游标，并传值

LOOP

FETCH c_name INTO my_a;

EXIT LOOP c_name%NOTFOUND;

DBMS_OUTPUT.PUT_LINE( '名字：'||my_a.ename );

END LOOP;

CLOSE c_name;

END;

l For循环游标

作用：简化游标处理代码（简化打开、提取、关闭）。

基本语法：

FOR r_index IN cursor_name LOOP

……

END LOOP;

示例：

For循环游标操作

DECLARE

CURSOR c_name IS

SELECT * FROM emp;

BEGIN

FOR i IN c_name LOOP

DBMS_OUTPUT.PUT_LINE( i.ename||' '||i.job );

END LOOP;

END;

注释：如同foreach语句一样，i默认为行类型(%rowtype)，自动打开、提取、关闭。

l 带参数的For循环游标

DECLARE

CURSOR c_name( dno number ) IS

SELECT * FROM emp WHERE deptno=dno;

BEGIN

FOR i IN c_name(30) LOOP

DBMS_OUTPUT.PUT_LINE( i.ename||' '||i.job );

END LOOP;

END;

l 游标的嵌套问题

固名思义，就是在游标中嵌套一个游标。

示例：

列出所有部门的人员信息。

要求结果如下：

部门号：10 部门名称：****

编号：** 姓名：**

编号：** 姓名：**

……

部门号：20 部门名称：****

编号：** 姓名：**

编号：** 姓名：**

……

分析：

1.创建部门游标，先将部门信息列出来；

2.在循环取部门信息时，再创建打开员工表游标，进行读取。

代码：

DECLARE

CURSOR mydc IS

SELECT * FROM dept;

mydr dept%ROWTYPE;--声明dept部门表对象

CURSOR myec(dno NUMBER) IS

SELECT * FROM emp WHERE empno=dno;

myer emp%ROWTYPE;--声明emp员工表对象

BEGIN

--先打开部门结果集游标

OPEN mydc;

LOOP

FETCH mydc INTO mydr;

EXIT LOOP mydc%NOTFOUND;--提取完退出LOOP

DBMS_OUTPUT.PUT_LINE( '部门号：'||mydr.deptno||' 部门名称：'||mydr.dname );

--再打开员工结果集游标，入部门编号

OPEN myec(mydr.deptno);

LOOP

FETCH myec INTO myer;

EXIT LOOP myec%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(' 编号：'||myer.empno||' 姓名：'||myer.ename);

END LOOP;

CLOSE myec;

END LOOP;

CLOSE mydc;

END;

--使用For循环简化

DECLARE

CURSOR mydc IS

SELECT * FROM dept;

CURSOR myec(dno number) IS

SELECT * FROM emp WHERE empno=dno;

BEGIN

--遍历部门结果集游标

FOR i INT mydc LOOP

DBMS_OUTPUT.PUT_LINE( '部门号'||i.deptno||' 部门名称：'||i.dname );

--遍历员工结果集游标，传下部门编号

FOR j INT myec(i.detpno) LOOP

DBMS_OUTPUT.PUT_LINE( ' 编号'||j.empno||' 姓名：'||j.ename );

END LOOP;

END LOOP;

END;

3. REF游标

REF游标也叫动态游标，动态SQL执行时产生。

REF游标更应该被称之为游标类型，而游标变量则为该类型的游标。

3.1 创建REF游标+游标变量

基本语法：

--创建REF游标

TYPE ref_cursor_name IS REF CURSOR

[RETURN 返回类型];

--声明REF游标变量

游标变量 ref_cursor_name;

示例：

创建个REF游标类型

TYPE ref_name IS REF CURSOR; --定义弱游标类型，名字叫ref_name

c_name ref_name;

3.2 REF游标分类

强类型：带RETURN返回类型

弱类型：不带RETURN返回类型

相对来说，弱类型更为灵活。

示例：

声明强类型REF游标+游标变量

DECLARE

TYPE c_type IS REF CURSOR

RETURN emp%ROWTYPE;

c_name c_type;

3.3 打开游标变量

OPEN cursor_name FOR 查询结果集;

示例：

用REF游标+游标变量显示数据

DECLARE

TYPE my_t IS REF CURSOR

RETURN emp%ROWTYPE; --声明一个游标类型

myc my_t; --定义一个REF类型的游标变量

myr emp%ROWTYPE;

BEGIN

OPEN myc FOR

SELECT * FROM emp;

LOOP

FETCH myc INTO myr;

EXIT WHEN myc%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(myr.ename);

END LOOP;

CLOSE myc;

END;

4. 标变量的优点和限制

4.1 游标变量功能强大，可以简化数据处理；

4.2 游标变量优点：

a.可从不同的select语句中提取结果集；

b.可以作为过程的参数进行传递；

c.可以引用游标的所有属性；

d.可以进行赋值运算；

4.3 游标变量的限制：

a.不能在程序包中声明游标变量；

b.for update子句不能与游标变量一起使用；

c.不能使用比较运算符。

三、动态语句拼接 :n

设定一个游标变量来执行动态的sql语句。

数据库管理员、设计师常用的知识点。

基本语法：

OPEN 游标名 FOR 'SELECT … >:1 …';

USEING 变量名;

示例1：

OPEN cursor_name FOR

'SELECT * FROM emp WHERE sal>:1 ORDER BY sal DESC'

USEING p_sal;

解释：

--设置占位符：sal>:1动态拼接（相当于JDBC中的?占位符）

--填充占位符：将变量p_sal的值代入SELECT中“:1”位置处

--如有多个动态拼接处，请设置:2，:3，……，同时using 后面变量用逗号分隔。

示例2：

DECLARE

r_emp emp%ROWTYPE;

--定义REF游标及游标变量

TYPE c_type IS REF CURSOR;

cur c_type;

p_salary NUMBER;

BEGIN

p_salary := 2500;

--打开游标

OPEN cur FOR

'select * from emp where sal>:1 order by sal desc'

--填充占位符

USING p_salary;

DBMS_OUTPUT.PUT_LINE('薪水大于'|| p_salary ||'的员工有：');

LOOP

--提取游标中的数据到变量r_emp

FETCH cur INTO r_emp;

EXIT WHEN cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('编号：'|| r_emp.empno||'姓名：'||r_emp.ename||'薪水：'||r_emp.sal);

END LOOP;

--关闭游标

CLOSE cur;

END;