实验PLSQL程序设计.docx
《实验PLSQL程序设计.docx》由会员分享，可在线阅读，更多相关《实验PLSQL程序设计.docx（14页珍藏版）》请在冰豆网上搜索。
实验PLSQL程序设计
实验6PL/SQL程序设计
1实验目的
（1）掌握PL/SQL程序开发方法。
（2）掌握存储过程、函数、触发器、包的创建于调用。
2实验要求
（1）根据图书销售系统业务要求创建特定的存储过程、函数、触发器。
（2）根据图书销售系统业务要求将图书销售系统相关的函数、存储过程封装到包里。
3实验步骤
以bs用户登录BOOKSALES数据库，利用PL/SQL程序编写下列功能模块。
（1）创建一个存储过程，输出不同类型图书的数量、平均价格。
SQL>createorreplaceprocedureproc_category_static
2as
3--定义游标，获取当前有哪些图书种类
4cursorc_all_categoryisselectdistinctcategoryfrombooks;
5--图书的平均价格
6v_avg_costnumber;
7begin
8--保存图书种类
9forv_each_categoryinc_all_categoryLOOP
10selectavg（retail）intov_avg_costfrombookswherecategory=v_each_category.categorygroupbycategory;
11dbms_output.put_line（'种类为：
'||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;
endLOOP;
closec_ISBN;
endproc_get_orderinfoo;
/
（4）创建一个存储过程，以出版社名为参数，输出该出版社出版的所有图书的名称、ISBN、批发价格、零售价格信息。
createorreplaceprocedureproc_get_name（
p_titlebooks.title%type）
as
cursorc_orderidisselectorder_idfromorderswherecustomer_id=p_customer_id;
v_orderidorders.order_id%type;
cursorc_orderitemisselectISBN,sum（quantity）totalnumfromorderitemwhereorder_id=v_orderidgroupbyISBN;
v_titlebooks.title%type;
begin
openc_orderid;
LOOP
fetchc_orderidintov_orderid;
exitwhenc_orderid%NOTFOUND;
forv_orderiteminc_orderitemLOOP
selecttitleintov_titlefrombookswhereISBN=v_orderitem.ISBN;
DBMS_OUTPUT.PUT_LINE（p_customer_id||''||v_title||'的数量是'||v_orderitem.totalnum）;
endLOOP;
endLOOP;
closec_orderid;
endproc_get_orderinfo;
/
setserveroutputon
declare
v_customernumber;
begin
v_customer:
=&x;
proc_get_orderinfo（v_customer）;
end;
/
（5）创建一个存储过程，输出每个客户订购的图书的数量、价格总额。
createorreplaceprocedureproc_category_static
as
cursorc_all_categoryisselectdistinctcategoryfrombooks;
v_sum_costnumber;
begin
forv_each_categoryinc_all_categoryLOOP
selectsum（retail）intov_sum_costfrombookswherecategory=v_each_category.categorygroupbycategory;
dbms_output.put_line（'种类为：
'||v_each_category.category||',总价格为：
'||v_sum_cost）;
ENDLOOP;
endproc_category_static;
/
setserveroutputon
execproc_category_static;
/
（6）创建一个存储过程，输出销售数量前3名的图书的信息及销售名次。
createorreplaceprocedureproc_category_static
as
cursorc_all_categoryisselectdistinctcategoryfrombooks;
v_sum_retailnumber;
begin
forv_each_categoryinc_all_categoryLOOP
selectsum（cost）intov_sum_retailfrombookswherecategory=v_each_category.categorygroupbycategory;
dbms_output.put_line（'种类为：
'||v_each_category.category||',数量为：
'||v_sum_retail）;
ENDLOOP;
endproc_category_static;
/
setserveroutputon
execproc_category_static;
（7）创建一个存储过程，输出订购图书数量最多的客户的信息及订购图书的数量。
（8）创建一个存储过程，输出各类图书中销售数量最多的图书的信息及销售的数量。
（9）创建一个包，实现查询客户订购图书详细信息的分页显示。
createorreplaceprocedureproc_title_static
as
cursorc_all_titleisselectdistincttitlefrombooks;
v_sum_retailnumber;
begin
forv_each_titleinc_all_titleLOOP
selectsum（cost）intov_sum_retailfrombookswheretitle=v_each_title.titlegroupbytitle;
dbms_output.put_line（'信息为：
'||v_each_title.title||',数量为：
'||v_sum_retail）;
ENDLOOP;
endproc_title_static;
/
（10）创建一个包，利用集合实现图书销售排行榜的分页显示。
（11）创建一个包，包含一个函数和一个过程。
函数以图书类型为参数，返回该类型图书的平均价格。
过程输出各种类型图书中价格高于同类型图书平均价格的图书信息。
createorreplacepackagepkg_book
as
functionget_book_avgcost（p_book_categoryBOOKS.category%type）returnnumber;
procedurepro_showbook（p_book_categoryBOOKS.category%type）;
end;
/
createorreplacepackagebodypkg_book
as
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）:
=0;
v_avgcostnumber:
=0;
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;
endLOOP;
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）
as
v_book_categoryvarchar2（10）;
cursorc_booksisselect*fromBOOKSwhereretail>=get_book_avgcost（v_book_category）;
begin
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;
end;
end;
/
setserveroutputon
declare
p_book_categoryBOOKS.category%type;
avgcostnumber;
begin
p_book_category:
='管理';
avgcost:
=pkg_book.get_book_avgcost（p_book_category）;
pkg_book.pro_showbook（'管理'）;
end;
/
（12）创建一个触发器，当客户下完订单后，自动统计该订单所有图书价格总额。
createorreplacepackageorder_total_cost
as
v_order_idorders.order_id%type;
end;
/
createorreplacetriggertrg_before_order
beforeinsertonORDERS
foreachrow
begin
order_total_cost.v_order_id:
=:
new.order_id;
end;
/
setserveroutputon
createorreplacetriggertrg_order
afterinsertonORDERitem
declare
cursorc_orderitemisselectISBN,quantityfromorderitemwhereorder_id=order_total_cost.v_order_id;
v_ISBNorderitem.ISBN%type;
v_quantityorderitem.quantity%type;
v_costbooks.cost%type;
v_sumcostnumber（6,2）:
=0;
begin
forv_orderiteminc_orderitemLOOP
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;
DBMS_OUTPUT.PUT_LINE（'2----'||v_cost||':
'||v_orderitem.ISBN）;
else
DBMS_OUTPUT.PUT_LINE（'numberofbookiserror!
'）;
endif;
v_sumcost:
=v_sumcost+v_orderitem.quantity*v_cost;
DBMS_OUTPUT.PUT_LINE（'3*****'||'nowv_sumcostis'||v_sumcost）;
endLOOP;
end;
/
（13）创建一个触发器，禁止客户在非工作时间（早上8:
00之前，晚上17:
00之后）下订单。
（14）创建一个函数，以客户号为参数，返回该客户订购图书的价格总额。
createorreplacefunctionget_sumcost（
v_customer_idcustomers.customer_id%type）
returnnumber
as
cursorc_orderidisselectorder_idfromorderswherecustomer_id=v_customer_id;
v_orderidorders.order_id%type;
cursorc_orderitemisselectISBN,quantityfromorderitemwhereorder_id=v_orderid;
v_ISBNorderitem.ISBN%type;
v_quantityorderitem.quantity%type;
v_costbooks.cost%type;
v_sumcostnumber（6,2）:
=0;
begin
openc_orderid;
LOOP
fetchc_orderidintov_orderid;
exitwhenc_orderid%NOTFOUND;
forv_orderiteminc_orderitemLOOP
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;
DBMS_OUTPUT.PUT_LINE（'2----'||v_cost||v_orderitem.ISBN）;
else
DBMS_OUTPUT.PUT_LINE（'numberofbookiserror!
'）;
endif;
v_sumcost:
=v_sumcost+v_orderitem.quantity*v_cost;
DBMS_OUTPUT.PUT_LINE（'3*****'||v_sumcost）;
endLOOP;
endLOOP;
closec_orderid;
returnv_sumcost;
endget_sumcost;
/
setserveroutputon
declare
v_totalMoneyBOOKS.cost%type;
v_customernumber;
begin
v_customer:
=&x;
v_totalMoney:
=get_sumcost（v_customer）;
dbms_output.put_line（v_customer||'的购买总额是'||v_totalMoney）;
end;
/
（15）创建一个函数，以订单号为参数，返回该订单订购图书的价格总额。
（16）创建一个函数，以出版社名为参数，返回该出版社出版的图书的平均价格。
createorreplacefunctionget_pub_avgcost（
v_pub_namepublishers.name%type）
returnnumber
as
v_pub_idpublishers.publisher_id%type;
cursorc_booksisselectretailfrombookswherepublisher_id=v_pub_id;
v_sumcostnumber（6,2）:
=0;
v_countnumber（6）:
=0;
begin
selectpublisher_idintov_pub_idfrompublisherswherename=v_pub_name;
forv_retailinc_booksLOOP
v_count:
=v_count+1;
v_sumcost:
=v_sumcost+v_retail.retail;
DBMS_OUTPUT.PUT_LINE（v_count||'--'||v_sumcost）;
endLOOP;
returnv_sumcost;
endget_pub_avgcost;
/
setserveroutputon
declare
v_avgMoneyBOOKS.cost%type;
v_pubnamepublishers.name%type;
begin
v_pubname:
=&x;
v_avgMoney:
=get_pub_avgcost（v_pubname）;
dbms_output.put_line（v_pubname||'的出版图书的平均价格是'||v_avgMoney）;
end;
/
（17）创建一个函数，以客户号为参数，返回该客户可以获得的礼品名称。
createorreplacefunctionget_gift（
v_customer_idcustomers.custo
升级会员 