Oracle2014课程设计题with answer








Oracle课程设计报告



姓 名
班级学号
指导教师






20XX年X月X 日







Oracle课程设计纲

课程设计目求
1 掌握数库设计理方法巩固SQL Server中数库表视图索引存储程触发器等基操作
2 掌握PLSQL编程语言应
3 巩固数查询种方法

二具体求
1 课程设计软件Oracle 11g
2 课程设计20学时 设计结束交课程设计报告份
3 考核方式：考勤成绩（20）+报告成绩（80）

三课程设计报告具体格式
1 封面
2 正文分三部分：
（1）课程设计目求
（2）课程设计容
（3）课程设计总结

四课程设计容
数库中创建姓名拼音首字母缩写账户密码学号方案进行设计务
务1创建数表
1OEM工具创建Book（图书表）BookType（图书类型表）
Book表
字段名称
数类型
长度
说明
B_ID
CHAR
9
图书编号
B_Name
VARCHAR2
40
图书名称
Author
VARCHAR2
20
作者
BT_ID
CHAR
2
图书类外键
P_ID
CHAR
4
出版社编号
PubDate
DATE

出版日期
Price
NUMBER
(52)
价格
BookType表
字段名称
数类型
长度
说明
BT_ID
CHAR
2
图书类编号键
BT_Name
VARCHAR2
20
图书类名称
BT_Info
VARCHAR2
50
描述信息

2SQL语句创建Reader表（读者表）ReaderType表（读者类型表）
Reader表
字段名称
数类型
长度
说明

R_ID
CHAR
10
读者书证编号键
R_Name
VARCHAR2
8
读者姓名
RT_ID
CHAR
1
读者类型外键
RDept
VARCHAR2
12
部门
RTel
VARCHAR2
11
联系电话
ReaderType表
字段名称
数类型
长度
说明
RT_ID
CHAR
1
读者类型编号键
RT_Name
VARCHAR2
10
读者类型姓名
LimitNum
NUMBER

限数量
LimitDays
NUMBER

限天数

3SQL Developer工具创建Borrow表（阅表）Publish表（出版社表）
Borrow表
字段名称
数类型
长度
说明
R_ID
CHAR
10
读者书证编号
B_ID
CHAR
9
图书编号
LendDate
DATE

阅日期
ReturnDate
DATE

书日期
LimitDays
NUMBER

限天数
BorrowInfo
VARCHAR2
2
否期
Publish表
字段名称
数类型
长度
说明
P_ID
CHAR
4
出版社编号
PubName
VARCHAR2
30
出版社名称
PubTel
VARCHAR2
20
联系电话
lattolatto

create table Reader(
R_ID char(10) primary keyR_Name varchar2(8)RT_ID number(1)RDept varchar2(10)RTel varchar2(11)) tablespace users
create table ReaderType(
RT_ID char(1) primary keyRT_Name varchar2(10)LimitNum numberLimitDays number) tablespace users
create table Reader(
R_ID char(10) primary keyR_Name varchar2(8)RT_ID char(1)RDept varchar2(10)RTel varchar2(11)) tablespace users
alter 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 DateReturnDate DateLimitDays numberBorrowInfo varchar2(2)) tablespace users
create table Publish(
P_ID char(4) PubName varchar2(30)PubTel varchar2(20)) tablespace users

务2修改数表
1Reader表中联系电话字段数类型修改VARCHAR2(20)
alter table reader modify rtel varchar2(20)
2指定Borrow表阅日期LendDate允许空
alter table borrow modify lenddate date not null
3删Borrow表中限天数LimitDays字段
alter table borrow drop column limitdays
4Book表增加ISBN字段数类型VARCHAR2(13)
alter table book add ISBN varchar2(13)

务3建立约束
1Book表添加键约束名PK_Book
alter table book add constraint PK_Book primary key(B_ID)
2Borrow表添加键键(R_IDB_IDLendDate)约束名PK_Borrow
alter table borrow add constraint PK_Borrow primary key(R_IDB_IDLendDate)
3设置Book表出版社编号字段P_ID外键参Publish表中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)
4Book表中价格字段Price添加检查约束求价格必须0约束名CHK_ price
alter table book add constraint chk_price check (price>0)
5 Reader表增加性（Rsex）字段数类型CHAR(2)设默认值男
alter table reader add rsex char(2) default '男'

务4表中插入数
Book表
B_ID
B_Name
Author
BT_ID
P_ID
PubDate
Price
ISBN
B00000001
数库系统概
萨师煊
01
P001
200651
3900
9787040195835
B00000002
数结构
宗华
01
P001
200841
2800
9787115169839
B00000003
SQL SERVER应技术
韦鹏程
01
P005
201151
2600
9787113124830
B00000004
系统工程（修订版）
吕永波
01
P006
200611
2900
9787810821186
B00000005
财应文写作教程
甘佩钦
03
P002
201231
3300
9787115270207
B00000006
面构成设计教程
姜巧玲
04
P002
201191
2900
9787115261052
BookType表
BT_ID
BT_Name
BT_Info
01
计算机类
NULL
02
通信类
NULL
03
类
NULL
04
数字艺术类
NULL
05
电气动化类
NULL
Reader表
R_ID
R_Name
RT_ID
RDept
RTel
Rsex
2004186001
张丽丽
1
信息工程学院
13526569236
女

2005186002
李晓
2
学生处
15103762156
男
2006216117
王海霞
1
工程技术学院
13343811220
女
2006216018
程鹏
3
信息工程学院
13703760258
男
2010211110
杨倩
3
工程技术学院
15223468978
女
2012123001
张芳
2
勤处
13700860288
女
ReaderType表
RT_ID
RT_Name
LimitNum
LimitDays
1
教师
20
120
2
职工
10
90
3
学生
5
60
Borrow表
R_ID
B_ID
LendDate
ReturnDate
BorrowInfo
2004186001
B00000001
2005830
2005926
否
2004186001
B00000003
200891
2009121
否
2006216117
B00000006
200891


2010211110
B00000004
201435


2010211110
B00000005
2014411


2012123001
B00000005
2014412


Publish表
P_ID
PubName
PubTel
P001
高等教育出版社
01077564582
P002
民邮电出版社
01025587788
P003
清华学出版社
01088556616
P004
北京学出版社
01078777444
P005
中国铁道出版社
01063560056
P006
北京交通学出版社
01059646999
P007
北京交出版社
01059646999
insert into booktype values('01'计算机类'NULL)
insert into booktype values('02''通信类'NULL)
insert into booktype values('03''类'NULL)
insert into booktype values('04''数字艺术类'NULL)
insert into booktype values('05''电气动化类'NULL)

insert into readertype values('1''教师'20120)
insert into readertype values('2''职工'1090)
insert into readertype values('3''学生'560)

insert into borrow(R_IDB_IDLendDateReturnDateBorrowInfo) values('2004186001''B00000001''2005830''2005926''否')
insert into borrow(R_IDB_IDLendDateReturnDateBorrowInfo) values('2004186001''B00000003''200891''2009121''否')
insert into borrow(R_IDB_IDLendDateBorrowInfo) values('2006216117''B00000006''200891''')
insert into borrow(R_IDB_IDLendDate) values('2010211110''B00000004''201435')
insert into borrow(R_IDB_IDLendDate) values('2010211110''B00000005''2014411')
insert into borrow(R_IDB_IDLendDate) values('2012123001''B00000005''2014312')

insert into publish values('P001''高等教育出版社''01077564582')
insert into publish values('P002''民邮电出版社''01025587788')
insert into publish values('P003''清华学出版社''01088556616')
insert into publish values('P004''北京学出版社''01078777444')
insert into publish values('P005''中国铁道出版社''01063560056')
insert into publish values('P006''北京交通学出版社''01059646999')
insert into publish values('P007''北京交出版社''01059646999')

insert into reader values('2004186001''张丽丽''1''信息工程学院''13526569236''女')
insert into reader values('2005186002''李晓''2''学生处''15103762156''男')
insert into reader values('2006216117''王海霞''1''工程技术学院''13343811220''女')
insert into reader values('2006216018''程鹏''3''信息工程学院''13703760258''男')
insert into reader values('2010211110''杨倩''3''工程技术学院''15223468978''女')
insert into reader values('2012123001''张芳''2''勤处''13700860288''女')

ALTER session SET nls_date_format YYYYMMDD

insert into book values('B00000001''数库系统概''萨师煊''01' 'P001''200651' 3900'9787040195835')
insert into book values('B00000002''数结构''宗华''01''P001''200841'2800'9787115169839')
insert into book values('B00000003''SQL SERVER应技术''韦鹏程''01''P005''201151'2600'9787113124830')
insert into book values('B00000004''系统工程（修订版）''吕永波''01''P006''200611'2900'9787810821186')
insert into book values('B00000005''财应文写作教程''甘佩钦''03''P002''201231'3300'9787115270207')
insert into book values('B00000006''面构成设计教程''姜巧玲''04''P002''201191'2900'9787115261052')

col b_name format a20
col author format a8
col price format 9999
set pagesize 100

务5更新表中数
1SQL语言Book表中图书编号B000000002出版社编号修改P002
update book set p_id'P002' where b_id'B00000002'
2SQL语言Reader表中张芳部门修改保卫处
update reader set rdept'保卫处' where r_name'张芳'
3SQL语言删Publish表中北京交出版社记录
delete from publish where pubname'北京交出版社'

务6简单查询连接查询
1查询图书基信息
select * from book
2查询图书编号图书名称价格
select b_idb_nameprice from book
3查询教师次书数量书天数输出结果字段名分书数书期限表示
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 null
6查询2014年阅记录
select * from borrow where to_char(lenddate'yyyy')2014
7统计图书信息表中出版社出版图书数目统计结果等2结果输出
select p_idcount(*) as pubnum from book group by p_id having count(*)>2
select p_idcount(p_id) as pubnum from book group by p_id having count(p_id)>2
8查询Borrow表中书读者书证号姓名图书图书证号
select borrowr_idr_nameb_id from borrowreader where borrowr_idreaderr_id
9查询Borrow表中书读者书证号姓名图书图书详细信息
select readerr_idr_namebook* from borrowreaderbook where borrowr_idreaderr_id and bookb_idborrowb_id
10查询阅书籍读者没阅读者显示读者号姓名书名阅日期
select readerr_idr_namebookb_namelenddate from bookreaderborrow
where readerr_idborrowr_id(+) and bookb_id(+)borrowb_id
select b_nameprice pubname from bookpublish where bookp_idpublishp_id and
pubname in ('民邮电出版社''高等教育出版社')
11查询民邮电出版社图书中单价高等教育出版社高单价高图书名单价
select b_nameprice from bookpublish where bookp_idpublishp_id and
pubname'民邮电出版社' and price>all(select price from bookpublish where
bookp_idpublishp_id and pubname'高等教育出版社')
12查询未阅图书信息
select * from book where b_id not in (select b_id from borrow)

select b_name from bookborrow where bookb_idborrowb_id
select p_idcount(*) as pubnum from book group by p_id
13查询出版社出版书籍数量
select pubnamecount(*) as pubnum from bookpublish where bookp_idpublishp_id group by pubname
14查询SQL SERVER应技术类型图书名称作者ISBN号
select b_nameauthorisbn from book where bt_id(select bt_id from book where b_name'SQL SERVER应技术')
15查询单价均单价图书号书名出版社

16查询SQL SERVER应技术出版社图书图书名称作者ISBN号
17查询姓名杨倩读者阅记录
select r_nameborrow* from readerborrow where borrowr_idreaderr_id and r_name'杨倩'
18查询姓名杨倩读者图书详细信息
select book* from readerborrowbook where borrowr_idreaderr_id and bookb_idborrowb_id and r_name'杨倩'
19查询阅高等教育出版社出版书名中包含数库3字图书者阅中国铁道出版社出版书名中含SQL3字图书读者姓名书名
select r_nameb_name from bookborrowpublishreader where readerr_idborrowr_id and bookb_idborrowb_id and bookp_idpublishp_id and
pubname'高等教育出版社' and b_name like '数库'
union
select r_nameb_name from bookborrowpublishreader where readerr_idborrowr_id and bookb_idborrowb_id and bookp_idpublishp_id and
pubname'中国铁道出版社' and b_name like 'SQL'
20查询阅高等教育出版社出版书名中包含数库3字图书阅中国铁道出版社出版书名中含SQL3字图书读者姓名
select r_name from bookborrowpublishreader where readerr_idborrowr_id and
bookb_idborrowb_id and bookp_idpublishp_id and
pubname'高等教育出版社' and b_name like '数库'
intersect
select r_name from bookborrowpublishreader where readerr_idborrowr_id and bookb_idborrowb_id
and bookp_idpublishp_id and
pubname'中国铁道出版社' and b_name like 'SQL'
21查询阅高等教育出版社出版书名中包含数库3字图书没阅中国铁道出版社出版书名中含SQL3字图书读者姓名
select r_name from bookborrowpublishreader where readerr_idborrowr_id and
bookb_idborrowb_id and bookp_idpublishp_id and
pubname'高等教育出版社' and b_name like '数库'
minus
select r_name from bookborrowpublishreader where readerr_idborrowr_id and bookb_idborrowb_id
and bookp_idpublishp_id and
pubname'中国铁道出版社' and b_name like 'SQL'

务7索引视图序列义词
1Book图书表书名列创建惟索引idx_Bname
create unique index idx_bname on book(b_name)
2索引idx_Bname重命名index_Bname
alter index idx_bname rename to index_bname
3删索引index_Bname命令写实验报告中
drop index index_bname
4建立民邮电出版社出版图书视图V_Pub视图中包含书号书名出版社信息
grant create view to latto
create view v_pub as select b_idb_namepublish* from bookpublish where bookp_idpublishp_id and pubname'民邮电出版社'
5创建阅统计视图名V_Count_1包含读者书证号总阅数求该视图读
create view v_count_1(书证号总阅数) as
select r_idcount(*) from borrow group by r_id with read only
6创建阅统计视图名V_Count_2包含阅总数两读者号总阅数
create view v_count_2(读者号总阅数) as
select r_idcount(*) from borrow group by r_id having count(*)>2
7修改视图V_Pub求增加图书单价信息该视图进行更新操作涉民邮电出版社
create or replace view v_pub as
select b_idb_namepricepublish* from bookpublish where publishp_idbookp_id and pubname'民邮电出版社' with check option
8删视图V_Pub
drop view v_pub
9创建序列S_BookUser求初值1序列增量2没值限制
create sequence s_bookuser start with 1 increment by 2
10修改序列S_BookUser值1000
alter sequence s_bookuser maxvalue 1000
11新建表Test（UserID NUMBERUserName VARCHAR2(10)）表中插入两条数中UserID字段序列S_BookUser提供查表test否插入成功
create table test(UserID NUMBERUserName VARCHAR2(10))
insert into test values(s_bookusernextval'tom')
12删序列S_BookUser
drop sequence s_bookuser

务8常量变量系统函数
1编写程序实现Reader表中书证号2004186001读者姓名赋值变量r_name输出该变量值
declare
r_name varchar2(20)
begin
select R_name into r_name from reader
where r_id'2004186001'
dbms_outputput_line('20041086001读者名：'||r_name)
end
2输出前系统日期月份年份
begin
dbms_outputput_line(extract(month from sysdate))
dbms_outputput_line(extract(year from sysdate))
end
3字符函数统计字符串 SQL Server 2008 长度
begin
dbms_outputput_line(length(' SQL Server 2008 '))
end
4函数删字符串 SQL Server 2008 左右两端窗格输出
begin
dbms_outputput_line(trim(' SQL Server 2008 '))
end

务9流程控制语句
1编写PLSQL语句块求2~500间素数
declare
s number0
flag booleantrue
begin
for i in 2500 loop
for j in 2i1 loop
if mod(ij)0 then
flagfalse
end if
end loop
if flag then
ss+i
end if
flagtrue
end loop
dbms_outputput_line('sum is'||s)
end
2编写PLSQL语句块IF语句求出3数中数
declare
i number
j number
k number
maxnum number
begin
i12
j9
k7
maxnumi
if maxnum if maxnum dbms_outputput_line('max is'||maxnum)
end

DECLARE
m numbern numberl numbermaxnum number
BEGIN
m18n8l14
if m>n and m>l then maxnumm
elsif n>l then maxnumn
else maxnuml
end if
dbms_outputput_line('max is '||maxnum)
END
3编写PLSQL语句块求循环结构计算10
declare
s number1
begin
for i in 110 loop
ss*i
end loop
dbms_outputput_line('sum is'||s)
end
4查询图书中没英语书SQL Server方面书果统计册数
declare
e_count number0
o_count number0
cursor bookcur is select b_name from book
bname_book bookb_nametype
begin
if bookcurisopenfalse then
open bookcur
end if
fetch bookcur into bname_book
while bookcurfound
loop
if instr(bname_book'英语')>0 then
e_counte_count+1
elsif instr(bname_book'SQL SERVER')>0 then
o_counto_count+1
end if
fetch bookcur into bname_book
exit when bookcurnotfound
end loop
close bookcur
dbms_outputput_line('英语书数量：'||e_count)
dbms_outputput_line('SQL SERVER书数量：'||o_count)
end

务10存储程
1创建存储程PRO_Borrow返回未图书读者书证号读者姓名阅日期图书名称图书作者
create or replace procedure pro_borrow
is
cursor pro_borrow_cur is
select borrowb_idr_namelenddateb_nameauthor from
borrowbookreader where borrowr_idreaderr_id and
borrowb_idbookb_id and returndate is null
bid borrowb_idtype
zhuozhe varchar2(20)
shuming varchar2(20)
borrowday date
duzhe varchar2(20)
begin
open pro_borrow_cur
fetch pro_borrow_cur into bidduzheborrowdayshumingzhuozhe
while pro_borrow_curfound loop
dbms_outputput_line(bid||''||duzhe||''||borrowday||''||shuming||''||zhuozhe)
fetch pro_borrow_cur into bidduzheborrowdayshumingzhuozhe
end loop
close pro_borrow_cur
end
2调存储程PRO_Borrow查询未图书详细信息
exec pro_borrow
begin
pro_borrow
end
3创建带参数存储程PRO_Borrow_RID求该存储程够根输入书证号返回该读者阅信息包括阅日期书日期图书名称图书ISBN号
create or replace procedure pro_borrow_rid(rid in varchar2)
is
cursor pro_borrow_rid_cur(rid varchar2) is
select lenddatereturndateb_nameisbn from
borrowbook where borrowb_idbookb_id and borrowr_idrid
code varchar2(20)
shuming varchar2(20)
returnday date
borrowday date
begin
open pro_borrow_rid_cur(rid)
fetch pro_borrow_rid_cur into borrowdayreturndayshumingcode
dbms_outputput_line(rid||''||borrowday||''||returnday||''||shuming||''||code)
close pro_borrow_rid_cur
end
4调存储程PRO_Borrow_RID查询书证号2010211110读者阅信息
exec pro_borrow_rid(2010211110’)
5修改存储程PRO_Borrow_RID求修改存储程根输入书证号返回该读者阅信息包括读者姓名阅日期书日期图书名称图书ISBN号
create or replace procedure pro_borrow_rid(rid in varchar2)
is
cursor pro_borrow_rid_cur(rid varchar2) is
select r_namelenddatereturndateb_nameisbn from
readerborrowbook where readerr_idborrowr_id and borrowb_idbookb_id and borrowr_idrid
xingmin varchar2(20)
code varchar2(20)
shuming varchar2(20)
returnday date
borrowday date
begin
open pro_borrow_rid_cur(rid)
fetch pro_borrow_rid_cur into xingminborrowdayreturndayshumingcode
dbms_outputput_line(rid||''||xingmin||''||borrowday||''||returnday||''||shuming||''||code)
close pro_borrow_rid_cur
end
6删存储程PRO_Borrow_RID
drop procedure pro_borrow_rid

务11函数
1编写函数F_GetBName该函数功图书中根指定书号返回该书书名匿名块中调函数F_GetBName找出ISBN号9787113124830图书名称
create or replace function f_getbname
(bookisbn in varchar2)
return varchar2
is
bookname varchar2(20)
begin
select b_name into bookname from book where isbnbookisbn
return bookname
end
begin
dbms_outputput_line(f_getbname('9787113124830'))
end
2删函数F_GetBName
Drop function f_getBname

务12触发器
1创建触发器TR_Delete实现果某读者读者表Reader中删系统动删该读者书信息书信息
create or replace trigger tr_delete
after delete
on reader
for each row
begin
delete from borrow where r_idoldr_id
dbms_outputput_line('已删'||oldr_id||'记录')
end
2测试触发器TR_Delete执行情况
Select * from borrow
Delete from reader where
Select * from borrow
3创建触发器TR_Pub禁止publish表中插入新记录然验证该触发器
create or replace trigger tr_pub
instead of insert on v_publish for each row
begin
dbms_outputput_line('禁止插入记录')
delete from publish where p_idnewp_id
end
4删触发器TR_Pub
Drop trigger tr_pub
文档香网(httpswwwxiangdangnet)户传

《香当网》用户分享的内容，不代表《香当网》观点或立场，请自行判断内容的真实性和可靠性！
该内容是文档的文本内容，更好的格式请下载文档