Oracle 中游标的使用

Oracle 中游标的使用

 游标(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;

  3  begin

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

  5     dbms_output.put_line(sql%rowcount);

  6  end;

  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;

  8  end;

  9  /

更新记录成功 影响了1行

  // %notfound 

  //%isopen

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

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

SQL> begin

  2  

  3  for 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;

  6  end;

  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、 关闭游标

关闭游标使得用户不能再从查询结果中检索数据。使用完游标后应该关闭游标释放select 语句的查询结果。语法：

  Close 游标名称

综上所述综合案例如下：

SQL> declare

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

  3  is select * from emp where empno=pno;

  4  

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

  6  begin

  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; //关闭游标

 12  end;

 13  /

游标for循环

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

SQL> declare

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

  3  is select * from emp where empno=pno;

  4  

  5  begin

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

  7     dbms_output.put_line(emp_row.ename);

  8    end loop;

  9  end;

 10  /

游标变量

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

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

1、 定义cursor类型的指针

语法：

  Type ref_cursor_name is ref cursor[return return_type]

举例：

   Type var_cursor_name is ref cursor;

2、 定义ref cursor类型的变量

  v_rc  var_cursor_name;

综合写法如下：

   Type var_cursor_name is ref cursor;

   v_rc  var_cursor_name;

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

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

声明方式如下：

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

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

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

综合案例如下：

  SQL> declare

  2  

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

  4  

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

  6  

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

  8  

  9  begin

 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('结束');

 19  end;

 20  /

 

开始

MILLER

结束

//复杂的案例

SQL> declare

  2  

  3  type emp_cname is ref cursor return emp%rowtype;

  4  

  5  ecname emp_cname;

  6  

  7  emp_row emp%rowtype;

  8  

  9  begin

 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('结束');

 19  end;

 20  /

 

开始

SMITH

ALLEN

WARD

JONES

MARTIN

BLAKE

CLARK

SCOTT

KING

TURNER

ADAMS

JAMES

FORD

MILLER

 

text-ali

1
顶

2
踩

分享到：

Oracle 中in out参数实现数字交换 | Oracle 中使用的%TYPE和%ROWTYPE类型的变 ...

• 2011-04-13 19:34
• 浏览 1147
• 评论(0)
• 分类:数据库
• 查看更多