SQL游标

隐含游标:专门处理SELECT INTO、INSERT、UPDATE以及DELETE语句。

显示游标:用于处理多行的SELECT语句。

当在PL/SQL块中执行INSERT、UPDATE及DELETE语句时,为了取得DML语句作用的结果,需要使用SQL游标属性。

第一,游标的4种属性

1. SQL%ISOPEN

该属性永远都是false,没有实际意义。

2. SQL%FOUND

语句是否操作成功。当SQL语句有作用行时为TRUE.

3. SQL%NOTFOUND

LOOP

FETCH  emp_cursor  INTO   v_name, v_deptno;

EXIT  WHEN  emp_cursor%NOTFOUND;

...

END LOOP;

 

4. SQL%ROWCOUNT

返回SQL语句所作用的总计行数。

该属性用于返回已提取的实际行数。

 

第二,显示游标专用于处理SELECT语句返回的多行数据。

  • 定义游标:

CURSOR  cursor_name  IS  select_statement;

  • 打开游标:

open  cursor_name;

  • 提到数据:

FETCH  cursor_name  INTO  var1[, var2, ...];

  • 关闭游标:

CLOSE  cursor_name;

例子:

DECLARE
   CURSOR category_cursor
   IS
      SELECT *
        FROM tb_out_service t
       WHERE t.servicename LIKE '长沙%';

   TYPE category_table_type IS TABLE OF tb_out_service%ROWTYPE
      INDEX BY BINARY_INTEGER;

   category_table   category_table_type;
   i                INT;
BEGIN
   OPEN category_cursor;

   LOOP
      i := category_cursor%ROWCOUNT + 1;

      FETCH category_cursor
       INTO category_table (i);

      EXIT WHEN category_cursor%NOTFOUND;
      DBMS_OUTPUT.put_line (   RPAD (category_table (i).serviceid, 30)
                            || '  '
                            || category_table (i).servicename
                           );
   END LOOP;

   CLOSE category_cursor;
END;

 

第三,游标FOR循环

语法:

FOR  record_name  IN  cursor_name  LOOP

statement;

...

END  LOOP;

注意:当使用游标FOR循环时,既可以在定义部分定义游标,也可以直接在FOR循环中使用SELECT语句。

 

第四,参数游标

CURSOR cursor_name(parameter_name datatype) is select_statement;

OPEN cursor_name(parameter_value)

注意:

定义参数只能指定数据类型,不能指定长度。

另外,应该在游标的select语句的where子句中引用游标参数,否则失去了定义参数游标的意义。

---------------------------------------------------------------------------------------------------------------------

DECLARE
   CURSOR category_cursor (NAME VARCHAR2)
   IS
      SELECT *
        FROM tb_out_service t
       WHERE t.servicename LIKE NAME || '%';
BEGIN
   FOR category_record IN category_cursor ('吉首')
   LOOP
      DBMS_OUTPUT.put_line (   RPAD (category_record.serviceid, 30)
                            || '  '
                            || category_record.servicename
                           );
   END LOOP;
END;

 

第五,更新或删除游标行

语法

CURSOR cursor_name IS select_statement

FOR UPDATE [OF column_reference]  [NOWAIT];

UPDATE table_name SET column=..  WHERE CURRENT OF cursor_name;

DELETE FROM table_name WHERE CURRENT OF cursor_name;

 

注意

1. OF子句的格式:column_reference为table_name.column_name

2. WHERE CURRENT OF cursor_name, 更新或者删除游标所在行的数据。

3. 在使用游标更新或删除数据时,定义游标必须带有FOR  UPDATE子句,并且在更新或者删除游标时必须带有WHERE CURRENT OF 子句。

当查询语句涉及到多张表时,如果不带有OF子句,会在多张表上同时加锁,如果只在特定的表上加锁,需要带有OF子句。

 

DECLARE
   CURSOR category_cursor (NAME VARCHAR2)
   IS
      SELECT        *
               FROM tb_out_service t
              WHERE t.servicename LIKE NAME || '%'
      FOR UPDATE OF t.querystr;

   v_name   CONSTANT VARCHAR2 (20) := '长沙';
BEGIN
   FOR category_record IN category_cursor (v_name)
   LOOP
      IF INSTR (category_record.servicename, v_name || '——') <> 1
      THEN
         DBMS_OUTPUT.put_line (   'delete: '
                               || RPAD (category_record.serviceid, 30)
                               || '  '
                               || category_record.servicename
                              );

         DELETE FROM tb_out_service
               WHERE CURRENT OF category_cursor;
      ELSE
         DBMS_OUTPUT.put_line (   'upate: '
                               || RPAD (category_record.serviceid, 30)
                               || '  '
                               || category_record.servicename
                              );

         UPDATE tb_out_service t
            SET t.querystr = v_name || '——' || t.servicename
          WHERE CURRENT OF category_cursor;
      END IF;
   END LOOP;
END;

 

第六, 游标变量

游标变量是基于REF CURSOR类型所定义的变量,它实际上是指向内存地址的指针。显式游标只能定义静态游标,而游标变量可以在打开时指定其所对应的SELECT语句,从而实现动态游标。

  • 定义游标:

TYPE  ref_type_name  IS  REF  CURSOR  [RETURN  return_type];

cursor_variable  ref_type_name;

  • 打开游标:

OPEN  cursor_name  FOR  select_statement;

  • 提到数据:

FETCH  cursor_variable  INTO  var1[, var2, ...];

  • 关闭游标:

CLOSE  cursor_variable;

注意:

不能在远程子程序中使用游标变量。

当指定子查询时,不能带有FOR UPDATE子句。

当指定RETURN子句时,返回类型必须使用PL/SQL记录类型。

例子:

DECLARE
   TYPE category_cursor_type IS REF CURSOR
      RETURN tb_out_service%ROWTYPE;

   category_cursor   category_cursor_type;
   category_record   tb_out_service%ROWTYPE;
   v_name   CONSTANT VARCHAR2 (40)            := '长沙';
BEGIN
   OPEN category_cursor FOR
      SELECT *
        FROM tb_out_service t
       WHERE t.servicename LIKE v_name || '%';

   LOOP
      FETCH category_cursor
       INTO category_record;

      EXIT WHEN category_cursor%NOTFOUND;
      DBMS_OUTPUT.put_line (   RPAD (category_record.serviceid, 30)
                            || '  '
                            || category_record.servicename
                           );
   END LOOP;
END;

posted on 2009-03-17 11:07 JavaBegger 阅读(615) 评论(0)  编辑  收藏


只有注册用户登录后才能发表评论。


网站导航:
 
<2009年3月>
22232425262728
1234567
891011121314
15161718192021
22232425262728
2930311234

导航

统计

常用链接

留言簿(2)

随笔分类

随笔档案

文章分类

文章档案

搜索

最新评论

阅读排行榜

评论排行榜