Oracle2014課程設(shè)計題with-answer

1、精選優(yōu)質(zhì)文檔-傾情為你奉上Oracle課程設(shè)計報告姓 名 班級學(xué)號 指導(dǎo)教師 徐 梅 2014年5月X 日Oracle課程設(shè)計大綱一、課程設(shè)計目的和要求1. 掌握數(shù)據(jù)庫的設(shè)計和管理方法，鞏固SQL Server中數(shù)據(jù)庫、表、視圖、索引、存儲過程、觸發(fā)器等基本操作。2. 掌握PL/SQL編程語言的應(yīng)用。 3. 鞏固數(shù)據(jù)查詢的各種方法。二、具體要求1. 本課程設(shè)計使用軟件為Oracle 11g。2. 本課程設(shè)計共20學(xué)時， 設(shè)計結(jié)束上交課程設(shè)計報告一份。3. 考核方式：考勤成績（20%）+報告成績（80%）。三、課程設(shè)計報告具體格式1. 封面2. 正文分三部分：（1）課程設(shè)計目的和要求（2）課程設(shè)

2、計內(nèi)容（3）課程設(shè)計總結(jié) 四、課程設(shè)計內(nèi)容在數(shù)據(jù)庫中創(chuàng)建一個以自己姓名拼音首字母縮寫的賬戶，密碼為學(xué)號。在此方案下進(jìn)行設(shè)計任務(wù)?！救蝿?wù)1】創(chuàng)建數(shù)據(jù)表1.使用OEM工具創(chuàng)建Book（圖書表）和BookType（圖書類型表）Book表字段名稱數(shù)據(jù)類型長度說明B_IDCHAR9圖書編號B_NameVARCHAR240圖書名稱AuthorVARCHAR220作者BT_IDCHAR2圖書類別，外鍵P_IDCHAR4出版社編號PubDateDATE出版日期PriceNUMBER(5,2)價格BookType表字段名稱數(shù)據(jù)類型長度說明BT_IDCHAR2圖書類別編號，主鍵BT_NameVARCHAR220圖

3、書類別名稱BT_InfoVARCHAR250描述信息2.使用SQL語句創(chuàng)建Reader表（讀者表）和ReaderType表（讀者類型表）。Reader表字段名稱數(shù)據(jù)類型長度說明R_IDCHAR10讀者借書證編號，主鍵R_NameVARCHAR28讀者姓名RT_IDCHAR1讀者類型，外鍵RDeptVARCHAR212部門RTelVARCHAR211聯(lián)系電話ReaderType表字段名稱數(shù)據(jù)類型長度說明RT_IDCHAR1讀者類型編號，主鍵RT_NameVARCHAR210讀者類型姓名LimitNumNUMBER限借數(shù)量LimitDaysNUMBER限借天數(shù)3.使用SQL Developer工具

4、創(chuàng)建Borrow表（借閱表）和Publish表（出版社表）。Borrow表字段名稱數(shù)據(jù)類型長度說明R_IDCHAR10讀者借書證編號B_IDCHAR9圖書編號LendDateDATE借閱日期ReturnDateDATE還書日期LimitDaysNUMBER限借天數(shù)BorrowInfoVARCHAR22是否過期Publish表字段名稱數(shù)據(jù)類型長度說明P_IDCHAR4出版社編號PubNameVARCHAR230出版社名稱PubTelVARCHAR220聯(lián)系電話latto/lattocreate table Reader(R_ID char(10) primary key,R_Name varch

5、ar2(8),RT_ID 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

6、varchar2(11) 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 va

7、rchar2(30),PubTel varchar2(20) tablespace users【任務(wù)2】修改數(shù)據(jù)表1.將Reader表中的聯(lián)系電話字段的數(shù)據(jù)類型修改為VARCHAR2(20)。alter table reader modify rtel varchar2(20)2.指定Borrow表的借閱日期LendDate不允許為空。alter table borrow modify lenddate date not null3.刪除Borrow表中的限借天數(shù)LimitDays字段。alter table borrow drop column limitdays4.為Book表增加ISBN

8、字段，數(shù)據(jù)類型為VARCHAR2(13)。alter table book add ISBN varchar2(13)【任務(wù)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.設(shè)置Book表的出版社編號字段P_ID是

9、外鍵，參照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)4.為Book表中的價格字段Price添加檢查約束，要求價格必須大于0，約束名為CHK_ price。alter table book add constraint chk_price check (price>0)5. 為Rea

10、der表增加性別（Rsex）字段，數(shù)據(jù)類型為CHAR(2)，并設(shè)默認(rèn)值為“男”。alter table reader add rsex char(2) default '男'【任務(wù)4】向表中插入數(shù)據(jù)Book表B_IDB_NameAuthorBT_IDP_IDPubDatePriceISBNB數(shù)據(jù)庫系統(tǒng)概論薩師煊01P0012006-5-139.0035B數(shù)據(jù)結(jié)構(gòu)宗大華01P0012008-4-128.0039BSQL SERVER應(yīng)用技術(shù)韋鵬程01P0052011-5-126.0030B系統(tǒng)工程（修訂版）呂永波01P0062006-1-129.0086B財經(jīng)應(yīng)用文寫作教程甘佩欽

11、03P0022012-3-133.0007B平面構(gòu)成設(shè)計教程姜巧玲04P0022011-9-129.0052BookType表BT_IDBT_NameBT_Info01計算機(jī)類NULL02通信類NULL03經(jīng)管類NULL04數(shù)字藝術(shù)類NULL05電氣自動化類NULLReader表R_IDR_NameRT_IDRDeptRTelRsex張麗麗1信息工程學(xué)院女李曉平2學(xué)生處男王海霞1工程技術(shù)學(xué)院女程鵬3信息工程學(xué)院男楊倩3工程技術(shù)學(xué)院女張芳2后勤處女ReaderType表RT_IDRT_NameLimitNumLimitDays1教師201202職工10903學(xué)生560Borrow表R_IDB_I

12、DLendDateReturnDateBorrowInfoB2005-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清華大學(xué)出版社010-P004北京大學(xué)出版社010-P005中國鐵道出版社010-P006北京交通大學(xué)出版社010-P007北京交大出版社010-insert into booktype values('01,'計算機(jī)類',NULL);inse

13、rt into booktype values('02','通信類',NULL);insert into booktype values('03','經(jīng)管類',NULL);insert into booktype values('04','數(shù)字藝術(shù)類',NULL);insert into booktype values('05','電氣自動化類',NULL);insert into readertype values('1','教師',

14、20,120);insert into readertype values('2','職工',10,90);insert into readertype values('3','學(xué)生',5,60);insert into borrow(R_ID,B_ID,LendDate,ReturnDate,BorrowInfo) values('','B','2005-8-30','2005-9-26','否');insert into borrow(R_ID,B

15、_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',&

16、#39;2014-3-5');insert into borrow(R_ID,B_ID,LendDate) 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 value

17、s('P002','人民郵電出版社','010-');insert into publish values('P003','清華大學(xué)出版社','010-');insert into publish values('P004','北京大學(xué)出版社','010-');insert into publish values('P005','中國鐵道出版社','010-');insert into publish v

18、alues('P006','北京交通大學(xué)出版社','010-');insert into publish values('P007','北京交大出版社','010-');insert into reader values('','張麗麗','1','信息工程學(xué)院','','女');insert into reader values('','李曉平','2',

19、'學(xué)生處','','男');insert into reader values('','王海霞','1','工程技術(shù)學(xué)院','','女');insert into reader values('','程鵬','3','信息工程學(xué)院',''，'男');insert into reader values('','楊倩','

20、3','工程技術(shù)學(xué)院','','女');insert into reader values('','張芳','2','后勤處','','女');ALTER session SET nls_date_format = "YYYY-MM-DD"insert into book values('B','數(shù)據(jù)庫系統(tǒng)概論','薩師煊','01','P001'

21、;,'2006-5-1', 39.00,'35');insert into book values('B','數(shù)據(jù)結(jié)構(gòu)','宗大華','01','P001','2008-4-1',28.00,'39');insert into book values('B','SQL SERVER應(yīng)用技術(shù)','韋鵬程','01','P005','2011-5-1',26.0

22、0,'30');insert into book values('B','系統(tǒng)工程（修訂版）','呂永波','01','P006','2006-1-1',29.00,'86');insert into book values('B','財經(jīng)應(yīng)用文寫作教程','甘佩欽','03','P002','2012-3-1',33.00,'07');insert int

23、o book values('B','平面構(gòu)成設(shè)計教程','姜巧玲','04','P002','2011-9-1',29.00,'52');col b_name format a20col author format a8col price format 99.99set pagesize 100【任務(wù)5】更新表中數(shù)據(jù)1.使用SQL語言將Book表中圖書編號為B的出版社編號修改為“P002”.update book set p_id='P002' where b_id

24、='B'2.使用SQL語言把Reader表中的張芳的部門修改為“保衛(wèi)處”。update reader set rdept='保衛(wèi)處' where r_name='張芳'3.使用SQL語言刪除Publish表中“北京交大出版社”的記錄。delete from publish where pubname='北京交大出版社'【任務(wù)6】簡單查詢及連接查詢1.查詢所有圖書的基本信息。select * from book2.查詢所有的圖書編號、圖書名稱和價格。select b_id,b_name,price from book3.查詢教師一次

25、可以借書數(shù)量以及借書天數(shù)，輸出的結(jié)果字段名分別用借書本數(shù)和借書期限表示。select limitnum as 借書本數(shù),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 where to_cha

26、r(lenddate,'yyyy')=20147.統(tǒng)計圖書信息表中不同出版社出版的圖書數(shù)目，把統(tǒng)計結(jié)果大于或等于2的結(jié)果輸出。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 f

27、rom borrow,reader where borrow.r_id=reader.r_id9.查詢Borrow表中所有借書的讀者的借書證號、姓名以及所借圖書的圖書的詳細(xì)信息。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,

28、reader,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=p

29、ublish.p_id andpubname='人民郵電出版社' and price>all(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,cou

30、nt(*) as pubnum from book group by p_id13.查詢每一個出版社出版的書籍的數(shù)量。select pubname,count(*) as pubnum from book,publish where book.p_id=publish.p_id group by pubname14.查詢與SQL SERVER應(yīng)用技術(shù)同一類型的所有圖書的名稱、作者、ISBN號。select b_name,author,isbn from book where bt_id=(select bt_id from book where b_name='SQL SERVER應(yīng)用

31、技術(shù)')15.查詢所有單價小于平均單價的圖書號、書名、出版社。16.查詢與SQL SERVER應(yīng)用技術(shù)同一出版社的所有圖書的圖書名稱、作者、ISBN號。17.查詢姓名為“楊倩”的讀者的借閱記錄。select r_name,borrow.* from reader,borrow where borrow.r_id=reader.r_id and r_name='楊倩'18.查詢姓名為“楊倩”的讀者的所借圖書的詳細(xì)信息。select book.* from reader,borrow,book where borrow.r_id=reader.r_id and book.b

32、_id=borrow.b_id and r_name='楊倩'19.查詢借閱了“高等教育出版社”出版的書名中包含有“數(shù)據(jù)庫”3個字的圖書，或者借閱了“中國鐵道出版社”出版的書名中含有“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

33、 '%數(shù)據(jù)庫%'unionselect 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 '%SQL%'20.查詢借閱了“高等教育出版社”出版的書名中包含有“數(shù)據(jù)庫”3個字的圖書，并且也借閱了“中國鐵道出版社”出版的書名中含有“SQL”3個字的圖書的讀者姓名。sele

34、ct 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 '%數(shù)據(jù)庫%'intersectselect r_name from book,borrow,publish,reader where reader.r_id=borrow.r_id and book.b_id=borrow.b_id and

35、 book.p_id=publish.p_id and pubname='中國鐵道出版社' and b_name like '%SQL%'21.查詢借閱了“高等教育出版社”出版的書名中包含有“數(shù)據(jù)庫”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.p_id and pub

36、name='高等教育出版社' and b_name like '%數(shù)據(jù)庫%'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=publish.p_id and pubname='中國鐵道出版社' and b_name like '%SQL%'【任務(wù)7】索引、視圖、序列及同義詞1.為Book圖書表的書名列創(chuàng)建惟一索引idx_Bname。crea

37、te 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,視圖中包含書號、書名、出版社信息。grant create view to latto;create view v_pub as select b_id,b_name,publish.* from book,pu

38、blish where book.p_id=publish.p_id and pubname='人民郵電出版社'5.創(chuàng)建一個借閱統(tǒng)計視圖，名為V_Count_1，包含讀者的借書證號和總借閱本數(shù)，要求該視圖為只讀。create view v_count_1(借書證號,總借閱本數(shù)) asselect r_id,count(*) from borrow group by r_id with read only6.創(chuàng)建一個借閱統(tǒng)計視圖，名為V_Count_2，包含借閱總本數(shù)大于兩本的讀者號和總借閱本數(shù)。create view v_count_2(讀者號,總借閱本數(shù)) asselect

39、r_id,count(*) from borrow group by r_id having count(*)>=27.修改視圖V_Pub，要求增加圖書的單價信息，并且該視圖進(jìn)行的更新操作只涉及“人民郵電出版社”。create or replace view v_pub as select b_id,b_name,price,publish.* from book,publish where publish.p_id=book.p_id and pubname='人民郵電出版社' with check option8.刪除視圖V_Pub。drop view v_pub9.

40、創(chuàng)建序列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)），向表中插入兩條數(shù)據(jù)，其中UserID字段由序列S_BookUser提供，并查看表test是否插入成功。create table test(UserID NUMBER,UserName VAR

41、CHAR2(10)insert into test values(s_bookuser.nextval,'tom')12.刪除序列S_BookUser。drop sequence s_bookuser【任務(wù)8】常量、變量和系統(tǒng)函數(shù)1.編寫程序?qū)崿F(xiàn)將Reader表中借書證號為“”的讀者的姓名賦值給變量r_name，并輸出該變量的值。declarer_name varchar2(20);beginselect R_name into r_name from reader where r_id=''dbms_output.put_line('讀者名為：'

42、;|r_name);end;2.輸出當(dāng)前系統(tǒng)日期月份和年份。begindbms_output.put_line(extract(month from sysdate);dbms_output.put_line(extract(year from sysdate);end;3.使用字符函數(shù)統(tǒng)計字符串“ SQL Server 2008 ”的長度。begindbms_output.put_line(length(' SQL Server 2008 ');end;4.使用函數(shù)刪除字符串“ SQL Server 2008 ”左右兩端的窗格并輸出。begindbms_output.put_

43、line(trim(' SQL Server 2008 ');end;【任務(wù)9】流程控制語句1.編寫PL/SQL語句塊，求2500之間的素數(shù)和。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_output.put_line('sum is'

44、|s);end;2.編寫PL/SQL語句塊，使用IF語句求出3個數(shù)中最大的數(shù)。declarei number;j number;k number;maxnum number;begin i:=12; j:=9; k:=7; maxnum:=i; if maxnum<j then maxnum:=j; end if; if maxnum<k then maxnum:=k; end if; dbms_output.put_line('max is'|maxnum);end;或DECLARE m number;n number;l number;maxnum number

45、;BEGINm:=18;n:=8;l:=14;if m>n and m>l then maxnum:=m;elsif n>l then maxnum:=n;else maxnum:=l;end if;dbms_output.put_line('max is '|maxnum);END;3.編寫PL/SQL語句塊，要求使用循環(huán)結(jié)構(gòu)來計算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.查詢

46、圖書中有沒有英語書和SQL Server方面的書，如果有則統(tǒng)計其冊數(shù)。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=false thenopen bookcur;end if;fetch bookcur into bname_book;while bookcur%found loopif instr(bname_book,'英語')>0 then e

47、_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('英語書的數(shù)量為：'|e_count);dbms_output.put_line('SQL SERVER書的數(shù)量為：'|o_count);end;【任務(wù)10】存儲過程1

48、.創(chuàng)建存儲過程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 andborrow.b_id=book.b_id and returndate is null;bid borrow.b_id%type;zhuozhe var

49、char2(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|','|borrowday|','|shuming|','|zhuozhe);fetch pro_b

50、orrow_cur into bid,duzhe,borrowday,shuming,zhuozhe;end loop;close pro_borrow_cur;end;2.調(diào)用存儲過程PRO_Borrow，查詢所有未還圖書的詳細(xì)信息。exec pro_borrowbeginpro_borrow;end;3.創(chuàng)建帶參數(shù)的存儲過程PRO_Borrow_RID，要求該存儲過程能夠根據(jù)輸入的借書證號返回該讀者的所有借閱信息，包括借閱日期、還書日期、圖書名稱、圖書ISBN號。create or replace procedure pro_borrow_rid(rid in varchar2)iscur

51、sor 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.調(diào)用存儲過程PRO_Borrow_RID，查詢借書證號為“”的