一、什么是游标?
PL/SQL是用游标来管理SQL的SELECT语句的,他提供了对select查询的结果集进行逐行处理的能力。可以看作是一种特殊的指针,它与某个查询结果集相关,可以指向结果集的任意位置,以便对指定位置的结果集进行处理,使用它可以在查询数据的同时对数据进行处理。
二、游标的分类?
(1)、静态游标:显式游标和隐式游标。
1、隐式游标:它是相对于显式游标定义的,也就是说没有明确的cursor的declare。在PL/SQL中,所有的DML操作都会被oracle内部解析为一个名为SQL的隐式游标。因为不需要OPEN,FETCH,CLOSE 等操作,所以我们从表面看不到。
注意: 隐式游标中必须要有select cur_name into [ 变量名或其他数据类型]. 此句完成OPEN,FETCH,CLOSE 操作。
隐式游标只能返回一行记录, 如果无符合条件的记录将会出现NO_DATA_FOUND 异常. 如果出现多条记录将出现TOO_MANY_ROWS 异常。
隐式游标只能用SQL% 判断其游标属性。
对于任何位置使用SQL%ISOPEN 结果都是FALSE, 隐式游标由PL/SQL 管理。
作用:可以通过隐式Cusor的属性来了解操作的状态和结果,从而达到流程的控制。Cursor的属性包含:
SQL%ROWCOUNT 整型 代表DML语句成功执行的数据行数
SQL%FOUND 布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND 布尔型 与SQL%FOUND属性返回值相反
SQL%ISOPEN 布尔型 DML执行过程中为真,结束后为假
--隐式游标1
declare
emp_record emp%rowtype;
begin
select * into emp_record from emp e where e.emp_id=1;
dbms_output.put_line(emp_record.emp_name);
end;
--隐式游标2
begin
for rec in (select * from emp e)
loop
dbms_output.put_line(rec.emp_name||'--'||rec.emp_address);
end loop;
end;
--隐式游标3
begin
update emp e set e.emp_name='zxf_noimp' where e.emp_id=1;
dbms_output.put_line(SQL%rowcount);
end;
2、显式游标:显示游标是相对于隐式游标的,就是有一个明确的cursor的declare。游标从declare、open、fetch、close是一个完整的生命旅程。显式游标的声明如下:
cursor cursor_name(paramlist list) is select .....
(1)、 对于从数据库中提取多行数据,就需要使用显式Cursor。显式Cursor的属性包含:
游标的属性 返回值类型 意 义
%ROWCOUNT 整型 获得FETCH语句返回的数据行数
%FOUND 布尔型 最近的FETCH语句返回一行数据则为真,否则为假
%NOTFOUND 布尔型 与%FOUND属性返回值相反
%ISOPEN 布尔型 游标已经打开时值为真,否则为假
(2)、 对于显式游标的运用分为四个步骤:
定义游标---Cursor [Cursor Name] IS;
打开游标---Open [Cursor Name];
操作数据---Fetch [Cursor name]
关闭游标---Close [Cursor Name],这个Step绝对不可以遗漏。
(3)、以下是三种常见显式Cursor用法:
--显式游标1
declare
--定义游标
cursor emp_cur is select * from emp e where e.emp_name like '%zxf_noimp%';
--定义游标变量
emp_record emp%rowtype;
begin
open emp_cur;
loop
fetch emp_cur into emp_record;
dbms_output.put_line(emp_record.emp_name||emp_record.emp_address);
exit when emp_cur%notfound;
end loop;
close emp_cur;
end;
--显式游标2
declare
--定义游标
cursor emp_cur is select e.emp_id,e.emp_name,e.emp_address from emp e where e.emp_name like '%zxf_noimp%';
v_emp_id emp.emp_id%type;
v_emp_name emp.emp_name%type;
v_emp_address emp.emp_address%type;
begin
open emp_cur;
loop
fetch emp_cur into v_emp_id ,v_emp_name,v_emp_address;
dbms_output.put_line(v_emp_name||v_emp_address);
exit when emp_cur%notfound;
end loop;
close emp_cur;
end;
--显式游标3
declare
--定义游标
cursor emp_cur is select e.emp_id,e.emp_name,e.emp_address from emp e where e.emp_name like '%zxf_noimp%';
begin
for emp_cur_t in emp_cur loop
dbms_output.put_line(emp_cur_t.emp_name);
end loop;
end;
(2)、REF游标:与隐式cursor和显式cursor的区别,ref cursor可以通过在运行期间传递参数来动态的获取结果集。而另外两种是静态的,也就是说在编译期间就决定了数据结果集。
1、强类型的REF游标:规定了结果集返回类型;
2、弱类型的REF游标:没有规定结果集返回的类型;
--REF游标
declare
--弱类型:没有指定返回结果集的类型
type emp_cur_type is ref cursor return emp%rowtype;
emp_cur emp_cur_type;
emp_record emp%rowtype;
begin
open emp_cur for select * from emp e where e.emp_name like '%zxf_noimp%';
loop
fetch emp_cur into emp_record;
dbms_output.put_line(emp_record.emp_name);
exit when emp_cur%notfound;
end loop;
close emp_cur;
end;
declare
--强类型:指定返回结果集的类型
--声明记录类型
type emp_cur_rec is record(
emp_id number,
emp_name varchar2(50)
);
--声明ref cursor,返回值为该记录类型
type emp_cur_type is ref cursor return emp_cur_rec;
--声明该ref cursor的游标变量
emp_cur emp_cur_type;
--定义该记录类型变量
emp_record emp_cur_rec;
begin
open emp_cur for select e.emp_id,e.emp_name from emp e where e.emp_name like '%zxf_noimp%';
loop
fetch emp_cur into emp_record;
dbms_output.put_line(emp_record.emp_name);
exit when emp_cur%notfound;
end loop;
close emp_cur;
end;