实验6 PLSQL程序设计

fetch c_orderid into v_orderid;

exit when c_orderid%NOTFOUND; 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*****'||v_sumcost); end LOOP; end LOOP; close c_orderid; return v_sumcost; end get_sumcost; /

set serveroutput on declare v_totalMoney BOOKS.cost%type; v_customer number; begin v_customer :=&x; v_totalMoney:=get_sumcost(v_customer); dbms_output.put_line(v_customer||'的购买总额是'||v_totalMoney); end; /

(15) 创建一个函数，以订单号为参数，返回该订单订购图书的价格总额。

(16) 创建一个函数，以出版社名为参数，返回该出版社出版的图书的平均价格。 create or replace function get_pub_avgcost( v_pub_name publishers.name%type) return number as

v_pub_id publishers.publisher_id%type;

cursor c_books is select retail from books where publisher_id=v_pub_id;

v_sumcost number(6,2):=0;

v_count number(6) :=0; begin

select publisher_id into v_pub_id from publishers where name=v_pub_name; for v_retail in c_books LOOP v_count:=v_count+1;

v_sumcost:= v_sumcost+v_retail.retail;

DBMS_OUTPUT.PUT_LINE(v_count|| '--'||v_sumcost); end LOOP; return v_sumcost; end get_pub_avgcost; /

set serveroutput on declare v_avgMoney BOOKS.cost%type;

v_pubname publishers.name%type; begin v_pubname :=&x; v_avgMoney:=get_pub_avgcost(v_pubname); dbms_output.put_line(v_pubname||'的出版图书的平均价格是'||v_avgMoney); end; /