游标3

1、PL/SQL里的游标可以分为显式游标和隐式游标两种，而隐式游标又分为(select into)隐式游标和(for .. in)隐式游标两种。
A、显式游标
B、隐式游标(select into)
C、隐式游标(for .. in)

A. 显式游标
普通显式游标，指的是通过定义获得游标，并通open, fetch, close的等方法来操作游标。
declare
  cursor emp_cursor is select ename from scott.emp;
  c_name varchar2(10);
begin
     open emp_cursor;
     loop
         fetch emp_cursor into c_name;
         exit when emp_cursor%notfound;
         dbms_output.put_line(c_name);
     end loop;
     close emp_cursor;
end;
/

declare
  cursor emp_cursor is select ename from scott.emp;
  type c_name_type is table of varchar2(10) index by binary_integer;
  c_name c_name_type;
  --c_name dbms_sql.varchar2_table;
begin
     open emp_cursor;
     fetch emp_cursor bulk collect into c_name;
     for i in 1 .. c_name.count loop
         dbms_output.put_line(c_name(i));
     end loop;
     close emp_cursor;
end;
/

declare
  cursor emp_cursor is select ename from scott.emp;
  type c_name_type is table of varchar2(10) index by binary_integer;
  c_name c_name_type;
  --c_name dbms_sql.varchar2_table;
begin
     open emp_cursor;
     loop
         fetch emp_cursor bulk collect into c_name limit 10;
     exit when emp_cursor%notfound;
         for i in 1 .. c_name.count loop
             dbms_output.put_line(c_name(i));
         end loop;
     end loop;
     close emp_cursor;
end;
/

隐式游标相对于显式游标而言，指的是不需要事先Declare,也无须open,fetch, close的等方法来操作，而是通过其它的方式来操作游标。

B、隐式游标(select into)
declare
  c_name varchar2(100);
begin
     select ename into c_name from scott.emp where empno=7788;
     dbms_output.put_line(c_name);
end;
/

declare
  type c_name_type is table of varchar2(10) index by binary_integer;
  c_name c_name_type;
  --c_name dbms_sql.varchar2_table;
begin
     select ename bulk collect into c_name from scott.emp;
     for i in 1 .. c_name.count loop
         dbms_output.put_line(c_name(i));
     end loop;
end;
/

动态SQL的隐式游标(select into)
declare
  c_name varchar2(100);
  c_table_name varchar2(100);
  c_sql varchar2(200);
begin
     c_table_name := 'scott.emp';
     c_sql := 'select ename from '||c_table_name||' where empno=7788';
     execute immediate c_sql into c_name;
     dbms_output.put_line(c_name);
end;
/

动态SQL的隐式游标(select into)+(bulk collect)
declare
  c_name_array dbms_sql.varchar2_table;
  c_table_name varchar2(100);
  c_sql varchar2(200);
begin
     c_table_name := 'scott.emp';
     c_sql := 'select ename from '||c_table_name;
     execute immediate c_sql bulk collect into c_name_array;
     for i in 1 .. c_name_array.count loop
         dbms_output.put_line(c_name_array(i));
     end loop;
end;
/

C、隐式游标(for .. in)
通过loop的开始与结束来控制cursor的open与close。
begin
  for emp_cursor in (select ename,sal from scott.emp) loop
      dbms_output.put_line(emp_cursor.ename||','||emp_cursor.sal);
  end loop;
end;
/

2.三种用法的优劣
A、显式游标
优点：
·可以用于Bulk Collect的批量处理句式以提高性能
·可以用于动态SQL的游标处理
缺点：
·麻烦，需要定义，打开，Fetch,Close一堆代码，增加代码复杂度，从而增加出错的可能性

B、隐式游标(select into)
优点：
·代码量最少
·可以自动Detect返回数据超过一行或少于一行的错误
·可以使用Bulk Collect批量处理，但是无法使用Limit关键字
缺点：
·如果不使用Bulk Collect批量处理，仅仅只能用于返回数据正好一行的情况，无法使用于返回数据超过一行或少于一行的环境
·使用Bulk Collect批量处理时，无法使用limit关键字，无法处理返回行数太多的情况(不好处理，容易造成PGA过大)

C、隐式游标(for .. in)
优点：
·代码量远少于显式游标
·代码可读性优于显式游标
·代码的出错可能性也小于显式游标
缺点：
·无法用于动态SQL的游标处理
·在返回行数超过10行的情况下，性能明显不如使用Bulk Collect的显式游标

在性能对比方面，除非是使用了Bulk Collect,否则，三种方式没有明显性能差距。

3、具体的选择
·在返回数据为一行的情况下，尽量使用(select into)的隐式游标

·返回0行或者

·或者在返回行稍多，但是不关心Fetch性能的情况下，也可考虑用(for .. in)隐式游标

·返回10行-100行(一个很随意选择的经验值，可以自己根据情况设定)，而且关心Fetch性能的情况下，可以使用(select into)的隐式游标+(bulk collect),在获得性能提升的情况下，代码量也不会增加太多。

·返回行数很多，>100, 应选用显示游标+(bulk collect)，以获得较高的Fetch 性能，同时不至于使用太大的PGA内存。

·如国使用动态SQL, 则根据select list (column list)是否固定，如果固定，仍然可以考虑使用(select into)的隐式游标+动态SQL的方式。当然，仍然需要考虑返回行数的问题。

·如果select list (column list)不固定，则只好使用显式游标

·或者动态语句返回行数太多，必须用limit,那么也只好用显式游标了。

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

转载于:http://blog.itpub.net/17012874/viewspace-693787/