实验LSQL程序设计

1、 实验 6 PL/SQL 程序设计 1实验目的 (1) 掌握 PL/SQL 程序开发方法。 (2) 掌握存储过程、函数、触发器、包的创建于调用。 2实验要求 (1) 根据图书销售系统业务要求创建特定的存储过程、函数、触发器。 (2) 根据图书销售系统业务要求将图书销售系统相关的函数、存储过程封装到包里。 3实验步骤 以 bs 用户登录 BOOKSALE 数据库，禾 U 用 PL/SQL 程序编写下列功能模块。 SQL Plus SOL+Tliis: Release 11.2.0. 1.0 Pr o Auc fc i cm on. 星期二涓 9 14:07:20 2017 Copyrigkt (

2、d) I船丈$ SfllO； Ora.de. All ri xeservel. 请输入用户宕 create or replace procedure proc_category_static 2 as 定义游标，获取当前有哪些图书种类 4 cursor c_all_category is select dist inct category from books; 5 - 图书的平均价格 6 v_avg_cost nu mber; 7 begi n 8 - 保存图书种类 9 for v_each_category in c_all_category LOOP 10 select avg（reta

3、il） into v_avg_cost from books where category=v_each_category.category group by category; 11 dbms_output.put_line（ 种类为：|v_each_category.category， 平均价格 为：| v_avg_cost）; 12 END LOOP; 13 end proc_category_static; 14 / or replace prjcBinre figc_cfitegor7_5tatic 法頰肺Bl当前有哪坐书楼 cursor c all cit HSflBWHS Tgc

4、cst mahy; 血n -s朋緩 :or veadi-Catsory :n o_ til category UDO? i*hct 轨（r*Wil i intf v sv cost r 旅k mktrs catsirk nt ;cry. citipry 护号 ty c就邨ry; dliii_mitput put丄id.种物：| |7_iich_ctt（ry. cattgo（r/| |平坯怫为：11 E_c&it）. HI I1EF; prflc_ create car replace prQS7elure procgetordrinfo( p_ar dler.id order item

5、. order_i dtype) 声明游标存储可单节的站射 cursor ISBJS is selec七 ISEK from orAeoritern wkere order .iQpjrM.锐; v ISBK order： t&m. ISBlT%type : 二声瞬标存储灯单倩息 _ cnrsor e_oriirittffl is select I5BH? sum (quantity) total num from ordr itw 9 v_tiTle books, titintype： 10 v_rttiil LookE r*t*il#typ*; 11 lepn 12 op an c

6、_ISBH; 13 LOOP - 14 fetch G ISIN into v ISBF; 15 删it when OSBNWJTFOW; 16 or v oarjer i tem XIL G or Jen tein LOOT 17 select title, retail into v_titleJ v_reta.il from book where ISBIv _ord.eri.teri. ISBH; 18 DENS_01fTFirr. Firr_UirE( (p_iE,AeT_i 4 | |v_titl |v_kFliri t 19 MIA LOOP： 20 end LOOP. 21 e

7、lflEft fl_ISEN： 22 pr f o_ge t_cr d er i. it o o; 23 / 过程已创崖。 se t server output oil de clar e r_oritm number: Legin r_ order item : M&E; pr oc_gtt_ord&r info (v_ord&r ); nd： 咒的值；1 4: T.g deri temi : ; 4: Jeri 七名血：=1: _ge t_r der i nf c (V.OT der); (4)创建一个存储过程，以出版社名为参数，输出该出版社出版的所有图书的名称、

8、ISBN、 批发价格、零售价格信息。 am 4 5 6 7 a J create or replace procedure proc get name( p_title books.title%type) as cursor c_orderid is select order_id from orders customer_id=p_customer_id; v_orderid orders.order_id%type; cursor c_orderitem is select ISBN, sum(quantity) totalnum from orderitem where order_id

9、=v_orderid group by ISBN; v_title books.title%type; begin open c_orderid; LOOP fetch c_orderid into v_orderid; exit when c_orderid%NOTFOUND; for v_orderitem in c_orderitem LOOP select title into v_title from books where ISBN=v_orderitem.ISBN; DBMS_OUTPUT.PUT_LINE(p_customer_id|v_title| 的 数 |v_orderi

10、tem.totalnum); end LOOP; end LOOP; close c_orderid; end proc_get_orderinfo; / set serveroutput on declare v_customer number; beginwhere 量是 v_customer :=&x; proc_get_orderi nfo( v_customer); en d; er* st* ar - pr*af tjiM*i I-1 R h r UE JBI: i fr 1 . M , Kitty FUT” F1.lTMt p_5t*ier._L E 、12 K rms是

11、 IIFX知 t otilri 口) 电 fcd MBF: uriJ Uja clo-M4 r_ar JBE* I E. MT.-弓sTvernitput Mi WL- 2 U USE ttaip Tmih-r 3丘n 攀三旳弓t*er p； 5 pTLfl.fiit.Ly dMfiaf&CT_flyj：tijr if I r / 1A片的IE 2 邇 4 v=?act ar 可 HE 4. eEtEQtf .- . tLILL 创建一个存储过程，输出每个客户订购的图书的数量、价格总额。 create or replace procedure proc_category_static

12、as cursor c_all_category is select dist inct category from books; v_sum_cost nu mber; begin for v_each_category in c_all_category LOOP select sum(retail) into v_sum_cost from books where category=v_each_category.category group by category; dbms_output.put_line( 种类为：|v_each_category.category. v_sum_c

13、ost);总价格为：| dbms_output.put_li ne( 种类为：|v_each_category.category. 数量为：| END LOOP; end proc_category_static; / set serveroutput on exec proc_category_static; / EH YWijdjn 甲口打 In L_aiLl_.iiii|.RjT LsXP ifinln-isil11 inin T- 晋 -冲 frm口峦丁 wti*T 记丈i rt尹：rr TSWIITV E pulJllbf 轉金捣 r Ikjfiwt扎:密审，活応世rll 耐( (S

14、为 i II c*S*. D LCEP . p OffTHEE Ml FlIpFi fir i 蓦口當力J It RIM ill m LuP ul (8) 仓咙一个存储过程，输出各类图书中销售数量最多的图书的信息及销售的数量。 O H 會34口0 Cn.JQX %! 5 b FEflr 0 dl Piytciffy is ;aliE 北三日E3L； ；QD 亡 FMd 上右 mr 山 尹0气严D書.or罠Mtiti* 宣0基的iE： R|E 4 2 酹1”幻町 Fhip J v : iistoriiiaE 吨; UL 严：a；T rv st : s-sl sc mr a cl put oo t

15、re r jii1 r 1M-T . b；jn r_cul cn-r prc K：t strOSTLHEO右r_ ：Tis fegu=get_book_avgcost(v_book_category); begin for v_books in c_books loop dbms_output.put_line(v_books.ISBN| |v_books.title| |v_books.author| |v_books.pubdate| |v_books.publisher_id| |v_books.retail); end loop; end; end; / set serveroutpu

16、t on declare p_book_category BOOKS.category%type; avgcost number; begin p_book_category:= 管理 ; avgcost:=pkg_book.get_book_avgcost(p_book_category); pkg__showbook( 管理 ); end;end; fim?tLiLii 乎 上三占D otyi厘耳m nlfc虫t 仍DOES 或mt话爲ccr KiByppJ i rit if n ：iimlMF _1503 KfiE E5Urt.|yp* tu ihOr ul i L E

17、4-vl*tt r Mtvi.I fr*i EODOC?丄聞.KFIT-i_I 0i cl XLIH bar 2) T !U_t is51. E a a i = g tni tihr. g.Wg；L5il -f5Ufw aa-ai,t BHE_OI1TFJT Fin_Lnn：(匸hJcjfiil:吃aryl I lluywgfg叮； a BEiiTh VBirrd K.I, 0 皿 (.raiikjre (iraih* wb *uk I: a BOriF 獗畠QI加k刃i -AE vJL UL l_efct-即巧 V aJf uL 泄置(心), cSB-Rtf r rtii ml R i s

18、a ct 就 和 5 乍血閑 vt*.ji|rft J * 1=(,*t_L口血.血皿各亡*福t CA1. *口丄口越已也孕丁$； ti figi EL 14i! v_,bMti I n t Id u- dbn Eautj-at |-qfH.LBA( (Y_LfrQkc IS| x 11wbAokc.-ti tlaj |H |TV1ICIoldE. nitLur | pqb整to | 卜.|u込g口blidkor.idl iind J DDpd ni and; - 4l djvfi-srcaitpLLt 此 ELXLLT | .b “k. gA ry E 0TS5 氏 at 襌ngr . 10

19、 then select cost into v_cost from books where ISBN = v_orderitem.ISBN; DBMS_OUTPUT.PUT_LINE(1 - |v_cost|:|v_orderitem.ISBN); elsif v_orderitem.quantitytyp e v_nuinti ty 席derit n. aaati ty*t：/pt: T_F10 thtu sei sc t less L xnt v v cost r ora booxsi wltei- e T3BE DBJEJTUTPUT. PUTJ-IKE 1 - | |章七 11# *

20、 11苗时打； *lsi d*ritfrii. 4uw.tL. ty10 then select cost into v_cost from books where ISBN = v_orderitem.ISBN; DBMS_OUTPUT.PUT_LINE(1 - |v_cost|v_orderitem.ISBN); elsif v_orderitem.quantity=10 then select retail into v_cost from books where ISBN = v_orderitem.ISBN; DBMS_OUTPUT.PUT_LINE(2 - |v_cost|v_or

21、deritem.ISBN); else DBMS_OUTPUT.PUT_LINE(number of book is error!); end if; v_sumcost:= v_sumcost+v_orderitem.quantity*v_cost; DBMS_OUTPUT.PUT_LINE(3*|v_sumcost); end LOOP; end LOOP; close c_orderid; return v_sumcost; end get_sumcost; / set serveroutput on declare v_totalMoney BOOKS.cost%type; v_cus

22、tomer number; begin v_customer :=&x; v_totalMoney:=get_sumcost(v_customer); dbms_output.put_line(v_customer| 的购买总额是 |v_totalMoney); end; IJSQLPILH is ifal.41 ui-l-di_L A frvti ixdu j RLJ W ZVIIHJ_ir_-rulL j r_L J T-wr-Terda arJtrT cir4*r_i w tuefjwLui -1 : S1( (II. i IZH. jii-urLCr f * -i iLfii

23、ti vlrt trdv.iiiR, _*r durj dl T E E srbr tBn TEWtifc* t_qi.iED-!：3 Iry JI KT i *h qv-Kct J lyMniv rLi；05iE DJAiLtr ti. Z). , 9f x E_raa EIFd, ftoh _-5r4 -jid 】iit _. iaei 4. Fit t w4-fcri30 iLia Telert 巾“t izitc FfX ta -：s vKcr* T53B - v_：id ritE. 153K ttWE_cuTrvr r-in_uBtL|卜胡|_盯.屛1： isu), L sl i*

24、Z； tid- L C 4k. .lULt. T/ -lD LJIH二 jr fe- V -4011 b*W |HS!4II|I3. RbeF? BH IBIffi LHF :mmT Tin LIHFT f - | P =ast | 肚 乂 二祢 TSBF ffWE OITTHT pinJLmrb4r of 血i. snoir1: 廿d W： f hla-TDR-t - r BUI -ni1： rd*r ； t 电口=in.tn r : k Hit; jryiFJi.fVma.护翻v ku it), eEd me； 栏曲Q与如沖 r*tv D i_yjikost. fenH /lib T|n

25、r r-| S AT S CJ_ D如15 2 _l!n- HDQEZ CDTtlifcjp . 1 V.fwk-iivlV UlftlkiJ . 4 4fi b 5 .w ruer 為 3 TF Em: BIJI =nar-“ r*t nr:w.t i,w_cni baw；! T .piUfs*!( ( _4iaLL41 r l| |(15) 创建一个函数，以订单号为参数，返回该订单订购图书的价格总额。 22 23 35 36 10 11 2 13 14 15 IS 18 19 20 阴 30 31 32 工 38 39 5 鰹告：创翟的帕魏帚有编译错 T_CQSt bocks, cost?

26、itype; v_ISER or d*r i t sir. ISMtyp e: _quantity rderitem, quntitj/Ktyp sat s rverQxitput QR vtotaJMoiiey BOOKS. costSJtypm w cui 七。!* num L er: cursor c_ordtriten is select ISEffj quantity TOB urdtrittm wh r ord r_id*rid; s t nuibk 吐陆夏)：m ： b*cin open e_or d*ri d; XQF fetch c_orderid into 一wdujHd.

27、; ri.t vh n c_c r deri d( HOT?OUWI: or v order L t em in c_or ler item I v_or le r i t an. quantity J10 select egt into Y_COS L _DBHS_OUTFUT. PUT_LJHEC 1 ;i v_ord*riterr. lUJTLti ty=lD lect “tfti 1 into v_coat IiBHS-OUTPUT FVT-口NE? 2 else DBMS OUTPUT. FUT_LJWEC number o Look is eriror end ； ; Y_sumc

28、*st:- v_sumcost-v_or der i teih ty*v_cost D魂丄UTFUT. PUT_UlfEC 3*：t*J | |y_sumcost): end LOOP： nd LOOF; close ?_orderid; ret-! viamcost; 唱nd get_sumc ost; (16) 仓 U 建一个函数，以出版社名为参数，返回该出版社出版的图书的平均价格。 create or replace function get_pub_avgcost( v_pub_ name publishers .n ame%type) retur n nu mber as v_pub

29、_id publishers.publisher_id%type; cursor c_books is select retail from books where publisher_id=v_pub_id; v_sumcost number(6,2):=0; v_count number(6) :=0; begin select publisher_id into v_pub_id from publishers where name=v_pub_name; for v_retail in c_books LOOP v_count:=v_count+1; v_sumcost:= v_sum

30、cost+v_retail.retail; DBMS_OUTPUT.PUT_LINE(v_count| -|v_sumcost); end LOOP; return v_sumcost; end get_pub_avgcost; / set serveroutput on declare v_avgMoney BOOKS.cost%type; v_pubname %type; begin v_pubname :=&x; v_avgMoney:=get_pub_avgcost(v_pubname); dbms_output.put_line(v_pubnam

31、e| 的出版图书的平均价格是 |v_avgMoney); cr*4t or rtpl unicti&n 常电tjuivgo&st ( vjub_iis p业blirh諾a n*宅漁typG return nunbr Y_puJb_id pufcliskers. publisher_i 矗七ype; ours or 0_hooks i s select retail from bo-oks wh&r publi sKer_i d= _pub_i d： v_sumcfl5t nxunber( (6 2) )； 0; slftct putllsher_id into v_pn

32、b_ll frci彌 publishes wkr$ n加电=v_pulb_rL&ffi* : fcr v_rttiil in 0_bofll _QL 2 s e t s er v er output on. declare v_avtoney BOOKS. cat%typ&： v_pubrtim* publishers- bagin vpubnajhe ：=tsx: v raav gjldiiiey: =get Dub_av 即 0 st 6 d.rBE_oiLtpiit. putT. i &e (T jjubnsa namftWtyp*; lubiiane); I |

33、T_avgMoney); |v suncost)； 10 v_cost books.cost%type; v_gift number(6,2):=0; begin open c_orderid; LOOP fetch c_orderid into v_orderid; exit when c_orderid%NOTFOUND; for v_orderitem in c_orderitem LOOP if v_orderitem.quantity 10 then select cost into v_cost from books where ISBN = v_orderitem.ISBN; D

34、BMS_OUTPUT.PUT_LINE(1- |v_cost|v_orderitem.ISBN); elsif v_orderitem.quantity=10 then select retail into v_cost from books where ISBN = v_orderitem.ISBN; DBMS_OUTPUT.PUT_LINE(2- |v_cost|v_orderitem.ISBN); else DBMS_OUTPUT.PUT_LINE(number of book is error!); end if; v_gift:= v_gift+v_orderitem.quantit

35、y*v_cost; DBMS_OUTPUT.PUT_LINE(3*|v_gift); end LOOP; end LOOP; close c_orderid; return v_gift; end get_gift; / set serveroutput on declare v_totalMoney BOOKS.cost%type; v_customer number; begin v_customer :=&x; v_totalMoney:=get_sumcost(v_customer); dbms_output.put_line(v_customer| 的礼物是 |v_total

36、Money); end; (18) 创建一个函数，以图书号为参数，统计该图书被订购的总数量。 create or replace function get_su mnum( v_customer_id customers.customer_id%type) retur n nu mber as cursor c_orderid is select order_id from orders where customer_id=v_customer_id; 8 ID 12 14 15 佰 5 6 ig 19 20 21 24 25 26 27 v_cO t Loots. Costtype; 囲数已

37、创建。 v_ISB!N Qr jeri t m. ISBiT*typ$: v_qu?ilt i ty or der i| qu旳11 tytyp H： 2 丰 :customer -Fbt; y 、u + fwn 叶专LOOP then ron boqjtz where ISBU = v_orderi ten. ISBW; * | | _CSt | |v_orrdurit where crAtr_it_wA.riA-. HL HL - 2 3 4 cursor c flrderi d is t ordsr_i d rwn ord峙TF oustomer_itomer_id: v_orderid

38、 orders. order_idfetype; _si ft unbar (fi, 2) ) begin pen c orderi d LOOP fth c_arderi10 selec?t Gowt illto v QQst ,DEMS_OUTPUT. PUT_HKEC 1 _ _ _ elsi _4Tieri tew. QUanti ty=lD thEll s ltct rtf&il into v coit from bocks *htrt ISBN v_orderitem. ISBH. DEMS_(JUTFUT. nJT_UHEti ty*v_cost. BMtS_OUTPUf

39、. | _i t)7 and LOOFE end LDOP; close corderi I; return T_gi t; enJ fet_gl f t; 丸七 server output on declare _tot41Mon*y BOOKS. cost%typ ： v=custo sr numb ar bein T_CUS t omer :我sc: T_to t alMoney 1 ge t_sum cost (v_cus tom er; dbm s_output. put_l i ne (.V_CUE t om er |的礼物是 | |v_totalM*ney). ereatg or

40、 replace function get=g;i t( Y_CUStomer_i d customers. customer_i diKtype) return numier &E v_orderid orders.order_id%type; cursor c_orderitem is select ISBN, quantity from orderitem where order_id=v_orderid; v_ISBN orderitem.ISBN%type; v_quantity orderitem.quantity%type; v_retail books.retail%t

41、ype; v_sumnum number(6,2):=0; begin open c_orderid; LOOP fetch c_orderid into v_orderid; exit when c_orderid%NOTFOUND; for v_orderitem in c_orderitem LOOP if v_orderitem.quantity 10 then select retail into v_retail from books where ISBN = v_orderitem.ISBN; DBMS_OUTPUT.PUT_LINE(1- |v_retail|v_orderit

42、em.ISBN); elsif v_orderitem.quantity=10 then select retail into v_retail from books where ISBN = v_orderitem.ISBN; DBMS_OUTPUT.PUT_LINE(2- |v_retail|v_orderitem.ISBN); else DBMS_OUTPUT.PUT_LINE(number of book is error!); end if; v_sumnum:= v_sumnum+v_orderitem.quantity*v_retail; DBMS_OUTPUT.PUT_LINE(3*|v_sumnum); end LOOP; end LOOP; close c_orderid; return v_sumnum; end get_sumnum; / set serveroutput on declare