实验LSQL程序设计.docx
《实验LSQL程序设计.docx》由会员分享,可在线阅读,更多相关《实验LSQL程序设计.docx(24页珍藏版)》请在冰豆网上搜索。
![实验LSQL程序设计.docx](https://file1.bdocx.com/fileroot1/2023-1/3/f6a0cfcd-59f9-4e14-8480-23a42cab91ed/f6a0cfcd-59f9-4e14-8480-23a42cab91ed1.gif)
实验LSQL程序设计
实验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)创建一个函数,以客户号为参数,返回该客户可以获得的礼品名称。