Oracle2014课程设计题with-answer

1、Oracle课程设计报告姓 名 班级学号 指导教师 徐 梅 2014年5月X 日Oracle课程设计大纲一、课程设计目的和要求1. 掌握数据库的设计和管理方法，巩固SQL Server中数据库、表、视图、索引、存储过程、触发器等基本操作。2. 掌握PL/SQL编程语言的应用。 3. 巩固数据查询的各种方法。二、具体要求1. 本课程设计使用软件为Oracle 11g。2. 本课程设计共20学时， 设计结束上交课程设计报告一份。3. 考核方式：考勤成绩（20%）+报告成绩（80%）。三、课程设计报告具体格式1. 封面2. 正文分三部分：（1）课程设计目的和要求（2）课程设计内容（3）课程设计总结

2、四、课程设计内容在数据库中创建一个以自己姓名拼音首字母缩写的账户，密码为学号。在此方案下进行设计任务。【任务1】创建数据表1.使用OEM工具创建Book（图书表）和BookType（图书类型表）Book表字段名称数据类型长度说明B_IDCHAR9图书编号B_NameVARCHAR240图书名称AuthorVARCHAR220作者BT_IDCHAR2图书类别，外键P_IDCHAR4出版社编号PubDateDATE出版日期PriceNUMBER(5,2)价格BookType表字段名称数据类型长度说明BT_IDCHAR2图书类别编号，主键BT_NameVARCHAR220图书类别名称BT_InfoV

3、ARCHAR250描述信息2.使用SQL语句创建Reader表（读者表）和ReaderType表（读者类型表）。Reader表字段名称数据类型长度说明R_IDCHAR10读者借书证编号，主键R_NameVARCHAR28读者姓名RT_IDCHAR1读者类型，外键RDeptVARCHAR212部门RTelVARCHAR211联系电话ReaderType表字段名称数据类型长度说明RT_IDCHAR1读者类型编号，主键RT_NameVARCHAR210读者类型姓名LimitNumNUMBER限借数量LimitDaysNUMBER限借天数3.使用SQL Developer工具创建Borrow表（借阅表

4、）和Publish表（出版社表）。Borrow表字段名称数据类型长度说明R_IDCHAR10读者借书证编号B_IDCHAR9图书编号LendDateDATE借阅日期ReturnDateDATE还书日期LimitDaysNUMBER限借天数BorrowInfoVARCHAR22是否过期Publish表字段名称数据类型长度说明P_IDCHAR4出版社编号PubNameVARCHAR230出版社名称PubTelVARCHAR220联系电话latto/lattocreate table Reader(R_ID char(10) primary key,R_Name varchar2(8),RT_ID

5、number(1),RDept varchar2(10),RTel varchar2(11) tablespace userscreate table ReaderType(RT_ID char(1) primary key,RT_Name varchar2(10),LimitNum number,LimitDays number) tablespace userscreate table Reader(R_ID char(10) primary key,R_Name varchar2(8),RT_ID char(1),RDept varchar2(10),RTel varchar2(11)

6、tablespace usersalter table reader add constraint fk_RT_ID foreign key (RT_ID) references ReaderType (RT_ID)create table Borrow(R_ID char(10) ,B_ID char(9),LendDate Date,ReturnDate Date,LimitDays number,BorrowInfo varchar2(2) tablespace userscreate table Publish(P_ID char(4) ,PubName varchar2(30),Pu

7、bTel varchar2(20) tablespace users【任务2】修改数据表1.将Reader表中的联系电话字段的数据类型修改为VARCHAR2(20)。alter table reader modify rtel varchar2(20)2.指定Borrow表的借阅日期LendDate不允许为空。alter table borrow modify lenddate date not null3.删除Borrow表中的限借天数LimitDays字段。alter table borrow drop column limitdays4.为Book表增加ISBN字段，数据类型为VARCH

8、AR2(13)。alter table book add ISBN varchar2(13)【任务3】建立约束1.为Book表添加主键，约束名为PK_Book。alter table book add constraint PK_Book primary key(B_ID)2.为Borrow表添加主键，主键是(R_ID,B_ID,LendDate)，约束名为PK_Borrow。alter table borrow add constraint PK_Borrow primary key(R_ID,B_ID,LendDate)3.设置Book表的出版社编号字段P_ID是外键，参照Publish表

9、中的P_ID，约束名为FK_book_pub。alter table publish add constraint pk_pub primary key (P_ID)alter table book add constraint FK_book_pub foreign key(P_ID) references Publish(P_ID)4.为Book表中的价格字段Price添加检查约束，要求价格必须大于0，约束名为CHK_ price。alter table book add constraint chk_price check (price0)5. 为Reader表增加性别（Rsex）字段，

10、数据类型为CHAR(2)，并设默认值为“男”。alter table reader add rsex char(2) default 男【任务4】向表中插入数据Book表B_IDB_NameAuthorBT_IDP_IDPubDatePriceISBNB数据库系统概论萨师煊01P0012006-5-139.0035B数据结构宗大华01P0012008-4-128.0039BSQL SERVER应用技术韦鹏程01P0052011-5-126.0030B系统工程（修订版）吕永波01P0062006-1-129.0086B财经应用文写作教程甘佩钦03P0022012-3-133.0007B平面构成设

11、计教程姜巧玲04P0022011-9-129.0052BookType表BT_IDBT_NameBT_Info01计算机类NULL02通信类NULL03经管类NULL04数字艺术类NULL05电气自动化类NULLReader表R_IDR_NameRT_IDRDeptRTelRsex张丽丽1信息工程学院女李晓平2学生处男王海霞1工程技术学院女程鹏3信息工程学院男杨倩3工程技术学院女张芳2后勤处女ReaderType表RT_IDRT_NameLimitNumLimitDays1教师201202职工10903学生560Borrow表R_IDB_IDLendDateReturnDateBorrowIn

12、foB2005-8-302005-9-26否B2008-9-12009-12-1否B2008-9-1是B2014-3-5B2014-4-11B2014-4-12Publish表P_IDPubNamePubTelP001高等教育出版社010-P002人民邮电出版社010-P003清华大学出版社010-P004北京大学出版社010-P005中国铁道出版社010-P006北京交通大学出版社010-P007北京交大出版社010-insert into booktype values(01,计算机类,NULL);insert into booktype values(02,通信类,NULL);inser

13、t into booktype values(03,经管类,NULL);insert into booktype values(04,数字艺术类,NULL);insert into booktype values(05,电气自动化类,NULL);insert into readertype values(1,教师,20,120);insert into readertype values(2,职工,10,90);insert into readertype values(3,学生,5,60);insert into borrow(R_ID,B_ID,LendDate,ReturnDate,Bo

14、rrowInfo) values(,B,2005-8-30,2005-9-26,否);insert into borrow(R_ID,B_ID,LendDate,ReturnDate,BorrowInfo) values(,B,2008-9-1,2009-12-1,否);insert into borrow(R_ID,B_ID,LendDate,BorrowInfo) values(,B,2008-9-1,是);insert into borrow(R_ID,B_ID,LendDate) values(,B,2014-3-5);insert into borrow(R_ID,B_ID,Lend

15、Date) values(,B,2014-4-11);insert into borrow(R_ID,B_ID,LendDate) values(,B,2014-3-12);insert into publish values(P001,高等教育出版社,010-);insert into publish values(P002,人民邮电出版社,010-);insert into publish values(P003,清华大学出版社,010-);insert into publish values(P004,北京大学出版社,010-);insert into publish values(P0

16、05,中国铁道出版社,010-);insert into publish values(P006,北京交通大学出版社,010-);insert into publish values(P007,北京交大出版社,010-);insert into reader values(,张丽丽,1,信息工程学院,女);insert into reader values(,李晓平,2,学生处,男);insert into reader values(,王海霞,1,工程技术学院,女);insert into reader values(,程鹏,3,信息工程学院,，男);insert into reader v

17、alues(,杨倩,3,工程技术学院,女);insert into reader values(,张芳,2,后勤处,女);ALTER session SET nls_date_format = YYYY-MM-DD;insert into book values(B,数据库系统概论,萨师煊,01,P001,2006-5-1, 39.00,35);insert into book values(B,数据结构,宗大华,01,P001,2008-4-1,28.00,39);insert into book values(B,SQL SERVER应用技术,韦鹏程,01,P005,2011-5-1,26

18、.00,30);insert into book values(B,系统工程（修订版）,吕永波,01,P006,2006-1-1,29.00,86);insert into book values(B,财经应用文写作教程,甘佩钦,03,P002,2012-3-1,33.00,07);insert into book values(B,平面构成设计教程,姜巧玲,04,P002,2011-9-1,29.00,52);col b_name format a20col author format a8col price format 99.99set pagesize 100【任务5】更新表中数据1.

19、使用SQL语言将Book表中图书编号为B的出版社编号修改为“P002”.update book set p_id=P002 where b_id=B2.使用SQL语言把Reader表中的张芳的部门修改为“保卫处”。update reader set rdept=保卫处 where r_name=张芳3.使用SQL语言删除Publish表中“北京交大出版社”的记录。delete from publish where pubname=北京交大出版社【任务6】简单查询及连接查询1.查询所有图书的基本信息。select * from book2.查询所有的图书编号、图书名称和价格。select b_i

20、d,b_name,price from book3.查询教师一次可以借书数量以及借书天数，输出的结果字段名分别用借书本数和借书期限表示。select limitnum as 借书本数,limitdays as 借书期限 from readertype where rt_name=教师4.查询姓“张”读者的基本信息。select * from reader where r_name like 张%5.查询Borrow表中未还图书的记录。select * from borrow where returndate is null6.查询2014年的借阅记录。select * from borrow

21、where to_char(lenddate,yyyy)=20147.统计图书信息表中不同出版社出版的图书数目，把统计结果大于或等于2的结果输出。select p_id,count(*) as pubnum from book group by p_id having count(*)=2select p_id,count(p_id) as pubnum from book group by p_id having count(p_id)=28.查询Borrow表中所有借书的读者的借书证号、姓名以及所借图书的图书证号。select borrow.r_id,r_name,b_id from bo

22、rrow,reader where borrow.r_id=reader.r_id9.查询Borrow表中所有借书的读者的借书证号、姓名以及所借图书的图书的详细信息。select reader.r_id,r_name,book.* from borrow,reader,book where borrow.r_id=reader.r_id and book.b_id=borrow.b_id10.查询借阅了书籍的读者和没有借阅的读者，显示他们的读者号、姓名、书名、借阅日期。select reader.r_id,r_name,book.b_name,lenddate from book,reader

23、,borrowwhere reader.r_id=borrow.r_id(+) and book.b_id(+)=borrow.b_idselect b_name,price ,pubname from book,publish where book.p_id=publish.p_id andpubname in (人民邮电出版社,高等教育出版社)11.查询“人民邮电出版社”的图书中单价比“高等教育出版社”最高单价还高的图书名、单价。select b_name,price from book,publish where book.p_id=publish.p_id andpubname=人民邮

24、电出版社 and priceall(select price from book,publish wherebook.p_id=publish.p_id and pubname=高等教育出版社)12.查询从未被借阅过的图书信息。select * from book where b_id not in (select b_id from borrow)/select b_name from book,borrow where book.b_id=borrow.b_idselect p_id,count(*) as pubnum from book group by p_id13.查询每一个出版社

25、出版的书籍的数量。select pubname,count(*) as pubnum from book,publish where book.p_id=publish.p_id group by pubname14.查询与SQL SERVER应用技术同一类型的所有图书的名称、作者、ISBN号。select b_name,author,isbn from book where bt_id=(select bt_id from book where b_name=SQL SERVER应用技术)15.查询所有单价小于平均单价的图书号、书名、出版社。16.查询与SQL SERVER应用技术同一出版社

26、的所有图书的图书名称、作者、ISBN号。17.查询姓名为“杨倩”的读者的借阅记录。select r_name,borrow.* from reader,borrow where borrow.r_id=reader.r_id and r_name=杨倩18.查询姓名为“杨倩”的读者的所借图书的详细信息。select book.* from reader,borrow,book where borrow.r_id=reader.r_id and book.b_id=borrow.b_id and r_name=杨倩19.查询借阅了“高等教育出版社”出版的书名中包含有“数据库”3个字的图书，或者借

27、阅了“中国铁道出版社”出版的书名中含有“SQL”3个字的图书的读者姓名、书名。select r_name,b_name from book,borrow,publish,reader where reader.r_id=borrow.r_id and book.b_id=borrow.b_id and book.p_id=publish.p_id and pubname=高等教育出版社 and b_name like %数据库%unionselect r_name,b_name from book,borrow,publish,reader where reader.r_id=borrow.r

28、_id and book.b_id=borrow.b_id and book.p_id=publish.p_id and pubname=中国铁道出版社 and b_name like %SQL%20.查询借阅了“高等教育出版社”出版的书名中包含有“数据库”3个字的图书，并且也借阅了“中国铁道出版社”出版的书名中含有“SQL”3个字的图书的读者姓名。select r_name from book,borrow,publish,reader where reader.r_id=borrow.r_id and book.b_id=borrow.b_id and book.p_id=publish.

29、p_id and pubname=高等教育出版社 and b_name like %数据库%intersectselect r_name from book,borrow,publish,reader where reader.r_id=borrow.r_id and book.b_id=borrow.b_id and book.p_id=publish.p_id and pubname=中国铁道出版社 and b_name like %SQL%21.查询借阅了“高等教育出版社”出版的书名中包含有“数据库”3个字的图书，但是没有借阅了“中国铁道出版社”出版的书名中含有“SQL”3个字的图书的读

30、者姓名。select r_name from book,borrow,publish,reader where reader.r_id=borrow.r_id and book.b_id=borrow.b_id and book.p_id=publish.p_id and pubname=高等教育出版社 and b_name like %数据库%minusselect r_name from book,borrow,publish,reader where reader.r_id=borrow.r_id and book.b_id=borrow.b_id and book.p_id=publi

31、sh.p_id and pubname=中国铁道出版社 and b_name like %SQL%【任务7】索引、视图、序列及同义词1.为Book图书表的书名列创建惟一索引idx_Bname。create unique index idx_bname on book(b_name)2.将索引“idx_Bname”重命名为index_Bname。alter index idx_bname rename to index_bname3.删除索引index_Bname，将命令写在实验报告中。drop index index_bname4.建立“人民邮电出版社”所出版的图书视图V_Pub,视图中包含书

32、号、书名、出版社信息。grant create view to latto;create view v_pub as select b_id,b_name,publish.* from book,publish where book.p_id=publish.p_id and pubname=人民邮电出版社5.创建一个借阅统计视图，名为V_Count_1，包含读者的借书证号和总借阅本数，要求该视图为只读。create view v_count_1(借书证号,总借阅本数) asselect r_id,count(*) from borrow group by r_id with read onl

33、y6.创建一个借阅统计视图，名为V_Count_2，包含借阅总本数大于两本的读者号和总借阅本数。create view v_count_2(读者号,总借阅本数) asselect r_id,count(*) from borrow group by r_id having count(*)=27.修改视图V_Pub，要求增加图书的单价信息，并且该视图进行的更新操作只涉及“人民邮电出版社”。create or replace view v_pub as select b_id,b_name,price,publish.* from book,publish where publish.p_id=

34、book.p_id and pubname=人民邮电出版社 with check option8.删除视图V_Pub。drop view v_pub9.创建序列S_BookUser，要求初值为1，序列增量为2，没有最大值限制。create sequence s_bookuser start with 1 increment by 210.修改序列S_BookUser的最大值为1000。alter sequence s_bookuser maxvalue 100011.新建表Test（UserID NUMBER,UserName VARCHAR2(10)），向表中插入两条数据，其中UserID字

35、段由序列S_BookUser提供，并查看表test是否插入成功。create table test(UserID NUMBER,UserName VARCHAR2(10)insert into test values(s_bookuser.nextval,tom)12.删除序列S_BookUser。drop sequence s_bookuser【任务8】常量、变量和系统函数1.编写程序实现将Reader表中借书证号为“”的读者的姓名赋值给变量r_name，并输出该变量的值。declarer_name varchar2(20);beginselect R_name into r_name fr

36、om reader where r_id=;dbms_output.put_line(读者名为：|r_name);end;2.输出当前系统日期月份和年份。begindbms_output.put_line(extract(month from sysdate);dbms_output.put_line(extract(year from sysdate);end;3.使用字符函数统计字符串“ SQL Server 2008 ”的长度。begindbms_output.put_line(length( SQL Server 2008 );end;4.使用函数删除字符串“ SQL Server 2

37、008 ”左右两端的窗格并输出。begindbms_output.put_line(trim( SQL Server 2008 );end;【任务9】流程控制语句1.编写PL/SQL语句块，求2500之间的素数和。declares number:=0;flag boolean:=true;beginfor i in 2.500 loop for j in 2.i-1 loop if mod(i,j)=0 then flag:=false; end if; end loop; if flag then s:=s+i; end if; flag:=true; end loop;dbms_outpu

38、t.put_line(sum is|s);end;2.编写PL/SQL语句块，使用IF语句求出3个数中最大的数。declarei number;j number;k number;maxnum number;begin i:=12; j:=9; k:=7; maxnum:=i; if maxnumj then maxnum:=j; end if; if maxnumn and ml then maxnum:=m;elsif nl then maxnum:=n;else maxnum:=l;end if;dbms_output.put_line(max is |maxnum);END;3.编写P

39、L/SQL语句块，要求使用循环结构来计算10！。declares number:=1;begin for i in 1.10 loop s:=s*i; end loop; dbms_output.put_line(sum is|s);end;4.查询图书中有没有英语书和SQL Server方面的书，如果有则统计其册数。declaree_count number:=0;o_count number:=0;cursor bookcur is select b_name from book;bname_book book.b_name%type;beginif bookcur%isopen=fals

40、e thenopen bookcur;end if;fetch bookcur into bname_book;while bookcur%found loopif instr(bname_book,英语)0 then e_count:=e_count+1;elsif instr(bname_book,SQL SERVER)0 then o_count:=o_count+1;end if;fetch bookcur into bname_book;exit when bookcur%notfound;end loop;close bookcur;dbms_output.put_line(英语书

41、的数量为：|e_count);dbms_output.put_line(SQL SERVER书的数量为：|o_count);end;【任务10】存储过程1.创建存储过程PRO_Borrow，返回还未归还图书的读者借书证号、读者姓名、借阅日期、图书名称和图书作者。create or replace procedure pro_borrowiscursor pro_borrow_cur isselect borrow.b_id,r_name,lenddate,b_name,author fromborrow,book,reader where borrow.r_id=reader.r_id and

42、borrow.b_id=book.b_id and returndate is null;bid borrow.b_id%type;zhuozhe varchar2(20);shuming varchar2(20);borrowday date;duzhe varchar2(20);beginopen pro_borrow_cur;fetch pro_borrow_cur into bid,duzhe,borrowday,shuming,zhuozhe;while pro_borrow_cur%found loopdbms_output.put_line(bid|,|duzhe|,|borro

43、wday|,|shuming|,|zhuozhe);fetch pro_borrow_cur into bid,duzhe,borrowday,shuming,zhuozhe;end loop;close pro_borrow_cur;end;2.调用存储过程PRO_Borrow，查询所有未还图书的详细信息。exec pro_borrowbeginpro_borrow;end;3.创建带参数的存储过程PRO_Borrow_RID，要求该存储过程能够根据输入的借书证号返回该读者的所有借阅信息，包括借阅日期、还书日期、图书名称、图书ISBN号。create or replace procedure

44、 pro_borrow_rid(rid in varchar2)iscursor pro_borrow_rid_cur(rid varchar2) isselect lenddate,returndate,b_name,isbn fromborrow,book where borrow.b_id=book.b_id and borrow.r_id=rid;code varchar2(20);shuming varchar2(20);returnday date;borrowday date;beginopen pro_borrow_rid_cur(rid);fetch pro_borrow_rid_cur into borrowday,returnday,shuming,code;dbms_output.put_line(rid|,|borrowday|,|returnday|,|shuming|,|code);close pro_borrow_rid_cur;end;4.调用存储过程PRO_Borrow_RID，查询借书证号为“”的读者的借阅信息。exec pro_borrow_rid()5.修改存储过程PRO_Borrow_RID，要求修改后的存储过程根据输入的借书证号返回该读者的所有借阅信息，包括读者姓名、借阅日期、还书日期、图书名称、图书ISBN号。create o