实验LSQL程序设计

1、实验6 PL/SQL程序设计1实验目的掌握PL/SQL程序开发方法。(2)掌握存储过程、函数、触发器、包的创建于调用。2实验要求(1)根据图书销售系统业务要求创建特定的存储过程、函数、触发器。(2)根据图书销售系统业务要求将图书销售系统相关的函数、存储过程封装到包里。3实验步骤以bs用户登录BOOKSALE数据库，利用 PL/SQL程序编写下列功能模块。叮 SQL PlusEQL+TIus ； Fel eaze 1 1 . . 0. 1 _ 0 Piro luc t i- on 星期二 5月 9 U:O7:2O 2017Copyri gKt (c) 1392； MIO； Oracle. All

2、 ri ts reserve!.S输入用户宕-hyliugin 电 booksalts sysdbt逹播到：.bracrle D1 If 7itirFris Eli ti on Eel*ase 11.2. . 1.0 - G4bitrHi th the FtitiOLAT D 岂t a Mini Tig ajid Kal Application Testing ojticnsS9L(1)创建一个存储过程，输出不同类型图书的数量、平均价格。SQL create or rep lace pro cedure pro c_category_static2 as定义游标，获取当前有哪些图书种类curs

3、or c_all_category is select dist inct category from books;图书的平均价格v_avg_cost nu mber;beg in保存图书种类9 for v_each_category in c_all_category LOOP10select avg(retail)into v_avg_cost from books wherecategory=v_each_category.category group by category;11dbms_out put.pu t_li ne(种类为：|v_each_category.category|

4、.平均价格为:| v_avg_cost);12END LOOP;13IIIL?B1血芟9 t 2 n 为: a yi fi I 4 ri - i .-2end pro c_category_static;14dr replace pricelure pac_ctegor7_：aticKcm5ar c all citejorv 咒lt distinctT_uvjccst nunb町；for v_e6fh_cat*?icy in c_all_ctteory UDO?fdfct 掰iiitt V sv_cflst fr3n ooth 节htiF c就鹉卅鬥adi citgcry. cdiry frt

5、iip by citejor/; pM_Liiu种勒：I|v_ia:K_：i：*g(ry. 性熬yHr勒尬稻九ll _wcoit!HIT躡ni prflc_c4te?wv_stici:；(2)创建一个存储过程，以客户号为参数，输出该客户订购的所有图书的名称与数量。create or replace procedure proc_get_orderinfo(2 p_customer_id customers.customer_id%type)3 as声明游标存储客户的订单号cursor c_orderid is selectorder_id from orders wherecustomer_i

6、d=p_customer_id;v_orderid orders.order_id%type;声明游标存储订单信息cursor c_orderitem is select ISBN, sum(quantity) totalnum from orderitem where order_id=v_orderid group by ISBN;10保存图书的书名v_title books.title%type;1112begin13open c_orderid;14LOOP15fetch c_orderid into v_orderid;16exit when c_orderid%NOTFOUND;1

7、7for v_orderitem in c_orderitem LOOP18select title into v_title from books where ISBN=v_orderitem.ISBN;19DBMS_OUTPUT.PUT_LINE(p_customer_id|订购 |v_title|的数量是|v_orderitem.totalnum);20end LOOP;21end LOOP;22close c_orderid;23end proc_get_orderinfo;24 /exec pro c_get_orderi nfoo(1001);卜j rr SQLPIjsE：l口无丄

8、01谨f子苻U件I JFt F ftpL-aci 尹匚ijrt pr 5c_g&t_ _cr 16rj.E.-o( ij _ J_i ( uti el ,j ! ju ( uh 裂. u 4 Lett EE _i J&Ly|.crtr.t4 or rasJv c - 一声肖岭存儲笛苦号 一 I .,uj lue u_prt；fli i i j h i-lcfli jictTiJvLubi c jua 1 utieit_ jiwhii _l 1,w ardfir: J nirdirc fTrlio i JTvr.fi片皑於瑚HN邓息1* 一 、，.，-UJ ku U . pC C-tJ. L L

9、vP 3 tJ.c rL ICDH kUlif-wU-BfatLl LwlilhUP JJ pIi 111 ilvh rtLcfc vl. d _ 1_ k-i J.i J* L-J J 县1$书旳马fev_f.i-LLeLv ISDIijL4cj tn c_z iirLOOT Titcl : ffilx-IC 3 711-0 -r DTh-dcTL?tito_DreflrL iAWJJ/JUil：qp *jir*l 2r I :h CHII 1-P : Pin TflflTu-Z -ac-t ti tli LTitc TI-1 tl b frcn ? vEmra 匸可 dr lari tan

10、 T?T.11JSIU_UU 1?171. rlJr_L_LJIi(p_-niiitcrid i | ktLiTr | | 手轴建是 |tc-td-oui),-DU IW?-_-tin J Lnr1 Pin-Dll IP jy jMS亡*L SUVCI.OTlpC.t otb仙:|釘“ T ESTMCi n-nubtr .冉 r iiiH-r弋JT .亍广口0.军1七_4 r J airix-f &(venzten 卫r * j SLLcl.H跳 34 .Lpiici唯I,4 T nKTntr巧：(3)创建一个存储过程，以订单号为参数，输出该订单中所有图书的名称、单价、数量。create or

11、 rep lace pro cedure pro c_get_orden nfoo(p_o rder_id orderitem.order_id%t ype)as声明游标存储订单号的ISBNcursor c_ISBN is select ISBN from orderitem where order_id=p_order_id;v_ISBN orderitem.lSBN%ty pe;声明游标存储订单信息cursor c_orderitem is select ISBN,sum(qua ntity) total num from orderitem whereISBN=v ISBN ;v_tit

12、le books.title%type;v_retail books.retail%type;beginopen c_ISBN;LOOPfetch c_ISBN into v_ISBN;exit when c_ISBN%NOTFOUND;for v_orderitem in c_orderitem LOOPselecttitle,retailinto v_title,v_retailfrom books whereISBN=v_orderitem.ISBN;DBMS_OUTPUT.PUT_LINE(p_order_id|v_title|v_retail|v_orderitem.totalnum

13、);end LOOP;end LOOP;close c_ISBN;end proc_get_orderinfoo;厂SQL Pin呂k：iL*Flus : Rel e ase 11.2. 0.1.0 Prolucti on on星期三 E月 24 19:32:37 2C1TCcpyrigKt Cu) 1362,2010,Oricle. All u；sUire i.45 sysila匡接到,.,.Cl acl*Ilf Ent er prise Edition R “證e* 11. .0 1.0 - 64b it Froductioni oiling OLAP. Dta Mining uid Rq

14、sI Applicati cnoptionshitk the Parti ti prOGcreate rejlce proceliure prccjetcrItTinftflC p_ffr(er_id order item. Qrier_i d%typ)一声明游标存储订单E的工引H_ISDII is select IEK : V ISBlf or deri tem. ISBlTtyp e : 二声明游标存皆可单m息,、,is select ISBH, sum (qiiaiiH ty) totsliium from order itemcursorcursorLiters rSEH=v_TSBU

15、 :91011IE13141516rom oflejri tern li/kere orcler_i(l=j_or(ler_i J.;v_ti tie books, litletype： v_rttiil toolcs. r*t*Ll端typ* ;leinopsn e_Z5IH iLoor - fetcK c ISIW into v ISEW; euit when o ISBHmTFOLBE;Por V oirler tem lil c_or Jer 1 tem select title, r etail into v,LOOTti tl e, V reta.il from boolcE wh

16、ere ISBW=v_or ier L t em. ISEH;ISDEMS_011TFirr. FirrjJiTE (p_iiriar_i i | |v_ti tl e | |v_rata.il | v_bv trn. tQtaln伽)：19202L眾23! LOOf;end IMF. elflEft flJSEH: tnl pr 5_ge t_or i er i. afo; / - 迫L迫L宅34 E&5et seiveroutput Cmde cl ar 电T_or(leTitem number: teeiftT_orderitem : Mbi： pr c_gtt_ordr info (v

17、 order *iid：3CrocT_or Jeri tern :我k;T_orJeri tem ：=1- deriiio(v_or der);(4)创建一个存储过程，以出版社名为参数，输出该出版社出版的所有图书的名称、 批发价格、零售价格信息。ISBN、create or rep lace pro cedure pro c_get_ name(p_title books.title%ty pe)ascursorc_orderidisselectorder_idfrom orderswherecustomer_id=p_customer_id;v_orderid orders.order_id

18、%type;cursor c_orderitem is select ISBN, sum(quantity) totalnum from orderitem where order_id=v_orderid group by ISBN;v_title books.title%type;beginopen c_orderid;LOOPfetch c_orderid into v_orderid;exit when c_orderid%NOTFOUND;for v_orderitem in c_orderitem LOOPselect title into v_title from books w

19、here ISBN=v_orderitem.ISBN;DBMS_OUTPUT.PUT_LINE(p_customer_id|v_title|的数量是|v_orderitem.totalnum);end LOOP;end LOOP;close c_orderid;end proc_get_orderinfo;set serveroutput on declarev_customer number;beginv_customer :=&兀pro c_get_orderi nfo( v_customer);end;it* ar曲中P.tiLt Jdb二 tLtlab婷kJcur IM- c_ar J

20、w 3 J la -aln-r-fc udr_L*i .rtd HriderJ.卜F.L 4/Typ;4k e rivir 4vui LJilLi i. L_t:tl* St I kDtJoi：*?-Cud UM#-： nrpJ UWT. _ clB-i =_Dr Jar J.虑pr切丿讥鼻苛也卄亞TH 干erverrE霄iifc g dk-Lut FU hE f-r iiLi*ihr眇廿酌IE；rr(Bi V -rutwiar 4:C4. J czitwiMt .YI, 创建一个存储过程，输出每个客户订购的图书的数量、价格总额。create or rep lace pro cedure pr

21、o c_category_staticascursor c_all_category is select dist inct category from books;v_sum_cost nu mber;beginfor v_each_category in c_all_category LOOPselect sum(retail) into v_sum_cost from books wherecategory=v_each_category.category group by category;dbms_out pu t. pu t_li ne(种类为：|v_each_category.c

22、ategory|.总价格为：|v_sum_cost);END LOOP;end pro c_category_static;set serverout put onexec pro c_category_static;rUL- riThTP CT iwl7? pTDcr-yrr pnc ciikty rustic :兀3EVrlW,#】_: tu ebq三_彌Wi mifbct.E0t7因4丫 :L L_l_“4rjirF iJJtPT汗】店* wnlEnl 5 咐】 記；:冲 Itf -Ott Hipt -t-TrjFT.rh tMnrr MWnrr trov r -fi-PTTV.0.!j

23、iJ= .IT:塾为 s lh-SqL_:4ME37 .%Kryll : ffl-ff-fiJn i II jW- wq.9 on LOX . : rt -rli_= a* i -.：1 ；,t a rtisclI c-x taa闻L JG二&n.FlWH*创建一个存储过程，输出销售数量前3名的图书的信息及销售名次。create or rep lace pro cedure pro c_category_static ascursor c_all_category is select dist inct category from books;v_sum_retail nu mber;begi

24、nfor v_each_category in c_all_category LOOPselect sum(cost) into v_sum_retail from books where category=v_each_category.category group by category;dbms_out pu t. pu t_li ne(种类为：|v_each_category.category|.数量为：llv_sum_retail);END LOOP;end pro c_category_static;set serverout put onexec pro c_category_s

25、tatic;5 七 3ttTsra-tpTi (,-HE yLTn RUXlw it,p ft Efit ZOf ZilTLE-EtT 7Tl=tin- CftJ：X 的 1： SBe Mt 十ruuL幻琉S也E圭成。ft4rurtT39TWTEflr fl *11 Piy*2*rv 二心#吐 3抚日”矶frmT_5UI_CKt -itrFn? v.rd c匕in 亡1：.匚* = LCCP.vl ucL1) lull, u Ln_cd tt Le L-n hi uk-. jSvfv CLilvgov厂鼻 v chcdC&ry ett- h,ur；rf DJIt =IihC 即甫为：h kic

26、k ?4CQ naUEvI I f 京1 轩円： I v fifi吓 p.EM KQP:ccJ pr*fl _ P4k*: ty_it4Ti 0己L：楚T KLVtrQTpat 昶IFL/!QL 创建一个存储过程，输出订购图书数量最多的客户的信息及订购图书的数量。却! I41 a-lni4、t4* .b hJvh=3.-_i3-hkv- in All F. LBTIP7hIr 峠jdl I Ph- T.Tf OSPJtJt 氐=rviQ业1 Fllcn-I |R-_ ill 暮m龟R ITt ! d PUH I V-l I II IP3 IMfm 3址“匕I百mjga.曲=i傭054 口 Oi

27、*E(8)仓个存储过程，输出各类图书中销售数量最多的图书的信息及销售的数量。Cm I ir .-liL 1 lit.T EK r-rti nkr.Id11珀aa u.dL.titU Li世u4 L-J I I . LE . H_ IV-_I * tr 片-，-h* 亠 C_EItI* E . Eh J/bbJi-L_-J*7Uh 小1丄”|* ll-.HI-irk lilb hllJvil 負B如 nri irtiiI11 *_aw TR-hitNL nt i-wirKliiwh/ jp -*1 -iCIl L MCdci u i匸T；fR鶴蓋鸚崔Ji：1H13 & a inl_ a(9) 创

28、建一个包，实现查询客尸订购图书详细信息的分页显示。create or rep lace p rocedure pro c_title_staticascursor c_all_title is select disti net title from books;v_sum_retail nu mber;beginfor v_each_title in c_all_title LOOPselectsum(cost)intov_sum_retailfrombooks wheredbms_out put.pu t_li ne(信息为：|v_each_title.title|.数量为：|v_sum_r

29、etail);END LOOP;end pro c_title_static;CTTfittfi or rajlit* 卩rocd-TurQ4pr o_z 11_ 匚.E till 1 ofurior r.dJ-ti tie isbeginlor v_eai3LtitI in =jn7?0=t ihte dbn5_(utiit put tHJ llmjF： , *riitlrlerl distLicb titl* tr=get_book_avgcost(v_book_category);beginfor v_books in c_books loopdbms_output.put_line(v

30、_books.ISBN| |v_books.title| |v_books.author| |v_books.pubdate| |v_books.publisher_id| |v_books.retail);end loop;end;end;set serveroutput on declarep_book_category BOOKS.category%type;avgcost number;beginp_book_category:= 管理 ;avgcost:=pkg_book.get_book_avgcost(p_book_category);pkg__showbook(

31、 管理 );end;cr*ut Hl- fc*lE*u uLb juul h 八 -1 t r ht i-i. fif CODFTS1E-IRKvimciGct wltiT 慣.空)-Tlv_ uiltiL Di uni Mt :攻)Ht-r -fl,Cl山: Agcry V at mL I Q JfcS-n乂LiTi.tto Vfh n 17Cl?Z wz *Iat U_l: u E I Lb. c di-b L2flPU-_ -I.IB-r-ft ,U70 Ct - V IJfl ?rCt*V TCtElJ. r-ivji Lihp.7&I.Ou 4| tIlHE_OtTFJT Finir

32、rEQifl-ZtigpryTlC；：Q II芍rur i. * 应溼口0).cL.htbbf iw -Cui rif KflTiFZ 卡hu-lLf-y I rpT * 气nr *晋t 洱h. noBhr;|i *.i u witci10 thenselect 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 eirgtb ar rcplzoa tiritrg：_ljaf o

33、rQ_crlar2 tetoore insert oil OEDESS3 for etch row4订話n,5 order to til_c*Et. v jrieF i A. - . n-w. orlei il.ar4Zld：reti cr rilac triecer trgLtfore_crler7 3 9 0 12zet SErvaroutput dilereit* or rtplsc* triggst trorderinnvrt n OBIERiaprl HTPCUT Ear cor Jen tern is select IS31T, qiart i tv from or ler i t

34、em where 5rier i Jor Jer _t o t al vJEBH or itri t tm. ISBlfttyp e_iuintity (rdiritn. uut:ty*typt;if_qQat bfloks.;v_Euncozt Ttunltar(6, ) ：legin,for v_oritritim in c_or lerit*m LOOP if v_oriiitsp 珂爼:曲 10 thzcIecI cnnL i nt v cost, fr on booln wliEr e Z5BV v oi de-r i Lem ZSEH.DEJE.CUTPin. PJT_LliiE

35、J 1* | 卜朋七丿 |打；“m ISEK):*liif *_rd*ritni. uttty10 thenselect 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 thenselect retail into v_cost from books where ISBN = v_orderitem.ISBN;DBMS_OUTPUT.PUT_LINE(2|v_cost|v_

36、orderitem.ISBN);elseDBMS_OUTPUT.PUT_LINE(number of book is error!);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 declarev_totalMoney BOOKS.cost%type;v_customer number;beginv_

37、customer :=&x;v_totalMoney:=get_sumcost(v_customer);dbms_output.put_line(v_customer|的购买总额是 |v_totalMoney);end;I SQL r-lusy*t -ax- r -i lcb aar=t a va. 半 t.iAiac* DLbT4tTi 匚 mt LT1na Nfi： Lhl*耳tJmi _Ld Ici iiduii pL-uj十出：.w诃上_j4*_，仏Le*j【(rqcxIS. s.Iqc*: I rm.nr rssvir_gB5T_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?1QTtlr-rt ort i-to T-_c*=t- DT sinnrr iw um: l Cd :CVF :J. IV WJ柑扌It.Ldvri LEfe.匕並I 吐1il I JI I- _in- UTPvr j-n ZJHE.JLDDFiLl*IVflo ti-ks vKcnt LES If = T_：irda-LtTi.rf3BI h = 31 II* P i-r i 1I-ID