oracle游标的简单使用规则

 

oracle 游标

 

最近对 oracle 中的游标有了新的认识所以写出来给大家分享分享。

在通过 select 语句查询时，返回的结果通常是多行记录组成的集合。这对于程序设计语言而言，并不能够处理以集合形式返回的数据，为此， sql 提供了游标机制。游标充当指针的作用，使应用程序设计的语言一次只能处理查询结果的一行。在 oracle 中可以使用显式和隐式两种游标。

同时对于 PL/SQL 程序中所发出的 dml 和 select 语句， oracle 都会自动的声明“隐式游标”。为了处理 select 语句返回一组记录，需要在 PL/SQL 程序中声明和处理“显式游标”。

隐式游标

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

游标的主要属性：

%Found boolean 布尔型，如果 sql 影响了一行，则为 true 否则为 false;

%NotFound boolean 布尔型 ，与 Found 相反 ;

%IsOpen boolean 布尔型，当游标已达开始返回 true 否则返回 false;

%RowCount 数字型属性返回 sql 影响的行数。

注：当使用隐式游标的属性时，属性前要加上 sql ，因为 oracle 在创建隐式游标时，默认的游标名为 sql 。

Boolean 布尔类型的值不能用 dnms_output.putline() 输出

ü 隐式游标的使用

SQL> begin

2 update emp

3 set sal=800

4 where empno=1;

5 if sql%notfound then

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

7 else

8 dbms_output.put_line(' 更新'|| sql%rowcount||' 条');

9 end if;

10 end ;/

注：游标的属性信息总是反应最新的 sql 语句处理结果。

SQL> declare

2 empr emp%rowtype;

3

4 begin

5 update emp

6 set sal=800

7 where empno=1;

8 if sql%notfound then

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

10 else

11 dbms_output.put_line(' 更新'|| sql%rowcount||' 条');

12 end if;

13 select * into empr from emp where empno=1;

14 dbms_output.put_line(' 更新'|| sql%rowcount||' 条');

15 end

16 ;

17 /

更新1 条

更新1 条

PL/SQL procedure successfully completed

SQL>

ü 另一种隐式游标 cursor( 光标 )

Cursor for loop

begin

for emps in (select empno,ename,job,hiredate,sal,comm,deptno from emp)

loop dbms_output.put_line(emps.empno||emps.ename||emps.job||emps.hiredate||emps.sal||emps.comm);

end loop;

end ;

使用 cursor for loop 遍历查询的结果集

显式游标

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

ü 声明游标

声明游标就是通过定义游标的名称、游标特征来声明游标，以及打开游标后就可用来调用查询语句。使用游标之前必须先声明游标。声明游标的语法如下：

Cursor 游标名字 [( 参数 )[ 参数 ]…] [return 返回的类型 ] is 查询的语句

Declare

Cursor empc (empno_p number default 1) is

Select * from emp where empno=empno_p;

注： number 不能有长度 否则报错

ü 打开游标

打开游标就是执行声明游标时所指定的查询语句。游标必须声明后才能打开。打开游标也就是调用游标中的 select 语句。

Open 游标名称；

例如打开上面的的游标

Open empc;

ü 检索数据

检索数据就是从检索到的结果集中获取数据保存到变量中，以便在程序中进行处理。检索数据就是使用 fetch 语句找出结果集的单行病从中提取单个值传给主变量。

Fetch 的语法：

Fetch 游标名字 into 主变量名称；

游标中包含一个指针，他将自动记录由 fetch 返回的下一行，最初的它设置为从查询的第一行。因此第一次执行 fetch 语句时，他将检索第一航中的数据保存到变量中。在随后的每执行一个 fetch 语句时，将指针移动到结果集的下一行。可以再循环中用 fetch 语句，这样每一次循环都会从表中读取一行数据，然后进行相同逻辑的处理。如果游标中没有剩余记录时，那么属性 %found 返回 false ，循环也就随之结束。

ü 关闭游标

关闭游标就是不能再从查询结果中检索数据。

Close 游标名称 ;

例如关闭上面的游标

Close empc;

ü 完整案例

declare

cursor empc (empnop number default 1)

is select * from emp where empno=empnop;// 声明现实的游标

type empt is record(

empno emp.empno%type,

ename emp.ename%type,

job emp.job%type,

mgr emp.mgr%type,

hiredate emp.hiredate%type,

sal emp.sal%type,

comm emp.comm%type,

deptno emp.deptno%type ); 记录类型

 

empr empt; 声明记录类型

begin

open empc(2);

loop

fetch empc into empr; 游标检索数据

exit when empc%notfound;

end loop;

dbms_output.put_line(empr.ename||empr.hiredate);

close empc;

end ;

注：使用 select * from table 表时 其数据库中的表的结果的顺序和声明记录类型声明的顺序要相同 否则会报出如下错误

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

数据表中列的顺序

SQL> declare

2 cursor empc (empnop number default 1)

3 is select * from emp where empno=empnop;

4 type empt is record(

5 empno emp.empno%type,

6 ename emp.ename%type,

7 job emp.job%type,

8 hiredate emp.hiredate%type,

9 mgr emp.mgr%type,

10 sal emp.sal%type,

11 comm emp.comm%type,

12 deptno emp.deptno%type );

13 empr empt;

14 begin

15 open empc(2);

16 loop

17 fetch empc into empr;

18 exit when empc%notfound;

19 end loop;

20 dbms_output.put_line(empr.ename||empr.hiredate);

21

22 close empc;

23 end ;

24 /

declare

cursor empc (empnop number default 1)

is select * from emp where empno=empnop;

type empt is record(

empno emp.empno%type,

ename emp.ename%type,

job emp.job%type,

hiredate emp.hiredate%type,

mgr emp.mgr%type,

sal emp.sal%type,

comm emp.comm%type,

deptno emp.deptno%type );

empr empt;

begin

open empc(2);

loop

fetch empc into empr;

exit when empc%notfound;

end loop;

dbms_output.put_line(empr.ename||empr.hiredate);

close empc;

end ;

ORA-06550: 第 18 行 , 第 17 列 :

PLS-00386: 发现