oralce游标案例

oralce游标案例

要求把价格高于1000的产地为杭州和中国的家电和电子产品放到表productinf_tmp，并转换商品类型编号为商品类型，商品价格高于2000下调5%

• 商品表sql

    CREATE TABLE "SCOTT"."PRODUCTINFO" 
   (    
    "PRODUCTID" VARCHAR2(10), 
    "PRODUCTNAME" VARCHAR2(20), 
    "PRODUCTPRICE" NUMBER(8,2), 
    "QUANTITY" NUMBER(10,0), 
    "CATEGORY" VARCHAR2(10), 
    "DESPERATION" VARCHAR2(1000), 
    "ORIGIN" VARCHAR2(10), 
     PRIMARY KEY ("PRODUCTID")
insert into PRODUCTINFO (productid, productname, productprice,  quantity, category, desperation, origin)
values ('1', '天堂伞', 59, 50, '1', null, '杭州');
insert into PRODUCTINFO (productid, productname, productprice, quantity, category, desperation, origin)
values ('2', '夏普LCD-48G100A', 7000, 20, '3', null, '日本');
insert into PRODUCTINFO (productid, productname, productprice, quantity, category, desperation, origin)
values ('3', '海尔XQB50-918A', 1100, 29, '3', null, '中国');
insert into PRODUCTINFO (productid, productname, productprice, quantity, category, desperation, origin)
values ('4', '三星XQB50-918A', 3600, 12, '3', null, '中国');
insert into PRODUCTINFO (productid, productname, productprice, quantity, category, desperation, origin)
values ('5', '华为XQB50-918A', 2500, 111, '3', null, '中国');
insert into PRODUCTINFO (productid, productname, productprice, quantity, category, desperation, origin)
values ('6', '台电QB50-918A', 400, 129, '4', null, '中国');
insert into PRODUCTINFO (productid, productname, productprice, quantity, category, desperation, origin)
values ('7', '恩克斯-918A', 2800, 22, '2', null, '中国');
commit;

• 类型表sql

CREATE TABLE "SCOTT"."CATEGORYINFO" 
   (    
     "CATEGORYID" VARCHAR2(10), 
     "CATEGORYNAME" VARCHAR2(30), 
     PRIMARY KEY ("CATEGORYID")
     insert into CATEGORYINFO (categoryid, categoryname)
        values ('1', '雨具');
    insert into CATEGORYINFO (categoryid, categoryname)
        values ('2', '路由器');
    insert into CATEGORYINFO (categoryid, categoryname)
        values ('3', '电视');
    insert into CATEGORYINFO (categoryid, categoryname)
        values ('4', '洗衣机');
    insert into CATEGORYINFO (categoryid, categoryname)
        values ('5', 'MP3');
    insert into CATEGORYINFO (categoryid, categoryname)
        values ('6', '鞋');
    commit;

• 游标代码

declare
  cur_categoryid categoryinfo.categoryid%type;
  cur_categoryname categoryinfo.categoryname%type;
  cur_prodrcd productinfo%rowtype;
  tmpnum number(8,0);

  cursor cur_prdt_catg is
         select * from productinfo where productprice>1000 and origin in('中国','杭州') and category in 
                (select categoryid from categoryinfo where categoryname in('路由器','电视','洗衣机','MP3'));
  cursor cur_catg is
         select categoryid,categoryname from categoryinfo where categoryname in('路由器','电视','洗衣机','MP3');
  begin
    --把符合要求的数据放进productinfo_tmp
    open cur_prdt_catg;
    loop
         fetch cur_prdt_catg into cur_prodrcd;
         if cur_prdt_catg%found then
            insert into productinfo_tmp(productid,productname,productprice,quantity,category,desperation,origin)
            values(cur_prodrcd.productid,cur_prodrcd.productname,cur_prodrcd.productprice,cur_prodrcd.quantity,cur_prodrcd.category,cur_prodrcd.desperation,cur_prodrcd.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 cur_categoryid,cur_categoryname;
      if cur_catg%found then
        update productinfo_tmp set productinfo_tmp.category=cur_categoryname where category=cur_categoryid;
        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 productprice=productprice*0.95 where productprice>2000;
    dbms_output.put_line('价格下调完毕!共下调'||sql%rowcount||'条商品');
    commit;
    end;
     --查询结果           
    select * from productinfo_tmp 

---

快捷键

• 加粗 Ctrl + B
• 斜体 Ctrl + I
• 引用 Ctrl + Q
• 插入链接 Ctrl + L
• 插入代码 Ctrl + K
• 插入图片 Ctrl + G
• 提升标题 Ctrl + H
• 有序列表 Ctrl + O
• 无序列表 Ctrl + U
• 横线 Ctrl + R
• 撤销 Ctrl + Z
• 重做 Ctrl + Y