實驗6PLSQL程序設計

1、實驗6 PL/SQL程序設計 1實驗目的 (1) 掌握PL/SQL程序開發(fā)方法。 (2) 掌握存儲過程、函數(shù)、觸發(fā)器、包的創(chuàng)建于調用。 2實驗要求 (1) 根據(jù)圖書銷售系統(tǒng)業(yè)務要求創(chuàng)建特定的存儲過程、函數(shù)、觸發(fā)器。 (2) 根據(jù)圖書銷售系統(tǒng)業(yè)務要求將圖書銷售系統(tǒng)相關的函數(shù)、存儲過程封裝到包里。 3實驗步驟 以bs用戶登錄BOOKSALES數(shù)據(jù)庫，利用 PL/SQL程序編寫下列功能模塊。 廠 SQL Plus SqL+Tlus； Release 11 0 1.0 Prolucti ozi on 星期二 5月 9 14 ：D7一 2D 卻 17 Copyri gKt (c) 1392； MIO；

2、Oj-a.cle. All rl ts reserve!. 請輸入用戶名：bvliagin 電 Looksalts sysdba piraulw Q 電七筆11科 7itirfri Eli ti on11.2.0. 1.0 一t FroJue-tion With the FartitiQuinj, OLAT”Mining ajid Heal Application Testing ojticns e9L 一拼青豐： (1)創(chuàng)建一個存儲過程，輸出不同類型圖書的數(shù)量、平均價格。 SQL create or rep lace pro cedure pro c_category_static 2 3

3、4 as -定義游標，獲取當前有哪些圖書種類 cursor c_all_category is select disti net category from books; -圖書的平均價格 v_avg_cost nu mber; begin -保存圖書種類 for v_each_category in c_all_category LOOP select avg(retail) into v_avg_cost from books where 10 category=v_each_category.category group by category; 11dbms_output.put_li

4、ne(種類為：|v_each_category.category|,平均價格為: ll 精選文庫 31 v_avg_cost); 12 13 14 END LOOP; end pro c_category_static; / ? 1 11 L； B LI J訛tt ay replace price lure p a c_cttegor7_E： c IE 汽頰斷飆舗匍理耶 . c all citejorv 】3 咒hut distiaot ci；efcry froi books; 卡呼渤R T_uvjccst nunb町： for v_e6fh_cat5?y in c_al_ctteory UO

5、OF fdfct 掰式：WiH mte v sv_cflst fr3ncster_*icli citgcry. chriry frtiip ty cah鉀y; putjint 書鄴氐I|7_ia:K_：it*g(rv. r*性緲川 勒尬稻貴：| _wcDst1 t】IT LOCf, - 掘i pn(_c4tefory_stici:； 蝕二let savdrnitput xi EQL? iitc猷龍M7 mt応汀 IW： 32 (2)創(chuàng)建一個存儲過程，以客戶號為參數(shù)，輸出該客戶訂購的所有圖書的名稱與數(shù)量。 create or rep lace pro cedure pro c_get_order

6、i nfo( 2 3 p_customer_id customers.customer_id%t ype) as -聲明游標存儲客戶的訂單號 cursor c_orderid is select order_id from orders where customer_id=p_customer_id; v_orderid orders.order_id%t ype; -聲明游標存儲訂單信息 cursor c_orderitem is select ISBN, sum(qua ntity) total num from orderitem where order_id=v_orderid gro

7、up by ISBN; 9 10 11 -保存圖書的書名 v_title books.title%ty pe; 12 13 14 begin open c_orderid; LOOP 15 16 17 18 19 fetch c_orderid into v_orderid; exit whe n c_orderid%NOTFOUND; for v_orderitem in c_orderitem LOOP select title into v_title from books where ISBN=v_orderitem.lSBN; DBMS_OUTPUT.PUT_LINE(p_custo

8、mer_id訂購|v_title的數(shù)量是 |v_orderitem.tota Inu m); 20 21 22 23 24 end LOOP; end LOOP; close c_orderid; end pro c_get_orderi nfo; / exec pro c_get_orderi nfoo(1001); SQLPIjs nm F riTM-ti CJT r4?Lx4 prcci iort pr 5C_56 t_cr IftriE.*- ( IJ _ rJi I ub eL _J d J LES ( uh 日 Sftit Q廷 ozrirL iAWj7?JUil： Cop iaf

9、Ctr 1.1 Mr ub rMii Kp : mm Tflfl T A-_ -art ti t1 -L LTitc T ti tl b fren b-oaV-c vl.ar a nW or lari tan TSTK. llJSfU_UU 1?171. rlJr_LLlJli(p_fliiitcridLJ購 I kLtlE I I tf戲壘是I vtzh-iXn tc-td-sui); -nc ifflir?-_- tin J Lmr clst fi_-ici.-： J; -Di? 1 r ：0p nr： nf / k：ae 已 QifiE * S亡*L ZU-VO.OTltpC-t ofb

10、 仙:|和 T CTisTsnci iiHitir . 勺亡piL r iiikr Yx . ptra_stc r J airix-f =llJ. H跳 3 4 .Lriiet J T-fTJ%Trn*T (3)創(chuàng)建一個存儲過程，以訂單號為參數(shù)，輸出該訂單中所有圖書的名稱、單價、數(shù)量。 create or rep lace pro cedure pro c_get_orderi nfoo( p_order_id orderitem.order_id%t ype) as -聲明游標存儲訂單號的ISBN cursor c_ISBN is select ISBN from orderitem whe

11、re order_id=p_o rder_id; v_ISBN orderitem.ISBN%ty pe; -聲明游標存儲訂單信息 cursor c_orderitem is select ISBN,sum(qua ntity) total numfrom orderitem where ISBN=v_ISBN ; v_title books.title%ty pe; v_retail books.retail%t ype; begin open c_ISBN; LOOP fetch c_ISBN into v_ISBN; exit whe n c_ISBN%NOTFOUND; for v_o

12、rderitem in c_orderitem LOOP select title,retail into v_title,v_retail from books where ISBN=v_orderitem.ISBN; DBMS_OUT PUT.PU T_LINE(p_order_id|v_title|v_retail|v_orderitem.tota In um); end LOOP; end LOOP; close c_ISBN; end pro c_get_orderi nfoo; / 廣 SQL Plus 8SQL*Plus: Rele aze 11.2. 0. 1.0 Proinc

13、ti on on 星期三 E月 24 13:32:57 2017 cpyri gk-t Cu) 13S2, 2010 Ora.cle. All n glihs jreservei. 青輸入用戶名： 關! DZ汽出.叫呻RZ,銘.1.0 :珈譏臥應如, 卅ith th* Fartiti 畑in OL. S就包 mining uid RealI eating options create ox replace procedure proc_et_orlerinfcc( Porderid OX Jer i + em . ovler_i JlypeJ as 聲明游標存儲訂單是的站站 cursor c_

14、ISBM is select ISBK from V ISEll order! tem. ISElTtype; 二宙明游標存緒可單信息 cursoris seltet ISBII ISBMW_ISBK : v_ti tie Books. v_retail liooks. retaillfetype： les；in opsR f_ESK: LOOf- fetch c_ISN into v_ItlT： ejiit when cISBHWraTFOVUE; for v_or JerI tern lil c_Qr Jeri tern select ti-tlcj retail into v_ 迫L 2

15、 3 4 C 4 T_orJeri tem ：=1- (leriiio(v_or der); ：*- 歹 t-or 占F.占時劣c. (4)創(chuàng)建一個存儲過程，以出版社名為參數(shù), 批發(fā)價格、零售價格信息。 輸出該出版社出版的所有圖書的名稱、 ISBN create or rep lace pro cedure pro c_get_ name( p_title books.title%ty pe) as cursor c_orderid is select order_id from orders where customer_id=p_customer_id; v_orderid orders.

16、order_id%t ype; cursor c_orderitem is select ISBN, sum(qua ntity) total num from orderitem order_id=v_orderid group by ISBN; v_title books.title%ty pe; where begin open c_orderid; LOOP fetch COrderid into v_ordend; exit whe n c_orderid%NOTFOUND; for v_orderitem in c_orderitem LOOP select title into

17、v_title from books where ISBN=v_orderitem.ISBN; DBMS_OUT PUT.PU T_LINE(p_customer_id|”|v_title的 |v_orderitem.tota Inu m); end LOOP; end LOOP; close c_orderid; end pro c_get_orderi nfo; / set serverout put on declare v_customer nu mber; begin v_customer := pro c_get_orderi nfo( v_customer); en d; / C

18、T-4t* ar Tpl 曲中 j.-pArtinre P_l1L.u LkGuklyRuJ cor MKT *：_ac Jw: J is a-lcti CTJcr_l t Ftf irdcs rimers cwstci w_: J-；_CTn-tFricr_L J. .rdrjd #1心73 wiIph.外rypm; riL ut IlEf V R h-1 Hr 1 T. hlK f 叩i h k 4 y, 于Stif TimIt b血九. iip.ti =_H-4r L 1. 】fnp i: t:h c.atta-Ld iz-tD F.oricfz.d. ui： vUuL wJuiidVA

19、fTrUBL far T HeL|Z L L iB-k 3.H. C_ HTeLh-T ! b*! _DCP /中戲 rirlt kcftto:嚴 I5?K 0胡廿 TSlf: M?E:TJ：.?：7 二m D Kt駛 id r I It 曰dulffrl詢辰設 It ,4口 rptkLwzi) 皿 juur. -iJ urr. 勺1*=亡d: vJf； pi .J “t詢！*! imiv. 曹吉 iW 2 生邂r曲帯有嫌”耶s巴* i*T I:-屮丨 y uh 5 V.ETEUr Tirnr Hpji 豈fci, pr rrd*ri uf+ rj- t g, ”也(T 2 A g.E:電 E

20、fF =tir 1. y= T-l lE n?佝L J理歆伽 (5)創(chuàng)建一個存儲過程，輸出每個客戶訂購的圖書的數(shù)量、價格總額。 create or rep lace pro cedure pro c_category_static 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 whe

21、re category=v_each_category.category group by category; dbms_output.put_line(種類為：|v_each_category.category，總價格為：| v_sum_cost); END LOOP; end pro c_category_static; / set serverout put on exec pro c_category_static; / :yerfc EMT 二細-:E IWbU. L*，I EsK taJfa , TT3 fcTP TT IWiSC? PTDCB iJrr TTO T CITKTY

22、ITtEC :IS Gt7 V_v4Jll肌國4F :L L_41!l_Lt*|urp LXP TwlEnl inn Thr-n “帝t otiiT ctryrj.mj+pyrT -iWrirT jrr jv -fi-pTTv 0*D_-PjLpiJi, pjl_i . n-：理勺丨 b-扣九：dhtT頭.yiTfKTxl Il|gq. 9 DU LOX . ：i piT i+m 4科* i時 11 . ha - Ur-:B Hl =, ?UL . lIS-L v_sum_retail nu mber; begin for v_each_category in c_all_category L

23、OOP select sum(cost) into v_sum_retail from books where category=v_each_category.category group by category; dbms_output.put_line(種類為：|v_each_category.category，數(shù)量為：| v_sum_retail); END LOOP; end pro c_category_static; / set serverout put on exec pro c_category_static; 5=七 :ttTira-tpTi 0二 Lt-C：! .-杲i

24、LRli*r, p: uc_g)et_ot 二Ulf 4胡： r !FiE f?niB vc-aatw - goL Hka 曲為E：。 ir ifsplK-t7ra ;,c*tt-r：ry 5tit:c 4? -IH- tnr 廠ll.n亂卜尸茗啊” i% 叫訂*! =1i =? m r*：tfr m hr.，4-R. V CUI ext C.Oi Jut stsa-h for V s星 oztfl porr Lb e all竺節(jié) LK? Lltdt z,n frztiil) JiitiIt irtn Y Jcz flhcL飛 Oal-t-：ioh_itt=pfy.ctfie tx .umf；

25、 JlisFot JIkC ftlxXlS h wA 仙討vy血口khI I 負frHfiF； V 3w ：q:z) e* kuf. EF_=Ii!.7齢 Si；L :tT MLVtJTOTpat l ：-Ql： r 創(chuàng)建一個存儲過程，輸出訂購圖書數(shù)量最多的客戶的信息及訂購圖書的數(shù)量。 XL -I I-* M p予Jilh hk+-ni4 bvivri 41 i-ltn nm 白如 rrilUEL 4iwlm 5比I L TJ-af氓丿in r )L hUIP T-L tv Ea . ! T 1111 T_3 Bsdf v_sum_retail nu mber; begin for v_eac

26、h_title in c_all_title LOOP select sum(cost) into v_sum_retail from books where title=v_each_title.title group by title; 數(shù)量為：ll v_sum_retail); dbms_output.put_line(信息為：|v_each_title.title|, END LOOP; end pro c_title_static; crb-c.tb or rajlit* 卩rocQ-aiTQ pr.iit-L.stii.tio rurior c.dJ-titl is leel di

27、itiLcb titl* trr4:Iq ti tIo 曠tup ty ti tJ q; p ut lint 廠信島為；Ik ttch ti tie li tie | |. lirW?p : iJ 卩 s im retil): tHJ llmjF： 廠 nA rr鬥d L.yo (10) 創(chuàng)建一個包，利用集合實現(xiàn)圖書銷售排行榜的分頁顯示。 (11) 創(chuàng)建一個包，包含一個函數(shù)和一個過程。函數(shù)以圖書類型為參數(shù)，返回該類型圖書 的平均價格。過程輸出各種類型圖書中價格高于同類型圖書平均價格的圖書信息。 create or rep lace p ackage p kg_book as function

28、get_book_avgcost (p _book_category BOOKS.category%t ype) return nu mber; p rocedure p ro_showbook (p _book_category BOOKS.category%t yp e); en d; / create or rep lace p ackage body p kg_book as function get_book_avgcost (p _book_category BOOKS.category%t ype) retur n nu mber as v_ISBN BOOKSSBN%t ype

29、; cursor c_books is select retail from BOOKS where ISBN=v_ISBN; v_sumcost nu mber(6,2):=0; v_co unt nu mber(6) :=0; v_avgcost nu mber :=0; v_book_category varchar2(10); begin select ISBN into v_ISBN from BOOKS where category=v_book_category; for v_retail in c_books LOOP v_co un t:=v_co un t+1; v_sum

30、cost:= v_sumcost+v_retail.retail; end LOOP; v_avgcost:=v_sumcost/v_co unt; DBMS_OUT PUT.PU T_LINE(v_book_category| -|v_avgcost); retur n v_avgcost; end; pro cedure pro _showbook(p_book_category BOOKS.category%t ype) as v_book_category varchar2(10); cursor c_books is select * from BOOKS where retail=

31、get_book_avgcost(v_book_category); begin for v_books in c_books loop dbms_out put.p ut_li ne(v_books.lSBN| |v_books.title| |v_books.author| |v_books. pubdate| |v_books. publisher_id| |v_books.retail); end loop; end; end; set serverout put on declare p_book_category BOOKS.category%t ype; avgcost nu m

32、ber; begin p_book_category:=管理; avgcost:=p kg_book.get_book_avgcost (p _book_category); pkg__showbook(管理); end; 皿 z-tLcii 孕 t_iocc Ct 5.b : okc-it u 滬牛 *ED05E r-at end; / create or rep lace trigger trg_before_order before in serton ORDERS for each row begin order_total_cost.v_order_id:=:n ew

33、.order_id; end; / set serverout put on create or rep lace trigger trg_order after in sert on ORDERitem declare cursor c_orderitem is select ISBN, qua ntity from orderitem where order_id=order_total_cost.v_order_id; v_ISBN orderitem.lSBN%ty pe; v_qua ntity orderitem.qua ntity%t ype; v_cost books.cost

34、%t ype; v_sumcost nu mber(6,2):=0; begin for v_orderitem in c_orderitem LOOP if v_orderitem.qua ntity 10 the n select cost into v_cost from books where ISBN = v_orderite m.I SBN; DBMS_OUTPUT.PUT_LINE(1-|v_cost:|v_orderite mSBN); elsif v_orderitem.qua ntity eirgtb ar rcplzoa tiritrg：_ljaf orQ_crlar 2

35、 tetoore insert oil OEDESS 3 for etch row 4訂訴n, 5 order to til_c*Et. v jrieF i A. - . n-w. orlei il. 4Zld： reti cr rilac triecer trgLtfore_crler 鴛- 7 3 9 0 12 set ZErvaroutput dr ereit( or rtplsc* trigger trborder d*cT ib34trt g a*! cursar cor Jen tem is select IS31T丿 qiartitv from orleri tem Lere d

36、rier i J=or(ler_total cost v_rEBH or t tm. ISBlfttyp _iuintL ty crdirittn. if_qQat bflokn, qpilWl/pt ； v_cujiircizt- Tvunbdir(6-2MX legin,. r v_oritri tim in c_or leriUm LOOP if T-omitfiturn 普Mity 10 thtn clccl I 1 nt V V CDzi ucin boclrs wKer e ZSBV * v oj dc-r i Lem ZSEN. DEMS_CUTPin. rJT_LIHE： 1

37、| 卜朋電 | b.打；“肝 ISEK): *liif *_rJ*ritfrr. uifitLty10 the n select cost i nto v_cost from books where ISBN = v_orderite m.I SBN; DBMS_OUTPUT.PUT_LINE(1-|v_cost|v_orderitem.ISBN); elsif v_orderitem.qua ntity=10 the n select retail into v_cost from books where ISBN = v_orderitem.ISBN; DBMS_OUTPUT.PUT_LI

38、NE(2-|v_cost|v_orderitem.ISBN); else DBMS_OUT PUT.PU T_LINE( nu mber of book is error!); end if; v_sumcost:= v_sumcost+v_orderitem.qua ntity*v_cost; DBMS_OUT PUT.PU T_LINE(3*|v_sumcost); end LOOP; end LOOP; close c_orderid; retur n v_sumcost; end get_sumcost; / set serverout put on declare v_totalM

39、oney BOOKS.cost%ty pe; v_customer nu mber; begin v_customer := v_totalM on ey:=get_sumcost(v_customer); dbms_output.put_line(v_customer|的購買總額是|v_totalMoney); end; / I SQL r-lus y*t -ax- r -i lcb aar=t a va. 半 t.iAiac* DLb T4tTi 匚 mt LT 1 na Nfi： Lhl*耳tJmi _Ld Ici iiduii pL-uj十出：.w訶上_j4*_，仏Le*j【 (rqc

40、xIS. s.Iqc*: I rm. nr rssv ir_gB5 T_tU : uLl Vyir. f H. c_ra ikr : d, LOT MlH. r-_ardTid. jpta vd. IKlt蟲”*山卄祕冃E粧 r*ar R_EE-im-L t + xb E_Eird-iT ! fc-n jl 丫-刊 dwi th?1Q Ttlr-rt ort i-to T-_c*=t- DT sinnrr iw um: l Cd : CVF : J. IV WJ 柑扌It. Ldvri LEfe.匕並 I 吐1il I J I I- _in- UTPVT J-n ZJHE.J LDDF iLl

41、* IVflo ti-ks vKcnt LES If = T_：irda-LtTi.rf3B I h = 31 II* P i-r i 1I -ID LhiZ. * _ -lib: Rb-FT IJBW -jip Art blM： ISM I |t”* I |d. or air ! -t tri TZBF , UTET Fin一irnfTiwtiw of lx心 i: QrrjF R lUfl-KTDLt- F ruFiT知 stJm* ； tb 怦血“序 mb LftJUVFJI.rVV.LKl；肝lh._Lu.MiU. 肝弓Lice： id LJ3F ttE4 fl -xiiiid H *

42、 tit ? *_suf 2 電 fuikctisn 6t_suffleistC T_rSBK BoolfB.ISEHWtyp*) raturn nunbtr cursor c orisrii is select ord6r_ii frcrii orders wRere custMniv_i J=v_cuEtom 10 1 12 13 14 15 Ig 17 13 cursor c_orderiteii is select ISEKj quantity froB Mrdtrittm wh電rt rdr_iJv_ordtrid; vJSBfT or dtritan.: Y_quantity cr

43、deritem. quanti tyKtype; Y_c(jst tooks. cost陣type. 0 21 22 23 24 西 7 3 3 30 31 32 33 34 35 36 37 33 39 v_ELmSfiSt Ik恤畀fl ujor _. fetch c_orderi4 ante T_0rderii; 勰itcorderiJ16HOT?DU11I): or v_or den t en i n c_or Jeri tem if v_Qr(lerl tem. quantity 10 select cost into v_cost DBWS_OVTFUT. PUT_LIHEC 1

44、LDOF tten iron where I5N v_orJeriUm. ISEH; _I |v_cost I |_ftrd*ri tn. ISBN): dmif v_srd*riten. u4ritity=lO tken select tail int DmS_01JTFUT FVT-LENE? 2| K.c !； ” , v_iuincost ； v_sumcot+T_*r der item, quanti.ty*v_cost. D魂丄UTFUT. Pirr_LEKE(3*| |_sumcost)： Hid LODF： and LOOF. _ clo c_Qrderi(l; return

45、v_sujncost ; end gwt.siimc ost ： ent 曹告；創(chuàng)翟的帕敎帯有塢譯錯譴。 SQL pOL 百at xrvfrroutput on. declare v_totlHciTiey BOOKS. cQzttype; customer num ter: begin V_CustoTih_id： v_5UjncQst nunher(6, )： =0; v_coKiiit numb er ) : F： 1 egiiL intoJ fro* publish世s whert： fcr v_rtt4il in c_hooJ(i LOOP v_a ount ； =v_c oxmt

46、+1; Y_5Tipic9st：= Y_STiPieQst+v_ret4il. retail; DBMS OUTPUT. PUT UWE (v coimt | | |v sumcost)： LOOP?- Y_suincost ： end return _ end 61_put_ivgc0st： pQL PUL set server output ork declare _ftvon5y BOOfS. ooEtifttype： vjuhnam* publ i ihtr s. Him fWtyp a: hacin v_pubTi4jns :強!： V av訓QTiey: =get pul_avgc

47、ost (v Dubname ); dTmm jutput. pu t3 i Ike (vjubnam e|的出版圖劉的平均價格是I |T_avgMoney): End： / X的值：電子工業(yè)出版社 5： vjubname 5； vputname :瀆子工It出版社, V putiLame ：子工業(yè)出頗土： 專屮押堆律， 10 (17)創(chuàng)建一個函數(shù)，以客戶號為參數(shù)，返回該客戶可以獲得的禮品名稱。 create or rep lace function get_gift( v_customer_id customers.customer_id%t ype) retur n nu mber as

48、cursor c_orderid is select order_id from orders where customer_id=v_customer_id; v_orderid orders.order_id%t ype; cursor c_ordentem is select ISBN, qua ntity from orderitem where order_id=v_orderid; v_ISBN orderitem.lSBN%ty pe; v_qua ntity orderitem.qua ntity%t ype; v_cost books.cost%t ype; v_gift number(6,2):=0; begin open c_orderid; LOOP fetch c_orderid into v_orderid; exit whe n c_orderid%NOTFOUND; for v_orderitem in c_orderitem LOOP if v_orderitem.qua ntity 10 the n select cost into v_cost from books where ISBN = v_orderite m.I SBN; DBMS_OUTPUT.PUT_LINE(1