实验6PLSQL程序设计

文档格式：DOC| 21 页|大小 1.03MB|积分 10|2022-03-21 发布|文档ID：64603295

下载文档

下载文档到电脑，查找使用更方便还剩页未读，继续阅读>>

侵权申诉举报

1 / 21

此文档下载收益归作者所有下载文档

版权提示

文本预览

常见问题

实验 6 PL/SQL程序设计1 实验目的(1) 掌握 PL/SQL程序开发方法2) 掌握存储过程、函数、触发器、包的创建于调用2 实验要求(1) 根据图书销售系统业务要求创建特定的存储过程、函数、触发器2) 根据图书销售系统业务要求将图书销售系统相关的函数、存储过程封装到包里3 实验步骤以 bs 用户登录 BOOKSALES数据库，利用 PL/SQL程序编写下列功能模块1) 创建一个存储过程，输出不同类型图书的数量、平均价格SQL> create or replace procedure proc_category_static2 as3 --定义游标，获取当前有哪些图书种类4 cursor c_all_category is select distinct category from books;5 --图书的平均价格6 v_avg_cost number;7 begin8 --保存图书种类9for v_each_category in c_all_category LOOP10select avg(retail)into v_avg_cost frombooks wherecategory=v_each_category.category group by category;11dbms_output.put_line(' 种类为： '||v_each_category.category||',平均价格为： '||精选文库v_avg_cost);12 END LOOP;13 end proc_category_static;14 /(2) 创建一个存储过程，以客户号为参数，输出该客户订购的所有图书的名称与数量。

create or replace procedure proc_get_orderinfo(2 p_customer_id customers.customer_id%type)3 as4 --声明游标存储客户的订单号5 cursor c_orderid is select order_id from orders where customer_id=p_customer_id;6 v_orderid orders.order_id%type;7 --声明游标存储订单信息8 cursor c_orderitem is select ISBN, sum(quantity) totalnum from orderitem where order_id=v_orderid group by ISBN;9 --保存图书的书名10 v_title books.title%type;1112 begin13 open c_orderid;14 LOOP15fetch c_orderid into v_orderid;16exit when c_orderid%NOTFOUND;17for v_orderitem in c_orderitemLOOP18select title into v_title from books where ISBN=v_orderitem.ISBN;19DBMS_OUTPUT.PUT_LINE(p_customer_id||' 订购 '||v_title||'的数量是— 2精选文库'||v_orderitem.totalnum);20 end LOOP;21 end LOOP;22 close c_orderid;23 end proc_get_orderinfo;24 /exec proc_get_orderinfoo(1001);(3) 创建一个存储过程，以订单号为参数，输出该订单中所有图书的名称、单价、数量。

create or replace procedure proc_get_orderinfoo(p_order_id orderitem.order_id%type)as--声明游标存储订单号的 ISBNcursor c_ISBN is select ISBN from orderitem where order_id=p_order_id; v_ISBN orderitem.ISBN%type;--声明游标存储订单信息cursor c_orderitem is select ISBN,sum(quantity) totalnum from orderitem where ISBN=v_ISBN ;v_title books.title%type;v_retail books.retail%type;beginopen c_ISBN;— 3精选文库LOOPfetch c_ISBN into v_ISBN;exit when c_ISBN%NOTFOUND;for v_orderitem in c_orderitem LOOPselect title,retail into v_title,v_retail from books where ISBN=v_orderitem.ISBN; DBMS_OUTPUT.PUT_LINE(p_order_id||v_title||v_retail||v_orderitem.totalnum);end LOOP;end LOOP;close c_ISBN;end proc_get_orderinfoo;/— 4精选文库(4) 创建一个存储过程，以出版社名为参数，输出该出版社出版的所有图书的名称、 ISBN、批发价格、零售价格信息。

create or replace procedure proc_get_name(p_title books.title%type)ascursor 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 whereorder_id=v_orderid group by ISBN;v_title books.title%type;beginopen c_orderid;LOOPfetch c_orderid into v_orderid;exit when c_orderid%NOTFOUND;for v_orderitem in c_orderitem LOOPselect 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 ondeclarev_customer number;beginv_customer :=&x;proc_get_orderinfo(v_customer);end;/— 5精选文库(5) 创建一个存储过程，输出每个客户订购的图书的数量、价格总额。

create or replace procedure proc_category_staticascursor c_all_category is select distinct category from books;v_sum_cost number;beginfor v_each_category in c_all_category LOOPselect 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 onexec proc_category_static;/— 6精选文库(6) 创建一个存储过程，输出销售数量前 3 名的图书的信息及销售名次create or replace procedure proc_category_staticascursor c_all_category is select distinct category from books;v_sum_retail number;beginfor v_each_category in c_all_category LOOPselect 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 onexec proc_category_static;— 7精选文库(7) 创建一个存储过程，输出订购图书数量最多的客户的信息及订购图书的数量。

8) 创建一个存储过程，输出各类图书中销售数量最多的图书的信息及销售的数量9) 创建一个包，实现查询客户订购图书详细信息的分页显示create or replace procedure proc_title_staticascursor c_all_title is select distinct title from books;v_sum_retail number;beginfor v_each_title in c_all_title LOOPselect sum(cost) into v_sum_retail from books where title=v_each_title.title group bytitle;dbms_output.put_line(' 信息为： '||v_each_title.title||', 数量为： '|| v_sum_retail);END LOOP;end proc_title_static;/— 8精选文库(10) 创建一个包，利用集合实现图书销售排行榜的分页显示11) 创建一个包，包含一个函数和一个过程函数以图书类型为参数，返回该类型图书的平均价格。

过程输出各种类型图书中价格高于同类型图书平均价格的图书信息create or replace package pkg_bookasfunction 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_bookasfunction get_book_avgcost(p_book_category BOOKS.category%type)return numberasv_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);beginselect ISBN into v_ISBN from BOOKS where category=v_book_category; for v_retail in c_books LOOPv_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;procedure pro_showbook(p_book_category BOOKS.category%type)asv_book_category varchar2(10);— 9精选文库cursor c_books is select * from BOOKS where retail>=get_book_avgcost(v_book_category); beginfor v_books in c_books loopdbms_output.put_line(v_books.ISBN||' '||v_books.title||' '||v_books.author||''||v_books.pubdate||' '||v_books.publisher_id||' '||v_books.retail); end loop;end;end;/set serveroutput ondeclarep_book_category BOOKS.category%type;avgcost number;beginp_book_category:=' 管理 ';avgcost:=pkg_book.get_book_avgcost(p_book_category);pkg_book.pro_showbook(' 管理 ');end;/(12) 创建一个触发器，当客户下完订单后，自动统计该订单所有图书价格总额。

create or replace package order_total_costasv_order_id orders.order_id%type;— 10精选文库end;/create or replace trigger trg_before_orderbefore insert on ORDERSfor each rowbeginorder_total_cost.v_order_id:=:new.order_id;end;/set serveroutput oncreate or replace trigger trg_orderafter insert on ORDERitemdeclarecursor c_orderitem is select ISBN, quantity from orderitem where order_id=order_total_cost.v_order_id;v_ISBN orderitem.ISBN%type;v_quantity orderitem.quantity%type;v_cost books.cost%type;v_sumcost number(6,2):=0;beginfor v_orderitem in c_orderitem LOOPif v_orderitem.quantity >10 thenselect 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 thenselect retail into v_cost from books where ISBN = v_orderitem.ISBN; DBMS_OUTPUT.PUT_LINE('2----'||v_cost||':'||v_orderitem.ISBN);elseDBMS_OUTPUT.PUT_LINE('number of book is error!');end if;v_sumcost:= v_sumcost+v_orderitem.quantity*v_cost;DBMS_OUTPUT.PUT_LINE('3*****'||'now v_sumcost is'||v_sumcost); end LOOP;end;/— 11精选文库(13) 创建一个触发器，禁止客户在非工作时间（早上 8:00 之前，晚上 17:00 之后）下订单。

14) 创建一个函数，以客户号为参数，返回该客户订购图书的价格总额create or replace function get_sumcost(v_customer_id customers.customer_id%type)return numberascursor c_orderid is select order_id from orders where customer_id=v_customer_id; v_orderid orders.order_id%type;cursor c_orderitem is select ISBN, quantity from orderitem where order_id=v_orderid;v_ISBN orderitem.ISBN%type;v_quantity orderitem.quantity%type;v_cost books.cost%type;v_sumcost number(6,2):=0;beginopen c_orderid;LOOP— 12精选文库fetch c_orderid into v_orderid;exit when c_orderid%NOTFOUND;for v_orderitem in c_orderitem LOOPif v_orderitem.quantity >10 thenselect 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 thenselect retail into v_cost from books where ISBN = v_orderitem.ISBN; DBMS_OUTPUT.PUT_LINE('2----'||v_cost||v_orderitem.ISBN);elseDBMS_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 ondeclarev_totalMoney BOOKS.cost%type;v_customer number;beginv_customer :=&x;v_totalMoney:=get_sumcost(v_customer);dbms_output.put_line(v_customer||' 的购买总额是 '||v_totalMoney);end;/— 13精选文库(15) 创建一个函数，以订单号为参数，返回该订单订购图书的价格总额。

— 14精选文库(16) 创建一个函数，以出版社名为参数，返回该出版社出版的图书的平均价格create or replace function get_pub_avgcost(v_pub_name publishers.name%type)return numberasv_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;— 15精选文库v_count number(6) :=0;beginselect publisher_id into v_pub_id from publishers where name=v_pub_name; for v_retail in c_books LOOPv_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 ondeclarev_avgMoney BOOKS.cost%type;v_pubname publishers.name%type;beginv_pubname :=&x;v_avgMoney:=get_pub_avgcost(v_pubname);dbms_output.put_line(v_pubname||' 的出版图书的平均价格是 '||v_avgMoney);end;/— 16精选文库(17) 创建一个函数，以客户号为参数，返回该客户可以获得的礼品名称。

create or replace function get_gift(v_customer_id customers.customer_id%type)return numberascursor c_orderid is select order_id from orders where customer_id=v_customer_id; v_orderid orders.order_id%type;cursor c_orderitem is select ISBN, quantity from orderitem where order_id=v_orderid;v_ISBN orderitem.ISBN%type;v_quantity orderitem.quantity%type;v_cost books.cost%type;v_gift number(6,2):=0;beginopen c_orderid;LOOPfetch c_orderid into v_orderid;— 17精选文库exit when c_orderid%NOTFOUND;for v_orderitem in c_orderitem LOOPif v_orderitem.quantity >10 thenselect 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 thenselect retail into v_cost from books where ISBN = v_orderitem.ISBN; DBMS_OUTPUT.PUT_LINE('2----'||v_cost||v_orderitem.ISBN);elseDBMS_OUTPUT.PUT_LINE('number of book is error!');end if;v_gift:= v_gift+v_orderitem.quantity*v_cost;DBMS_OUTPUT.PUT_LINE('3*****'||v_gift);end LOOP;end LOOP;close c_orderid;return v_gift;end get_gift;/set serveroutput ondeclarev_totalMoney BOOKS.cost%type;v_customer number;beginv_customer :=&x;v_totalMoney:=get_sumcost(v_customer);dbms_output.put_line(v_customer||' 的礼物是 '||v_totalMoney);end;/— 18精选文库(18) 创建一个函数，以图书号为参数，统计该图书被订购的总数量。

create or replace function get_sumnum(v_customer_id customers.customer_id%type)return numberascursor c_orderid is select order_id from orders where customer_id=v_customer_id; v_orderid orders.order_id%type;cursor c_orderitem is select ISBN, quantity from orderitem where order_id=v_orderid;v_ISBN orderitem.ISBN%type;v_quantity orderitem.quantity%type;— 19精选文库v_retail books.retail%type;v_sumnum number(6,2):=0;beginopen c_orderid;LOOPfetch c_orderid into v_orderid;exit when c_orderid%NOTFOUND;for v_orderitem in c_orderitem LOOPif v_orderitem.quantity >10 thenselect retail into v_retail from books where ISBN = v_orderitem.ISBN; DBMS_OUTPUT.PUT_LINE('1----'||v_retail||v_orderitem.ISBN);elsif v_orderitem.quantity<=10 thenselect retail into v_retail from books where ISBN = v_orderitem.ISBN; DBMS_OUTPUT.PUT_LINE('2----'||v_retail||v_orderitem.ISBN);elseDBMS_OUTPUT.PUT_LINE('number of book is error!');end if;v_sumnum:= v_sumnum+v_orderitem.quantity*v_retail;DBMS_OUTPUT.PUT_LINE('3*****'||v_sumnum);end LOOP;end LOOP;close c_orderid;return v_sumnum;end get_sumnum;/set serveroutput ondeclarev_totalMoney BOOKS.cost%type;v_customer number;beginv_customer :=&x;v_totalMoney:=get_sumnum(v_customer);dbms_output.put_line(v_customer||' 的数量是 '||v_totalMoney);end;/— 20精选文库4 实验总结本次实验难度大，代码多，锻炼了我的代码的书写能力和逻辑思维能力，实验过程遇到许多的困难，通过上网查询和求助同学最终将大部分问题解决。

点击阅读更多内容