ORACLE游标详解

前言：本文为本人学习笔记，所用到的例子均来自oracle11g自带的scott模式，参考书籍清华大学出版社Oracle从入门到精通，还请大家支持购买正版书籍，话不多说，直接开始！

一、概述

什么是游标

• 游标提供了一种从表中检索数据并进行操作的灵活手段
• 处理客服端发送到服务器端的sql语句，或者是批处理、存储过程、触发器中的数据处理请求

游标的作用

• 通过游标PL/SQL程序可以一次处理查询结果集其中的一行，对该数据进行特定操作

基本原理

• PL/SQL块在执行SELECT、INSERT、UPDATE、DELETE语句时，Oracle会在内存中为其分配上下文区（缓冲区），游标是指向该区的一个指针。
• 游标为应用程序提供了一种具有对多行数据查询结果集中的每一行数据分班进行单独处理的方法
• 游标分显示游标、隐式游标、REF游标

二、显示游标

• 显示游标是用户声明和操作的一种游标，通常用于操作查询结果集（SELECT语句返回的查询结果）
• 使用显示游标处理数据的步骤：声明游标、打开游标、读取游标、关闭游标
• 读取游标操作每次只能读取一行数据，对于多条记录需要反复读取直到读取不到为之，可使用for循环遍历
• 声明游标需要在声明部分进行，其他在执行或异常处理部分进行

1、声明游标

cursor 游标名(游标输入参数)
is select语句
-- 游标参数可以有多个
1
2
3

示例：声明游标读取emp表中job='SALEMAN’的职员

declare
 cursor cur_emp(var_job in varchar2:='SALEMAN') -- varchar不可以指定长度，其他类型也一样
 is select empno,ename,sal
    from emp where job = var_job;
1
2
3
4

2、打开游标

OPEN 游标名（参数） -- 参数如果为空，则为默认参数
1

示例：用上面声明的游标查找job='MANAGER’的人

open cur_emp('MANAGER'); -- 如果没有输入参数，则默认查找SALEMAN
1

3、读取游标

FETCH CUR_NAME INTO {VARIABLE}; -- VARIABLE 一个变量列表或“记录”变量
1

• 游标中包含一个数据行指针用来指向当前数据行
• 打开游标时指针指向第一行，使用 FETCH … INTO 语句后指向下一行
• 指针指到最后一条记录为止（实际上指针最后一条记录之后是不存在的，为空，这里只是表示遍历完所有数据），此时游标的%FOUND属性值为FALSE

示例：声明一个检索emp表的游标，搜索职务为 MANAGER 的员工信息，使用 FETCH…INTO 语句和 WHILE 语句来读取游标中所有员工信息，最后输出读取到的信息

-- 去cmd - sqlplus执行
set serveroutput on
declare
    cursor cur_emp(var_job in varchar2:='SALMANE')  -- 声明游标，检索员工信息
    is select empno,ename,sal from emp  where job = var_job;
    type record_emp is record(  -- 声明一个记录类型（RECORD 类型）
    -- 定义当前成员变量
    var_empno emp.empno%type,
    var_ename emp.ename%type,
    var_sal emp.sal%type
       );
    emp_row record_emp; -- 声明一个 record_enp 类型的变量
begin
    open cur_emp('MANAGER');   -- 打开游标
    fetch cur_emp into emp_row;  -- 让游标指向第一行，并将值保存到emp_row中
    while cur_emp%found loop
      dbms_output.put_line(emp_row.var_ename||'的编号是'||emp_row.var_empno||'，工资是'||emp_row.var_sal);
      fetch cur_emp into emp_row;  -- 让指针指向下一行，并将值保存到emp_row中
    end loop;
    close cur_emp; -- 关闭游标
end;      
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

使用while之前，先试用 FETCH … INTO 将游标指针指向第一行，保证 %FOUND 值为true，从而保证循环条件成立。

4、关闭游标

-- close 游标名
close cur_emp;
1
2

关闭游标后，系统会释放被占用的内存

三、隐式游标

• 执行一个SQL语句时，系统会自动创建一个隐式游标
• 隐式游标主要用来处理数据操作语句（如UPDATE、DELETE等）
• 隐式游标也有属性，因此在使用隐式游标的属性时，需要在属性前加上隐式游标的默认名称 – SQL
• 实际开发中，常使用隐式游标来判断更新数据行或删除数据行的情况

示例：吧emp表中的 SALESMAN 的工资上调20%，然后使用隐式游标的sql的%ROWCOUNT属性输出上调工资的员工数量

set serveroutput on
begin
  update emp set sal=sal*(1+0.2) where job = 'SALESMAN';
  if sql%notfound then
    dbms_output.put_line('0'); --如果没有数据行被影响
  else
    dbms_output.put_line(sql%rowcount); --输出受影响的数据行
  end if;  
end;      
/
1
2
3
4
5
6
7
8
9
10

四、游标的属性

• 游标属性只能用在PL/SQL的流程控制语句内，而不能用在SQL语句内
• 显示游标与隐式游标都具有以下四个属性，通过这些属性可以获知SQL语句的执行结果以及该游标的状态信息

1、%FOUND（是否找到游标）

• 表示当前游标是否指向有效一行，是 -> true ； 否 -> false
• 在隐式游标中%FOUND属性的引用方法是SQL %FOUND

示例：

delete from emp where empno = emp_id;  -- emp_id为一个有值变量
if sql %FOUND then
  insert into success valuse(empno);   -- 删除成功，写进SUCCESS表里
else
  insert into fail valuse(empno);      -- 删除失败，写进Fail表里
end if;  
1
2
3
4
5
6

2、%NOTFOUND（是否没找到游标）

与%FOUND相反

3、%ROWCOUNT（游标行数）

• 记录了游标抽取过的记录行数，也可以理解为当前游标所在的行号

示例：

loop
  fetch cur_emp into var_empno,var_ename,var_job;
  exit when cur_emp%rowcount = 10                 -- 只抽取10条记录
  ...
end loop;  
1
2
3
4
5

4、%ISOPEN（游标是否打开）

示例：

if cur_emp%isopen tneh
  fetch cur_emp into var_empno,var_ename,var_job;
else
  open cur_emp;
end if;    
1
2
3
4
5

5、参数化游标

定义游标时带上参数，使得在使用游标时根据参数不同所选中的数据行也不同，达到动态使用的目的

示例：声明一个游标检索指定员工的信息.

set serveroutput on
declare 
 var_ename varchar2(50);      -- 声明变量，用来存储员工名称
 var_job varchar2(50);        -- 声明变量，用来存储员工职务
 cursor cur_emp               -- 定义游标，检索指定编号的信息
 is select ename,job from emp where empno = 7499;
begin
 open cur_emp;                -- 打开游标  
 fetch cur_emp into var_ename,var_job; --读取游标，并存储员工名称和职务
 if cur_emp%found then
   dbms_output.put_line(var_ename || var_job);
 else
    dbms_output.put_line('无数据');
 end if;
end; 
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

五、游标变量

• 游标变量是动态的，可以在运行时与不同的语句相关联
• 被用于处理多行的查询结果集
• 不同于特定的查询绑定，游标变量在打开游标时才能确定对于的查询

1、声明游标变量

• 游标变量是一种引用类型，程序运行时可以指向不同的存储单元
• 游标变量的返回类型必须是一个记录类型

定义一个游标变量的完整语法

-- 类型名是新的引用类型的名字
-- 返回类型是一个记录类型
TYPE<类型名> IS REF CURSOR
RETURN<返回类型>
1
2
3
4

2、打开游标变量

OPEN <游标变量> FOR <SELECT语句>
1

如果游标变量是受限的，则SELECT语句返回的类型必须与游标变量所受限的记录类型匹配

3、关闭游标变量

• 与关闭静态游标类似，均使用close语句直接关闭，释放查询所使用的内存空间
• 关闭已关闭的游标是非法的

六、通过for循环遍历游标

• 使用游标处理结果集时，可以配合使用FOR语句来完成
• 使用FOR语句遍历游标中的数据时，可以把它的计时器看做一个自动的RECORD类型变量

（1）FOR语句遍历隐式游标中的数据时，通常在关键字 IN 后面提供由 SELECT 查询出的结果集，在检索结果集的过程中，ORACL 会自动提供一个隐式的游标SQL。
示例：使用隐式游标查询除JOB为 SALESMAN 的员工并输出

set serveroutput on
begin
  for emp_record in(select empno,ename,sal from emp where job = 'SALESMAN') -- 遍历隐式游标中的记录
  loop
    dbms_output.put_line(emp_record.empno); --输出员工编号
    dbms_output.put_line(emp_record.ename); --输出员工姓名
    dbms_output.put_line(emp_record.sal);   --输出员工薪资
  end loop;
end ;
/   
1
2
3
4
5
6
7
8
9
10

（2）使用显示游标时，IN 关键字后面提供游标名称
示例：检索emp表部门编号为30的员工并输出

set serveroutput on
declare
    cursor cur_emp is select * from emp where deptno = 30;
begin
  for emp_record in cur_emp -- 遍历显示游标中的记录
  loop
    dbms_output.put_line(emp_record.empno); --输出员工编号
    dbms_output.put_line(emp_record.ename); --输出员工姓名
    dbms_output.put_line(emp_record.sal);   --输出员工薪资
  end loop;
end ;
/   
1
2
3
4
5
6
7
8
9
10
11
12