实验6 PLSQL程序设计

(4) 创建一个存储过程，以出版社名为参数，输出该出版社出版的所有图书的名称、ISBN、批发价格、零售价格信息。

create or replace procedure proc_get_name( p_title books.title%type) as

cursor c_orderid is select order_id from orders where customer_id=p_customer_id; v_orderid orders.order_id%type;

cursor c_orderitem is select ISBN, sum(quantity) totalnum from orderitem where order_id=v_orderid group by ISBN; v_title books.title%type;

begin

open c_orderid; LOOP

fetch c_orderid into v_orderid;

exit when c_orderid%NOTFOUND; for v_orderitem in c_orderitem LOOP

select title into v_title from books where ISBN=v_orderitem.ISBN; DBMS_OUTPUT.PUT_LINE(p_customer_id||''||v_title||'的数量是'||v_orderitem.totalnum); end LOOP; end LOOP; close c_orderid; end proc_get_orderinfo; /

set serveroutput on declare v_customer number; begin v_customer :=&x; proc_get_orderinfo(v_customer); end; /

(5) 创建一个存储过程，输出每个客户订购的图书的数量、价格总额。

create or replace procedure proc_category_static as

cursor c_all_category is select distinct category from books; v_sum_cost number; begin

for v_each_category in c_all_category LOOP

select sum(retail) into v_sum_cost from books where category=v_each_category.category group by category;

dbms_output.put_line('种类为：'||v_each_category.category||',总价格为：'|| v_sum_cost); END LOOP;

end proc_category_static; /

set serveroutput on

exec proc_category_static; /

(6) 创建一个存储过程，输出销售数量前3名的图书的信息及销售名次。

create or replace procedure proc_category_static as

cursor c_all_category is select distinct category from books; v_sum_retail number; begin

for v_each_category in c_all_category LOOP

select sum(cost) into v_sum_retail from books where category=v_each_category.category group by category;

dbms_output.put_line('种类为：'||v_each_category.category||',数量为：'|| v_sum_retail); END LOOP;

end proc_category_static; /

set serveroutput on

exec proc_category_static;

(7) 创建一个存储过程，输出订购图书数量最多的客户的信息及订购图书的数量。

(8) 创建一个存储过程，输出各类图书中销售数量最多的图书的信息及销售的数量。

(9) 创建一个包，实现查询客户订购图书详细信息的分页显示。

create or replace procedure proc_title_static as

cursor c_all_title is select distinct title from books; v_sum_retail number; begin

for v_each_title in c_all_title LOOP

select sum(cost) into v_sum_retail from books where title=v_each_title.title group by title; dbms_output.put_line('信息为：'||v_each_title.title||',数量为：'|| v_sum_retail); END LOOP; end proc_title_static; /