当前位置:   article > 正文

ORACLE 游标 原理及使用讲解_cursor返回值类型

cursor返回值类型

1.什么是游标?

 从表中检索出结果集,从中每次指向一条记录进行交互的机制。

     用来管理从数据源返回的数据的属性(结果集)。这些属性包括并发管理、在结果集中的位置、返回的行数,以及是否能够在结果集中向前和/或向后移动(可滚动性)。

     游标跟踪结果集中的位置,并允许对结果集逐行执行多个操作,在这个过程中可能返回至原始表,也可能不返回至原始表。

    换句话说,游标从概念上讲基于数据库的表返回结果集。

游动的光标(指针),可以指向一个结果集,通过游标的移动逐行提取每一行的记录  

  用来查询数据库,获取记录集合(结果集)的指针,可以让开发者一次访问一行结果集,在每条结果集上作操作。

用来存储多条查询数据的一种数据结构(结果集),它有一个指针,用来从上往下移动,从而达到遍历每条记录的作用。

 由于它指示结果集中的当前位置 ,就像计算机屏幕上的光标指示当前位置一样,“游标”由此得名。

2.游标有什么作用?

 ①指定结果集中特定行的位置。

 ②基于当前的结果集位置检索一行或连续的几行。

 ③在结果集的当前位置修改行中的数据。

 ④对其他用户所做的数据更改定义不同的敏感性级别。

 ⑤可以以编程的方式访问数据库。

程序语言是面向记录的,一组变量一次只能存放一个变量或者一条记录,无法直接接收数据库中的查询结果集引入游标就解决了这个问题

 

    

3.为什么避免使用游标?

 ①在创建游标时,最需要考虑的事情是,“是否有办法避免使用游标?”

  因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该改写;

  如果使用了游标,就要尽量避免在游标循环中再进行表连接的操作。

 

4.Oracle游标的类型?

 ①静态游标:结果集已经确实(静态定义)的游标。在执行前,明确知道sql语句游标 ,分为隐式和显示游标。

 

  1.  显示游标 

    用户显示声明的游标,即指定结果集。当查询返回结果超过一行时,就需要一个显式游标。用户自己写的sql语句,编译时能明确知道sql语句 

  2. 隐式游标 

    所有DML语句(增、删、改、查询单条记录)为隐式游标,该变量名不需要用户自己声明,它由系统帮我们定义,叫sql。使用时不需要声明隐式游标,它由系统定义 .

     隐式游标的使用:通过%rowcour判断是有使用了。 

     动态游标 (REF游标)

    在执行前不知道sql语句游标,执行时才知道sql语句的游标。 动态关联结果集的临时对象。

  3.  强类型游标

    规定返回类型

  4.  弱类型游标

    不规定返回类型,可以获取任何结果集。

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.怎样更新和删除显示游标中的记录?

 UPDATEDELETE语句中的WHERE CURRENT OF子串专门处理要执行UPDATEDELETE操作的表中取出的最近的数据

  要使用这个方法,在声明游标时必须使用FOR UPDATE子串,当对话使用FOR UPDATE子串打开一个游标时,

  所有返回集中的数据行都将处于行级(ROW-LEVEL)独占式锁定,其他对象只能查询这些数据行,

  不能进行UPDATEDELETESELECT...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游标 ?

 动态关联结果集的临时对象。即在运行的时候动态决定执行查询。

 

2REF 游标 有什么作用?

 实现在程序间传递结果集的功能,利用REF CURSOR也可以实现BULK SQL,从而提高SQL性能。

 

3,静态游标和REF 游标的区别是什么?

 ①静态游标是静态定义,REF 游标是动态关联;

 ②使用REF 游标需REF 游标变量。

 ③REF 游标能做为参数进行传递,而静态游标是不可能的。

 

4,什么是REF 游标变量?

 REF游标变量是一种 引用REF游标类型  的变量,指向动态关联的结果集。

 

5,怎么使用  REF游标 ?

 ①声明REF 游标类型,确定REF 游标类型;

  ⑴强类型REF游标:指定retrun typeREF 游标变量的类型必须和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;

 

 

原文地址:http://www.2cto.com/database/201203/122387.html

 

 

 

 

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隐式游标

不用明确建立游标变量,分两种:

1PL/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; 

2CURSOR 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%FOUNDSQL%NOTFOUND的值都是NULL,在执行DML语句后,SQL%FOUND的属性值将是: 
. TRUE :INSERT 
. TRUE :DELETEUPDATE,至少有一行DELETEUPDATE. 
. TRUE :SELECT INTO至少返回一行 

SQL%FOUNDTRUE,SQL%NOTFOUNDFALSE. 

  
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; 

/ 

 


 

显式和隐式游标的区别: 

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


 


2REF 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 INTIS 

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


 


 


 

3cursor  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

 

 

 

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

   ____________________________________________________

   ———————————————————————————

   编号   姓名    职务  领导   入职           工资  奖金  

  ————————————————————————————

  7369    aaa      aaa   1  1987-4-5日    3000  500

  ————————————————————————————

   编号   姓名    职务  领导   入职           工资  奖金  

  ————————————————————————————

  7369    aaa      aaa   1  1987-4-5日    3000  500

  

  

 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个变量来存放enamesal的内容

    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进行操作了,不会因为类型不匹配而报莫名的错误。

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/羊村懒王/article/detail/619390
推荐阅读
相关标签
  

闽ICP备14008679号