Oracle学习笔记整理之游标篇

游标分为隐式、显式、REF三种游标。

而隐式游标和显式游标又都属于静态游标,REF游标属于动态游标。

静态游标和动态游标的最大区别在于静态游标是在运行前将游标变量和SQL关联，而动态游标恰恰相反是在运行后将游标变量和SQL进行关联。

隐式游标是由Oracle自动管理，也称sql游标。所有的DML都被Oracle解析成为一个名为SQL的隐式游标。DML包括Insert、Delete、Update、Merge into。而Select属于DQL。

隐式游标的属性有：
 

属性名	说明
sql%found	影响行数大于等于1是为 ture
sql%notfound	没有影响行是为true
sql%rowcount	受影响的行数
sql%isopen	游标是否打开，始终为false
sql%bulk_rowcount(index)	...
sql%bulk_exceptions(index).error_index	...
sql%bulk_exceptions(index).error_code	...

---

隐式游标实例：

begin

  --执行DML操作

  update chg_test_b b set b.chg_name = 'ff' where b.chg_id = 5;

  --判断是否有受影响行

  if sql%found then

    --打印受影响行数

    dbms_output.put_line('影响行数：' || sql%rowcount);

  end if;

  --判断是否没有受影响行

  if sql%notfound then

    dbms_output.put_line('id为5的记录不存在');

  end if; 

end;

---

无参显式游标实例：

declare

  --声明游标表变量并关联sql

  cursor rowList is

    select * from chg_test_b b;

  --声明行变量

  --如果上面的查询语句只有一个查询字段，这里也可以使用正常的变量声明方式（v_rowValue varchar2(20);）。

  rowValue chg_test_b%rowtype;

begin

  open rowList; --打开游标

  --如果确定由表中只有一条记录的话，loop和end loop可以不写，而exit必须存在于游标循环内，所以也不需要写。

  loop

    fetch rowList

      into rowValue; --取出游标内的值放到rowValue中

    exit when rowList%notfound; --判断是否还存在记录，如果不存在终止游标

    dbms_output.put_line(rowValue.chg_name); --将的到的值打印,如果查询只有一个字段这里只需写变量名即可。

  end loop;

  close rowList; --关闭游标

end;

---

有参显式游标实例：

declare

  --声明带参数的游标变量并关联sql,并将参数与sql进行关联

  cursor rowList(c_name varchar2, c_id number) is

    select * from chg_test_b b where b.chg_name = c_name and b.chg_id = c_id;

  --声明行变量

  --如果上面的查询语句只有一个查询字段，这里也可以使用正常的变量声明方式（v_rowValue varchar2(20);）。

  rowValue chg_test_b%rowtype;

begin

  open rowList('chg1', 1); --打开游标，并将参数给出

  --如果确定由表中只有一条记录的话，loop和end loop可以不写，而exit必须存在于游标循环内，所以也不需要写。

  loop

    fetch rowList

      into rowValue; --取出游标内的值放到rowValue中

    exit when rowList%notfound;--判断是否还存在记录，如果不存在终止游标

    dbms_output.put_line(rowValue.chg_name); --将的到的值打印,如果查询只有一个字段这里只需写变量名即可。

  end loop;

  close rowList; --关闭游标

end;

---

在游标内进行DML操作实例：

declare

  --声明游标变量，并关联sql

  --如果要在游标内执行DML操作，必须使用for update。

  --使用for update后你所操作的行或列将会被锁，如果这时其他人操作就会进入等待状态。

  --如果你在执行select时记录被锁，那么就会进入等待状态，

  --为了避免这种情况可以在 for update后使用 nowait，这样你的结果就可以立即返回，但不建议使用

  --如果被锁在打开游标时会报出ORA-00054资源正忙异常，需捕获异常进行处理

  --使用of的区别时锁字段和锁行。

  cursor rowList is

    select b.* from chg_test_b b for update of b.chg_name nowait;

  --如果上面的查询语句只有一个查询字段，这里也可以使用正常的变量声明方式（v_rowValue varchar2(20);）。

  rowValue chg_test_b%rowtype;

  v_i      number(11);

begin

  v_i := 1;

  open rowList; --打开游标

  --如果确定由表中只有一条记录的话，loop和end loop可以不写，而exit必须存在于游标循环内，所以也不需要写。

  loop

    fetch rowList

      into rowValue; --取出游标内的值放到rowValue中

    exit when rowList%notfound;--判断是否还存在记录，如果不存在终止游标

    dbms_output.put_line(rowValue.chg_name); --将的到的值打印,如果查询只有一个字段这里只需写变量名即可。

    --执行DML操作

    update chg_test_b b

       set b.chg_name = 'chg' || v_i

     where current of rowList;

    v_i := v_i + 1;

  end loop;

  close rowList; --关闭游标

  commit;

end;

---

循环游标实例：
循环游标不需要手动进行打开和管理操作，全部由oracle进行管理。

declare

   --声明游标变量并关联sql

  cursor rowList is

    select level a from dual connect by level <= 10;

begin

  for rowValue in rowList loop

     --rowValue是每条记录不需要事先声明，rowList是集合中的所有记录

    dbms_output.put_line(rowValue.a); --取出集合中的值进行打印

  end loop;

end;

declare
begin
   --可以将select语句for xx in 内，无论传参或是嵌套更为方便简洁。
  for rowValue in (select level a from dual connect by level <=10) loop
     for rv in (select col1 from tab1 where tab1.id = rowValue.a) loop
         dbms_output.put_line(rv.col1 );--将的到的值打印。
     end loop;
  end loop;
end;

declare

begin

  for rowValue in 1..10 loop

    dbms_output.put_line(rowValue); --将的到的值打印,如果查询只有一个字段这里只需写变量名即可。

  end loop;

end;

---

REF游标实例：

declare

  type cus_cur_type is ref cursor return chg_test_b%rowtype; --强类型Ref游标，查询的sql必须返回chg_test_b表类型

   --type cus_cur_type is ref cursor; 弱类型Ref游标，返回类型没有限制

  rowList cus_cur_type; -- 声明游标变量

  rowValue chg_test_b%rowtype; --声明行变量

begin

  open rowList for --打开游标，并关联sql

    select * from chg_test_b b;

  loop

    fetch rowList

      into rowValue; - -按行取出数据

    exit when rowList%notfound; --判断是否还存在记录，如果不存在终止游标

    dbms_output.put_line(rowValue.chg_name); --将的到的值打印,如果查询只有一个字段这里只需写变量名即可。

  end loop;

  close rowList; --关闭游标

end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25357621/viewspace-686922/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25357621/viewspace-686922/