实验6 PLSQL程序设计

(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