Oracle数据库游标案例讲解与源码，oracle游标

和通数据库htsjk.Com2019-03-08 03:54 来源:未知阅读:12553 评论 463 热度2

标签：数据库 oracle sql cursor 游标

Oracle数据库游标案例讲解与源码，oracle游标

1.游标的概念

游标（cursor）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字,用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条SQL 查询语句相关联因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。

使用游标(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;

执行效果图：