发布时间 : 星期五 文章实验6 PLSQL程序设计更新完毕开始阅读239e0d577a3e0912a21614791711cc7931b778bb
(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; /