Oracle总结（二）：Oracle游标CURSOR

游标 CURSOR

1游标概述

• 游标（cursor）是数据库系统在内存中开设的一个数据缓冲区，存放SQL语句的执行结果。
• 每个游标都有一个名字，用户可以用SQL语句逐一从游标中获取记录，并赋给变量做进一步处理。
• 作用：用于定位结果集的行 和 遍历结果集。
• 游标产生时间：当执行DML SQL语句时；
• 游标用于存放：结果集
• 游标有名字吗：有，SQL或用户给他取名
• 游标如何操作：用FETCH语句逐一从游标中提取记录，并赋给变量进一步处理
• 同时可打开几个游标：多个，具体数量由数据库初始化参数OPEN_CURSOR决定。

2游标分类

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

2.1游标的属性

• %FOUND SQL语句影响了一行或多行时为true；
• %NOTFOUND SQL语句没有影响任何行时为true(常用，没找到为T，就退出)
• %ROWCOUNT SQL语句影响的行数；
• %ISOPEN 游标是否打开，始终为false。

2.2静态游标

• 静态游标是指结果集已经确实（静态定义）的游标。
• 静态游标分：隐式游标和显示游标。

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.1显示游标

显示游标用于处理SELECT时返回多行的查询；
增删改时不会用显示游标；
显示游标需要手动的去做声明、打开、提取、关闭操作。
示例：

显示游标操作

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;

带参数的显示游标

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;

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)，自动打开、提取、关闭。

带参数的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;

游标的嵌套问题

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

示例：
列出所有部门的人员信息。
要求结果如下：
部门号：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;

3REF游标

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标变量的优点和限制

• 游标变量功能强大，可以简化数据处理；
• 游标变量优点：
  a.可从不同的select语句中提取结果集；
  b.可以作为过程的参数进行传递；
  c.可以引用游标的所有属性；
  d.可以进行赋值运算；
• 游标变量的限制：
  a.不能在程序包中声明游标变量；
  b.for update子句不能与游标变量一起使用；
  c.不能使用比较运算符。
  三、动态语句拼接
  设定一个游标变量来执行动态的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;