实验 plsql程序设计

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

(10) 创建一个包，利用集合实现图书销售排行榜的分页显示。

(11) 创建一个包，包含一个函数和一个过程。函数以图书类型为参数，返回该类型图书的平均价格。过程输出各种类型图书中价格高于同类型图书平均价格的图书信息。 create or replace package pkg_book as

function get_book_avgcost(p_book_category BOOKS.category%type) return number;

procedure pro_showbook(p_book_category BOOKS.category%type); end; /

create or replace package body pkg_book as

function get_book_avgcost(p_book_category BOOKS.category%type) return number as

v_ISBN BOOKS.ISBN%type;

cursor c_books is select retail from BOOKS where ISBN=v_ISBN; v_sumcost number(6,2):=0; v_count number(6) :=0; v_avgcost number :=0; v_book_category varchar2(10); begin

select ISBN into v_ISBN from BOOKS where category=v_book_category; for v_retail in c_books LOOP v_count:=v_count+1;

v_sumcost:= v_sumcost+v_retail.retail; end LOOP;

v_avgcost:=v_sumcost/v_count;

DBMS_OUTPUT.PUT_LINE(v_book_category|| '--'||v_avgcost); return v_avgcost; end;