游标分为静态游标和动态游标两大类。静态游标又可以分为显式游标和隐式游标,动态游标又可以分为强类型动态游标和弱类型动态游标。
静态游标之显式游标:
显式游标的使用顺序可以分为声明游标、打开游标、读取游标和关闭游标4个步骤。例(结合loop循环):
declare
cursor cus_loop_cur is --声明游标
select customer_id,cust_first_name from customers
where city = 'Philadelphia' order by customer_id;
cus cus_loop_cur%rowtype;
begin
if not cus_loop_cur%isopen then
open cus_loop_cur; --打开游标
end if;
fetch cus_loop_cur into cus; --读取第一行数据
while cus_loop_cur%found loop --循环读取游标
dbms_output.put_line('编号为:'||cus.customer_id||'姓名'||cus.cust_first_name);
fetch cus_loop_cur into cus;
end loop;
dbms_otput.put_line('顾客总数:'||cus_loop_cur%rowcount);--用fetch命令获取的总行数
close cus_loop_cur; --关闭游标
end;
bulk collect批量提取数据:
使用fetch into语句提取数据是单条提取,在数据量很大的情况下执行效率不是很理想。此时引用fetch bulk collect into语句可以批量提取数据,在数据量大的情况下它的执行效率比单条提取高。例:
declare
cursor cus_loop_cur is
select * from customers where city = 'Philadelphia' order by customer_id;
type cus_tab is table of customers%rowtype;
cus_cur cus_tab;
begin
if not cus_loop_cur%isopen then
open cus_loop_cur;
end if;
loop
fetch cus_loop_cur bulk collect into cus_cur limit 3; --批量提取3行数据
for i in 1..cus_cur.count loop
dbms_output.put_line('编号:'||cus_cur(i).customer_id||'姓名:'||
cus_cur(i).cus_first_name);
end loop;
exit when cus_loop_cur%notfound;
end loop;
dbms_output.put_line('顾客总数:'||cus_loop_cur%rowcount); --用fetch命令获取的总行数
close cus_loop_cur; --关闭游标
end;
for loop语句:
不需要按照通常使用显式游标的4个步骤,简单方便。例:
declare
cursor cus_loop_cur is
select customer_id,cust_first_name from customers where ciy = 'Philadelphia';
cus_count integer:=0;
cus cus_loop_cur%rowtype;
begin
for cus in cus_loop_cur loop
dbms_output.put_line('编号:'||cus.customer_id||'姓名:'||cus.cust_first_name);
cus_coun:=cus_count+1;
end loop;
dbms_output.put_line('顾客总数:'||cus_coun);--获取的总行数
end;
为游标传递参数,例(修改前面的例子):
declare
city_temp customers.city%type := 'Philadelphia'; --声明变量并赋值
cursor cus_loop_cur(cus_city) is --传递参数
select customer_id,cust_first_name from customers where ciy = cus_city;
cus_count integer:=0;
cus cus_loop_cur%rowtype;
begin
for cus in cus_loop_cur(city_temp) loop
dbms_output.put_line('编号:'||cus.customer_id||'姓名:'||cus.cust_first_name);
cus_coun:=cus_count+1;
end loop;
dbms_output.put_line('顾客总数:'||cus_coun);--获取的总行数
end;
显示游标的4个属性:
%isopen:判断游标是否打开;
%found:判断当前游标最近一次fetch是否取到数据;
%notfound:与%found相反;
%rowcount:判断字游标打开以来用fetch命令获取的行数 。
用游标修改(update)或删除(delete)数据,例:
declare
cursor cus_loop_cur is
select * from customers where customer_id = 983 for update;
begin
for r1 in cus_loop_cur loop
update customers set city = 'chongqing' where current of cus_loop_cur; --current of用法
end loop;
end;
静态游标之隐式游标:
如果在PL/SQL程序中使用select或DML语句进行操作,数据库服务器将自动打开一个隐式游标,用来存放该语句的执行结果。隐式游标始终存放最近一条语句的执行结果。
隐式游标DML操作,例:
begin
delete from customers where customer_id = 983;
if sql%found then --隐式游标默认名称'sql'
dbms_output.put_line('被删除的行数:'||sql%rowcount);
end if;
insert into customers(customer_id,cust_first_name,cust_last_name)
values(983,'Mason','HAHA');
if sql%found then
dbms_output.put_line('最近插入的行数:'||sql%rowcount);
end if;
rollback; --rollback回滚
end;
隐式游标select操作(要有into子句,结果只能是一行数据),例:
declare
id_temp customers.customer_id%type;
name_temp customers.cust_first_name%type;
begin
select customer_id,cust_first_name into id_temp,name_temp from customers
where customer_id = 230;
if sql%found then
dbms_output.put_line('编号:'||id_temp||'姓名:'||name_temp);
end if;
end;
隐式游标循环,例:
begin
for cus in(select customer_id,cust_first_name from customers where city='Philadelphia')
loop
dbms_output.put_line('编号:'||cus.customer_id||'姓名:'||cus.cust_first_name);
end loop;
end;
隐式游标的4个属性:
%isopen:判断游标是否打开;
%found:对于DML语句,该属性表明表中是否有数据值受影响。对于select语句,如果发生异常,其值为true;
%notfound:与%found相反;
%rowcount:对于DML语句,该属性值为受影响的数据行数。对于select语句,如果发生异常,其值为 1;
动态游标之强类型动态游标:
静态游标在编译时已经知道相关联的SQL语句,而动态游标可以在运行时与不同的语句关联,是动态的。动态游标是ref类型的变量,声明时应该使用ref cursor关键字。
强类型动态游标是指在游标使用之前,虽为指定游标的查询定义,但是游标的类型已经确定,它带有return语句。例:
declare
type categories_name is ref cursor --声明游标类型
return categories%rowtype;
c_count number;
categorie categories%rowtype;
cu_categorie categories_name; --声明游标
begin
select count(*) into c_count from categories where category_name='software1';
if c_count=0 then
open cu_categorie for select * from categories; --打开游标
else
open cu_categorie for select * from categories where category_name='software1';
end if;
fetch cu_categorie into categorie; --读取游标
while cu_categorie%found loop
dbms_output.put_line('编号:'||categories.category_id||',名称:'
||categorie.category_name||',描述:'||categorie.category_description);
fetch cu_categorie into categorie;
end loop;
end;
动态游标之弱类型动态游标:
弱类型动态游标没有return子句,主要在涉及到多张表时使用。用法和强类型动态游标差不多,故不再举例说明。