END LOOP;
end proc_category_static; /
set serveroutput on exec proc_category_static; /
(6) 创建一个存储过程，输出销售数量前3名的图书的信息及销售名次。 create or replace procedure proc_category_static as
cursor c_all_category is select distinct category from books; v_sum_retail number; begin
for v_each_category in c_all_category LOOP select sum(cost) into v_sum_retail from books where category=v_each_category.category group by category;
dbms_output.put_line('种类为：'||v_each_category.category||',数量为：'||
v_sum_retail); END LOOP;
end proc_category_static; /
set serveroutput on
exec proc_category_static;
(7) 创建一个存储过程，输出订购图书数量最多的客户的信息及订购图书的数量。
(8) 创建一个存储过程，输出各类图书中销售数量最多的图书的信息及销售的数量。
(9) 创建一个包，实现查询客户订购图书详细信息的分页显示。 create or replace procedure proc_title_static as
cursor c_all_title is select distinct title from books; v_sum_retail number; begin
for v_each_title in c_all_title LOOP select
sum(cost)
into
v_sum_retail
from
books
where
title=v_each_title.title group by title;
dbms_output.put_line('信息为：'||v_each_title.title||',数量为：'|| v_sum_retail); END LOOP;
end proc_title_static; /
(10) 创建一个包，利用集合实现图书销售排行榜的分页显示。
(11) 创建一个包，包含一个函数和一个过程。函数以图书类型为参数，返回该类型图书的平均价格。过程输出各种类型图书中价格高于同类型图书平均价格的图书信息。 create or replace package pkg_book as
function get_book_avgcost(p_book_category BOOKS.category%type) return number;
procedure pro_showbook(p_book_category BOOKS.category%type); end; /
create or replace package body pkg_book as
function get_book_avgcost(p_book_category BOOKS.category%type) return number as
v_ISBN BOOKS.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); begin
select ISBN into v_ISBN from BOOKS where category=v_book_category; for v_retail in c_books LOOP v_count:=v_count+1;
v_sumcost:= v_sumcost+v_retail.retail; end LOOP;
v_avgcost:=v_sumcost/v_count;
DBMS_OUTPUT.PUT_LINE(v_book_category|| '--'||v_avgcost); return v_avgcost; end;
