ImageVerifierCode 换一换
格式:DOCX , 页数:24 ,大小:364.50KB ,
资源ID:4822061      下载积分:3 金币
快捷下载
登录下载
邮箱/手机:
温馨提示:
快捷下载时,用户名和密码都是您填写的邮箱或者手机号,方便查询和重复下载(系统自动生成)。 如填写123,账号就是123,密码也是123。
特别说明:
请自助下载,系统不会自动发送文件的哦; 如果您已付费,想二次下载,请登录后访问:我的下载记录
支付方式: 支付宝    微信支付   
验证码:   换一换

加入VIP,免费下载
 

温馨提示:由于个人手机设置不同,如果发现不能下载,请复制以下地址【https://www.bdocx.com/down/4822061.html】到电脑端继续下载(重复下载不扣费)。

已注册用户请登录:
账号:
密码:
验证码:   换一换
  忘记密码?
三方登录: 微信登录   QQ登录  

下载须知

1: 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。
2: 试题试卷类文档,如果标题没有明确说明有答案则都视为没有答案,请知晓。
3: 文件的所有权益归上传用户所有。
4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
5. 本站仅提供交流平台,并不能对任何下载内容负责。
6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

版权提示 | 免责声明

本文(实验6PLSQL程序设计.docx)为本站会员(b****5)主动上传,冰豆网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对上载内容本身不做任何修改或编辑。 若此文所含内容侵犯了您的版权或隐私,请立即通知冰豆网(发送邮件至service@bdocx.com或直接QQ联系客服),我们立即给予删除!

实验6PLSQL程序设计.docx

1、实验6PLSQL程序设计实验6 PL/SQL程序设计1 实验目的(1) 掌握PL/SQL程序开发方法。(2) 掌握存储过程、函数、触发器、包的创建于调用。2 实验要求(1) 根据图书销售系统业务要求创建特定的存储过程、函数、触发器。(2) 根据图书销售系统业务要求将图书销售系统相关的函数、存储过程封装到包里。3 实验步骤以bs用户登录BOOKSALES数据库,利用PL/SQL程序编写下列功能模块。(1) 创建一个存储过程,输出不同类型图书的数量、平均价格。SQL create or replace procedure proc_category_static 2 as 3 -定义游标,获取当前

2、有哪些图书种类 4 cursor c_all_category is select distinct category from books; 5 -图书的平均价格 6 v_avg_cost number; 7 begin 8 -保存图书种类 9 for v_each_category in c_all_category LOOP 10 select avg(retail) into v_avg_cost from books where category=v_each_category.category group by category; 11 dbms_output.put_line(种

3、类为:|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 as 4 -声明游标存储客户的订单号 5 cursor c_orderid is select order_id from orders

4、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; 11 12 begin 13 open c_orderid; 14 LOOP 15 fetch c_orderid into v

5、_orderid; 16 exit when c_orderid%NOTFOUND; 17 for v_orderitem in c_orderitem LOOP 18 select title into v_title from books where ISBN=v_orderitem.ISBN; 19 DBMS_OUTPUT.PUT_LINE(p_customer_id|订购|v_title|的数量是|v_orderitem.totalnum); 20 end LOOP; 21 end LOOP; 22 close c_orderid; 23 end proc_get_orderinfo;

6、 24 /exec proc_get_orderinfoo(1001);(3) 创建一个存储过程,以订单号为参数,输出该订单中所有图书的名称、单价、数量。create or replace procedure proc_get_orderinfoo( p_order_id orderitem.order_id%type)as -声明游标存储订单号的ISBN cursor c_ISBN is select ISBN from orderitem where order_id=p_order_id; v_ISBN orderitem.ISBN%type; -声明游标存储订单信息 cursor c_

7、orderitem is select ISBN,sum(quantity) totalnum from orderitem where ISBN=v_ISBN ; v_title books.title%type; v_retail books.retail%type;begin open c_ISBN; LOOP fetch c_ISBN into v_ISBN; exit when c_ISBN%NOTFOUND; for v_orderitem in c_orderitem LOOP select title,retail into v_title,v_retail from book

8、s 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) 创建一个存储过程,以出版社名为参数,输出该出版社出版的所有图书的名称、ISBN、批发价格、零售价格信息。create or replace procedure proc_get_name( p_title books.title%type)as cursor c_ord

9、erid 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; ex

10、it 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 ondeclare v_customer n

11、umber;begin v_customer :=&x; proc_get_orderinfo(v_customer);end;/(5) 创建一个存储过程,输出每个客户订购的图书的数量、价格总额。create or replace procedure proc_category_staticas 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) int

12、o 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) 创建一个存储过程,输出销售数量前3名的图书的信息及销售名次。create or replace procedure pr

13、oc_category_staticas 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.c

14、ategory|,数量为:| v_sum_retail); END LOOP;end proc_category_static;/set serveroutput onexec proc_category_static; (7) 创建一个存储过程,输出订购图书数量最多的客户的信息及订购图书的数量。(8) 创建一个存储过程,输出各类图书中销售数量最多的图书的信息及销售的数量。(9) 创建一个包,实现查询客户订购图书详细信息的分页显示。create or replace procedure proc_title_staticas cursor c_all_title is select disti

15、nct 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) 创建一个包,利用集合实现图书销售排行

16、榜的分页显示。(11) 创建一个包,包含一个函数和一个过程。函数以图书类型为参数,返回该类型图书的平均价格。过程输出各种类型图书中价格高于同类型图书平均价格的图书信息。create or replace package pkg_bookas 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_b

17、ookas 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_IS

18、BN 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; procedure pro_showbook(p_book_category BOOKS.catego

19、ry%type) as v_book_category varchar2(10); cursor c_books is select * from BOOKS where retail=get_book_avgcost(v_book_category); begin for v_books in c_books loop dbms_output.put_line(v_books.ISBN| |v_books.title| |v_books.author| |v_books.pubdate| |v_books.publisher_id| |v_books.retail); end loop; e

20、nd;end;/set serveroutput ondeclare p_book_category BOOKS.category%type; avgcost number;begin p_book_category:=管理; avgcost:=pkg_book.get_book_avgcost(p_book_category); pkg_book.pro_showbook(管理);end;/(12) 创建一个触发器,当客户下完订单后,自动统计该订单所有图书价格总额。create or replace package order_total_costas v_order_id orders.o

21、rder_id%type;end;/create or replace trigger trg_before_orderbefore insert on ORDERS for each rowbegin order_total_cost.v_order_id:=:new.order_id;end;/set serveroutput oncreate or replace trigger trg_orderafter insert on ORDERitem declare cursor c_orderitem is select ISBN, quantity from orderitem whe

22、re 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;begin 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_O

23、UTPUT.PUT_LINE(1-|v_cost|:|v_orderitem.ISBN); elsif v_orderitem.quantity10 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; D

24、BMS_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 ondeclare v_tot

25、alMoney 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 publishe

26、rs.name%type)return numberas 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_co

27、unt:=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 ondeclare 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;/(17) 创建一个函数,以客户号为参数,返回该客户可以获得的礼品名

copyright@ 2008-2022 冰豆网网站版权所有

经营许可证编号:鄂ICP备2022015515号-1