实验6--PLSQL程序设计

山东建筑大学计算机学院实验报告班级：软测143姓名：刘骎学号：20141113089实验成绩：课程：Oracle同组者：实验日期：2017.5.16实验6PL/SQL程序设计1实验目的(1)掌握PL/SQL程序开发方法。(2)掌握存储过程、函数、触发器、包的创建于调用。2实验要求(1)根据图书销售系统业务要求创建特定的存储过程、函数、触发器。(2)根据图书销售系统业务要求将图书销售系统相关的函数、存储过程封装到包里。3实验步骤以bs用户登录BOOKSALES数据库，利用PL/SQL程序编写下列功能模块。(1)创建一个存储过程，输出不同类型图书的数量、平均价格。SQLcreateorreplaceprocedureproc_category_static2as3--定义游标，获取当前有哪些图书种类4cursorc_all_categoryisselectdistinctcategoryfrombooks;5--图书的平均价格6v_avg_costnumber;7begin8--保存图书种类9forv_each_categoryinc_all_categoryLOOP10selectavg(retail)intov_avg_costfrombookswhere山东建筑大学计算机学院实验报告班级：软测143姓名：刘骎学号：20141113089实验成绩：课程：Oracle同组者：实验日期：2017.5.16category=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)3as4--声明游标存储客户的订单号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;1112begin13openc_orderid;14LOOP15fetchc_orderidintov_orderid;山东建筑大学计算机学院实验报告班级：软测143姓名：刘骎学号：20141113089实验成绩：课程：Oracle同组者：实验日期：2017.5.1616exitwhenc_orderid%NOTFOUND;17forv_orderiteminc_orderitemLOOP18selecttitleintov_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--声明游标存储订单号的ISBNcursorc_ISBNisselectISBNfromorderitemwhereorder_id=p_order_id;v_ISBNorderitem.ISBN%type;--声明游标存储订单信息山东建筑大学计算机学院实验报告班级：软测143姓名：刘骎学号：20141113089实验成绩：课程：Oracle同组者：实验日期：2017.5.16cursorc_orderitemisselectISBN,sum(quantity)totalnumfromorderitemwhereISBN=v_ISBN;v_titlebooks.title%type;v_retailbooks.retail%type;beginopenc_ISBN;LOOPfetchc_ISBNintov_ISBN;exitwhenc_ISBN%NOTFOUND;forv_orderiteminc_orderitemLOOPselecttitle,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;/山东建筑大学计算机学院实验报告班级：软测143姓名：刘骎学号：20141113089实验成绩：课程：Oracle同组者：实验日期：2017.5.16(4)创建一个存储过程，以出版社名为参数，输出该出版社出版的所有图书的名称、ISBN、批发价格、零售价格信息。createorreplaceprocedureproc_get_name(p_titlebooks.title%type)ascursorc_orderidisselectorder_idfromorderswherecustomer_id=p_customer_id;v_orderidorders.order_id%type;cursorc_orderitemisselectISBN,sum(quantity)totalnumfromorderitemwhereorder_id=v_orderidgroupbyISBN;v_titlebooks.title%type;beginopenc_orderid;LOOPfetchc_orderidintov_orderid;exitwhenc_orderid%NOTFOUND;forv_orderiteminc_orderitemLOOPselecttitleintov_titlefrombookswhereISBN=v_orderitem.ISBN;DBMS_OUTPUT.PUT_LINE(p_customer_id||''||v_title||'的数量是'||v_orderitem.totalnum);endLOOP;endLOOP;closec_orderid;endproc_get_orderinfo;/setserveroutputondeclarev_customernumber;beginv_customer:=&x;proc_get_orderinfo(v_customer);山东建筑大学计算机学院实验报告班级：软测143姓名：刘骎学号：20141113089实验成绩：课程：Oracle同组者：实验日期：2017.5.16end;/(5)创建一个存储过程，输出每个客户订购的图书的数量、价格总额。createorreplaceprocedureproc_category_staticascursorc_all_categoryisselectdistinctcategoryfrombooks;v_sum_costnumber;beginforv_each_categoryinc_all_categoryLOOPselectsum(retail)intov_sum_costfrombookswherecategory=v_each_category.categorygroupbycategory;dbms_output.put_line('种类为：'||v_each_category.category||',总价格为：'||v_sum_cost);ENDLOOP;endproc_category_static;/setserveroutputonexecproc_category_static;/山东建筑大学计算机学院实验报告班级：软测143姓名：刘骎学号：20141113089实验成绩：课程：Oracle同组者：实验日期：2017.5.16(6)创建一个存储过程，输出销售数量前3名的图书的信息及销售名次。createorreplaceprocedureproc_category_staticascursorc_all_categoryisselectdistinctcategoryfrombooks;v_sum_retailnumber;beginforv_each_categoryinc_all_categoryLOOPselectsum(cost)intov_sum_retailfrombookswherecategory=v_each_category.categorygroupbycategory;dbms_output.put_line('种类为：'||v_each_category.category||',数量为：'||v_sum_retail);ENDLOOP;endproc_category_static;/setserveroutputonexecproc_category_static;山东建筑大学计算机学院实验报告班级：软测143姓名：刘骎学号：20141113089实验成绩：课程：Oracle同组者：实验日期：2017.5.16(7)创建一个存储过程，输出订购图书数量最多的客户的信息及订购图书的数量。(8)创建一个存储过程，输出各类图书中销售数量最多的图书的信息及销售的数量。(9)创建一个包，实现查询客户订购图书详细信息的分页显示。createorreplaceprocedureproc_title_staticascursorc_all_titleisselectdistincttitlefrombooks;v_sum_retailnumber;beginforv_each_titleinc_all_titleLOOPselectsum(cost)intov_sum_retailfrombookswheretitle=v_each_title.titlegroupbytitle;dbms_output.put_line('信息为：'||v_each_title.title||',数量为：'||v_sum_retail);ENDLOOP;山东建筑大学计算机学院实验报告班级：软测143姓名：刘骎学号：20141113089实验成绩：课程：Oracle同组者：实验日期：2017.5.16endproc_title_static;/(10)创建一个包，利用集合实现图书销售排行榜的分页显示。(11)创建一个包，包含一个函数和一个过程。函数以图书类型为参数，返回该类型图书的平均价格。过程输出各种类型图书中价格高于同类型图书平均价格的图书信息。createorreplacepackagepkg_bookasfunctionget_book_avgcost(p_book_categoryBOOKS.category%type)returnnumber;procedurepro_showb