(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;
procedure pro_showbook(p_book_category BOOKS.category%type) as
v_book_category varchar2(10);
cursor c_books is select * from BOOKS where retail>=get_book_avgcost(v_book_category); begin
for v_books in c_books loop
dbms_output.put_line(v_books.ISBN||' '||v_books.title||' '||v_books.author||' '||v_books.pubdate||' '||v_books.publisher_id||' '||v_books.retail); end loop; end; end; /
set serveroutput on declare
p_book_category BOOKS.category%type; avgcost number; begin
p_book_category:='管理';
avgcost:=pkg_book.get_book_avgcost(p_book_category); pkg_book.pro_showbook('管理'); end; /
(12) 创建一个触发器，当客户下完订单后，自动统计该订单所有图书价格总额。 create or replace package order_total_cost as
v_order_id orders.order_id%type;
end; /
create or replace trigger trg_before_order before insert on ORDERS for each row begin
order_total_cost.v_order_id:=:new.order_id; end; /
set serveroutput on
create or replace trigger trg_order after insert on ORDERitem declare
cursor c_orderitem is select ISBN, quantity from orderitem where order_id=order_total_cost.v_order_id; v_ISBN orderitem.ISBN%type; v_quantity orderitem.quantity%type; v_cost books.cost%type; v_sumcost number(6,2):=0; begin
for v_orderitem in c_orderitem LOOP if v_orderitem.quantity >10 then
select cost into v_cost from books where ISBN = v_orderitem.ISBN; DBMS_OUTPUT.PUT_LINE('1----'||v_cost||':'||v_orderitem.ISBN); elsif v_orderitem.quantity<=10 then
select retail into v_cost from books where ISBN = v_orderitem.ISBN; DBMS_OUTPUT.PUT_LINE('2----'||v_cost||':'||v_orderitem.ISBN); else
DBMS_OUTPUT.PUT_LINE('number of book is error!'); end if;
v_sumcost:= v_sumcost+v_orderitem.quantity*v_cost;
DBMS_OUTPUT.PUT_LINE('3*****'||'now v_sumcost is'||v_sumcost); end LOOP; end; /
(13) 创建一个触发器，禁止客户在非工作时间（早上8:00之前，晚上17:00之后）下订单。
(14) 创建一个函数，以客户号为参数，返回该客户订购图书的价格总额。 create or replace function get_sumcost(
v_customer_id customers.customer_id%type) return number as
cursor c_orderid is select order_id from orders where customer_id=v_customer_id; v_orderid orders.order_id%type;
cursor c_orderitem is select ISBN, quantity from orderitem where order_id=v_orderid;
v_ISBN orderitem.ISBN%type; v_quantity orderitem.quantity%type;
v_cost books.cost%type;
v_sumcost number(6,2):=0; begin
open c_orderid; LOOP