Oracle学习笔记整理之游标篇

转自:http://www.itpub.net/thread-1394528-1-1.html

游标分为隐式、显式、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进行管理。for yy in xx 等同于fetch xx into yy;
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;

 

 

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

CREATE TABLE T (N1 NUMBER,C1 VARCHAR2(1));

INSERT INTO T VALUES (1,NULL);

INSERT INTO T VALUES (2,NULL);

INSERT INTO T VALUES (2,NULL);

INSERT INTO T VALUES (3,NULL);

INSERT INTO T VALUES (3,NULL);

INSERT INTO T VALUES (3,NULL);

INSERT INTO T VALUES (4,NULL);

INSERT INTO T VALUES (4,NULL);

----sql%bulk_rowcount(index)使用例子

DECLARE

   TYPE num_t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

   lv_num num_t;

BEGIN

   lv_num(1):=2;

   lv_num(2):=3;

   lv_num(3):=4;

   lv_num(4):=1;

   

   FORALL i IN 1..lv_num.COUNT  ---- 批量执行UPDATE, 里面其实有三个UPDATE

      UPDATE t SET c1=n1 WHERE n1=lv_num(i);

   

   FOR i IN 1..lv_num.COUNT LOOP

       --- SQL%ROWCOUNT(i)告诉你每个UPDATE执行了多少行

       DBMS_OUTPUT.PUT_LINE('n1='||lv_num(i)||' '||SQL%BULK_ROWCOUNT(i)||' rows updated');

   END LOOP;

END;

/

输出：

n1=2 2 rows updated

n1=3 3 rows updated

n1=4 2 rows updated

n1=1 1 rows updated

PL/SQL procedure successfully completed.

SELECT * FROM T;

        N1 C

---------- -

         1 1

         2 2

         2 2

         3 3

         3 3

         3 3

         4 4

         4 4

8 rows selected.

---- sql%bulk_exceptions(index).error_index sql%bulk_exceptions(index).error_code 使用例子

DECLARE

   TYPE num_t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

   lv_num num_t;

   dml_errors EXCEPTION;

   lv_err_cnt number := 0;

   PRAGMA exception_init(dml_errors, -24381);

BEGIN

   lv_num(1):=2;

   lv_num(2):=3;

   lv_num(3):=4;

   lv_num(4):=1;

               

   BEGIN

      FORALL i IN 1 .. lv_num.count SAVE EXCEPTIONS

         UPDATE t SET c1=n1*4 WHERE n1=lv_num(i);     --- n1*4 在n1=3和4的时候将会溢出，因为c1定义是VARCHAR2(1)

   EXCEPTION

       WHEN dml_errors THEN

            lv_err_cnt := SQL%BULK_EXCEPTIONS.COUNT;

            

            DBMS_OUTPUT.PUT_LINE('There are '||lv_err_cnt||' errors');

            

            FOR i IN 1..lv_err_cnt LOOP

                DBMS_OUTPUT.PUT_LINE('error number '||i

                                     ||' happened in index '||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX  ---- lv_num数组的下标

                                     ||' data='||lv_num(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX)

                                     ||' error code='||SQL%BULK_EXCEPTIONS(i).ERROR_CODE

                                     ||' error msg='||SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE) ---- 根据错误代码反推错误信息

                                     );

            END LOOP;

   END;

   

END pr_copy_data;

/

输出：

There are 2 errors

error number 1 happened in index 2 data=3 error code=12899 error msg=ORA-12899: value too large for column  (actual: , maximum: )

error number 2 happened in index 3 data=4 error code=12899 error msg=ORA-12899: value too large for column  (actual: , maximum: )

PL/SQL procedure successfully completed.

SELECT * FROM T;

        N1 C

---------- -

         1 4     ---- c1 被修改为 N1*4

         2 8     ---- c1 被修改为 N1*4

         2 8     ---- c1 被修改为 N1*4

         3 3     ---- 以下 c1 保持原样因为 N1*4 溢出了

         3 3

         3 3

         4 4

         4 4

8 rows selected.