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: 发现 FETCH 游标和 INTO 变量之间的 'EMPR' 的类型不匹配

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

PL/SQL: SQL Statement ignored