实验 plsql程序设计

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;