ORACLE游标概念讲解

骑猪看日落 2022-05-25 11:54 179阅读 0赞

转载来自：[https://blog.csdn.net/qq877507054/article/details/52886329][https_blog.csdn.net_qq877507054_article_details_52886329]

1.什么是游标？  
从表中检索出结果集，从中每次指向一条记录进行交互的机制。  
用来管理从数据源返回的数据的属性（结果集）。这些属性包括并发管理、在结果集中的位置、返回的行数，以及是否能够在结果集中向前和/或向后移动（可滚动性）。  
游标跟踪结果集中的位置，并允许对结果集逐行执行多个操作，在这个过程中可能返回至原始表，也可能不返回至原始表。  
换句话说，游标从概念上讲基于数据库的表返回结果集。  
游动的光标（指针），可以指向一个结果集，通过游标的移动逐行提取每一行的记录 。  
用来查询数据库，获取记录集合（结果集）的指针，可以让开发者一次访问一行结果集，在每条结果集上作操作。  
用来存储多条查询数据的一种数据结构（结果集），它有一个指针，用来从上往下移动，从而达到遍历每条记录的作用。  
由于它指示结果集中的当前位置 ，就像计算机屏幕上的光标指示当前位置一样，“游标”由此得名。  
2.游标有什么作用？  
①指定结果集中特定行的位置。  
②基于当前的结果集位置检索一行或连续的几行。  
③在结果集的当前位置修改行中的数据。  
④对其他用户所做的数据更改定义不同的敏感性级别。  
⑤可以以编程的方式访问数据库。  
程序语言是面向记录的，一组变量一次只能存放一个变量或者一条记录，无法直接接收数据库中的查询结果集引入游标就解决了这个问题

3.为什么避免使用游标？  
①在创建游标时，最需要考虑的事情是，“是否有办法避免使用游标？”  
因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；  
如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。

4.Oracle游标的类型？  
①静态游标：结果集已经确实（静态定义）的游标。在执行前，明确知道sql语句游标 ,分为隐式和显示游标。

显示游标  
用户显示声明的游标，即指定结果集。当查询返回结果超过一行时，就需要一个显式游标。用户自己写的sql语句，编译时能明确知道sql语句  
隐式游标  
所有DML语句（增、删、改、查询单条记录）为隐式游标，该变量名不需要用户自己声明，它由系统帮我们定义，叫sql。使用时不需要声明隐式游标，它由系统定义 .  
隐式游标的使用：通过%rowcour判断是有使用了。  
② 动态游标 (REF游标)  
在执行前不知道sql语句游标，执行时才知道sql语句的游标。 动态关联结果集的临时对象。  
强类型游标  
规定返回类型  
弱类型游标  
不规定返回类型，可以获取任何结果集。  
5.Oracle游标的状态有哪些，怎么使用游标属性？  
如何判断是否到了结果集的尾部，oracle中，对于游标的属性，它通过属性值来判断的  
①游标的状态是通过属性来表示。  
%Found ：用于检验游标是否成功，通常在fetch语句前使用，当游标按照条件查询一条记录是，返回true,Fetch语句（获取记录）执行情况True or False。  
%NotFound : 最后一条记录是否提取出True or False。 到了游标尾部，没有记录了，就返回true  
%ISOpen ： 游标是否打开True or False。  
%RowCount ：游标当前提取的行数 ,即获得影响的行数 1  
②使用游标的属性。

例子：

Begin
       Update emp Set  SAL = SAL + 0.1  Where JOB = 'CLERK';
       If  SQL%Found  Then
        DBMS_OUTPUT.PUT_LINE('已经更新！');
       Else
        DBMS_OUTPUT.PUT_LINE('更新失败！');
       End  If；
      End；

6.如何使用显示游标,？如何遍历循环游标？  
①使用显示游标  
一,  
⑴声明游标：划分存储区域，注意此时并没有执行Select 语句。  
CURSOR 游标名( 参数 列表) \[返回值类型\] IS Select 语句；  
⑵打开游标：执行Select 语句，获得结果集存储到游标中，此时游标指向结果集头， 而不是第一条记录。  
Open 游标名( 参数 列表);  
⑶获取记录：移动游标取一条记录  
Fetch 游标名InTo 临时记录或属性类型变量；  
⑷关闭游标：将游标放入缓冲池中，没有完全释放资源。可重新打开。  
Close 游标名；  
二,  
1、声明游标：  
cursor curname is select…….  
2、打开游标:  
open curname;  
3、提取数据:循环提取数据：游标中的谓词 curname%found 、curname%notfound。  
当结果集中的数据没有提取完时 %found的值true,否则是false.  
%notfound与%found 相反。通过两个谓词可以控制循环  
fetch curname into …….  
4、关闭游标  
close curname;

②遍历循环游标  
⑴For 循环游标  
循环游标隐式打开游标，自动滚动获取一条记录，并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。  
For 变量名 In 游标名  
Loop  
数据处理语句；  
End Loop；  
⑵Loop循环游标  
。。。  
Loop  
Fatch 游标名InTo 临时记录或属性类型变量；  
Exit When 游标名%NotFound；  
End Loop；  
。。。  
例子1：

/* conn scott/tiger */
       Declare
         Cursor myCur is select empno,ename,sal from emp;
         vna varchar2(10);
         vno number(4);
         vsal number(7,2);
      Begin open myCur;
         fetch myCur into vno,vna,vsal;
         dbms_output.put_line(vno||'    '||vna||'    '||vsal);
         close myCur;
      End;
      /

例子2：使用loop遍历游标。

Declare
         Cursor myCur is select ename,job,sal,empno from emp;
         varE myCur%rowType;
      Begin
         if myCur%isopen = false then
            open myCur;
           dbms_output.put_line('Opening...');
         end if;
         loop
            fetch myCur into varE;
            exit when myCur%notfound;
            dbms_output.put_line(myCur%rowCount||' '||vare.empno||' '||vare.ename||' '||vare.sal);
         end loop;
         if myCur%isopen then
            Close myCur;
            dbms_output.put_line('Closing...');
         end if;
      End;
      /

例子3：使用For循环遍历游标

/* conn scott/tiger */
      Declare
         Cursor myCur is select * from emp;
      Begin for varA in myCur loop dbms_output.put_line(myCur%rowCount||' '||varA.empno||' '||varA.ename||' '||varA.sal);
          end loop;
      End;
      /

7.怎样更新和删除显示游标中的记录？  
①UPDATE或DELETE语句中的WHERE CURRENT OF子串专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据。  
要使用这个方法，在声明游标时必须使用FOR UPDATE子串，当对话使用FOR UPDATE子串打开一个游标时，  
所有返回集中的数据行都将处于行级（ROW-LEVEL)独占式锁定，其他对象只能查询这些数据行，  
不能进行UPDATE、DELETE或SELECT…FOR UPDATE操作。  
在多表查询中，使用OF子句来锁定特定的表,如果忽略了OF子句，那么所有表中选择的数据行都将被锁定。  
如果这些数据行已经被其他会话锁定，那么正常情况下ORACLE将等待，直到数据行解锁。  
②使用更新或删除：  
⑴声明更新或删除显示游标：  
Cursor 游标名IS SELECT 语句 For Update \[ Of 更新列列名\]；  
Cursor 游标名IS SELECT 语句 For Delete \[ Of 更新列列名\]；  
⑵使用显示游标当前记录来更新或删除：  
Update 表名 SET 更新语句 Where Current Of 游标名；  
Delete From 表名 Where Current Of 游标名；

例子1:更新显示游标记录

/*conn scott/tiger*/
       Declare
         Cursor myCur is select job from emp for update;
           vjob empa.job%type;
           rsal empa.sal%type;
        Begin
           open myCur;
           loop
              fetch myCur into vjob;
              exit when myCur%notFound;
              case  (vjob)
                 when 'ANALYST' then  rsal := 0.1;
                when  'CLERK' then  rsal := 0.2;
                 when  'MANAGER' then  rsal := 0.3;
                 else
                   rsal := 0.5;
              end case;
            update emp set sal = sal + rsal where current of myCur;
           end loop;
        End;
        /

例子2：删除显示游标记录

/*conn scott/tiger
        Crate table  empa  Select * from scott.emp;
        */
        Declare
          Cursor MyCursor  Select   JOB  From  empa  For  Update;
          vSal   emp.Sal%TYPE;
        Begin
          Loop
           Fetch  MyCursor  InTo  vSal;
           Exit  When  MyCursor%NotFound;
           If   vSal < 800 Then
            Delete  From empa  Where  Cursor  Of   MyCursor;
           End  If;  
          End    Loop;
        End;/

8.什么是带参数的显示游标？  
①与过程和函数相似，可以将参数传递给游标并在查询中使用。  
参数只定义数据类型，没有大小(所有Oracle中的形参只定义数据类型，不指定大小)。  
与过程不同的是，游标只能接受传递的值，而不能返回值。  
　 可以给参数设定一个缺省值，当没有参数值传递给游标时，就使用缺省值。  
游标中定义的参数只是一个占位符，在别处引用该参数不一定可靠。  
②使用带参数的显示游标  
⑴声明带参数的显示游标：  
CURSOR 游标名 \[(parameter\[,parameter\],…)\] IS Select语句；;

参数形式：1，参数名 数据类型  
2，参数名 数据类型 DEFAULT 默认值

例子：

/*conn scott/tiger
        Crate table  empa  Select * from scott.emp;
        */
        Declare
          Cursor MyCursor(pSal  Number  Default   800)  Select   JOB  From  empa Where  SAL >  pSal ;
          varA  MyCursor%ROWTYPE;
        Begin
          Loop
           Fetch  MyCursor  InTo  varA;
           Exit  When  MyCursor%NotFound;
           DBMS_OUTPUT.PUT_LINE(MyCursor%RowCount||' '||varA.empno||' '||varA.ename||' '||varA.sal);
          End    Loop;
        End;/

REF CURSOR

1，什么是REF游标 ？  
动态关联结果集的临时对象。即在运行的时候动态决定执行查询。

2，REF 游标 有什么作用？  
实现在程序间传递结果集的功能，利用REF CURSOR也可以实现BULK SQL，从而提高SQL性能。

3，静态游标和REF 游标的区别是什么？  
①静态游标是静态定义，REF 游标是动态关联；  
②使用REF 游标需REF 游标变量。  
③REF 游标能做为参数进行传递，而静态游标是不可能的。

4，什么是REF 游标变量？  
REF游标变量是一种 引用REF游标类型 的变量，指向动态关联的结果集。

5，怎么使用 REF游标 ？  
①声明REF 游标类型，确定REF 游标类型；  
⑴强类型REF游标：指定retrun type，REF 游标变量的类型必须和return type一致。  
语法：Type REF游标名 IS Ref Cursor Return 结果集返回记录类型；  
⑵弱类型REF游标：不指定return type，能和任何类型的CURSOR变量匹配，用于获取任何结果集。  
语法：Type REF游标名 IS Ref Cursor；

②声明Ref 游标类型变量；  
语法：变量名 已声明Ref 游标类型；

③打开REF游标，关联结果集 ；  
语法：Open Ref 游标类型变量 For 查询语句返回结果集；

④获取记录，操作记录；  
语法：Fatch REF游标名InTo 临时记录类型变量或属性类型变量列表；

⑤关闭游标，完全释放资源；  
语法：Close REF游标名；

例子：强类型REF游标

/*conn scott/tiger*/
     Declare
      Type MyRefCurA IS  REF CURSOR RETURN emp%RowType;
      Type MyRefCurB IS  REF CURSOR RETURN emp.ename%Type;
      vRefCurA  MyRefCurA;
      vRefCurB  MyRefCurB;
      vTempA  vRefCurA%RowType;
      vTempB  vRefCurB.ename%Type;
    
     Begin
      Open  vRefCurA  For Select  *  from   emp   Where  SAL > 2000;
      Loop
       Fatch  vRefCurA InTo  vTempA;
       Exit  When  vRefCurA%NotFound;
       DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount||' '|| vTempA.eno||' '||vTempA.ename ||' '||vTempA.sal)
      End Loop;
      Close vRefCurA;
    
      DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------------');
    
      Open  vRefCurB  For Select  ename  from   emp   Where  SAL > 2000;
      Loop
       Fatch  vRefCurB InTo  vTempB;
       Exit  When  vRefCurB%NotFound;
       DBMS_OUTPUT.PUT_LINE(vRefCurB%RowCount||' '||vTempB)
      End Loop;
      Close vRefCurB;
    
      DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------------------------'); 
    
      Open  vRefCurA  For Select  *  from   emp   Where  JOB = 'CLERK';
      Loop
       Fatch  vRefCurA InTo  vTempA;
       Exit  When  vRefCurA%NotFound;
       DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount||' '|| vTempA.eno||' '||vTempA.ename ||' '||vTempA.sal)
      End Loop;
      Close vRefCurA;
     End;

例子：弱类型REF游标

/*conn scott/tiger*/
     Declare
      Type MyRefCur  IS  Ref  Cursor;
      vRefCur MyRefCur;
      vtemp  vRefCur%RowType;
     Begin Case(&n) When 1 Then Open vRefCur For Select * from emp;
       When  2 Then Open vRefCur  For Select * from dept;
       Else
        Open vRefCur  For Select eno, ename from emp Where JOB = 'CLERK';
      End Case;
      Close  vRefCur;
     End;

9.ORACLE中的游标汇总

游标（Cursor）：用来查询数据库，获取记录集合（结果集）的指针，可以让开发者一次访问一行结果集，在每条结果集上作操作。  
游标可分为：  
静态游标：分为显式游标和隐式游标。  
REF游标：是一种引用类型，类似于指针。—-也称为动态游标

1、静态游标

1.1显式游标

定义格式：  
CURSOR 游标名 ( 参数 ) \[返回值类型\] IS Select 语句 ;  
例子

open 游标; 
    loop  fetch  游标  into 游标变量; 
    exit   when 游标%notfound; 
    end loop; 
    close 游标; 
    cursor emp_cur ( p_deptid in number) is 
    set serveroutput on 
    declare 
    select * from employees where department_id = p_deptid;
    l_emp employees%rowtype;
    begin 
     dbms_output.put_line('Getting employees from department 30'); 
    open emp_cur(30);
     loop 
      fetch emp_cur into l_emp; 
      exit when emp_cur%notfound; 
      dbms_output.put_line('Employee id '|| l_emp.employee_id || ' is'); 
      dbms_output.put_line(l_emp.first_name || ' ' || l_emp.last_name); 
    emp_cur; 
     end loop; 
     close 
     dbms_output.put_line('Getting employees from department 90'); 
    open emp_cur(90); 
     loop 
     fetch emp_cur into l_emp; 
      exit when emp_cur%notfound; 
      dbms_output.put_line('Employee id '|| l_emp.employee_id || ' is'); 
      dbms_output.put_line(l_emp.first_name || ' ' || l_emp.last_name); 
     end loop; 
     close emp_cur; 
    end;

1.2隐式游标  
不用明确建立游标变量，分两种：  
1．在PL/SQL中使用DML语言，使用ORACLE提供的名为“SQL”的隐示游标。  
举例：

declare 
    begin update departments set department_name=department_name where 1=2; 
    dbms_output.put_line('update '|| sql%rowcount ||' records'); end;

2．CURSOR FOR LOOP，用于for in loop 语句  
for 游标变量 in 游标 loop end loop;用于静态游标中，不能用于动态游标,且不需要显示的打开、关闭、取数据、测试数据的存在、定义数据的变量等等。  
举例：  
游标FOR循环，简化游标操作my\_dept\_rec 不需要声明

declare
    begin for my_dept_rec in ( select department_name, department_id from epartments) loop dbms_output.put_line(my_dept_rec.department_id || ' : ' || my_dept_rec.department_name);
     end loop;
    end;
    /

1.3静态游标常用属性：  
显式游标属性：  
%FOUND：变量最后从游标中获取记录的时候，在结果集中找到了记录。  
%NOTFOUND：变量最后从游标中获取记录的时候，在结果集中没有找到记录。  
%ROWCOUNT：当前时刻已经从游标中获取的记录数量。  
%ISOPEN：是否打开。  
隐式游标属性:  
SQL%FOUND  
SQL%NOTFOUND

在执行任何DML语句前SQL%FOUND和SQL%NOTFOUND的值都是NULL,在执行DML语句后，SQL%FOUND的属性值将是：  
. TRUE :INSERT  
. TRUE :DELETE和UPDATE，至少有一行被DELETE或UPDATE.  
. TRUE :SELECT INTO至少返回一行

当SQL%FOUND为TRUE时,SQL%NOTFOUND为FALSE.

SQL%ROWCOUNT  
在执行任何DML语句之前，SQL%ROWCOUNT的值都是NULL,对于SELECT INTO语句，如果执行成功，SQL%ROWCOUNT的值为1,如果没有成功，SQL%ROWCOUNT的值为0，同时产生一个异常NO\_DATA\_FOUND.  
SQL%ISOPEN  
SQL%ISOPEN是一个布尔值，如果游标打开，则为TRUE, 如果游标关闭，则为FALSE.对于隐式游标而言SQL%ISOPEN总是FALSE，这是因为隐式游标在DML语句执行时打开，结束时就立即关闭。

Declare 
       Cursor emps is 
     Select * from employees where rownum<6 order by 1; 
       emp employees%rowtype; 
     Row number :=1; 
    Begin 
     Open emps;  
     Fetch emps into emp;  
     Loop 
      If emps%found then 
       Dbms_output.put_line('Looping over record '||row|| ' of ' || emps%rowcount); 
       Fetch emps into emp; 
       Row := row + 1; 
      Else if emps%notfound then 
       Exit;  
      End if; 
     End loop; 
    
     If emps%isopen then 
      Close emps;   
     End if; 
    End; 
    /

显式和隐式游标的区别：

尽量使用隐式游标，避免编写附加的游标控制代码（声明，打开，获取，关闭），也不需要声明变量来保存从游标中获取的数据。

2、REF CURSOR游标  
动态游标，在运行的时候才能确定游标使用的查询。可以分为：  
强类型（限制）（Strong REF CURSOR），规定返回类型  
弱类型（非限制）（Weak REF CURSOR），不规定返回类型，可以获取任何结果集。  
定义格式：  
TYPE ref\_cursor\_name IS REF CURSOR \[RETURN return\_type\]  
cursor ref\_cursor\_name

例如：

open cursor for  select ......from (多个open for 同时用时 ，不需要有关闭游标的语句) Declare Type refcur_t is ref cursor; 
     Type emp_refcur_t is ref cursor return employee%rowtype; 
    Begin Null; 
    End;

强类型举例：

declare 
     --声明记录类型
     type emp_job_rec is record( 
      employee_id number, 
      employee_name varchar2(50), 
      job_title varchar2(30) 
     ); 
     --声明REF CURSOR，返回值为该记录类型
     type emp_job_refcur_type  is  ref  cursor  return emp_job_rec; 
     --定义REF CURSOR游标的变量
     emp_refcur  emp_job_refcur_tpe; 
     emp_job  emp_job_rec; 
    begin 
    
     open emp_refcur for 
      select e.employee_id, e.first_name || ' ' ||e.last_name "employee_name", 
        j.job_title 
      from employees e, jobs j 
      where e.job_id = j.job_id and rownum < 11 order by 1; 
    
     fetch emp_refcur into emp_job; 
     while emp_refcur%found loop 
      dbms_output.put_line(emp_job.employee_name || '''s job is '); 
      dbms_output.put_line(emp_job.job_title); 
      fetch emp_refcur into emp_job; 
     end loop; 
    end;

指定了retrun 类型，CURSOR变量的类型必须和return 类型一致。  
例子：

CREATE OR REPLACE PACKAGE emp_data AS
    TYPE empcurtyp IS REF CURSOR RETURN emp%rowtype;
    --定义Strong REF CURSOR
    PROCEDURE open_emp_cv(emp_cv IN OUT empcurtyp, choice INT);
    --根据不同的choice选择不同的CURSOR
    PROCEDURE retrieve_data(choice INT);
    --通过调用procedure open_emp_cv，返回指定的结果集。 END emp_data;

CREATE OR REPLACE PACKAGE BODY emp_data AS
    
    PROCEDURE open_emp_cv(emp_cv IN OUT empcurtyp, choice INT) IS --emp_cv作为传入/传出的CURSOR PARAMETER BEGIN IF choice = 1 THEN OPEN emp_cv FOR SELECT * FROM emp WHERE empno < 7800;
    ELSIF choice = 2 THEN
    
    OPEN emp_cv FOR SELECT * FROM emp WHERE SAL < 1000;
    ELSIF choice = 3 THEN
    
    OPEN emp_cv  FOR SELECT * FROM emp WHERE ename like 'J%';
    END IF;
    
    END;
    
    PROCEDURE retrieve_data(choice INT) IS return_cv empcurtyp;
    --定义传入open_emp_cv的CURSOR变量
    return_row emp%ROWTYPE;
    invalid_choice EXCEPTION;
    BEGIN
    
    --调用 procedure OPEN_EMP_CV open_emp_cv(return_cv, choice);
    dt> 
    
    
    IF choice = 1 THEN
    
    DBMS_OUTPUT.PUT_LINE('EMPLOYEES with empno less t han7800');
    ELSIF choice = 2 THEN
    
    DBMS_OUTPUT.PUT_LINE('EMPLOYEES with salary less than 1000');
    ELSIF choice = 3 THEN
    
    DBMS_OUTPUT.PUT_LINE('EMPLOYEES with name starts with ''J''');
    ELSE
    
    RAISE invalid_choice;
    END IF;
    
    LOOP 
    
    FETCH return_cv   INTO return_row;
    EXIT WHEN return_cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(return_row.empno || '--' || return_row.ename || '--' ||
    return_row.sal);
    
    END LOOP;
    
    
    
    EXCEPTION
    
    WHEN invalid_choice THEN
    DBMS_OUTPUT.PUT_LINE('The CHOICE should be in one of (1,2,3)!');
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Errors in procedure RETRIEVE_DATA!');
    END; 
    
    
    
    END emp_data;
    
    
    
    
    执行： 
    
    SQL> EXEC emp_data.retrieve_data(1);
    
    SQL> EXEC emp_data.retrieve_data(2);
    
    SQL> EXEC emp_data.retrieve_data(3);
    
    SQL> EXEC emp_data.retrieve_data(34);

使用弱类型游标的例子

create or replace procedure open\_cv(choice IN INT, return\_cv OUT SYS\_REFCURSOR) is

–参数return\_cv为weak REF CURSOR,利用SYS\_CURSOR来定义

begin
    
    if choice = 1 then
    
    open return_cv for 'select * from emp';
    elsif choice = 2 then
    
    open return_cv for 'select * from dept';
    end if;
    
    end open_cv;
    
    
    
    
    CREATE or replace procedure retrieve_data(choice IN INT) is emp_rec emp%rowtype;
    
    dept_rec dept%rowtype;
    
    return_cv SYS_REFCURSOR;
    
    invalid_choice exception; 
    
    
    
    BEGIN
    
    if choice=1 then
    
    dbms_output.put_line('employee information');
    open_cv(1,return_cv); --调用procedure open_cv;
    loop
    fetch return_cv into emp_rec;
    exit when return_cv%notfound;
    dbms_output.put_line(emp_rec.empno||'-'||emp_rec.ename||'-'||emp_rec.sal);
    end loop;
    elsif choice=2 then
    
    dbm_output.put_line('department information');
    open_cv(2,return_cv);
    
    loop
    fetch return_cv into dept_rec;
    exit when return_cv%notfound;
    dbms_output.put_line(dept_rec.deptno||'-'||dept_rec.dname||'-'||dept_rec.loc);
    end loop;
    else
    
    raise invalid_choice;
    end if;
    
    exception
    
    when invalid_choice then
    dbms_output.put_line('The CHOICE should be one of 1 and 2!');
    when others then
    
    dbms_output.put_line('Errors in procedure retrieve_data');
    END retrieve_data;

执行：

SQL> exec retrieve\_data(1);

SQL> exec retrieve\_data(2);

用REF CURSOR实现BULK功能

1.  可以加速INSERT, UPDATE, DELETE语句的执行，也就是用FORALL语句来替代循环语句。
2.  加速SELECT，用BULK COLLECT INTO 来替代INTO。

SQL> create table tab2  as select empno ID, ename NAME, sal SALARY from emp where 1=2;
    
    create or replace procedure REF_BULK is type empcurtyp is ref cursor;
    type idlist  is table of emp.empno%type;
    type namelist  is table of emp.ename%type;
    type sallist  is table of emp.sal%type;
    
    
    emp_cv  empcurtyp;
    ids  idlist;
    names namelist;
    sals sallist;
    row_cnt number;
    begin
    open emp_cv for select empno, ename, sal from emp;
    fetch emp_cv  BULK COLLECT  INTO ids, names, sals;
    --将字段成批放入变量中，此时变量是一个集合
    close emp_cv;
    
    for i in ids.first .. ids.last loop
    dbms_output.put_line(' || ids(i) || ' || names(i) ||' salary=' || sals(i));
    end loop;
    
    FORALL  i  IN  ids.first .. ids.last
    insert into tab2 values (ids(i), names(i), sals(i));
    commit;
    select count(*) into row_cnt from tab2; dbms_output.put_line('-----------------------------------'); dbms_output.put_line('The row number of tab2 is ' || row_cnt); end REF_BULK;

3、cursor 和 ref cursor的区别

从技术底层看，两者是相同的。普通plsql cursor在定义时是“静态”的。而  
Ref cursors可以动态打开。  
例如下面例子：

Declare 
    typerc is ref cursor; 
    cursor c is select * from dual; 
    l_cursor rc; 
    begin 
    if ( to_char(sysdate,'dd') = 30 ) then 
           open l_cursor for 'select * from emp'; 
    elsif ( to_char(sysdate,'dd') = 29 ) then 
           open l_cursor for select * from dept; 
    else 
           open l_cursor for select * from dual; 
    end if; 
    open c; 
    end;

rc根据逻辑动态打开；而游标c定义好了只有就无法修改了。  
ref cursor可以返回给客户端，cursor则不行。cursor可以是全局的global ，  
ref cursor则必须定义在过程或函数中。  
ref cursor可以在子程序间传递，cursor则不行。  
cursor中定义的静态sql比ref cursor效率高，所以ref cursor通常用在：向客户端返回结果集。

参考资料:[http://blog.sina.com.cn/s/blog\_95cfa64601011v0z.html][http_blog.sina.com.cn_s_blog_95cfa64601011v0z.html]

10.其他教程  
–案例：打印所有的员工的全部信息

declare
             --声明游标：
             cursor c_emp is  select * from emp ;
             --声明行类型变量
             v_emp emp%rowtype;
          begin
            --打开游标
            open c_emp;
            --通过loop循环提起结果集
            loop
              --提取数据
              fetch c_emp into v_emp;
              exit when c_emp%notfound ;
              dbms_output.put_line(v_emp.empno||' '||v_emp.ename
              ||' '||v_emp.job||' '||v_emp.mgr||' '
              ||v_emp.hiredate||' '||v_emp.sal||' '
              ||v_emp.comm||' '||v_emp.deptno );
            end loop;
            --关闭游标
            close c_emp;
    
          end;

--使用while循环改写上例

declare
             --声明游标：
             cursor c_emp is  select * from emp ;
             --声明行类型变量
             v_emp emp%rowtype;
          begin
            --打开游标
            open c_emp;
            --通过loop循环提起结果集
    
            --提取数据
            fetch c_emp into v_emp;
            while c_emp%found loop
               dbms_output.put_line(v_emp.empno||' '||v_emp.ename
              ||' '||v_emp.job||' '||v_emp.mgr||' '
              ||v_emp.hiredate||' '||v_emp.sal||' '
              ||v_emp.comm||' '||v_emp.deptno );
              --提取数据
              fetch c_emp into v_emp;        
            end loop;
            --关闭游标
            close c_emp;
    
          end;

–使用游标 for循环提取数据： 游标自动打开，自动关闭

declare
             --声明游标：
             cursor c_emp is  select * from emp ;
            begin    
              for v_emp in c_emp loop
                dbms_output.put_line(v_emp.empno||' '||v_emp.ename
                ||' '||v_emp.job||' '||v_emp.mgr||' '
                ||v_emp.hiredate||' '||v_emp.sal||' '
                ||v_emp.comm||' '||v_emp.deptno );
              end loop;   
               exception
                  when cursor_already_open then
                    dbms_output.put_line('游标已经打开');
                  when invalid_cursor then
                    dbms_output.put_line('无效游标');
            end;

--游标使用中的异常：
    invalid_cursor:无效的游标
    cursor_aleaday_open；游标已经打开在关闭前再次打开
    
    
    --案例：
    使用游标按照下列各式打印信息
    
    部门号：20    总工资：30000  平均工资： 8000

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

———————————————————————————  
编号 姓名 职务 领导 入职 工资 奖金  
————————————————————————————  
7369 aaa aaa 1 1987年-4月-5日 3000 500  
————————————————————————————  
编号 姓名 职务 领导 入职 工资 奖金  
————————————————————————————  
7369 aaa aaa 1 1987年-4月-5日 3000 500  
————————————————————————————-

部门号：:30 总工资：300000 平均工资：20000

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

declare
      --声明分组查询的游标
      cursor c_sal_dept is select deptno,sum(sal),avg(sal)
      from emp group by deptno;
      v_deptno emp.deptno%type;
      v_sumsal number(10,2);
      v_avgsal number(8,2);
     begin
       open c_sal_dept;
       loop
         fetch c_sal_dept into v_deptno,v_sumsal,v_avgsal;
         exit when c_sal_dept%notfound;
         dbms_output.put_line('部门号：'||v_deptno||
         ' 总工资：'||v_sumsal||' 平均工资：'
         ||v_avgsal);
         dbms_output.put_line('_______________________________________');
         dbms_output.put_line('_______________________________________');
    
         declare
            cursor c_emp is select empno,ename,job,mgr,hiredate,sal,comm
            from emp where deptno=v_deptno;
            v_empno emp.empno%type;
            v_ename emp.ename%type;
            v_job emp.job%type;
            v_mgr emp.mgr%type;
            v_hiredate emp.hiredate%type;
            v_sal emp.sal%type;
            v_comm emp.comm%type;
         begin
           open c_emp;
           fetch c_emp into v_empno,v_ename,v_job,v_mgr,v_hiredate,v_sal,
           v_comm;
           while c_emp%found loop
            dbms_output.put_line(' 编号 姓名 职务 领导 入职 工资 奖金');
            dbms_output.put_line('------------------------------------------------------');
            dbms_output.put_line(v_empno||' '||v_ename||' '||v_job||' '||v_mgr
            ||' '||v_hiredate||' '||v_sal||' '||v_comm);
            dbms_output.put_line('------------------------------------------------------');
    
           fetch c_emp into v_empno,v_ename,v_job,v_mgr,v_hiredate,v_sal,
           v_comm;
          end loop;
          close c_emp;
         end;
    
       end loop;
       close c_sal_dept;
    
     end;

ref游标：  
1、定义游标类型：  
type cursor\_type is ref cursor;  
2、声明游标变量：  
c\_emp cursor\_type ;  
3、游标变量的初始化：  
open c\_emp for select……………..

4、关闭游标变量：  
close c\_emp;

–案例：通过游标变量打印所有的员工：

declare
         --声明游标类型
         type c_type is ref cursor;
         --声明游标变量：
         c_emp c_type;
         v_emp emp%rowtype;
      begin
        --初始化游标变量
        open c_emp for select * from emp;
        loop
          fetch c_emp into v_emp;
          exit when c_emp%notfound;
          dbms_output.put_line(v_emp.empno||'  '||v_emp.ename);
        end loop;
        --关闭游标变量
        close c_emp;
        exception
          when others then
            dbms_output.put_line('出错啦');
      end;

其他案例

– 示例. 显式游标

DECLARE
        name emp.name%type;
        sal  emp.sal%type; --定义2个变量来存放ename和sal的内容
        CURSOR emp_cursor IS
    SELECT name,sal FROM emp;
    BEGIN
        OPEN emp_cursor;
        LOOP
    FETCH emp_cursor INTO name,sal;
            EXIT WHEN emp_cursor%NOTFOUND;       
    dbms_output.put_line('第'||emp_cursor%ROWCOUNT||'个雇员：'||name|| sal);
        END LOOP;
        CLOSE emp_cursor;
    END;

– 示例. 带参数显式游标

DECLARE
    CURSOR emp_cursor(no NUMBER) IS
    select name,sal FROM emp WHERE deptno=no;
    emp_record emp_cursor%ROWTYPE;
    v_sal emp.sal %TYPE;
    BEGIN IF NOT emp_cursor%ISOPEN THEN OPEN emp_cursor(10);
    END IF;
    LOOP
    FETCH emp_cursor INTO emp_record;
    EXIT WHEN emp_cursor%NOTFOUND;
    dbms_output.put_line(emp_record.ename||':'||emp_record.sal);
    END LOOP;
    CLOSE emp_cursor;
    END;

–示例. 使用显式游标更新或删除  
–单表

DECLARE
    CURSOR emp_cursor IS
    select name,sal,deptno FROM emp FOR UPDATE;
    emp_record emp_cursor%ROWTYPE;
    BEGIN
    IF NOT emp_cursor%ISOPEN THEN
    OPEN emp_cursor;
    END IF;
    LOOP
    FETCH emp_cursor INTO emp_record;
    EXIT WHEN emp_cursor%NOTFOUND;
    if emp_record.deptno=30 AND emp_record.sal>2500 THEN
    DELETE FROM emp WHERE CURRENT OF emp_cursor;
    END IF;
    END LOOP;
    CLOSE emp_cursor;
    END;

–多表

DECLARE
    CURSOR emp_cursor IS
    select name,sal,emp.deptno,name FROM emp,dept WHERE emp.deptno=dept.deptno FOR UPDATE of emp.sal;    --注意：多表时FOR UPDATE OF 后面明确的某个列的作用是，这个列是属于哪个表的则后面通过游标更新或修改的是哪个表。例如，该例中明确的是emp表的sal列，说明下列语句中通过游标更新或修改的表为emp表，如果下列语句中更新的是dept表，则报错。
    
    emp_record emp_cursor%ROWTYPE;
    BEGIN IF NOT emp_cursor%ISOPEN THEN OPEN emp_cursor;
    END IF;
    LOOP
    FETCH emp_cursor INTO emp_record;
    EXIT WHEN emp_cursor%NOTFOUND;
    if emp_record.deptno=30 THEN
    UPDATE emp SET sal=sal+100 WHERE CURRENT OF emp_cursor;
    END IF;
    dbms_output.put_line(emp_record.sal);
    END LOOP;
    CLOSE emp_cursor;
    END;
    
    DECLARE
    CURSOR emp_cursor IS
    select name,sal,emp.deptno,name FROM emp,dept WHERE emp.deptno=dept.deptno FOR UPDATE of dept.dname;    --该例中明确的是dept表的dname列，说明下列语句中通过游标更新或修改的表为dept表，如果下列语句中更新的是emp表，则报错。
    
    emp_record emp_cursor%ROWTYPE;
    BEGIN IF NOT emp_cursor%ISOPEN THEN OPEN emp_cursor;
    END IF;
    LOOP
    FETCH emp_cursor INTO emp_record;
    EXIT WHEN emp_cursor%NOTFOUND;
    if emp_record.deptno=30 THEN
    UPDATE emp SET sal=sal+100 WHERE CURRENT OF emp_cursor;
    END IF;
    dbms_output.put_line(emp_record.sal);
    END LOOP;
    CLOSE emp_cursor;
    END;

– 示例. 循环游标

–显示雇员表中所有雇员的姓名和薪水

DECLARE
    CURSOR emp_cursor IS
    select name,sal FROM emp;
    BEGIN FOR emp_record IN emp_cursor LOOP dbms_output.put_line('第'||emp_cursor%ROWCOUNT||'个雇员：'||emp_record.name|| emp_record.sal);
    END LOOP;
    END;

示例. REF游标  
–显示雇员表中20号部门的雇员信息和部门表中部分部门信息

DECLARE
       --定义一个游标数据类型
       TYPE emp_cursor_type  IS REF CURSOR;
       --声明一个游标变量
       c1  EMP_CURSOR_TYPE;
       --声明两个记录变量
       v_emp_record emp%ROWTYPE;
       v_dept_record dept%ROWTYPE;
    BEGIN
       OPEN c1 FOR SELECT * FROM emp WHERE deptno= 20;
       LOOP
          FETCH  c1  INTO  v_emp_record;
          EXIT  WHEN  c1%NOTFOUND;
          DBMS_OUTPUT.PUT_LINE(v_emp_record.name||'的工资是'||v_emp_record.sal);
       END LOOP;
    --end;
       --将同一个游标变量对应到另一个SELECT语句
       OPEN c1 FOR SELECT * FROM dept WHERE deptno IN(10,20);
       LOOP
          FETCH c1 INTO v_dept_record;
          EXIT WHEN c1%NOTFOUND;
          DBMS_OUTPUT.PUT_LINE(v_dept_record.deptno||'表示'
                                ||v_dept_record.deptname);
       END LOOP;
       CLOSE c1;
    END;

Oracle %rowtype与%type  
%rowtype  
表示该类型为行数据类型，存储的是一行数据，一行数据里可以有多列，类似于表里的一行数据，也可以是游标里的一行数据，  
如：vs\_row1 表%rowtype;  
vs\_row2 游标%rowtype;

%type  
例如  
vempno emp.empno%type;  
例如上面的这句话，你的vempno就是你定义的变量，和面的那个emp是你数据库里面存在的表，他的表里面有意个empno字段，然后%type就是empno的数据类型，总体说这句话就是，让vempno与你数据库里面的表字段的属性匹配，这个你就可以很方便的对emp进行操作了，不会因为类型不匹配而报莫名的错误。

[https_blog.csdn.net_qq877507054_article_details_52886329]: https://blog.csdn.net/qq877507054/article/details/52886329
[http_blog.sina.com.cn_s_blog_95cfa64601011v0z.html]: http://blog.sina.com.cn/s/blog_95cfa64601011v0z.html