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; /