实验6 PLSQL程序设计.docx

实验6 PLSQL程序设计.docx

《实验6 PLSQL程序设计.docx》由会员分享，可在线阅读，更多相关《实验6 PLSQL程序设计.docx（24页珍藏版）》请在冰豆网上搜索。

实验6PLSQL程序设计

实验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_costwhere

books

from

category=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）totalnumfromorderitemwhere

order_id=v_orderidgroupbyISBN;

9--保存图书的书名

10v_titlebooks.title%type;

11

12begin

13openc_orderid;

14LOOP

15fetchc_orderidintov_orderid;

16exitwhenc_orderid%NOTFOUND;

17forv_orderiteminc_orderitemLOOP

selecttitleintov_titlefrombookswhereISBN=v_orderitem.ISBN;

18

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）totalnumfromorderitemwhere

ISBN=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）totalnumfromorderitemwhere

order_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.category

groupbycategory;

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.category

groupbycategory;

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

orders.order_id%type;

v_order_id

end;

/

createorreplacetriggertrg_before_order

beforeinsertonORDERS

foreachrow

begin

order_total_cost.v_order_id:

=:

new.order_id;

end;

/

setserveroutputon

createorreplacetriggertrg_order

afterinsertonORDERitem

declare

cursorc_orderitemisselectISBN,quantityfromorderitemwhere

order_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;

DB