oracle 11g 的游标

2022-07-28 12:07:34

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 Tom212 Kite223 Bob234 Mike24

编写如下 pl/sql 执行块：

set serverouton;-- 开启控制台打印beginupdate studentset sage= sage+10;if(sql%found)then-- 判断时候影响多行
		dbms_output.put_line('表已更新'||sql%rowcount||'行记录！');-- 影响的行数endif;end;/
表已更新4行记录！-- 输出结果
beginupdate studentset sage= sage+10where sno=5;-- sno = 5 的不存在if(sql%notfound)then-- 判断时候影响多行
		dbms_output.put_line('表已更新'||sql%rowcount||'行记录！');-- 影响的行数endif;end;/
表已更新0行记录！-- 输出结果

4. 显式游标

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

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

输出结果如下所示：

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

5. 带参数显式游标

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

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

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

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

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

学号是：1，姓名是：Tom

6. 使用显式游标更新行

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

-- 声明游标时CURSOR<cursor_name>ISSELECT statementFORUPDATE;-- 记得带上 for update-- 更新语句的语法UPDATE<table_name>SET<set_clause>WHERECURRENTOF<cursor_name>-- 记得带上 current of 游标名称-- 删除语句的语法DELETEFROM<table_name>WHERECURRENTOF<cursor_name>-- 记得带上 current of 游标名称

示例如下：

SQL>select*from student;

                                    SNO SNAME                                         SAGE--------------------------------------- ---------- ---------------------------------------1 Tom212 Kite223 Bob234 Mike24declare
	stud student%rowtype;-- 表示一行 student 表记录类型标量cursor stu_curisselect*from studentwhere sno=2or sno=3forupdate;-- 1. 声明游标，带上 for updatebeginopen stu_cur;-- 2. 打开游标loopfetch stu_curinto stud;-- 3. 使用游标取出记录并保存到变量 stud 中exitwhen stu_cur%notfound;update studentset sage= sage+100wherecurrentof stu_cur;-- 带上 current of stu_curendloop;close stu_cur;-- 4. 关闭游标end;/

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

SQL>select*from student;

                                    SNO SNAME                                         SAGE--------------------------------------- ---------- ---------------------------------------1 Tom212 Kite1223 Bob1234 Mike24

7. 循环游标

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

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

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

示例如下所示：

declarecursor stu_curisselect*from student;beginfor cur_subin stu_curloop-- cur_sub 表示游标指向的每一行记录
		dbms_output.put_line('学号是：'|| cur_sub.sno||'，姓名是：'|| cur_sub.sname);endloop;end;/

执行结果如下所示：

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

8. 批量处理

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

declarecursor my_cursorisselect enamefrom empwhere deptno=10;-- 声明名为 my_cursor 的游标type  ename_table_typeistableof varchar2(10);-- 声明名为 ename_table_type 的表类型，该表类型由 varchar2(10) 字段类型组成
  ename_table  ename_table_type;-- 声明名为 ename_table 的 ename_table_type 表类型变量beginopen  my_cursor;-- 打开游标fetch my_cursorbulk collectinto  ename_table;-- 批量取出游标执行的记录并设置到 ename_table 变量中，其实 ename_table 保存的即是-- select ename from emp where deptno = 10 的查询结果集，其中 ename 的类型就是 varchar2(10)for  iin1..ename_table.countloop-- 遍历打印 ename_table 的所有记录
     dbms_output.put_line(ename_table(i));endloop;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 游标类型的语法为：

TYPE<ref_cursor_name>IS REFCURSOR[RETURN<return_type>];

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

OPEN cursor_nameFOR select_statement;

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

student 表中的数据如下所示：

SQL>select*from student;

                                    SNO SNAME                                         SAGE--------------------------------------- ---------- ---------------------------------------1 Tom212 Kite223 Bob234 Mike24

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

declaretype refcuris refcursor;-- 声明名称为 refcur 的参照游标类型变量
	cursor2 refcur;-- 声明名称为 cursor2 的 refcur 类型的变量
	my_sno student.sno%type;
	no2 student.sno%type;
	name2 student.sname%type;begin
	my_sno :='&学号';-- 控制台输入学生学号open cursor2forselect sno, snamefrom studentwhere sno= my_sno;-- 打开游标，此处才绑定 select 语句，而不是在声明的时候loopfetch cursor2into no2, name2;-- 取出游标指定的值并设置到指定变量中exitwhen cursor2%notfound;
        dbms_output.put_line('学号是：'|| no2||'，姓名是：'|| name2);endloop;close cursor2;end;/

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

学号是：4，姓名是：Mike
  • 作者：微冷...
  • 原文链接：https://blog.csdn.net/weixin_40374341/article/details/109431677
    更新时间：2022-07-28 12:07:34