Oracle Cursors语法总结

关于Oracle Cursors的语法总结，Oracle Cursors是用来查询数据库，获取记录集合（结果集）的指针

，可以让开发者一次访问一行结果集，在每条结果集上作操作。

一．Oracle的Cursors概念：

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

二．Oracle的Cursors分类：

1．静态游标：分为显式游标和隐式游标。

2．REF游标：是一种引用类型，类似于指针。

三．Oracle的Cursors详细内容：

1．显式游标：CURSOR游标名(参数) [返回值类型] IS  Select语句
生命周期：
a.打开游标(OPEN):解析，绑定。。。不会从数据库检索数据
b.从游标中获取记录(FETCH INTO):执行查询，返回结果集。通常定义局域变量作为从游标获取数据的缓冲区。
c.关闭游标(CLOSE):完成游标处理，用户不能从游标中获取行。还可以重新打开。
选项：参数和返回类型

set serveroutput on
declare
cursor emp_cur ( p_deptid in number) is
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);
end loop;
close emp_cur;
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;
/

2．隐式游标：

不用明确建立游标变量，分两种：
a.在PL/SQL中使用DML语言，使用ORACLE提供的名为SQL的隐示游标
b.CURSOR FOR LOOP，用于for loop语句
a.举例：

declare
begin
update departments set department_name=department_name;
--where 1=2;

dbms_output.put_line('update '|| sql%rowcount ||' records');
end;

b.举例：

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

c.举例：
单独select

declare
l_empno emp.EMPLOYEE_ID%type;
-- l_ename emp.ename%type;
begin
select EMPLOYEE_ID  
  into l_empno
from emp;
--where rownum =1;
dbms_output.put_line(l_empno);
end;

使用INTO获取值，只能返回一行。

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

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;
  Elsif emps%notfound then
   Exit;  ---exit loop, not IF
  End if;
End loop;

If emps%isopen then
  Close emps;
End if;
End;

显式和隐式游标的区别：

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

保存从游标中获取的数据。

3．REF CURSOR游标：

动态游标，在运行的时候才能确定游标使用的查询。分类：

1．强类型（限制）REF CURSOR，规定返回类型

2．弱类型（非限制）REF CURSOR，不规定返回类型，可以获取任何结果集。

TYPE ref_cursor_name IS REF CURSOR [RETURN return_type]

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

 

 

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

 

一、Cursor的分类

1．静态游标：分为显式游标和隐式游标。

2．REF游标：是一种引用类型，类似于指针。

二、各类Cursor举例

 

 

---- 静态游标 - 显式游标

set serveroutput on

declare

  cursor emp_sor(emp_deptno in number ) is

    select * from emp where deptno=emp_deptno ;

  emp_i emp% rowtype ;

begin

  dbms_output.put_line( 'Getting emp from deptno 10' );

  open emp_sor( 10 );

  loop

    fetch emp_sor into emp_i;

    exit when emp_sor% notfound ;

    dbms_output.put_line( 'Employee id ' ||emp_i.empno|| ' is:' );

    dbms_output.put_line(emp_i.ename);

  end loop ;

  close emp_sor;

end ;

/

 

 

---- 静态游标 - 隐式游标 -1.DML

begin

  update emp set ename=ename ; --where 1=2;

 dbms_output.put_line( 'update ' || sql % rowcount || ' records' );

end ;

/

 

---- 静态游标 - 隐式游标 -2.loop for

begin

  for r_sor in ( select empno,ename from emp)

  loop

   dbms_output.put_line(r_sor.empno || ' : ' || r_sor.ename);

  end loop ;

end ;

/

 

---- 静态游标 - 隐式游标 -3.select into

declare

  v  varchar2 ( 20 );

begin

  select ename into v from emp

  where rownum = 1 ;

  dbms_output.put_line(v);

  dbms_output.put_line( sql % rowcount );

end ;

/

 

 

 

---- 动态游标 - 弱类型

Declare

  type rc 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' ;-- ref cursor with dynamic sql

 

  elsif (to_char( sysdate , 'dd' ) = 29 ) then

     open l_cursor for select * from dept;-- ref cursor with static sql

  else

     open l_cursor for select * from dual;-- with ref cursor with static sql

  end if ;

  open c;-- the "normal" static cursor

end ;

/

 

 

 

 

 

---- 动态游标 - 强类型

declare

  type emp_job is record (empno number ,

                         ename varchar2 ( 20 ),

                         job   varchar2 ( 30 )

                         );

  type emp_refcur is ref cursor return emp_job; -- 声明 REF CURSOR

  emp_sor emp_refcur;

  emp_i   emp_job;

begin

  open emp_sor for

    select empno,ename,job from emp where rownum < 10 order by 1 ;

  loop

    fetch emp_sor into emp_i;

  exit when emp_sor% notfound ;

    dbms_output.put_line(emp_i.ename || '''s job is :' );

    dbms_output.put_line(emp_i.job);

  end loop ;

  close emp_sor;

end ;

/

 

 

 

 

 

 

普通cursor与REF cursor的区别：

1）静态cursor不能返回到客户端，只有PL/SQL才能利用它。ref cursor能够被返回到客户端，这就是从Oracle的存储过程返回结果集的方式。

2）静态cursor可以是全局的，而ref cursor则不是。

3）ref cursor可以从子例程传递到子例程，而cursor则不能。为了共享静态cursor，必须在包说明或包体中把它定义为全局cursor。

   因为使用全局变量通常不是一种很好的编码习惯，因此可以用ref cursor来共享PL/SQL中的cursor，无需混合使用全局变量。

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

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

 

 

 

 

 

---- 动态游标 -sys_refcursor

DECLARE

  TYPE mytable IS TABLE OF emp% ROWTYPE ;

  l_data mytable;

  l_refc sys_refcursor ;

BEGIN

  OPEN l_refc FOR

  SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno FROM emp;

  FETCH l_refc BULK COLLECT INTO l_data;

  CLOSE l_refc;

  FOR i IN 1 .. l_data.COUNT

  LOOP

    DBMS_OUTPUT.put_line ( l_data(i).ename || ' was hired since ' || l_data (i).hiredate );

  END LOOP ;

END ;

 

 

 

 

 

 

非强类型的Ref cursor 和sys_refcursor的区别：

A REF CURSOR that does not specify the return type such as SYS_REFCURSOR. Meaning the SYS_REFCURSOR can be opened for a dynamic SQL query, where as simple REF CURSOR can not be opened for a query dynamically built at execution time.

 

 

 

 

 

 

三、游标属性

 

 

/*************************************************************

游标属性：

%FOUND ：变量最后从游标中获取记录的时候，在结果集中找到了记录。

%NOTFOUND ：变量最后从游标中获取记录的时候，在结果集中没有找到记录。

%ROWCOUNT ：当前时刻已经从游标中获取的记录数量。

%ISOPEN ：是否打开。

**************************************************************/

 

---- 静态游标 - 游标属性

Declare

  Cursor emp_sor is

  Select * from emp where rownum< 6 order by 1 ;

  emp_i emp% rowtype ;

  num number := 1 ;

Begin

  Open emp_sor;

  Fetch emp_sor into emp_i;

  Loop

    If emp_sor% found then

      Dbms_output.put_line( 'Looping over record ' ||num|| ' of ' || emp_sor% rowcount );

      Fetch emp_sor into emp_i;

       num := num + 1 ;

    Elsif emp_sor% notfound then

      Exit ;  ---exit loop, not IF

    End if ;

  End loop ;

 

  If emp_sor% isopen then

    Close emp_sor;

  End if ;

End ;

/