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进行管理。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;

[ 本帖最后由 陈洪光 于 2011-2-10 00:12 编辑 ]

〇〇

nice job

fumuju

非常详细,顶

qingyun

好像没说那种最简单的游标：

begin
  for  xxx in (select * from table1)
  loop
    ...
   end loop;
end;

陈洪光

楼上说的有理，马上补上，

newkid

"DML包括Create、Delete、Update"
CREATE应该为INSERT吧，DML还有一个MERGE INTO。

隐式游标属性还有SQL%BULK_ROWCOUNT是给批量操作用的。

newkid

http://download.oracle.com/docs/ ... 8370/sql_cursor.htm

隐式游标的属性：

SQL% { FOUND
     | ISOPEN
     | NOTFOUND
     | ROWCOUNT
     | BULK_ROWCOUNT ( index )
     | BULK_EXCEPTIONS ( index ).{ ERROR_INDEX | ERROR_CODE }
     }

陈洪光

非常感谢newkid，上边的DML是我手误打错了，还有你说的那两个SQL%BULK_ROWCOUNT ( index )和SQL%BULK_EXCEPTIONS ( index ).ERROR_INDEX还有SQL%BULK_EXCEPTIONS ( index ). ERROR_CODE怎么用呀，没够明白，麻烦大哥帮忙解释一下可以吗，最好举个例子啥的^_^

newkid

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);


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.

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.

fengzj

学习学习 mark