关于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 ;
/