游标的各种用法和各自特点

尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
游标的概念:
    游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。
游标有两种类型：显式游标和隐式游标。在前述程序中用到的SELECT...INTO...查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。
游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。

隐式游标
如前所述，DML操作和单行SELECT语句会使用隐式游标，它们是：
插入操作：INSERT。更新操作：UPDATE。删除操作：DELETE。单行查询操作：SELECT ... INTO ...。
当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。游标的属性有四种，如下所示。
隐式游标的属性 返回值类型
    SQL%ROWCOUNT    整型  代表DML语句成功执行的数据行数  
    SQL%FOUND   布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功  
    SQL%NOTFOUND    布尔型 与SQL%FOUND属性返回值相反  
    SQL%ISOPEN  布尔型 DML执行过程中为真，结束后为假  
显式游标
游标的定义和操作
游标的使用分成以下4个步骤。
1．声明游标
在DECLEAR部分按以下格式声明游标：
CURSOR 游标名[(参数1 数据类型[，参数2 数据类型...])]
IS SELECT语句;
参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数，则必须在打开游标时传递相应的实际参数。
SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。
2．打开游标
在可执行部分，按以下格式打开游标：
OPEN 游标名[(实际参数1[，实际参数2...])];
打开游标时，SELECT语句的查询结果就被传送到了游标工作区。
3．提取数据
在可执行部分，按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。
FETCH 游标名 INTO 变量名1[，变量名2...];
或
FETCH 游标名 INTO 记录变量;
游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。
下面对这两种格式进行说明：
第一种格式中的变量名是用来从游标中接收数据的变量，需要事先定义。变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。
第二种格式一次将一行数据取到记录变量中，需要使用%ROWTYPE事先定义记录变量，这种形式使用起来比较方便，不必分别定义和使用多个变量。
定义记录变量的方法如下：
变量名 表名|游标名%ROWTYPE；
其中的表必须存在，游标名也必须先定义。
4．关闭游标
CLOSE 游标名;
显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。

declare
  cursor c_dept is select * from t_dept;
  v_ID number;
  v_name varchar2(20);
begin
  open c_dept;
  loop
    fetch c_dept into v_ID,v_name;
    exit when c_dept%notfound;
    dbms_output.put_line(to_char(v_ID)||'  '||v_name);
  end loop;
  close c_dept;
end;

declare
  cursor c_dept is select * from t_dept;
begin
  for v_dept in c_dept loop
    dbms_output.put_line(to_char(v_dept.p_n_deptID)||'  '||v_dept.c_deptname);
  end loop;
end;

--参数游标
declare
  v_ID number;
  v_name varchar2(20);
  cursor c_dept(v_deptid number) is select * from t_dept where p_n_deptid>=v_deptid;
begin
  open c_dept(3);
  loop
    fetch c_dept into v_ID,v_name;
    exit when c_dept%notfound;
    dbms_output.put_line(to_char(v_ID)||'  '||v_name);
  end loop;
  close c_dept;
end;
--游标变量
declare
  v_dept t_dept%rowtype;
  v_emp t_emp%rowtype;
  type c_cur is ref cursor;
  v_temp c_cur;
  v_num number;
begin
  v_num := 10;
  if v_num >10 then
    open v_temp for select * from t_dept;
    loop
      fetch v_temp into v_dept;
      exit when v_temp%notfound;
      dbms_output.put_line(to_char(v_dept.p_n_deptid)||'  '||v_dept.c_deptname);
    end loop;
    close v_temp;
  else
    open v_temp for select * from t_emp;
    loop
      fetch v_temp into v_emp;
      exit when v_temp%notfound;
      dbms_output.put_line(to_char(v_emp.p_n_empid)||'  '||v_emp.c_empname);
    end loop;
    close v_temp;
  end if;
end;

--动态游标  游标本来就比较慢，所以尽量不要使用动态游标。
declare
v_cur number;
v_sql varchar2(200);
v_id varchar(20);
v_name varchar(20);
v_dummy number;
begin
    v_sql := 'select * from t_dept' ;
    v_cur := dbms_sql.open_cursor() ;
    dbms_sql.parse( v_cur , v_sql , dbms_sql.V7 );
    dbms_sql.define_column( v_cur , 1 , v_id , 100 );
    dbms_sql.define_column( v_cur , 2 , v_name , 100 );
    v_dummy := dbms_sql.execute( v_cur );
    loop
       if dbms_sql.fetch_rows( v_cur ) = 0 then
          exit;
       end if;
       dbms_sql.column_value( v_cur , 1 , v_id );
       dbms_sql.column_value( v_cur , 2 , v_name );
       dbms_output.put_line(v_id||'---'||v_name);
    end loop;
    dbms_sql.close_cursor(v_cur) ;
end;

sys_refcursor 和 cursor 优缺点比较

优点比较
优点一：sys_refcursor，可以在存储过程中作为参数返回一个table格式的结构集（我把他认为是table类型，容易理解，其实是一个游标集）， cursor 只能用在存储过程，函数，包等的实现体中，不能做参数使用。
优点二：sys_refcursor 这东西可以使用在包中做参数，进行数据库面向对象开放。哈哈。我喜欢。cursor就不能。

缺点比较：
缺点：sys_refcursor 不能用open，close ，fetch 进行操作。不好学，难理解。
cursor可以用 open，close ，fetch操作，容易学，易懂
其他就目前不知道，至于游标的的基础概念，去google，百度一大堆的。这里就不累赘了。看例子：
建立一个存储过程
create or replace procedure up_test(o out sys_refcursor) is
begin
open o for select * from lq_test;
end;
返回的类型是sys_refcursor；
建立第二个存储过程
create or replace procedure up_getData(aMsg out varchar2) is
type p_table_type is table of lq_test%rowtype;
p_table p_table_type;
v sys_refcursor;
begin
up_test(v);
fetch v bulk collect into p_table;
for i in 1..p_table.count loop
dbms_output.put_line('字段1：'||p_table(i).v1 || ' 字段2：' || p_table(i).v2);
end loop;
end;

这里要注意fetch 带参数的用法，bulk collect ，这是第集合的操作，必须先定义一个结合类。见上面的例子，还不懂就google了。用法就简单，没啥好介绍的。
取集合的值应该这样p_table(i).v1，其中i标识几行，带上字段，即可了，容易理解。

像游标cursor一样，游标变量ref cursor指向指定查询结果集当前行。游标变量显得更加灵活因为其声明并不绑定指定查询。
其主要运用于PLSQL函数或存储过程以及其他编程语言java等程序之间作为参数传递。
不像游标的一点，游标变量没有参数。
    游标变量具有以下属性：
    (%FOUND, %NOTFOUND,%ISOPEN, %ROWCOUNT)


二 用法介绍：
1、声明格式：
    DECLARE  
    TYPE DeptCurTyp IS REF CURSOR RETURN departments%ROWTYPE;
2、游标变量又分为强类型strong(with a return type)和弱类型(with no return type)：
    DECLARE
   TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; -- 强类型
   TYPE genericcurtyp IS REF CURSOR; -- 弱类型
   cursor1 empcurtyp;
   cursor2 genericcurtyp;
   my_cursor SYS_REFCURSOR; -- 使用预定义游标变量sys_refcursor
   TYPE deptcurtyp IS REF CURSOR RETURN departments%ROWTYPE;
   dept_cv deptcurtyp; -- 声明游标变量
   或是返回record类型：
   DECLARE
   TYPE EmpRecTyp IS RECORD (
   employee_id NUMBER,
   last_name VARCHAR2(25),
   salary NUMBER(8,2));
   TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
   emp_cv EmpCurTyp; -- declare cursor variable


3、使用游标变量作为参数传递：
view plaincopy在CODE上查看代码片派生到我的代码片
    DECLARE  
    TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;  
    emp empcurtyp;  
    -- after result set is built, process all the rows inside a single procedure  
    -- rather than calling a procedure for each row  
    PROCEDURE process_emp_cv (emp_cv IN empcurtyp) IS  
    person employees%ROWTYPE;  
    BEGIN  
    DBMS_OUTPUT.PUT_LINE('-----');  
    DBMS_OUTPUT.PUT_LINE('Here are the names from the result set:');  
    LOOP  
    FETCH emp_cv INTO person;  
    EXIT WHEN emp_cv%NOTFOUND;  
    DBMS_OUTPUT.PUT_LINE('Name = ' || person.first_name ||  
    ' ' || person.last_name);  
    END LOOP;  
    END;  
    BEGIN  
    -- First find 10 arbitrary employees.  
    OPEN emp FOR SELECT * FROM employees WHERE ROWNUM < 11;  
    process_emp_cv(emp);  
    CLOSE emp;  
    -- find employees matching a condition.  
    OPEN emp FOR SELECT * FROM employees WHERE last_name LIKE 'R%';  
    process_emp_cv(emp);  
    CLOSE emp;  
    END;  
4、使用游标熟悉检查游标变量是否打开
 view plaincopy在CODE上查看代码片派生到我的代码片
    DECLARE  
    TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;  
    emp_cv empcurtyp;  
    BEGIN  
    IF NOT emp_cv%ISOPEN THEN -- open cursor variable  
    OPEN emp_cv FOR SELECT * FROM employees;  
    END IF;  
    CLOSE emp_cv;  
    END;  
5、在包package中声明游标变量：
 view plaincopy在CODE上查看代码片派生到我的代码片
    CREATE PACKAGE emp_data AS  
    TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;  
    PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp);  
    END emp_data;  
    CREATE PACKAGE BODY emp_data AS  
    PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS  
    BEGIN  
    OPEN emp_cv FOR SELECT * FROM employees;  
    END open_emp_cv;  
    END emp_data;   
6、提取游标变量到集合类型collection：
 view plaincopy在CODE上查看代码片派生到我的代码片
    DECLARE  
    TYPE empcurtyp IS REF CURSOR;  
    TYPE namelist IS TABLE OF employees.last_name%TYPE;  
    TYPE sallist IS TABLE OF employees.salary%TYPE;  
    emp_cv empcurtyp;  
    names namelist;  
    sals sallist;  
    BEGIN  
    OPEN emp_cv FOR SELECT last_name, salary FROM employees  
    WHERE job_id = 'SA_REP';  
    FETCH emp_cv BULK COLLECT INTO names, sals;  
    CLOSE emp_cv;  
    -- loop through the names and sals collections  
    FOR i IN names.FIRST .. names.LAST  
    LOOP  
    DBMS_OUTPUT.PUT_LINE('Name = ' || names(i) || ', salary = ' || sals(i));  
    END LOOP;  
    END;  
三 游标变量的使用限制：
 1、不能再包说明中声明游标变量；
 2、不能用“=”运算符比较游标变量相等性、不等性及是否为空；
 3、不能存储于表列中；
 4、不能将游标变量存在于关联数组、嵌套表或数组；
 5、游标和游标变量之前是不可互操作的！
1、强类型游标：
view plaincopy在CODE上查看代码片派生到我的代码片
    CREATE OR REPLACE PACKAGE strongly_typed IS
    TYPE return_cur IS REF CURSOR RETURN all_tables%ROWTYPE;  
    PROCEDURE child(p_return_rec OUT return_cur);  
    PROCEDURE parent(p_NumRecs PLS_INTEGER);        
    END strongly_typed;  
view plaincopy在CODE上查看代码片派生到我的代码片
    CREATE OR REPLACE PACKAGE BODY strongly_typed IS  
    PROCEDURE child(p_return_rec OUT return_cur) IS        
    BEGIN  
      OPEN p_return_rec FOR   
      SELECT * FROM all_tables;   
    END child;  
    PROCEDURE parent (p_NumRecs PLS_INTEGER) IS   
     p_retcur return_cur;  
     at_rec   all_tables%ROWTYPE;  
    BEGIN  
      child(p_retcur);  
      
      FOR i IN 1 .. p_NumRecs  
      LOOP  
        FETCH p_retcur  
        INTO at_rec;  
      
        dbms_output.put_line(at_rec.table_name ||   
        ' - ' || at_rec.tablespace_name ||   
        ' - ' || TO_CHAR(at_rec.initial_extent) ||   
        ' - ' || TO_CHAR(at_rec.next_extent));  
      END LOOP;  
    END parent;  
    END strongly_typed;  
view plaincopy在CODE上查看代码片派生到我的代码片
2、弱类型游标：
 view plaincopy在CODE上查看代码片派生到我的代码片
    CREATE OR REPLACE PROCEDURE child (  
     p_NumRecs IN PLS_INTEGER,  
     p_return_cur OUT SYS_REFCURSOR)  
    IS        
    BEGIN  
      OPEN p_return_cur FOR  
      'SELECT * FROM all_tables WHERE rownum <= ' || p_NumRecs ;  
    END child;  
      
    CREATE OR REPLACE PROCEDURE parent (pNumRecs VARCHAR2) IS  
     p_retcur  SYS_REFCURSOR;  
     at_rec    all_tables%ROWTYPE;  
    BEGIN  
      child(pNumRecs, p_retcur);       
      FOR i IN 1 .. pNumRecs  
      LOOP  
        FETCH p_retcur  
        INTO at_rec;       
        dbms_output.put_line(at_rec.table_name ||  
        ' - ' || at_rec.tablespace_name ||  
        ' - ' || TO_CHAR(at_rec.initial_extent) ||  
        ' - ' || TO_CHAR(at_rec.next_extent));  
      END LOOP;  
    END parent;  


3、预定义游标变量：
view plaincopy在CODE上查看代码片派生到我的代码片
    CREATE TABLE employees (  
    empid   NUMBER(5),  
    empname VARCHAR2(30));        
    INSERT INTO employees (empid, empname) VALUES (1, 'Dan Morgan');  
    INSERT INTO employees (empid, empname) VALUES (2, 'Hans Forbrich');  
    INSERT INTO employees (empid, empname) VALUES (3, 'Caleb Small');  
    COMMIT;  
    CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor SYS_REFCURSOR) IS        
    TYPE array_t IS TABLE OF VARCHAR2(4000)  
    INDEX BY BINARY_INTEGER;  
    rec_array array_t;        
    BEGIN  
      FETCH p_cursor BULK COLLECT INTO rec_array;        
      FOR i IN rec_array.FIRST .. rec_array.LAST  
      LOOP  
        dbms_output.put_line(rec_array(i));  
      END LOOP;  
    END pass_ref_cur;    
    set serveroutput on        
    DECLARE  
     rec_array SYS_REFCURSOR;  
    BEGIN  
      OPEN rec_array FOR  
      'SELECT empname FROM employees';        
      pass_ref_cur(rec_array);  
      CLOSE rec_array;  
    END;    
在上面的代码中，声明了一个弱类型的REF cursor，下面再看一个强类型（受限）的REF cursor，这种类型的REF cursor在实际的应用系统中用的也是比较多的。
create table gsm_resource
    (
      gsmno varchar2(11),
      status varchar2(1),
      price number(8,2),
      store_id varchar2(32)
    );
    insert into gsm_resource values('13905310001','0',200.00,'SD.JN.01');
    insert into gsm_resource values('13905312002','0',800.00,'SD.JN.02');
    insert into gsm_resource values('13905315005','1',500.00,'SD.JN.01');
    insert into gsm_resource values('13905316006','0',900.00,'SD.JN.03');
    commit;
 declare
          type gsm_rec is record(
             gsmno varchar2(11),
             status varchar2(1),
             price number(8,2));
     
        type app_ref_cur_type is ref cursor return gsm_rec;
          my_cur app_ref_cur_type;
         my_rec gsm_rec;
     
     begin
         open my_cur for select gsmno,status,price
            from gsm_resource
              where store_id='SD.JN.01';
          fetch my_cur into my_rec;
         while my_cur%found loop
               dbms_output.put_line(my_rec.gsmno||'#'||my_rec.status||'#'||my_rec.price);
               fetch my_cur into my_rec;
         end loop;
          close my_cur;
      end;

普通cursor与REF cursor还有一些大家应该都熟悉的区别，我再浪费点唾沫。
1）PL/SQL静态光标不能返回到客户端，只有PL/SQL才能利用它。ref光标能够被返回到客户端，这就是从Oracle的存储过程返回结果集的方式。
2）PL/SQL静态光标可以是全局的，而ref光标则不是。 也就是说，不能在包说明或包体中的过程或函数之外定义ref光标。 只能在定义ref光标的过程中处理它，或返回到客户端应用程序。

3）ref光标可以从子例程传递到子例程，而光标则不能。 为了共享静态光标，必须在包说明或包体中把它定义为全局光标。 因为使用全局变量通常不是一种很好的编码习惯，因此可以用ref光标来共享PL/SQL中的光标，无需混合使用全局变量。

最后，使用静态光标--通过静态SQL（但不用ref光标）--比使用ref光标效率高，而ref光标的使用仅限于以下几种情况：

把结果集返回给客户端；
在多个子例程之间共享光标（实际上与上面提到的一点非常类似）；
没有其他有效的方法来达到你的目标时，则使用ref光标，正如必须用动态SQL时那样；

简言之，首先考虑使用静态SQL，只有绝对必须使用ref光标时才使用ref光标，也有人建议尽量使用隐式游标，避免编写附加的游标控制代码（声明，打开，获取，关闭），也不需要声明变量来保存从游标中获取的数据。这个就因人因具体的case大家去酌定吧。

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

转载于:http://blog.itpub.net/27924079/viewspace-1784260/