原创：oracle PL/SQL编程基础 下<十二>

游标： 游标(cursor)可以被看作指向结果集(a set of rows)中一行的指针(pointer)。在oracle数据库中可以使用显示或隐式两种游标。

隐式游标：

在执行一个 sql 语句时 ,oracle 服务器将自动创建一个隐式游标 , 这个游标是内存中处理该语句的工作区域，其中存储了执行 SQL 语句的结果 . 通过游标的属性可获得 sql 语句执行的结果以及游标状态信息。

游标的主要属性如下：

%found 布尔属性 如果 sql 语句至少影响一行 则为 true 否则为 false

%notfound 布尔属性 与 %found 相反。

%isopen 布尔属性 游标是否打开 打开为 true 否则为 false

%rowcount 数字属性 返回受 sql 语句影响的行数

怎么来使用这些属性呢 ?

则可以通过 “sql 属性名 “ 来查看结果

//%rowcount 用来检查受影响的行

SQL> declare

2 emp_row emp%rowtype;

3begin

4 select * into emp_row from emp where empno=7369;

5 dbms_output.put_line(sql%rowcount);

6end;

7/

1 // 返回的结果为1

//%found 用来检查是否影响到了行

SQL> begin

2 update emp set sal=2000 where empno=7369;

3 if sql%found then

4 dbms_output.put_line(' 更新记录成功影响了'||sql%rowcount||'行');

5 else

6 dbms_output.put_line(' 未更新记录');

7 end if;

8end;

9/

更新记录成功 影响了1行

// %notfound

//%isopen

一以上两个的使用方式参考 %found进行理解。

另外一种隐式游标 cursor for loop 可用于处理 sql 语句的结果集

SQL> begin

2

3for rec in (select * from emp) loop

4 dbms_output.put_line(rec.empno||'/'||rec.ename||'/'||rec.job||'/'||rec.mgr||'/'||rec.hiredate||'/'||rec.sal||'/'||rec.comm||'/'||rec.deptno);

5 end loop;

6end;

7/

显示游标

是在PL/SQL程序中使用包含select语句来声明的游标。如果需要处理从数据库中检索的一组记录,则可以使用显示游标.使用显示游标处理数据需要四个步骤：声明游标,打开游标，检索数据，关闭游标。

1、声明游标

声明游标就是通过定义游标的名称,游标的特性来声明游标,以及打开游标后就可调用查询语句,声明的语法如下：

Cursor cursor_name[parameter[,parameter]….]

[return return_type] is select_statement;

Parameter作为游标的输入参数,它可以让用户在打开游标式,向游标传递值；语法如下：

Parameter_name [in] datatype[{:=|default} expression]/

举例：

declare

cursor emp_cursor (pno in number(4) default 7369)

is select * from emp

where empno=pno;

2、打开游标

就是指执行声明游标时指定的查询语句。打开的方式只需使用open打开语法：

Open cursor_name(参数);

如果没有指定参数就采用默认值执行select语句

3、检索数据

检索数据就是从检索到的结果集中获取数据保存到变量中,以便变量进行处理。

使用fetch语句找出结果集中的单行,并从中提取单个值传递给主变量。

语法如下：

Fetch cursor_name into [variable_list[record_variable]]

变量用于存储检索的数据

4、关闭游标

close 游标名称

综上所述综合案例如下：

SQL> declare

2cursor emp_cursor (pno in number default 7369) // 声明游标

3is select * from emp where empno=pno;

4

5emp_row emp%rowtype;// 声明变量

6begin

7 open emp_cursor(7934);// 打开游标

8 fetch emp_cursor into emp_row; // 检索数据结果为一行

9 dbms_output.put_line(emp_row.ename);// 输出检索结果

10

11 close emp_cursor; // 关闭游标

12end;

13/

游标for循环

依次读取结果集中的行,当for循环开始时,游标会自动打开(不需要使用open方法开启),每循环读取一次,系统自动读取当前数据(不需要使用fetch),当退出for循环时,游标也会自动关闭(不需要使用close方法)。

SQL> declare

2cursor emp_cursor (pno in number default 7369)// 声明游标

3is select * from emp where empno=pno;

4

5begin

6 for emp_row in emp_cursor(7934) loop //for 循环开始时 自动打开游标 并且自动获取数据 自动关闭

7 dbms_output.put_line(emp_row.ename);

8 end loop;

9end;

10/

游标变量

游标变量也可以处理多行查询结果集。

游标变量的定义包括两个步骤：

1、定义cursor类型的指针

语法：

Type ref_cursor_name is ref cursor[return return_type]

举例：

Type var_cursor_name is ref cursor;

2、定义ref cursor类型的变量

v_rcvar_cursor_name;

综合写法如下：

Type var_cursor_name is ref cursor;

v_rcvar_cursor_name;

上面的综合声明的游标变量 称为弱的ref cursor类型,因为它没有指明游标返回的结果,因此它可以指向任何一个具有多列的select查询结果.

相对于上面还有一种称为:强ref cursor类型.

声明方式如下：

Type varcursorName is ref cursor return emp%rowtype; //指明了返回的结果

Vcn varcursorName; //声明一个强的ref cursor类型的变量

使用游标变量与游标使用方式一样,也需要声明,打开,检索，关闭游标变量。

综合案例如下：

SQL> declare

2

3type emp_cname is ref cursor return emp%rowtype;// 声明游标变量第一步

4

5ecname emp_cname;// 声明游标变量第二步

6

7emp_row emp%rowtype; // 声明用于保存检索数据的变量

8

9begin

10 dbms_output.put_line(' 开始');

11 open ecname for select * from emp where empno=7934; // 打开游标变量

12 loop

13 fetch ecname into emp_row; // 查询结果赋值给保存的变量

14 exit when ecname%notfound;// 退出条件

15 dbms_output.put_line(emp_row.ename); // 输出结果

16 end loop; // 退出循环

17 close ecname;// 关闭游标变量

18 dbms_output.put_line(' 结束');

19end;

20/

开始

MILLER

结束

// 复杂的案例

SQL> declare

2

3type emp_cname is ref cursor return emp%rowtype;

4

5ecname emp_cname;

6

7emp_row emp%rowtype;

8

9begin

10 dbms_output.put_line(' 开始');

11 open ecname for select * from emp;

12 loop

13 fetch ecname into emp_row;

14 exit when ecname%notfound;

15 dbms_output.put_line(emp_row.ename);

16 end loop;

17 close ecname;

18 dbms_output.put_line(' 结束');

19end;

20/

开始

SMITH

ALLEN

WARD

JONES

MARTIN

BLAKE

CLARK

SCOTT

KING

TURNER

ADAMS

JAMES

FORD

MILLER

结束

使用游标更新数据库

1、定位游标之后进行删除|修改指定的数据行 更新的时候需要使用for update选项 语法如下：

Cursor cursor_name is select_statement;

For update[of column[,column],[nowait]]

Of用来指定要锁定的列,如果忽略of那么表中选择的数据行都将锁定。如果被锁定行已经被锁定了,那么必须等待释放才能锁定对于这种情况我们可以使用nowait语句。

当使用for update语句声明游标后,可以再delete|update语句中使用where current of子句，修改|删除游标结果集中当前行对应的表中的数据。

语法如下：

Where { current of cursor_name|search_condition}

举例说明：

//修改操作

SQL> declare

2

3 cursor ecname is select * from emp where empno=7934

4 for update of sal nowait;

5

6 esal number(7,2);

7

8

9begin

10 dbms_output.put_line(' 开始');

11

12 for r in ecname loop

13 esal:=r.sal*10;

14 update emp set sal=esal where current of ecname;

15 end loop;

16

17 dbms_output.put_line(' 结束');

18end;

19/

开始

结束

以上内容归redarmy_chen所有,如需转载请附带出处,如有疑问请发送至redarmy_chen@qq.com