1.游标的概念
使用游标(cursor)的一个主要的原因就是把集合操作转换成单个记录处理方式。用SQL语言从数据库中检索数据后,结果放在内存的一块区域中,且结果往往是一个含有多个记录的集合。游标机制允许用户在SQL server内逐行地访问这些记录,按照用户自己的意愿来显示和处理这些记录。
2.游标的用法
一般地,使用游标都遵循下列的常规步骤:
(1) 声明游标。把游标与T-SQL语句的结果集联系起来。
(2) 打开游标。
(3) 使用游标操作数据。
(4) 关闭游标。
3.经典案例
I要求:
利用游标转换两张表的数据。
首先,将满足以下三个条件的数据插入到一张新表(productinfo_tmp)中 :
<1>价格大于1000
<2>产地为“中国”或“杭州”
<3> 商品类型为“家电”或“电子产品”
然后,在新表(productinfo_tmp)中进行如下两个操作:
<1>价格大于2000的下调5%
<2>商品类型编号转换为商品类型名称
II 分析:
在获得包含数据的商品信息表和商品类型信息表两张表之后,在PL/SQL语句块中进行如下5步操作:
<1>创建新表(productinfo_tmp);
<2>利用SQL语句把符合要求的数据查询出来;
<3>把符合要求的数据插入新表(productinfo_tmp);
<4>在新表(productinfo_tmp)中把价格大于2000的下调5%
<5>在新表(productinfo_tmp)中把商品类型编号转换为商品类型名称
|||建原始表并插入数据
--创建原始表
create table categoryinfo
(
cid varchar2(10) primary key,
cname varchar2(20)
);
commit;
create table productinfo
(
pid varchar2(10) primary key,
pname varchar2(20),
price number(8,2),
quanty number(10),
category varchar2(10),
desperation varchar2(1000),
origin varchar2(20)
);
commit;
--插入原始数据
insert into categoryinfo(cid,cname) values('01','食品');
insert into categoryinfo(cid,cname) values('02','家电');
insert into categoryinfo(cid,cname) values('03','洗化');
insert into categoryinfo(cid,cname) values('04','电子产品');
insert into categoryinfo(cid,cname) values('05','办公用品');
insert into categoryinfo(cid,cname) values('06','玩具');
insert into categoryinfo(cid,cname) values('07','文具');
commit;
insert into productinfo(pid,pname,price,quanty,category,desperation,origin) values('001','纸巾',20,10000,'03','原生木浆','河北');
insert into productinfo(pid,pname,price,quanty,category,desperation,origin) values('002','笔记本电脑',5000,300,'04','坚如磐石,中国品质','中国');
insert into productinfo(pid,pname,price,quanty,category,desperation,origin) values('003','冰箱',7000,900,'02','每晚只用一度电','杭州');
insert into productinfo(pid,pname,price,quanty,category,desperation,origin) values('004','U盘',50,500,'04','随插随拔','中国');
insert into productinfo(pid,pname,price,quanty,category,desperation,origin) values('005','空调',4000,100,'02','让你四季如春','杭州');
commit;
--创建一个字段属性与productinfo表相同的空表
create table productinfo_tmp as select * from productinfo where 1=0;
||||在PL/SQL语句块中实现任务要求
declare
--定义变量,分别存放商品类别的编号和名称
v_cid categoryinfo.cid%type;
v_cname categoryinfo.cname%type;
--定义变量,存放商品信息的记录
v_prod productinfo%rowtype;
--定义临时变量
tmpnum number(8,0);
--定义游标
cursor cur_prdt_catg is
select * from productinfo where price>1000 and origin in('中国','杭州')
and category in
(select cid from categoryinfo where cname in('电子产品','家电')
);
cursor cur_catg is
select cid,cname from categoryinfo
where cname in ('电子产品','家电');
begin
--把符合要求是数据放进新表
open cur_prdt_catg; --打开游标
Loop
fetch cur_prdt_catg into v_prod;
if cur_prdt_catg%found then
insert into productinfo_tmp (pid,pname,price,quanty,category,desperation,origin)
values(v_prod.pid,v_prod.pname,v_prod.price,v_prod.quanty,v_prod.category,v_prod.desperation,v_prod.origin);
else
dbms_output.put_line('已取出所有符合条件的数据,共'||cur_prdt_catg%rowcount||'条');
exit;
end if;
end loop;
commit;
--转换产品类型
open cur_catg;
tmpnum:=0;
loop
fetch cur_catg into v_cid,v_cname;
if cur_catg%found then
update productinfo_tmp set productinfo_tmp.category=v_cname
where category=v_cid;
if sql%found then
tmpnum:=tmpnum+sql%rowcount;
end if;
else
dbms_output.put_line('产品类型转换完毕,共转换'||tmpnum||'条');
exit;
end if;
end loop;
--产品价格下调
update productinfo_tmp set productinfo_tmp.price=productinfo_tmp.price*0.95
where productinfo_tmp.price>2000;
dbms_output.put_line('产品价格更改完毕,共更改'||sql%rowcount||'条');
commit;
end;
执行效果图:
代码图:
此解决方案用到了子查询与游标,将会造成更大的系统开销,造成查询速度变慢,其他解决方案,请参照《Oracle数据库游标案例讲解与源码 (2)》一文。
http://blog.csdn.net/sinat_26342009/article/details/45223161