实验6PLSQL程序设计Word格式文档下载.docx
《实验6PLSQL程序设计Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《实验6PLSQL程序设计Word格式文档下载.docx(24页珍藏版)》请在冰豆网上搜索。
![实验6PLSQL程序设计Word格式文档下载.docx](https://file1.bdocx.com/fileroot1/2022-12/9/2c51269b-965d-459f-a714-0c86b75db722/2c51269b-965d-459f-a714-0c86b75db7221.gif)
||v_each_category.category||'
平均价格为:
||v_avg_cost);
12ENDLOOP;
13endproc_category_static;
14/
(2)创建一个存储过程,以客户号为参数,输出该客户订购的所有图书的名称与数量。
createorreplaceprocedureproc_get_orderinfo(
2p_customer_idcustomers.customer_id%type)
3as
4--声明游标存储客户的订单号
5cursorc_orderidisselectorder_idfromorderswherecustomer_id=p_customer_id;
6v_orderidorders.order_id%type;
7--声明游标存储订单信息
8cursorc_orderitemisselectISBN,sum(quantity)totalnumfromorderitemwhereorder_id=v_orderidgroupbyISBN;
9--保存图书的书名
10v_titlebooks.title%type;
11
12begin
13openc_orderid;
14LOOP
15fetchc_orderidintov_orderid;
16exitwhenc_orderid%NOTFOUND;
17forv_orderiteminc_orderitemLOOP
18selecttitleintov_titlefrombookswhereISBN=v_orderitem.ISBN;
19DBMS_OUTPUT.PUT_LINE(p_customer_id||'
订购'
||v_title||'
的数量是'
||v_orderitem.totalnum);
20endLOOP;
21endLOOP;
22closec_orderid;
23endproc_get_orderinfo;
24/
execproc_get_orderinfoo(1001);
(3)创建一个存储过程,以订单号为参数,输出该订单中所有图书的名称、单价、数量。
createorreplaceprocedureproc_get_orderinfoo(
p_order_idorderitem.order_id%type)
as
--声明游标存储订单号的ISBN
cursorc_ISBNisselectISBNfromorderitemwhereorder_id=p_order_id;
v_ISBNorderitem.ISBN%type;
--声明游标存储订单信息
cursorc_orderitemisselectISBN,sum(quantity)totalnumfromorderitemwhereISBN=v_ISBN;
v_titlebooks.title%type;
v_retailbooks.retail%type;
begin
openc_ISBN;
LOOP
fetchc_ISBNintov_ISBN;
exitwhenc_ISBN%NOTFOUND;
forv_orderiteminc_orderitemLOOP
selecttitle,retailintov_title,v_retailfrombookswhereISBN=v_orderitem.ISBN;
DBMS_OUTPUT.PUT_LINE(p_order_id||v_title||v_retail||v_orderitem.totalnum);
endLOOP;
closec_ISBN;
endproc_get_orderinfoo;
/
(4)创建一个存储过程,以出版社名为参数,输出该出版社出版的所有图书的名称、ISBN、批发价格、零售价格信息。
createorreplaceprocedureproc_get_name(
p_titlebooks.title%type)
cursorc_orderidisselectorder_idfromorderswherecustomer_id=p_customer_id;
v_orderidorders.order_id%type;
cursorc_orderitemisselectISBN,sum(quantity)totalnumfromorderitemwhereorder_id=v_orderidgroupbyISBN;
openc_orderid;
fetchc_orderidintov_orderid;
exitwhenc_orderid%NOTFOUND;
selecttitleintov_titlefrombookswhereISBN=v_orderitem.ISBN;
DBMS_OUTPUT.PUT_LINE(p_customer_id||'
closec_orderid;
endproc_get_orderinfo;
setserveroutputon
declare
v_customernumber;
v_customer:
=&
x;
proc_get_orderinfo(v_customer);
end;
(5)创建一个存储过程,输出每个客户订购的图书的数量、价格总额。
createorreplaceprocedureproc_category_static
cursorc_all_categoryisselectdistinctcategoryfrombooks;
v_sum_costnumber;
forv_each_categoryinc_all_categoryLOOP
selectsum(retail)intov_sum_costfrombookswherecategory=v_each_category.categorygroupbycategory;
dbms_output.put_line('
总价格为:
||v_sum_cost);
ENDLOOP;
endproc_category_static;
execproc_category_static;
(6)创建一个存储过程,输出销售数量前3名的图书的信息及销售名次。
v_sum_retailnumber;
selectsum(cost)intov_sum_retailfrombookswherecategory=v_each_category.categorygroupbycategory;
数量为:
||v_sum_retail);
(7)创建一个存储过程,输出订购图书数量最多的客户的信息及订购图书的数量。
(8)创建一个存储过程,输出各类图书中销售数量最多的图书的信息及销售的数量。
(9)创建一个包,实现查询客户订购图书详细信息的分页显示。
createorreplaceprocedureproc_title_static
cursorc_all_titleisselectdistincttitlefrombooks;
forv_each_titleinc_all_titleLOOP
selectsum(cost)intov_sum_retailfrombookswheretitle=v_each_title.titlegroupbytitle;
信息为:
||v_each_title.title||'
endproc_title_static;
(10)创建一个包,利用集合实现图书销售排行榜的分页显示。
(11)创建一个包,包含一个函数和一个过程。
函数以图书类型为参数,返回该类型图书的平均价格。
过程输出各种类型图书中价格高于同类型图书平均价格的图书信息。
createorreplacepackagepkg_book
functionget_book_avgcost(p_book_categoryBOOKS.category%type)returnnumber;
procedurepro_showbook(p_book_categoryBOOKS.category%type);
createorreplacepackagebodypkg_book
functionget_book_avgcost(p_book_categoryBOOKS.category%type)
returnnumber
as
v_ISBNBOOKS.ISBN%type;
cursorc_booksisselectretailfromBOOKSwhereISBN=v_ISBN;
v_sumcostnumber(6,2):
=0;
v_countnumber(6):
v_avgcostnumber:
v_book_categoryvarchar2(10);
begin
selectISBNintov_ISBNfromBOOKSwherecategory=v_book_category;
forv_retailinc_booksLOOP
v_count:
=v_count+1;
v_sumcost:
=v_sumcost+v_retail.retail;
v_avgcost:
=v_sumcost/v_count;
DBMS_OUTPUT.PUT_LINE(v_book_category||'
--'
||v_avgcost);
returnv_avgcost;
end;
procedurepro_showbook(p_book_categoryBOOKS.category%type)
cursorc_booksisselect*fromBOOKSwhereretail>
=get_book_avgcost(v_book_category);
forv_booksinc_booksloop
dbms_output.put_line(v_books.ISBN||'
'
||v_books.title||'
||v_books.author||'
||v_books.pubdate||'
||v_books.publisher_id||'
||v_books.retail);
endloop;
p_book_categoryBOOKS.category%type;
avgcostnumber;
p_book_category:
='
管理'
;
avgcost:
=pkg_book.get_book_avgcost(p_book_category);
pkg_book.pro_showbook('
);
(12)创建一个触发器,当客户下完订单后,自动统计该订单所有图书价格总额。
createorreplacepackageorder_total_cost
as
v_order_idorders.order_id%type;
createorreplacetriggertrg_before_order
beforeinsertonORDERS
foreachrow
order_total_cost.v_order_id:
=:
new.order_id;
createorreplacetriggertrg_order
afterinsertonORDERitem
cursorc_orderitemisselectISBN,quantityfromorderitemwhereorder_id=order_total_cost.v_order_id;
v_quantityorderitem.quantity%type;
v_costbooks.cost%type;
ifv_orderitem.quantity>
10then
selectcostintov_costfrombookswhereISBN=v_orderitem.ISBN;
DBMS_OUTPUT.PUT_LINE('
1----'
||v_cost||'
:
||v_orderitem.ISBN);
elsifv_orderitem.quantity<
=10then
selectretailintov_costfrombookswhereISBN=v_orderitem.ISBN;
2----'
else
numberofbookiserror!
endif;
=v_sumcost+v_orderitem.quantity*v_cost;
3*****'
||'
nowv_sumcostis'
||v_sumcost);
(13)创建一个触发器,禁止客户在非工作时间(早上8:
00之前,晚上17:
00之后)下订单。
(14)创建一个函数,以客户号为参数,返回该客户订购图书的价格总额。
createorreplacefunctionget_sumcost(
v_customer_idcustomers.customer_id%type)
returnnumber
cursorc_orderidisselectorder_idfromorderswherecustomer_id=v_customer_id;
cursorc_orderitemisselectISBN,quantityfromorderitemwhereorder_id=v_orderid;
||v_cost||v_orderitem.ISBN);
returnv_sumcost;
endget_sumcost;
v_totalMoneyBOOKS.cost%type;
v_totalMoney:
=get_sumcost(v_customer);
dbms_output.put_line(v_customer||'
的购买总额是'
||v_totalMoney);
(15)创建一个函数,以订单号为参数,返回该订单订购图书的价格总额。
(16)创建一个函数,以出版社名为参数,返回该出版社出版的图书的平均价格。
createorreplacefunctionget_pub_avgcost(
v_pub_namepublishers.name%type)
v_pub_idpublishers.publisher_id%type;
cursorc_booksisselectretailfrombookswherepublisher_id=v_pub_id;
selectpublisher_idintov_pub_idfrompublisherswherename=v_pub_name;
DBMS_OUTPUT.PUT_LINE(v_count||'
endget_pub_avgcost;
v_avgMoneyBOOKS.cost%type;
v_pubnamepublishers.name%type;
v_pubname:
v_avgMoney:
=get_pub_avgcost(v_pubname);
dbms_output.put_line(v_pubname||'
的出版图书的平均价格是'
||v_avgMoney);
(17)创建一个函数,以客户号为参数,返回该客户可以获得的礼品名