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）課程設

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

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

4、創建Borrow表（借閱表）和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 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【任務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

8、字段，數據類型為VARCHAR2(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是

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）字段，數據類型為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財經應用文寫作教程甘佩欽

11、03P0022012-3-133.0007B平面構成設計教程姜巧玲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_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清華大學出版社010-P004北京大學出版社010-P005中國鐵道出版社010-P006北京交通大學出版社010-P007北京交大出版社010-insert into booktype values('01,'計算機類',NULL);inse

13、rt 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','教師',

14、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,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','清華大學出版社','010-');insert into publish values('P004','北京大學出版社','010-');insert into publish values('P005','中國鐵道出版社','010-');insert into publish v

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

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

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

21、;,'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.0

22、0,'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 int

23、o 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.使用SQL語言將Book表中圖書編號為B的出版社編號修改為“P002”.update book set p_id='P002' where b_id

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

25、可以借書數量以及借書天數，輸出的結果字段名分別用借書本數和借書期限表示。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 where to_cha

26、r(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 f

27、rom borrow,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,

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.查詢每一個出版社出版的書籍的數量。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應用

31、技術')15.查詢所有單價小于平均單價的圖書號、書名、出版社。16.查詢與SQL SERVER應用技術同一出版社的所有圖書的圖書名稱、作者、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

32、_id=borrow.b_id and r_name='楊倩'19.查詢借閱了“高等教育出版社”出版的書名中包含有“數據庫”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、 '%數據庫%'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.查詢借閱了“高等教育出版社”出版的書名中包含有“數據庫”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 '%數據庫%'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.查詢借閱了“高等教育出版社”出版的書名中包含有“數據庫”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 '%數據庫%'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%'【任務7】索引、視圖、序列及同義詞1.為Book圖書表的書名列創建惟一索引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.創建一個借閱統計視圖，名為V_Count_1，包含讀者的借書證號和總借閱本數，要求該視圖為只讀。create view v_count_1(借書證號,總借閱本數) asselect r_id,count(*) from borrow group by r_id with read only6.創建一個借閱統計視圖，名為V_Count_2，包含借閱總本數大于兩本的讀者號和總借閱本數。create view v_count_2(讀者號,總借閱本數) asselect

39、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=book.p_id and pubname='人民郵電出版社' with check option8.刪除視圖V_Pub。drop view v_pub9.

40、創建序列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字段由序列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【任務8】常量、變量和系統函數1.編寫程序實現將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.輸出當前系統日期月份和年份。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 2008 ”左右兩端的窗格并輸出。begindbms_output.put_

43、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_output.put_line('sum is'

44、|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 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語句塊，要求使用循環結構來計算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方面的書，如果有則統計其冊數。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('英語書的數量為：'|e_count);dbms_output.put_line('SQL SERVER書的數量為：'|o_count);end;【任務10】存儲過程1

48、.創建存儲過程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.調用存儲過程PRO_Borrow，查詢所有未還圖書的詳細信息。exec pro_borrowbeginpro_borrow;end;3.創建帶參數的存儲過程PRO_Borrow_RID，要求該存儲過程能夠根據輸入的借書證號返回該讀者的所有借閱信息，包括借閱日期、還書日期、圖書名稱、圖書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.調用存儲過程PRO_Borrow_RID，查詢借書證號為“”的