实验plsql程序设计

1、实验6 PL/SQL程序设计 1实验目的 (1) 掌握PL/SQL程序开发方法。 (2) 掌握存储过程、函数、触发器、包的创建于调用。 2实验要求 (1) 根据图书销售系统业务要求创建特定的存储过程、函数、触发器。 (2) 根据图书销售系统业务要求将图书销售系统相关的函数、存储过程封装到包里。 3实验步骤 以bs用户登录BOOKSALE数据库，禾U用 PL/SQL程序编写下列功能模块。 create or replace procedure proc_category_static 2 as 定义游标，获取当前有哪些图书种类 4 cursor c_all_category is select

2、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(retail) into v_avg_cost from books where category=v_each_category.category group by category; 11 dbms_output.put_line(种类为：|v_each_category.category，平均价格 为：|

3、v_avg_cost); 12 END LOOP; 13 end proc_category_static; 14 / create or replace procedure procoatgory_stitle 逹义牺熄硝前邮總拥奠. cursor c ul ctttsvry ii select diitinct catfjor/ iqmi; -s弔孵帅格 number. begin T郴书懺 for缚cry in c_dl_catjory LOOP seLect wlretail) into v 胡口旳t Ero*血氏 吐egorj/=v_eacli category, category

4、的up by category： dlfis output, put linmf 种奏为：电皿 citsgry. catenary11 揮咖懈为：| v ivcost); E HD LO 矗；： |SqU set serYtroutp吐 m end pr oc_cat 聶ory_m tat i g : (2)创建一个存储过程，以客户号为参数，输出该客户订购的所有图书的名称与数量。 where 的数量是 create or replace procedure proc_get_orderinfo( 2 p_customer_id customers.customer_id%type) 3 as

5、4 - 声明游标存储客户的订单号 5 cursor c_orderid is select order_id from orders customer_id=p_customer_id; 6 v_orderid orders.order_id%type; 7 - 声明游标存储订单信息 8 cursor c_orderitem is select ISBN, sum(quantity) totalnum from orderitem where order_id=v_orderid group by ISBN; 9 - 保存图书的书名 10 v_title books.title%type; 1

6、1 12 begin 13 open c_orderid; 14 LOOP 15 fetch c_orderid into v_orderid; 16 exit when c_orderid%NOTFOUND; 17 for v_orderitem in c_orderitem LOOP 18 select title into v_title from books where ISBN=v_orderitem.ISBN; 19 DBMS_OUTPUT.PUT_LINE(p_customer_id| 订购 |v_title| |v_orderitem.totalnum); 20 end LOO

7、P; 21 end LOOP; 22 close c_orderid; 23 end proc_get_orderinfo; 24 / exec proc_get_orderi nfoo(1001); SQL Plus FFAmw：无法创薩保存文件 + _, .bu r replace pirflcedur-ei proc_tit_orderinfsi( p=eustoffier_i J eastamsrs. ciistcn eri dfttypt) i： 一声明游标存诸腎尸的订单寻 MFE-Dr e oirlerid i 5 select order.id from oriers; wker

8、e cuetomeir.i dbpcustoflir_id: v order id oriers. crder 1 dtttypc, 二声明游祐存储打单焉息-fx cureof_c orieriten is; s-elect ISSN ew*(quantsty) totalnun fron orderiitem where order_id=*_ordjsrid group- by ISBN; t新囹苗殆书名、 r_ti fcla- Looks, titlaityp 13 19 20 b*gia optn. n_order d; LP f-eteh e_#rderid intoJ; cxb t

9、 when carderidOOTFOLIBIl, flr v_QrLarii 囚 0_Q7* Jtri t on LOOP select titie into tie fron books vshereorderi tem. I5BI; DHE_0in?UT. FUr_UME(p_CUitcmei,_id|订购| |v_ti tit | | 的数里是 I Ivrdtri ten. totaliLrn)； nd mop? end LODF; clo3 eerierid, end proo_g*t_ SQL 2 set server output on declure T_cutiiiar nu

10、nL ar: begin _cus tonitr : tac, pi 口暑 t_urd4r E no (v_cii七06 tr ); enl; x m： 3 4； 4: v_cu5toner ：*3_ :QL (3)创建一个存储过程，以订单号为参数，输出该订单中所有图书的名称、单价、数量。 create or replace procedure proc_get_orderi nfoo( p_order_id orderitem.order_id%type) as -声明游标存储订单号的ISBN cursor c_ISBN is select ISBN from orderitem where

11、 order_id=p_order_id; v_ISBN orderitem.ISBN%type; -声明游标存储订单信息 cursor c_orderitem is select ISBN,sum(qua ntity) total num from orderitem where ISBN=v_ISBN ; v_title books.title%type; v_retail books.retail%type; begin open c_ISBN; LOOP fetch c_ISBN into v_ISBN; exit when c_ISBN%NOTFOUND; for v_orderit

12、em in c_orderitem LOOP where select title,retail into v_title,v_retail from books ISBN=v_orderitem.ISBN; DBMS_OUTPUT.PUT_LINE(p_order_id|v_title|v_retail|v_orderitem.totalnum); end LOOP; end LOOP; close c_ISBN; end proc_get_orderinfoo; L, SQL Plus ilxPlus :圭 11.2. 0. 1.0 Product ion on星期三B月 24 16:32

13、:37 2017 7opyright (c) 1982, 2010v Cr v_ti tie bocks, titlefttype：; vretiil books, retail融typ电； b 65111 open g ISBN; uaoF fetch 0 ISBN into v ISBF; xit wh-n c_ISBHWTfOUM : for v_orderi tem iii c_ tegin v_orderi tem ；= wid loop； end WOF; cloe c_ISEJI; find proc_get_ordtrinto； 过程已创建* ISBN、 (4)创建一个存储过程

14、，以出版社名为参数，输出该出版社出版的所有图书的名称、 批发价格、零售价格信息。 create or replace procedure proc get name( p_title books.title%type) as where 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 w

15、here order_id=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_titl

16、e| 的 数 |v_orderitem.totalnum); end LOOP; end LOOP; close c_orderid; end proc_get_orderinfo; / set serveroutput on declare v_customer number; begin v_customer := proc_get_orderi nfo( v_customer); en d; / 4569-01234507|69012 Hill11222 cireite or repine prpr?：_et_n.ine( tit b44kl ti AS. cursar c_ v_4ir

17、i#rid rdAirE. ordiridSttyf-i; curscir cordtritem l5 select ISBlf,. twIq.Ti-uitLtjr) tatalniun Eron orderit:e wLerie order_id=r_rderadl srcujp by ISBH, v_tltie boKx. titleKtypt: 恥禹 opan e ord.4r l d.; LDOF fatcK a.iflirJjwid LiLto .fdhfLd; xit vkeai 0 cixl proo_pterdr mEa: SQL t 4trYrdvtu.t SQLd.4 cl

18、 iu：- Z T_PUEt:nr riiwliftr; 3 4 T_Fustcner =ftic; 5 pr p_C*t_or deri nf 4-(v_cs t m tr S 宅胡： ffiX H的沮： 腳富* v_en5tonsr ：=tc; ffl4. v :nitort4r :总； 当文化殳出的針虫昱工 FLZS0L过程已M三舲 3QL (5)创建一个存储过程，输出每个客户订购的图书的数量、价格总额。 create or replace procedure proc_category_static as cursor c_all_category is select dist inc

19、t 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_cost); END LOOP; end proc_category_sta

20、tic; / set serveroutput on exec proc_category_static; 9 UH. 誓污：削理詢讨转未有舞诵構谡. cnrpsw or rvp prw:edurr iproc：_catflsni7_FttiG 4 El 岀 IPEDLY kfid5fil.LlfiCfT亡日 bOCLkfi. Quitaia邑a bQEn fw vjacLh-s-iteftary an c_all_catPEdT LOOP ssfflwt Funfjxetil) Spin t iMi_cnRt rrn bonkE tiere cats *tw ijutpy. put Ji n

21、i? (丨丨1廿751讯 rv口啊凹 DCi LCCiP. and gxroc_cAtaory_ji：Atlc; ?soryiby Ete?.Qry. 注更已也用. li flAl MLTVAtflLllplll Ctl FU罔L泗S已曲功気成* tjL / 垃程已bjd. I I4：I4 (6)创建一个存储过程，输出销售数量前3名的图书的信息及销售名次。 create or replace procedure proc_category_static as cursor c_all_category is select dist inct category from books; v_sum

22、_retail nu mber; begin for v_each_category in c_all_category LOOP select sum(cost) into v_sum_retail from books where category=v_each_category.category group by category; dbms_output.put_li ne( 种类为：|v_each_category.category. 数量为：| v_sum_retail); END LOOP; end proc_category_static; / set serveroutput

23、 on exec proc category static; ：QL :QL lit iertroiatpXLt mi da-oLti* b gin for Teach, g a.te fry in d.q1L.qbe 耳匸学 LOOP sun (retail) into v suffi_ct tr onvh4r c ad pr七比上人 恿fil格为：11s t). JtJ.rur 切花总 toM鼎 5 -C.S mW -h- zc聲 on ry_5t4tic_ 为：32 :買 仓y建一个存储过程，输出订购图书数量最多的客户的信息及订购图书的数量。 mLqRi 4i*l iofI: *_ fr

24、w n.ETOKIK. Ilimm io c iLL_mm- LMjt* c-hlscfc 丁!1七4* il? ir:* r_m_:isd Ertu ntnUEES vUrii ehmt fehdi mmm 网JiE可户色为： |”泌亠时 r.iM -?： - II V_r-_e-li) 9 EK ZJUF Lfl *Hl 声!HJkf 1遇 P*F 斤 LM4 岖己卫* rlu户户 (8) 仓咙一个存储过程，输出各类图书中销售数量最多的图书的信息及销售的数量。 QL? C?*l HfcK KFr-7E44u, l RM tlljtRiti E* Srnu*tir cBsll si fc*

25、a e keLaeii lu tbaEizt ei fru bibx： 4T_-E*_T4kKil ErmJbar: 电farti i* cBiJlAin t3* iJT xalctixt-a, t rtlsll fr* le-slx ihar* si tlW isk ti tL tiki* KF磚 如 零 dlwi_mi!:p3.?.(188.1 * I |w_a. _ qL： 竝杓 urad 1$ tl*e -丈期1莓曲罩朴ra KiSi阳和曲刃；4： (9) 创建一个包，实现查询客户订购图书详细信息的分页显示。 create or replace procedure proc_title

26、_static as cursor c_all_title is select disti net title from books; v_sum_retail nu mber; begin for v_each_title in c_all_title LOOP selectsum(cost) into v_sum_retail from books where title=v_each_title.title group by title; dbms_output.put_line(信息为：|v_each_title.title，数量为：| v_sum_retail); END LOOP;

27、 end proc_title_static; / 59L create or replace procedure proc_title_st密tic dbrns output, pnt lineC 信息为：| |v_eftch_ti tie. ti tie | |-.埶里为：j v_s-uj_retail): EiTO LOOP; (10) 创建一个包，利用集合实现图书销售排行榜的分页显示。 (11) 创建一个包，包含一个函数和一个过程。函数以图书类型为参数，返回该类型图书 的平均价格。过程输出各种类型图书中价格高于同类型图书平均价格的图书信息。 create or replace pack

28、age pkg_book as function get_book_avgcost(p_book_category BOOKS.category%type) retur n nu mber; procedure pro_showbook(p_book_category BOOKS.category%type); end; / create or replace package body pkg_book as function get_book_avgcost(p_book_category BOOKS.category%type) return number as v_ISBN BOOKS.

29、ISBN%type; cursor c_books is select retail from BOOKS where ISBN=v_ISBN; v_sumcost number(6,2):=0; v_count number(6) :=0; v_avgcost number :=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_count:=v_count+1; v_su

30、mcost:= v_sumcost+v_retail.retail; end LOOP; v_avgcost:=v_sumcost/v_count; DBMS_OUTPUT.PUT_LINE(v_book_category| -|v_avgcost); return v_avgcost; procedure pro_showbook(p_book_category BOOKS.category%type) as v_book_category varchar2(10); cursor c_books is select * from BOOKS where retail=get_book_av

31、gcost(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 serveroutput on declare p_book_category BOOKS.category%type; avgcost number; begin p_book

32、_category:= 管理 ; avgcost:=pkg_book.get_book_avgcost(p_book_category); pkg__showbook( 管理 ); end; 3 辭时已耐 2 titon doLura I_hoo-l_catecMy SDOES cite cor y%tyjt tiLTgCOE t TiUJlLr; bein pjboot_3t 會踽try 苣理； aTrcnnt：tlr boo):. Et book kfocst:(p book oaitcrory): 4_前上 曲*_业川甜HT昔厘L create ar repl-ace-

33、pachaft Lody 卩l_boot 京鼻 funcitioEi gt t_btol_ooc_ntttgory BOOtS. nitagorylityp*：.) v_ho bk_CA.tgary VArchar2(10)!; carsorIjcgJ?5 is select 电 rcm BOOKS There retailL.fetboulkaiTfcost(v_lionk_Gattjory): for T_bas)：3 m c_tocki: lop dbnt JSBB BOOKS.ISBmtjrae； ewrter c_bgJti; iEr* BOOKS wher* ISBSFJSK. v

34、_sjncost nunb*r2)： ; v_c*unt hUA4t (6) T ； *tv，3Cfi= UTifibadr :R; v_bcGk_o-B.tetbtcLw (10J ; 14111 salgc七 ISBN intofrau BClOKS wtere is for veLii 1 i氏LOOP v_aDunt: =v_c Bujn.t+I; v sumco-st：w t sunccst*r .retail, ret-iil. nd war； v n!co e七：=r_sura ast/T aoimt; UME_(JUTPUT. f irr_UHE (*340)t_est tea

35、ry 11 - - lh_auset). r-huril V_4.TJC*E t; end.; (12) 创建一个触发器，当客户下完订单后，自动统计该订单所有图书价格总额。 create or replace package order_total_cost as v_order_id orders.order_id%type; en d; / create or replace trigger trg_before_order before in sert on ORDERS for each row begin order_total_cost.v_order_id:=:n ew.orde

36、r_id; set serveroutput on create or replace trigger trg_order after insert on ORDERitem declare cursor c_orderitem is select ISBN, quantity from orderitem where order_id=order_total_cost.v_order_id; v_ISBN orderitem.ISBN%type; v_quantity orderitem.quantity%type; v_cost books.cost%type; v_sumcost num

37、ber(6,2):=0; begin 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; DBMS_OUTPUT.PUT_LINE(1|v_cost|:|v_orderitem.ISBN); elsif v_orderitem.quantity create or replace tri gger tr g_l：iefore_uriler 2 Lhfore insert on OF

38、UEES 3 fur e ach rijw 4 L e gi n 5 uriler_tot：il_cust. _ijrilHi-_i d： = ： new. ui-ilHr_i d； 6 end; 7 /_ re ate or r epl ：=lc e tr i gger trg_b e ten- p_i：u-der 肖1行出现错误： RA-04089:无法对SYS拥有的对象创建触发器 QL QL 2 set EerYHi-ijutput cm ereate or repiace tri gger trg_order after insert cm OFLDERi tem de cl：=Q_

39、e cm- e or c_or der item i e sei pc t ISBN. qu：=LTLt i ty r um uriler i t erTi wher e df d=ijriler_t o t：dl_cciEt. v_urlt：r_i d; v_ISBN ordHri tem. ISBNfcti e ; v_quant i ty or deri tem. qu：iilt i ty%typ e ； V_C u 51 bo ok 5. CueE； v_sijjticijst niaiTiter: =0 ; Begin f or v_or deri tern in c_or deri

40、 tern LOOP i v_i：10 then Eelect ci?et into v_cuet hrom Loolre where ISBN = v_orderitem. ISBN; rBJiE_Lll_ITFl.n，. Fl.rr_UNE I：? 1 I |v_cr.Et I |J ： I Iv-urderitHm. ISBN); elsi f v_or iler it em. qu：TLt i ty10 then select cost into v_cost from books where ISBN = v_orderitem.ISBN; DBMS_OUTPUT.PUT_LINE(

41、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_sumcost:= v_sumcost+v_orderitem.quantity*v_cost; DBMS_OUT

42、PUT.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_customer number; begin v_customer := v_totalMoney:=get_sumcost(v_customer); dbms_output.put_line(v_customer| 的购买总额是 |v_totalMoney); end; i

43、 SQL Plus 4S 6T0 7 勒訂出曲 J 4 6 6 7 8 -y M 5e i = y 、 n =rr sr aa 勺tr Apl-iu!-* ftrnjek ie(n k_.irtC4i.k( T_JPWlR*F_i卫 MltawfTX pqrVtvnF_! r tturo. nLer E. nirsw c nrdwid ii ftlect wdw_i3 frcm wdtrs rJhere ?wsVora10 clian, 4vk ant* roit frc b*eJksISBV T_flr-dwi te ISK: D3E_QUIPW. PUT_iniE ( 1 -| Iv.co

44、it | k.wAtir l ten. I5E：H：I = alsiftLaii 11 F4*tval i nt t 环皿卜 raahlirra I5S3I w frhr； *n 骂BN*. D3re：_oinpur.FUTJJOffiT ?-| Ir.ovst 1 k_.nrlrLtn.TSHH; 5： vcus tinnsr :=3; : v_cost books. co-stWtype* v_I5BH ordfrritem. ISBMtyp自； v_quant i ty or ler item, qu 直at i电； cursor corderitEin is select ISBH q

45、puantity from orderi te10 slat cost ixitiO y_cci!st DEMS .OUTPUT. PUT.uSeC 1 elsi v_or der it em. quant i ty t cost from books where ISBN = v order! tem. ISBN; DEMS_OUTFUT- FUT_UMC 2 | |字_”毗 | Ivorderitm. ISBS): else BEMS_OUTFUT. FUT.UHEC number of boo3c 辽 error!); end i; v swTicoEt.- v Eumcost+v or

46、der i tem. quant it y*v cost;, DWS_OVTFlTa FOtJLEBE C 3了神*章,| |y_sunCCS； t); 沁 IDOR- end LOOP; clo5G C. _ordleri &; return v_siimcost; end 茸吕um唱os；t; QL set serveroutput on de cl art v_toltalMoney BOOKS. cost%type; _cnstcimer number : begin v_cus t cun 电f : =toc: v_t q t slMflney - -gecost(T_cn3; d.

47、bms_output. ut_l i ne (v_cils t er |的订购总额是 (16) 仓U建一个函数，以出版社名为参数，返回该出版社出版的图书的平均价格。 create or replace function get_pub_avgcost( v_pub_ name publishers .n ame%type) retur n nu mber as v_pub_id publishers.publisher_id%type; cursor c_books is select retail from books where publisher_id=v_pub_id; v_sumco

48、st 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_sumcost+v_retail.retail; DBMS_OUTPUT.PUT_LINE(v_count| -|v_sumcost); end LOOP; return v_sumcost; end get_pub_avgcost

49、; / set serveroutput on declare v_avgMoney BOOKS.cost%type; v_pubname %type; begin v_pubname := v_avgMoney:=get_pub_avgcost(v_pubname); dbms_output.put_line(v_pubname| 的出版图书的平均价格是 |v_avgMoney); end; L-2 345s 7 8901234567 8 90 12 .fl 4a .-MHH _t C*J OJ create or replace fimetioji g：eta

50、rgcost ( 曹 jpub_nme publ i sher 3. nam efttyp s) return number as vjnibi A puLl i shers. puLlisLer_i dfttype; cursor e-books is leltct artnil from books whra publishtr_idvjub_i d; v_Eumcost nuimlftr(6, 2).=0： nujnber(6) : WJ ； begin select publi sher_id intd v_pub_id from publish电ite where najne=jpu

51、b_n DKC.OUTFUT. Pirr_LWI(T_imt | T I |v _5UmCQ5t): LDOF; v_sumcost; end ge： t_pub_av gc * s t. end return 3QL 2 s.et server output on declare y_av|oney BO01S_ cos tintype: v jubnam e piabl i sher 5. n am e%typ t:; b暮 in v_pubn.ame : =fts. v_avloTii Jfcn5_cmtput- put_line (v_juhname|的出版图书的平均们格是 I | a

52、.r_aFgMoiiey); 10 / n入览的值：电子工业出版社 51 v pubnam e : =i8aE ; ubnajn ；: (17)创建一个函数，以客户号为参数，返回该客户可以获得的礼品名称。 create or replace function get_gift( 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; v_orderid orders.o

53、rder_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_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; f

54、or v_orderitem in c_orderitem LOOP if v_orderitem.quantity 10 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

55、_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.quantity*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

56、_customer number; begin v_customer := v_totalMoney:=get_sumcost(v_customer); dbms_output.put_line(v_customer| 的礼物是 |v_totalMoney); end; 社 3 4 F 7 g 5111213141516171619m2122E3242E2fi272e293031艾閃343536373839 create or replace fun丐tioii 富 bgin, opn corderid; DQOF _ fetch c_orderid ir.to v_orderid; 侦it when c_QriridSJOTFOUHD