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;

執行效果圖：

代碼圖：