呕心整理Oracle10G版本后的游标使用详解

呕心整理Oracle10G版本后的游标使用详解

游标分为隐式、显式、REF三种游标。
而隐式游标和显式游标又都属于静态游标,REF游标属于动态游标。
静态游标和动态游标的最大区别在于静态游标是在运行前将游标变量和SQL关联，而动态游标恰恰相反是在运行后将游标变量和SQL进行关联。
隐式游标是由Oracle自动管理，也称sql游标。所有的DML都被Oracle解析成为一个名为SQL的隐式游标。DML包括Insert、Delete、Update、Merge into。而Select属于DQL。

隐式游标的属性有：

隐式游标实例：

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 thendbms_output.put_line('id为5的记录不存在');end if; 
end;

无参显式游标实例：

declare--声明游标表变量并关联sqlcursor rowList isselect * from chg_test_b b;--声明行变量--如果上面的查询语句只有一个查询字段，这里也可以使用正常的变量声明方式（v_rowValue varchar2(20);）。rowValue chg_test_b%rowtype;
beginopen rowList; --打开游标--如果确定由表中只有一条记录的话，loop和end loop可以不写，而exit必须存在于游标循环内，所以也不需要写。loopfetch rowListinto 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) isselect * 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;
beginopen rowList('chg1', 1); --打开游标，并将参数给出--如果确定由表中只有一条记录的话，loop和end loop可以不写，而exit必须存在于游标循环内，所以也不需要写。loopfetch rowListinto 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 isselect 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);
beginv_i := 1;open rowList; --打开游标--如果确定由表中只有一条记录的话，loop和end loop可以不写，而exit必须存在于游标循环内，所以也不需要写。loopfetch rowListinto rowValue; --取出游标内的值放到rowValue中exit when rowList%notfound;--判断是否还存在记录，如果不存在终止游标dbms_output.put_line(rowValue.chg_name); --将的到的值打印,如果查询只有一个字段这里只需写变量名即可。--执行DML操作update chg_test_b bset b.chg_name = 'chg' || v_iwhere current of rowList;v_i := v_i + 1;end loop;close rowList; --关闭游标commit;
end;

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

declare--声明游标变量并关联sqlcursor rowList isselect level a from dual connect by level <= 10;
beginfor 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) loopfor rv in (select col1 from tab1 where tab1.id = rowValue.a) loopdbms_output.put_l1 );--将的到的值打印。end loop;end loop;
end;

declare
beginfor rowValue in 1..10 loopdbms_output.put_line(rowValue);--将的到的值打印,如果查询只有一个字段这里只需写变量名即可。end loop;
end;

REF游标实例：

declaretype 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; --声明行变量
beginopen rowList for --打开游标，并关联sqlselect * from chg_test_b b;loopfetch rowListinto rowValue; --按行取出数据exit when rowList%notfound;--判断是否还存在记录，如果不存在终止游标dbms_output.put_line(rowValue.chg_name);--将的到的值打印,如果查询只有一个字段这里只需写变量名即可。end loop;close rowList;--关闭游标
end;