oracle书店图书销售管理数据库.doc-道客多多

资源描述

1、中北大学软件学院Oracle 作业姓名：高田田学号：1221010516班级：12210A02设计题目：书店图书销售系统指导老师：孔祥艳目录一、项目背景 1二、系统用表 11. 藏书信息表(book) 12. 进货表(stock) 23. 出货表(sold) .34. 用户表(users) 3三、数据初始化 41. 藏书信息表插入数据 .42. 进货表插入数据 .53. 出货表插入数据 .64. 用户表插入数据 .7四、索引 7五、视图 81.进货信息视图 (stock_infor)82. 出货信息视图 (sold_infor)83.盈利视图(profit) .9六、过程 1

2、0七、函数 13八、包 14九、用户创建 191.创建用户 .192.创建角色 .193.授予角色权限 .19十、复杂功能实现 20十一、 Oracle 体系结构 24附录： 26第 0 页共 26 页书店图书销售系统1、项目背景随着信息时代的到来，IT 产业和 Internet 获得了飞速发展，计算机应用已渗透到了各个领域，网络应用越来越普遍，而且走进了千家万户，一直使用手工记账的小镇书店老板，也想购买一个图书销售系统，但想先体验一下计算机的快捷方便。因此，设计了如下测试用例，供其初步体验！2、系统用表系统用表共四张，分别为：藏书信息表(book)、进货表(stock)、出货表(s

3、old) 、用户表(users)。藏书信息表：用来存放书店现有图书的信息。进货表：用来存放书店进货信息。出货表：用来存放书店卖出的图书信息。用户表：用来存放书店员工信息。建表：1. 藏书信息表(book)藏书信息表(book) 表 1建表语句：create table book ( bookno varchar2(6) primary key,bookname varchar2(20) unique,列名数据类型长度描述bookno varchar2 6 图书编号，定义为主键bookname varchar2 20 书名，唯一species varchar2 5 图书分类，不允许为空

4、bookprice number (7,2) 图书单价，不允许为空publish_house varchar2 20 出版社，不允许为空publishdate date 出版日期，不允许为空author varchar2 10 作者，不允许为空number number 4 藏书量，大于 0第 1 页共 26 页species varchar2(5) not null,bookprice number(7,2) not null,publish_house varchar2(20),publishdate date,author varchar2(10) not null,numb

5、ers number check(numbers=0) );修改表结构：alter table book modify species varchar2(15);alter table book modify bookname varchar2(20);alter table book modify numbers number(4);2. 进货表(stock)进货表(stock) 表 2列名数据类型长度描述iono varchar2 6 进货编号，定义为主键bookno varchar2 6 图书编号，定义为外键numbers number 进货量，大于等于 0in_price nu

6、mber 进价，不能为空in_time date 进货时间，不能为空建表语句：create table stock(iono varchar2(6) primary key,bookno varchar2(6),numbers number check(numbers=0),in_price number not null,in_time date not null,第 2 页共 26 页constraint fk_stock foreign key(bookno) references book(bookno);3.出货表(sold)出货表(sold) 表 3列名数据类型长度描述oo

7、no varchar2 6 出货编号，定义为主键bookno varchar2 6 图书编号，定义为外键numbers number 出货量，大于等于 0out_price number 售卖价格，不能为空out_time date 售卖时间，不能为空建表语句：create table sold(oono varchar2(6) primary key,bookno varchar2(6),numbers number check(numbers=0),out_price number not null,out_time date not null,constraint fk_sold fo

8、reign key(bookno) references book(bookno);4.用户表(users)用户表(users) 表 4列名数据类型长度描述username varchar2 10 用户名，定义为主键password varchar2 15 密码，不允许为空sal number 月薪，可以为空comm number 奖金，可以为空remark varchar2 15 用户类型，允许为空建表语句：第 3 页共 26 页create table users(username varchar2(10) primary key,password varchar2(15) no

9、t null,sal number,comm number,remark varchar2(15);3、数据初始化1.藏书信息表插入数据-insert 语句插入insert into book values(001010,细说 PHP,IT,109.00,电子工业出版社,to_date(2013-04,yyyy-mm),高洛峰,50);-替代变量插入- 语句保存到 d:oraclereplace_sqlinsert_book.sqlaccept v_bookno prompt 编号(六位数字)：accept v_bookname prompt 书名书名：accept v_species pro

10、mpt 种类：accept v_bookprice prompt 价格：accept v_ph prompt 出版社： accept v_pt prompt 出版日期（ yyyy-mm）：accept v_author prompt 作者：accept v_numbers prompt 库存量（=0）：insert into boss.book values(第 4 页共 26 页执行：start d:oraclereplace_sqlinsert_book.sql2.进货表插入数据-insert 语句插入insert into stock values(000001,001001,3,15

11、.00,to_date(2014-11-07,yyyy-mm-dd);-替代变量插入- 语句保存到 d:oraclereplace_sqlinsert_stock.sqlaccept v_iono prompt 进货编号( 六位数字)：accept v_bookno prompt 图书编号(六位数字) ：accept v_numbers prompt 进货数量：accept v_in_price prompt 进货价格：accept v_in_time prompt 进货日期(yyyy-mm-dd)：insert into stock values(执行：start d:oraclerepla

12、ce_sqlinsert_stock.sql第 5 页共 26 页3.出货表插入数据-insert 语句插入insert into sold values(000001,001002,1,25.80,to_date(2014-11-07,yyyy-mm-dd);-替代变量插入- 语句保存到 d:oraclereplace_sqlinsert_sold.sqlaccept v_oono prompt 出货编号(六位数字)： accept v_bookno prompt 图书编号(六位数字) ：accept v_numbers prompt 售卖数量：accept v_in_price prom

13、pt 售卖价格：accept v_in_time prompt 售卖日期(yyyy-mm-dd)：insert into sold values(执行：start d:oraclereplace_sqlinsert_sold.sql第 6 页共 26 页4.用户表插入数据-insert 语句插入insert into users values(boss,boss,admin);-替代变量插入- 语句保存到 d:oraclereplace_sqlinsert_users.sqlaccept v_name prompt 用户名：accept v_pword prompt 密码：accept v_

14、sal prompt 月薪： accept v_comm prompt 奖金：accept v_remark prompt 备注：insert into users values(执行：start d:oraclereplace_sqlinsert_users.sql4、索引1.由于对 book 表的图书的分类查找会比较频繁，所以对 book 表的 species 列建索引create index book_species_idx on book(species);2.由于对 stock 表的进货量，进价，进货时间的查询会比较频繁，所以对 stock 表的numbers，in_price，i

15、n_time 列建索引create index stock_num_ip_idx on stock(numbers,in_price);create index stock_num_it_idx on stock(numbers,in_time);第 7 页共 26 页3.由于对 sold 表的进货量，进价，进货时间的查询会比较频繁，所以对 sold 表的numbers，out_price，out_time 列建索引create index sold_num_op_idx on sold(numbers,out_price);create index sold_num_ot_idx on s

16、old(numbers,out_time);5、视图视图共三个，分别为：进货信息视图(stock_infor)、出货信息视图(sold_infor)、盈利视图(profit)。进货信息视图：由藏书信息表和进货表整合。包括进货编号，图书编号，书名，类别，进价，数量，总进价。出货信息视图：由藏书信息表和出货表整合。包括出货编号，图书编号，书名，类别，卖价，售卖数量，总价，售卖日期。盈利视图：由进货表和出货表整合。包括图书编号，进价，卖价，数量，盈利，售卖日期。1.进货信息视图 (stock_infor)建视图语句：create view stock_infor asselect s.iono“进

17、货编号“,b.bookno“ 图书编号“,b.bookname“书名 “,b.species“类别“,s.in_price“进价“,s.numbers“数量“,s.in_price*s.numbers“总价“,s.in_time“ 进货日期 “from book b,stock swhere b.bookno=s.bookno;2.出货信息视图 (sold_infor)建视图语句：第 8 页共 26 页create view sold_infor as select s.oono“出货编号“,b.bookno“图书编号“,b.bookname“书名“,b.species“类别“,s.out

18、_price“卖价“,s.numbers“售卖数量 “,s.out_price*s.numbers“总价“,s.out_time“售卖日期“ from book b,sold s where b.bookno=s.bookno;3.盈利视图(profit)建视图语句：create view profitasselect so.bookno“图书编号“,st.in_price“ 进价 “,so.out_price“卖价“,so.numbers“数量“,(so.out_price-st.in_price)*so.numbers)“盈利“,so.out_time“售卖日期“from (select

19、distinct bookno,in_price from stock) st,sold sowhere st.bookno=so.booknoorder by so.out_time,so.bookno;第 9 页共 26 页6、过程1.输入（图书编号，增/减，数量）实现增减库存 -条件选择create or replace procedure xgkc(bno varchar2,sign char,num number)isv_bno boss.book.bookno%type;beginselect bookno into v_bno from boss.book where boo

20、kno=bno;if sign=+ thenupdate boss.book set numbers=numbers+num where bookno=v_bno;dbms_output.put_line(v_bno|号图书成功增加库存 |num);elsif sign=- thenupdate boss.book set numbers=numbers-num where bookno=v_bno;dbms_output.put_line(v_bno|号图书成功减少库存 |num);elsedbms_output.put_line(第二个参数只能为+/-);end if;exceptionw

21、hen no_data_found thendbms_output.put_line(库存表中不存在该图书，请修改图书编号或增加该图书信息到库存中);end;/执行：exec xgkc(001002,-,1);第 10 页共 26 页exec xgkc(001002,+,1);2. 输入（图书类型），根据参数传递来查询图书编号，图书名称，价格-游标create or replace procedure lxcx(c1species varchar2)istype book_record_type is record(v_no boss.book.bookno%type,v_name bos

22、s.book.bookname%type,v_price boss.book.bookprice%type);book_no_name_price book_record_type;cursor c1(c1species varchar2) return book_no_name_price isselect bookno,bookname,bookprice from boss.book where species=c1species;beginopen c1 (c1species);loopfetch c1 into book_no_name_price;if c1%found thend

24、cord_type is record(v_bno boss.book.bookno%type,v_bname boss.book.bookname%type,v_num boss.book.numbers%type,v_sum boss.book.numbers%type);v_no_name_num_sum book_record_type;cursor c1 return v_no_name_num_sum is select b.bookno,b.bookname,b.numbers,s.sumnumber from boss.book b,(select 图书编号,sum(售卖数量)

25、 sumnumber from boss.sold_infor group by 图书编号) s where b.bookno=图书编号 and b.numbers:grant bmanager to staff1 with admin option;:grant bmanager to staff3;-将 smanager 角色授予 staff2 和 staff4:grant smanager to staff2 with admin option;:grant smanager to staff4;10、复杂功能实现第 20 页共 26 页1.查询 book 表并让其有序显示-表 boo

26、k-该语句可被所有用户使用set linesize 130select * from boss.book;2.顾客在购买某一本书之后，让店员推荐相同类型的图书（名称，类型，作者，单价）-表 book-单行子查询- 保存到 d:oraclereplace_sqlslcx_book.sql-该语句可被所有用户使用accept v_bname prompt 输入书名后将显示与该书类型相同的书籍信息，请输入书名：select bookname,species,bookprice,author from book where species in(select distinct species from

27、 boss.book where bookname=执行：start d:oraclereplace_sqlslcx_book.sql3.五周年店庆老板打算搞一个打折促销活动，其中，工具类九折，文学八折，IT七折，需要给销售人员折后价格表（编号，书名，单价，活动价，备注）-表 book-decode-该语句可被所有用户使用select bookno,bookname,bookprice,species,decode(species,工具类,bookprice*0.9,文学,bookprice*0.8,IT,bookprice*0.7) “活动价“,decode(species,工具类 ,9 折

28、,文学,8 折,IT,7 折) “ 备注“ from boss.book order by “备注“;4.查询图书售卖情况（未售卖的图书售卖数量为 0）-表 book，表 sold-该语句可被 boss,seller manager,seller 用户使用select 第 21 页共 26 页b.bookno,b.bookname,b.species,b.bookprice,nvl(s.numbers,0),s.out_time from boss.book b,boss.sold s where b.bookno=s.bookno(+) order by s.out_time;5.查询某年

29、，月，日进货时每种类型的书花费多少钱？-视图 stock_infor-该语句可被 boss,buyer manager,buyer 用户使用select 类别,sum(总价)“进货总价“ from boss.stock_infor where to_char(“进货日期“,6.查询年，月，日的销售总额-内嵌视图（sold_infor）-保存 d:oraclereplace_sqlse1_sold_infor.sql-该语句可被 boss,seller manager,seller 用户使用accept v_datetype prompt 请输入您要查询的时间段类型年（yyyy）,月（yyyy-

30、mm ）,日（yyyy-mm-dd ）：select sum(总价)“销售总额“,售卖日期 from (select 总价,to_char(售卖日期,执行：start d:oraclereplace_sqlse1_sold_infor.sql7.查询按要求的属性求销售总额-保存 d:oraclereplace_sqlse2_sold_infor.sql-该语句可被 boss,seller manager,seller 用户使用accept v_col prompt请输入您要按(书名 or 类别 or 单价 or 售卖日期)查看销售总量：第 22 页共 26 页select sum(“售卖数量

31、“)“总数量“,sum(“总价“)“ 销售额“,执行：start d:oraclereplace_sqlse2_sold_infor.sql8.查询某天，某月，某年的盈利-保存 start d:oraclereplace_sqlse_profit.sql-该语句可被 boss 用户使用accept v_datetype prompt 请输入您要查询的盈利的时间段类型（yyyy）,月（yyyy-mm ）,日（yyyy-mm-dd ）：select sum(盈利),售卖日期 from (select 盈利,to_char(售卖日期,-打开控制台输出1)输入（图书编号，增 /减，数量）实现增减库存e

32、xec boss.pk.xgkc(001002,-,1);exec boss.pk.xgkc(001002,+,1);2)查询某类型所有图书的图书编号，图书名称和价格exec boss.pk.lxcx(IT);3)执行过程，输出提示库存少于 5 的图书信息方便进货exec boss.pk.jhtx;4)输入（员工姓名），求出员工年薪var yearsal numbercall boss.pk.y_sal(staff2) into:yearsal;第 23 页共 26 页print yearsal11、 Oracle 体系结构 No1.alter system flush shared_po

33、ol;-清空共享池执行语句 1：insert into boss.book values(001012,红与黑,文学,49.80,吉林出版集团 ,to_date(2011-10,yyyy-mm),司汤达,0)；已用时间: 00: 00: 00.12统计信息-322 recursive calls 递归调用9 db block gets84 consistent gets 逻辑读2 physical reads 物理读1076 redo size668 bytes sent via SQL*Net to client658 bytes received via SQL*Net from clie

34、nt4 SQL*Net roundtrips to/from client5 sorts (memory)0 sorts (disk)1 rows processed执行语句 1： insert into boss.book values(001013,小王子,文学,19.80,西苑出版社,to_date(2008-09,yyyy-mm),吴群芳,0)；已用时间: 00: 00: 00.01统计信息-8 recursive calls 递归调用9 db block gets 3 consistent gets 逻辑读0 physical reads 物理读1004 redo size第 24

35、页共 26 页673 bytes sent via SQL*Net to client656 bytes received via SQL*Net from client4 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)1 rows processed解释：执行上述语句 1、2，在清空缓冲池的情况下，执行语句 1 时 oracle 需要对语句进行解析，还需从物理存储中读取数据，所以耗时较多；执行语句 2 时由于第一次对语句一做过解释，并且保存到共享池中，所以第二次执行时省去了对语句的解释过程，速度会相应加快！No

36、2.alter system flush shared_pool;-清空共享池执行语句 3：select count(*) from book;已用时间: 00: 00: 00.10统计信息-807 recursive calls0 db block gets166 consistent gets0 physical reads0 redo size408 bytes sent via SQL*Net to client385 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client9 sorts (me

37、mory)0 sorts (disk)1 rows processed执行语句 4：select count(*) from book;已用时间: 00: 00: 00.00统计信息-0 recursive calls0 db block gets1 consistent gets0 physical reads0 redo size408 bytes sent via SQL*Net to client385 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 s

38、orts (disk)1 rows processed第 25 页共 26 页解释：执行上述语句 1、2，在清空缓冲池的情况下，执行语句 1 时 oracle 需要对语句进行解析，还需从物理存储中读取数据，所以耗时较多；执行语句 2 时由于第一次对语句一做过解释，并且保存到共享池中，而将读出的数据放入数据缓存区中，所以第二次执行时省去了对语句的解释过程，也省去了物理读，而是直接从数据缓存区读取数据，速度会相应加快！附录：-进货人员工作流程如果所进书籍为新图书则从第 1 步开始，如果所进书籍为原有图书则从第 2 步开始：1.新增图书信息（新增图书时库存量应为 0）start d:oraclereplace_sqlinsert_book.sql2.新增进货信息start d:oraclereplace_sqlinsert_stock.sql3.修改库存exec boss.pk.xgkc(图书编号,+,进货量);-售货人员工作流程1.新增出货信息start d:oraclereplace_sqlinsert_sold.sql2.修改库存exec boss.pk.xgkc(图书编号,-,售卖数量);

展开阅读全文