实验6 PL/SQL程序设计1 实验目的(1)掌握PL/SQL程序开发方法。

(2)掌握存储过程、函数、触发器、包的创建于调用。

2 实验要求(1)根据图书销售系统业务要求创建特定的存储过程、函数、触发器。

(2)根据图书销售系统业务要求将图书销售系统相关的函数、存储过程封装到包里。

3 实验步骤以bs用户登录BOOKSALES数据库，利用PL/SQL程序编写下列功能模块。

(1)创建一个存储过程，输出不同类型图书的数量、平均价格。

SQL> create or replace procedure proc_category_static2 as3 --定义游标，获取当前有哪些图书种类4 cursor c_all_category is select distinct category from books;5 --图书的平均价格6 v_avg_cost number;7 begin8 --保存图书种类9 for v_each_category in c_all_category LOOP10 select avg(retail) into v_avg_cost from books where category= group by category;11 ('种类为：'||||',平均价格为：'|| v_avg_cost);12 END LOOP;13 end proc_category_static;14 /(2)创建一个存储过程，以客户号为参数，输出该客户订购的所有图书的名称与数量。

create or replace procedure proc_get_orderinfo(2 p_customer_id %type)3 as4 --声明游标存储客户的订单号5 cursor c_orderid is select order_id from orders where customer_id=p_customer_id;6 v_orderid %type;7 --声明游标存储订单信息8 cursor c_orderitem is select ISBN, sum(quantity) totalnum from orderitem where order_id=v_orderid group by ISBN;9 --保存图书的书名10 v_title %type;1112 begin13 open c_orderid;14 LOOP15 fetch c_orderid into v_orderid;16 exit when c_orderid%NOTFOUND;17 for v_orderitem in c_orderitem LOOP18 select title into v_title from books where ISBN=;19 (p_customer_id||'订购'||v_title||'的数量是'||;20 end LOOP;21 end LOOP;22 close c_orderid;23 end proc_get_orderinfo;24 /exec proc_get_orderinfoo(1001);(3)创建一个存储过程，以订单号为参数，输出该订单中所有图书的名称、单价、数量。

create or replace procedure proc_get_orderinfoo(p_order_id %type)as--声明游标存储订单号的ISBNcursor c_ISBN is select ISBN from orderitem where order_id=p_order_id;v_ISBN %type;--声明游标存储订单信息cursor c_orderitem is select ISBN,sum(quantity) totalnum from orderitem where ISBN=v_ISBN ;v_title %type;v_retail %type;beginopen c_ISBN;LOOPfetch c_ISBN into v_ISBN;exit when c_ISBN%NOTFOUND;for v_orderitem in c_orderitem LOOPselect title,retail into v_title,v_retail from books where ISBN=; (p_order_id||v_title||v_retail||;end LOOP;end LOOP;close c_ISBN;end proc_get_orderinfoo;/(4)创建一个存储过程，以出版社名为参数，输出该出版社出版的所有图书的名称、ISBN、批发价格、零售价格信息。

create or replace procedure proc_get_name(p_title %type)ascursor c_orderid is select order_id from orders where customer_id=p_customer_id;v_orderid %type;cursor c_orderitem is select ISBN, sum(quantity) totalnum from orderitem where order_id=v_orderid group by ISBN;v_title %type;beginopen c_orderid;LOOPfetch c_orderid into v_orderid;exit when c_orderid%NOTFOUND;for v_orderitem in c_orderitem LOOPselect title into v_title from books where ISBN=;(p_customer_id||''||v_title||'的数量是'||;end LOOP;end LOOP;close c_orderid;end proc_get_orderinfo;/set serveroutput ondeclarev_customer number;beginv_customer :=&x;proc_get_orderinfo(v_customer);end;/(5)创建一个存储过程，输出每个客户订购的图书的数量、价格总额。

create or replace procedure proc_category_staticascursor c_all_category is select distinct category from books;v_sum_cost number;beginfor v_each_category in c_all_category LOOPselect sum(retail) into v_sum_cost from books where category= group by category;('种类为：'||||',总价格为：'|| v_sum_cost);END LOOP;end proc_category_static;/set serveroutput onexec proc_category_static;/(6)创建一个存储过程，输出销售数量前3名的图书的信息及销售名次。

create or replace procedure proc_category_staticascursor c_all_category is select distinct category from books;v_sum_retail number;beginfor v_each_category in c_all_category LOOPselect sum(cost) into v_sum_retail from books where category= group by category;('种类为：'||||',数量为：'|| v_sum_retail);END LOOP;end proc_category_static;/set serveroutput onexec proc_category_static;(7)创建一个存储过程，输出订购图书数量最多的客户的信息及订购图书的数量。

(8)创建一个存储过程，输出各类图书中销售数量最多的图书的信息及销售的数量。

(9)创建一个包，实现查询客户订购图书详细信息的分页显示。

create or replace procedure proc_title_staticascursor c_all_title is select distinct title from books;v_sum_retail number;beginfor v_each_title in c_all_title LOOPselect sum(cost) into v_sum_retail from books where title= group by title;('信息为：'||||',数量为：'|| v_sum_retail);END LOOP;end proc_title_static;/(10)创建一个包，利用集合实现图书销售排行榜的分页显示。

(11)创建一个包，包含一个函数和一个过程。

函数以图书类型为参数，返回该类型图书的平均价格。

过程输出各种类型图书中价格高于同类型图书平均价格的图书信息。

create or replace package pkg_bookasfunction get_book_avgcost(p_book_category %type) return number;procedure pro_showbook(p_book_category %type);end;/create or replace package body pkg_bookasfunction get_book_avgcost(p_book_category %type)return numberasv_ISBN %type;cursor c_books is select retail from BOOKS where ISBN=v_ISBN;v_sumcost number(6,2):=0;v_count number(6) :=0;v_avgcost number :=0;v_book_category varchar2(10);beginselect ISBN into v_ISBN from BOOKS where category=v_book_category; for v_retail in c_books LOOPv_count:=v_count+1;v_sumcost:= v_sumcost+;end LOOP;v_avgcost:=v_sumcost/v_count;(v_book_category|| '--'||v_avgcost);return v_avgcost;end;procedure pro_showbook(p_book_category %type) asv_book_category varchar2(10);cursor c_books is select * from BOOKS where retail>=get_book_avgcost(v_book_category);beginfor v_books in c_books loop||' '||||' '||||' '||||' '||||' '||; end loop;end;end;/set serveroutput ondeclarep_book_category %type;avgcost number;beginp_book_category:='管理';avgcost:=(p_book_category); ('管理');end;/(12)创建一个触发器，当客户下完订单后，自动统计该订单所有图书价格总额。