oracle 11g 的游标

1. 简介

在使用 select 语句查询数据时，查询返回的数据存放在结果集中，用户如何从查询得到的结果集中逐行逐列地获取存储的数据，从而在应用程序中使用这些值，就需要一种定位并控制结果集的机制，即游标。

游标是映射在结果集中一行数据上的位置实体，用来处理从数据库中检索的多行记录。利用游标，程序可以逐个地处理和便利一次检索返回的整个记录表，即可以对当前行数据进行操作。

2. 类型

1，隐式游标：在 PL/SQL 程序中执行 DML SQL 语句时自动创建隐式游标，游标名字固定叫 sql。

2，显式游标：显式游标用于处理返回多行的查询。

3，REF 游标：REF （参照）游标用于处理运行时才能确定的动态 SQL 查询的结果。

3. 隐式游标

在 PL/SQL 中使用 DML 语句时自动创建隐式游标。隐式游标自动声明、打开和关闭，其名为 sql。我们可以通过检查隐式游标的属性可以获得最近执行的 DML 语句的信息，隐式游标的属性有：

%FOUND – SQL 语句影响了一行或多行时为 TRUE
%NOTFOUND – SQL 语句没有影响任何行时为TRUE
%ROWCOUNT – SQL 语句影响的行数
%ISOPEN - 游标是否打开，始终为FALSE，因为游标打开执行后会立即关闭。

下面我们举例来说明隐式游标 sql 的存在，首先由如下 student 表：

SQL> select * from student;

                                    SNO SNAME                                         SAGE
--------------------------------------- ---------- ---------------------------------------
                                      1 Tom                                             21
                                      2 Kite                                            22
                                      3 Bob                                             23
                                      4 Mike                                            24

编写如下 pl/sql 执行块：

set serverout on; -- 开启控制台打印
begin
    update student set sage = sage + 10;
    if (sql%found) then -- 判断时候影响多行
        dbms_output.put_line('表已更新' || sql%rowcount || '行记录！'); -- 影响的行数
    end if;
end;
/
表已更新4行记录！ -- 输出结果

begin
    update student set sage = sage + 10 where sno = 5; -- sno = 5 的不存在
    if (sql%notfound) then -- 判断时候影响多行
        dbms_output.put_line('表已更新' || sql%rowcount || '行记录！'); -- 影响的行数
    end if;
end;
/
表已更新0行记录！ -- 输出结果

4. 显式游标

显式游标在 PL/SQL 块的声明部分定义查询，该查询可以返回多行。显式游标的操作过程为声明游标、打开游标、使用游标取出记录和关闭游标。我们可以以下述的示例来体现说明，实现效果类似于上述使用隐式游标操作 student 一样：

declare
    stud student%rowtype; -- 表示一行 student 表记录类型标量
    cursor stu_cur is select * from student; -- 1. 声明游标
begin
    open stu_cur; -- 2. 打开游标
    loop 
        fetch stu_cur into stud; -- 3. 使用游标取出记录并保存到变量 stud 中
        exit when stu_cur%notfound;
        dbms_output.put_line('学号是：' || stud.sno || '，姓名是：' || stud.sname);
    end loop;
    close stu_cur; -- 4. 关闭游标
end;
/

输出结果如下所示：

学号是：1，姓名是：Tom
学号是：2，姓名是：Kite
学号是：3，姓名是：Bob
学号是：4，姓名是：Mike

5. 带参数显式游标

声明显式游标时可以带参数以提高灵活性。声明带参数的显式游标的语法如下：

1	CURSOR <cursor_name>(<param_name> <param_type>) IS select_statement;

下述示例我们通过控制台输出序号打印输出执行学生信息，具体如下所示：

declare
    in_sno number; -- 记录输出的学生学号
    stud student%rowtype; -- 表示一行 student 表记录类型标量
    cursor stu_cur(in_sno number) is select * from student where sno = in_sno; -- 1. 声明游标，in_sno 为参数
begin
    in_sno := &学生学号;
    open stu_cur(in_sno); -- 2. 打开游标，带上参数
    loop 
        fetch stu_cur into stud; -- 3. 使用游标取出记录并保存到变量 stud 中
        exit when stu_cur%notfound;
        dbms_output.put_line('学号是：' || stud.sno || '，姓名是：' || stud.sname);
    end loop;
    close stu_cur; -- 4. 关闭游标
end;
/

当我们输出学号 1 的时候，会打印输出如下结果：

1	学号是：1，姓名是：Tom

6. 使用显式游标更新行

允许使用游标删除或更新活动集中的行，但是声明游标时必须使用 SELECT … FOR UPDATE语句。语法如下所示：

-- 声明游标时
CURSOR <cursor_name> IS SELECT statement FOR UPDATE; -- 记得带上 for update

-- 更新语句的语法
UPDATE <table_name> SET <set_clause> WHERE CURRENT OF <cursor_name> -- 记得带上 current of 游标名称

-- 删除语句的语法
DELETE FROM <table_name> WHERE CURRENT OF <cursor_name> -- 记得带上 current of 游标名称

示例如下：

SQL> select * from student;

                                    SNO SNAME                                         SAGE
--------------------------------------- ---------- ---------------------------------------
                                      1 Tom                                             21
                                      2 Kite                                            22
                                      3 Bob                                             23
                                      4 Mike                                            24
                                      
declare
    stud student%rowtype; -- 表示一行 student 表记录类型标量
    cursor stu_cur is select * from student where sno = 2 or sno = 3 for update; -- 1. 声明游标，带上 for update
begin
    open stu_cur; -- 2. 打开游标
    loop 
        fetch stu_cur into stud; -- 3. 使用游标取出记录并保存到变量 stud 中
        exit when stu_cur%notfound;
        update student set sage = sage + 100 where current of stu_cur; -- 带上 current of stu_cur
    end loop;
    close stu_cur; -- 4. 关闭游标
end;
/

执行结果如下，其中学号为2 和 3 的学生的年龄增加了 100：

SQL> select * from student;

                                    SNO SNAME                                         SAGE
--------------------------------------- ---------- ---------------------------------------
                                      1 Tom                                             21
                                      2 Kite                                           122
                                      3 Bob                                            123
                                      4 Mike                                            24

7. 循环游标

循环游标只适用于查询的情况，不适用于更新和删除的情况

上面的示例中我们中用到了 loop 语句来循环访问游标，同时在访问前后分别需要打开和关闭游标，同时还需要取出游标的记录并赋值到指定的变量中。为了简化操作，我们可以直接使用循环游标。当用户需要从游标中提取所有记录时使用，循环游标的语法如下：

FOR <record_index> IN <cursor_name>
LOOP
    <executable statements>
END LOOP;

示例如下所示：

declare
    cursor stu_cur is select * from student;
begin
    for cur_sub in stu_cur loop -- cur_sub 表示游标指向的每一行记录
        dbms_output.put_line('学号是：' || cur_sub.sno || '，姓名是：' || cur_sub.sname);
    end loop;
end;
/

执行结果如下所示：

学号是：1，姓名是：Tom
学号是：2，姓名是：Kite
学号是：3，姓名是：Bob
学号是：4，姓名是：Mike

8. 批量处理

可以使用 fetch … bulk collect into 语句来进行批量、快速提取数据，如下是使用示例：

declare
  cursor my_cursor is select ename from emp where deptno = 10; -- 声明名为 my_cursor 的游标
  type  ename_table_type is table of varchar2(10); -- 声明名为 ename_table_type 的表类型，该表类型由 varchar2(10) 字段类型组成
  ename_table  ename_table_type; -- 声明名为 ename_table 的 ename_table_type 表类型变量
begin
  open  my_cursor; -- 打开游标
  fetch my_cursor bulk collect into  ename_table; -- 批量取出游标执行的记录并设置到 ename_table 变量中，其实 ename_table 保存的即是
  -- select ename from emp where deptno = 10 的查询结果集，其中 ename 的类型就是 varchar2(10)
  for  i in 1..ename_table.count  loop -- 遍历打印 ename_table 的所有记录
     dbms_output.put_line(ename_table(i));
  end  loop;
  close my_cursor; -- 关闭游标
end;
/

查看上述语句的第七行，我们这里使用fetch my_cursor bulk collect into ename_table;，而不使用fetch my_cursor into ename_table;是可以提高游标取书速度。

9. REF 游标

REF 游标和游标变量用于处理运行时动态执行的 SQL 查询，创建游标变量需要两个步骤：

声明 REF 游标类型
声明 REF 游标类型的变量

用于声明 REF 游标类型的语法为：

1	TYPE <ref_cursor_name> IS REF CURSOR [RETURN <return_type>];

打开游标变量的语法如下：

1	OPEN cursor_name FOR select_statement;

我们使用 REF 游标来实现通过对输入的学号参数打印输出指定学号的学生信息，由于在声明游标的时候我们无法知道要输出的是哪个学号的学生，因此我们需要使用 REF 游标来实现，示例如下：

student 表中的数据如下所示：

SQL> select * from student;

                                    SNO SNAME                                         SAGE
--------------------------------------- ---------- ---------------------------------------
                                      1 Tom                                             21
                                      2 Kite                                            22
                                      3 Bob                                             23
                                      4 Mike                                            24

如下是使用参照游标的具体实现 sql：

declare
    type refcur is ref cursor; -- 声明名称为 refcur 的参照游标类型变量
    cursor2 refcur; -- 声明名称为 cursor2 的 refcur 类型的变量
    my_sno student.sno%type;
    no2 student.sno%type;
    name2 student.sname%type;
begin
    my_sno := '&学号'; -- 控制台输入学生学号
    open cursor2 for select sno, sname from student where sno = my_sno; -- 打开游标，此处才绑定 select 语句，而不是在声明的时候
    loop
        fetch cursor2 into no2, name2; -- 取出游标指定的值并设置到指定变量中
        exit when cursor2%notfound;
        dbms_output.put_line('学号是：' || no2 || '，姓名是：' || name2);
    end loop;
    close cursor2;
end;
/

当我们输入 4 的时候，执行结果如下所示：

1	学号是：4，姓名是：Mike