圖書信息管理系統(tǒng)實驗報告

圖書信息管理系統(tǒng)實驗報告目錄1 實驗1—實驗環(huán)境搭建 31.1 實驗環(huán)境概述 31.2 ORACLE10G安裝 31.3 表空間創(chuàng)建 41.4 數(shù)據(jù)庫用戶創(chuàng)建 41.5 系統(tǒng)實體表結(jié)構(gòu) 52 實驗2－基表操作 82.1 基表關(guān)系圖 82.2 實體表數(shù)據(jù)操作 92.2.1 使用SQL*LOADER插入原始數(shù)據(jù) 92.2.2 使用SQL*PLUS插入原始數(shù)據(jù) 102.2.3 數(shù)據(jù)查詢 112.2.4 數(shù)據(jù)修改和刪除 113 實驗3－SQLPLUS學(xué)習(xí) 123.1 執(zhí)行SQL腳本文件 123.1.1 顯示錯誤信息 123.1.2 顯示表結(jié)構(gòu) 123.1.3 改變?nèi)笔〉牧袠?biāo)題 133.1.4 改變列的顯示長度 133.1.5 設(shè)置每頁行數(shù) 133.1.6 顯示每個SQL語句花費的執(zhí)行時間 143.1.7 顯示SQLbuffer中的SQL語句 143.1.8 編輯執(zhí)行SQLbuffer中的SQL語句 154 實驗4－創(chuàng)建基表 164.1 基表創(chuàng)建腳本 164.1.1 圖書信息表BOOK_INFO（實體表） 164.1.2 作者信息表AUTHOR_INFO（實體表） 164.1.3 圖書作者關(guān)系表BOOK_AUTHOR 174.1.4 出版社信息表PUBLISHER_INFO 184.1.5 圖書出版社關(guān)系表BOOK_PUBLISHER 184.1.6 出版社分類參數(shù)信息表PUBLISHER_CLASS_INFO（實體表） 194.1.7 出版社分類關(guān)系表PUBLISHER_CLASS 194.1.8 圖書分類參數(shù)信息表BOOK_CLASS_INFO（實體表） 204.1.9 圖書分類關(guān)系表BOOK_CLASS 214.1.10 角色信息表ROLE_INFO 214.1.11 用戶信息表USER_INFO（實體表，依賴角色表） 224.1.12 用戶借書關(guān)系表USER_BOOK 234.1.13 預(yù)定關(guān)系表USER_RESERVE 234.1.14 書本評論表BOOK_COMMENT 245 實驗5－PL/SQL基礎(chǔ) 265.1 按出版社查詢購書金額的平均值 265.2 按出版社查詢購書金額的最高及最低購書金額 275.3 查詢出版社購書金額超過平均值的出版社數(shù) 285.4 查詢購書金額高于1萬元的出版社數(shù) 295.5 查詢本月借出書的數(shù)量 296 實驗6—基表數(shù)據(jù)操作 316.1 在TOAD中插入記錄 316.2 SQLPLUS在基表中插入記錄 326.3 一年內(nèi)無借閱記錄的圖書在備注欄中注明 336.4 刪除圖書借閱信息中超過兩年且已歸還的記錄 337 實驗7—索引和完整性 347.1 建立出版社名稱不能重名的索引 347.2 建立圖書借閱信息中書號加借閱日期加借書卡號的索引 347.3 完善域完整性、實體完整性和參照完整性 358 實驗8—數(shù)據(jù)庫的查詢和視圖 368.1 建立查詢數(shù)據(jù)視圖 368.2 實現(xiàn)對各基表及多表的數(shù)據(jù)查詢 369 實驗9—PL/SQL編程 389.1 統(tǒng)計本月圖書借出總數(shù)量、總金額 389.2 使用循環(huán)結(jié)構(gòu)求和 399.3 使用CASE語句查圖書的所屬種類 409.4 使用游標(biāo)統(tǒng)計當(dāng)前未歸還圖書的總金額 4110 實驗10—存儲過程與觸發(fā)器 4210.1 存儲過程 4210.2 觸發(fā)器 4410.2.1 BOOK_INFO刪除操作審計 4410.2.2 自動更新出版社購書數(shù)量觸發(fā)器 4411 實驗11—函數(shù)與包 4711.1 函數(shù) 4711.1.1 借出圖書數(shù) 4711.1.2 歸還圖書數(shù) 4811.2 包 4912 實驗12—用戶、角色和概要文件 5312.1 用戶 5312.2 角色 5412.3 概要文件 5513 實驗13—系統(tǒng)數(shù)據(jù)維護(hù) 5713.1 邏輯備份 5813.2 邏輯導(dǎo)出 5813.3 邏輯導(dǎo)入 59

實驗1—實驗環(huán)境搭建實驗環(huán)境概述操作系統(tǒng)：WindowsXPSP2數(shù)據(jù)庫：Oracle10g10.1.0.2企業(yè)版數(shù)據(jù)庫管理工具：ToadforOracleVersion9.0.1

數(shù)據(jù)庫客戶端：Oracle10gClientORACLE10G安裝Oracle10g對軟硬件環(huán)境要求：內(nèi)存最小需要：512MB系統(tǒng)交換區(qū)SWAP最小需要：1GB或2倍內(nèi)存容量的空間，如果內(nèi)存大于等于2GB那么SWAP在1倍或2倍內(nèi)存容量之間。/tmp最小需要：400MB磁盤空間最小需要：3.7GB其中2.5GB用于oraclesoftwarefiles，1.2GB用于databasefiles。系統(tǒng)結(jié)構(gòu)必須是：64位表空間創(chuàng)建創(chuàng)建的表空間名為BOOK_DATA，由于本數(shù)據(jù)庫規(guī)模不大，故無需單獨建立索引表空間BOOK_INDEX。createtablespacebook_datadatafile'D:\oracle\product\10.1.0\oradata\orcl\book_data.dbf'size200mautoextendonnext10mmaxsize2000mextentmanagementlocaluniform;數(shù)據(jù)庫用戶創(chuàng)建首先用SYS用戶登錄數(shù)據(jù)庫BOOK，然后添加本系統(tǒng)得管理員用戶BOOK，并給其賦予DBA權(quán)限。同時，要建立供其他人員使用的用戶，如TEACHER和STUDENT這些用戶只有修改和查詢的權(quán)限，其它用戶的創(chuàng)建及權(quán)限詳見實驗12。createuserbookidentifiedbybookdefaulttablespacebook_datatemporarytablespacetemp;grantdbatobook;關(guān)聯(lián)用戶和表空間alteruserbookidentifiedbybookdefaulttablespacebook_datatemporarytablespacetemp;系統(tǒng)實體表結(jié)構(gòu)圖書信息表BOOK_INFO字段名注釋類型約束備注BOOK_ID書本編號NUMBER(10)非空，主鍵BOOK_NAME書本名VARCHAR2(20CHAR)BOOK_PUBDATE出版日期DATEBOOK_PRICE書本價格NUMBER(10,2)BOOK_QUANTITY庫存數(shù)量NUMBER(10)BOOK_INFO書本簡介VARCHAR2(100CHAR)BOOK_RESNUMBER預(yù)訂數(shù)量NUMBER(6)BOOK_REMARK備注VARCHAR2(100CHAR)作者信息表AUTHOR_INFO字段名注釋類型約束備注AUTHOR_ID作者編號NUMBER(10)非空，主鍵AUTHOR_NAME作者名VARCHAR2(20CHAR)AUTHOR_INFO作者簡介VARCHAR2(100CHAR)圖書作者關(guān)系表BOOK_AUTHOR字段名注釋類型約束備注BOOK_ID書本編號NUMBER(10)非空，外鍵AUTHOR_ID作者編號NUMBER(10)非空，外鍵出版社信息表PUBLISHER_INFO字段名注釋類型約束備注PUBLISHER_ID出版社編號NUMBER(10)非空，主鍵PUBLISHER_NAME出版社名字VARCHAR2(20CHAR)非空PUBLISHER_ADD出版社地址VARCHAR2(100CHAR)PUBLISHER_BOOK_NUMBER出版社圖書數(shù)量NUMBER(10)圖書出版社關(guān)系表BOOK_PUBLISHER字段名注釋類型約束備注BOOK_ID書本編號NUMBER(10)非空，外鍵PUBLISHER_ID出版社編號NUMBER(10)非空，外鍵出版社分類參數(shù)信息表PUBLISHER_CLASS_INFO字段名注釋類型約束備注PUBLISHER_CLASS_ID出版社分類編號NUMBER(10)非空，主鍵PUBLISHER_CLASS_NAME出版社分類名字VARCHAR2(20CHAR)出版社分類關(guān)系表PUBLISHER_CLASS字段名注釋類型約束備注PUBLISHER_ID出版社編號NUMBER(10)非空，外鍵PUBLISHER_CLASS_ID出版社分類編號NUMBER(10)非空，外鍵圖書分類參數(shù)信息表BOOK_CLASS_INFO字段名注釋類型約束備注BOOK_CLASS_ID書本分類編號NUMBER(10)非空，主鍵BOOK_CLASS_NAME書本分類名稱VARCHAR2(20CHAR)非空圖書分類關(guān)系表BOOK_CLASS字段名注釋類型約束備注BOOK_ID書本編號NUMBER(10)非空，外鍵BOOK_CLASS_ID書本分類編號NUMBER(10)非空，外鍵角色信息表ROLE_INFO字段名注釋類型約束備注ROLE_ID角色編號NUMBER(10)非空，主鍵ROLE_NAME角色名稱VARCHAR2(10CHAR)非空MAX_BORROW_COUNT最大借書數(shù)量NUMBER(2)非空ADMIN是否管理員NUMBER(1)非空用戶信息表USER_INFO字段名注釋類型約束備注USER_ID用戶編號NUMBER(10非空，主鍵USER_NAME登入賬號VARCHAR2(15CHAR)非空USER_REALNAME真實姓名VARCHAR2(20CHAR)非空USER_PASSWORD登入密碼VARCHAR2(15CHAR)非空USER_SEX性別NUMBER(1)男0女1USER_EMAIL郵箱VARCHAR2(20CHAR)USER_TELEPHONE電話NUMBER(11)ROLE_ID角色編號NUMBER(5)非空，外鍵USER_BORROW_COUNT已經(jīng)借書數(shù)量NUMBER(2)用戶借書關(guān)系表USER_BOOK字段名注釋類型約束備注USER_ID用戶編號NUMBER(10)非空，外鍵BOOK_ID書本編號NUMBER(10)非空，外鍵BORROW_DATE借書數(shù)量DATERETURN_DATE歸還日期DATE預(yù)定關(guān)系表USER_RESERVE字段名注釋類型約束備注USER_ID用戶編號NUMBER(10)非空，外鍵BOOK_ID書本編號NUMBER(10)非空，外鍵RESERVE_DATE預(yù)約日期DATE非空書本評論表BOOK_COMMENT字段名注釋類型約束備注COMMENT_ID評論編號NUMBER(12)非空，主鍵COMMENT_TEXT評論內(nèi)容VARCHAR2(200CHAR)COMMENT_DATE評論日期DATEBOOK_ID書本編號NUMBER(10)非空，外鍵USER_ID用戶編號NUMBER(10)非空，外鍵

實驗2－基表操作基表關(guān)系圖實體表數(shù)據(jù)操作使用SQL*LOADER插入原始數(shù)據(jù)下面以書本信息表為例：整理Excel數(shù)據(jù)原始文件，“另存為”“文本文件（制表符分隔）”，取名為book10.txt。編輯一個和Excel數(shù)據(jù)相對應(yīng)的表。編輯控制文件，控制文件取名為：book10.ctl其中格式如下：Loaddatainfile'd:\book10.txt'AppendintotableBOOK_INFOfieldsterminatedbyX'09'(book_id,book_name,book_pubdateDate"yyyy-mm-dd",book_price,book_quantity,book_info,book_resnumber)調(diào)用SQL*LOADER，加載數(shù)據(jù)Sqlldrbook/bookcontrol=d:\book10.ctl添加數(shù)據(jù)結(jié)果：使用SQL*PLUS插入原始數(shù)據(jù)其中圖書信息表數(shù)據(jù)較多，采用SQLLOAD方法導(dǎo)入數(shù)據(jù)，其他表采用SQL語句插入數(shù)據(jù)，以圖書種類關(guān)系表為例：添加圖書種類關(guān)系信息insertintobook_classselectbook_id,10frombook_infowherebook_idlike'10%';insertintobook_classselectbook_id,20frombook_infowherebook_idlike'20%';insertintobook_classselectbook_id,30frombook_infowherebook_idlike'30%';insertintobook_classselectbook_id,40frombook_infowherebook_idlike'40%';commit;數(shù)據(jù)查詢查詢書本信息表中編號為10開頭的全部書籍：Select*frombook_infowherebook_idlike‘10%’;數(shù)據(jù)修改和刪除刪除用戶借閱表里編號是“3006”用戶的所有借閱情況。圖中顯示已經(jīng)刪除了6條信息。

實驗3－SQLPLUS學(xué)習(xí)執(zhí)行SQL腳本文件SQL>connbook/bookConnected.SQL>alteruserbook2identifiedbybook3defaulttablespacebook_data4identifiedbybookSQL>createtablespacebook_data2datafile'D:\oracle\product\10.1.0\oradata\orcl\book_data.dbf'size200m3autoextendonnext10mmaxsize2000m4extentmanagementlocaluniform;createtablespacebook_data顯示錯誤信息ERRORatline1:ORA-01119:errorincreatingdatabasefile'D:\oracle\product\10.1.0\oradata\orcl\book_data.dbf'ORA-27038:createdfilealreadyexistsOSD-04010:<create>optionspecified,filealreadyexists顯示表結(jié)構(gòu)顯示書本信息表的結(jié)構(gòu)：descbook_info改變?nèi)笔〉牧袠?biāo)題修改書本信息表的列名name為“書本名”Selectbook_name“書本名”frombook_infoWherebook_idbetween100001and100005；改變列的顯示長度設(shè)置一行可以容納的字符數(shù)

SQL>

SET

LIN[ESIZE]

{80|n}

如果一行的輸出內(nèi)容大于設(shè)置的一行可容納的字符數(shù)，則折行顯示SQL>setlin30設(shè)置每頁行數(shù)設(shè)置一頁有多少行數(shù)

SQL>

SET

PAGES[IZE]

{24|n}SQL>setpagesize6顯示每個SQL語句花費的執(zhí)行時間顯示每個sql語句花費的執(zhí)行時間

set

TIMING

{ON|OFF}

SQL>settimingon顯示SQLbuffer中的SQL語句顯示sqlbuffer中的sql語句，listn顯示sqlbuffer中的第n行，并使第n行成為當(dāng)前行L[IST][n]編輯執(zhí)行SQLbuffer中的SQL語句編輯sqlbuffer中的sql語句EDI[T]顯示sql

buffer中的sql語句，list

n顯示sql

buffer中的第n行，并使第n行成為當(dāng)前行

L[IST]

[n]

將sql

buffer中的sql語句保存到一個文件中

SAVE

file_name

將一個文件中的sql語句導(dǎo)入到sql

buffer中

GET

file_name

再次執(zhí)行剛才已經(jīng)執(zhí)行的sql語句

RUN

or

/

實驗4－創(chuàng)建基表基表創(chuàng)建腳本圖書信息表BOOK_INFO（實體表）CREATETABLEBOOK_INFO(BOOK_IDNUMBER(10)NOTNULL,BOOK_NAMEVARCHAR2(20CHAR)NOTNULL,BOOK_PUBDATEDATE,BOOK_PRICENUMBER(10,2),BOOK_QUANTITYNUMBER(10),BOOK_INFOVARCHAR2(100CHAR),BOOK_RESNUMBERNUMBER(6))TABLESPACEBOOK_DATA;CREATEUNIQUEINDEXBOOK_INFO_PKONBOOK_INFO(BOOK_ID)LOGGINGTABLESPACEBOOK_DATA;ALTERTABLEBOOK_INFOADD(CONSTRAINTBOOK_INFO_PKPRIMARYKEY(BOOK_ID)USINGINDEXTABLESPACEBOOK_DATA);作者信息表AUTHOR_INFO（實體表）CREATETABLEAUTHOR_INFO(AUTHOR_IDNUMBER(10)NOTNULL,AUTHOR_NAMEVARCHAR2(20CHAR),AUTHOR_INFOVARCHAR2(100CHAR))TABLESPACEBOOK_DATA;CREATEUNIQUEINDEXAUTHOR_INFO_PKONAUTHOR_INFO(AUTHOR_ID)LOGGINGTABLESPACEBOOK_DATA;ALTERTABLEAUTHOR_INFOADD(CONSTRAINTAUTHOR_INFO_PKPRIMARYKEY(AUTHOR_ID)USINGINDEXTABLESPACEBOOK_DATA);圖書作者關(guān)系表BOOK_AUTHORCREATETABLEBOOK_AUTHOR(BOOK_IDNUMBER(10)NOTNULL,AUTHOR_IDNUMBER(10)NOTNULL)TABLESPACEBOOK_DATA;ALTERTABLEBOOK_AUTHORADD(PRIMARYKEY(BOOK_ID,AUTHOR_ID)USINGINDEXTABLESPACEBOOK_DATA);ALTERTABLEBOOK_AUTHORADD(FOREIGNKEY(BOOK_ID)REFERENCESBOOK_INFO(BOOK_ID));ALTERTABLEBOOK_AUTHORADD(CONSTRAINTBOOK_AUTHOR_R01FOREIGNKEY(AUTHOR_ID)REFERENCESAUTHOR_INFO(AUTHOR_ID));出版社信息表PUBLISHER_INFOCREATETABLEPUBLISHER_INFO(PUBLISHER_IDNUMBER(10)NOTNULL,PUBLISHER_NAMEVARCHAR2(20CHAR)NOTNULL,PUBLISHER_ADDVARCHAR2(100CHAR))TABLESPACEBOOK_DATA;CREATEUNIQUEINDEXPUBLISHER_INFO_PKONPUBLISHER_INFO(PUBLISHER_ID)LOGGINGTABLESPACEBOOK_DATA;ALTERTABLEPUBLISHER_INFOADD(CONSTRAINTPUBLISHER_INFO_PKPRIMARYKEY(PUBLISHER_ID)USINGINDEXTABLESPACEBOOK_DATA);圖書出版社關(guān)系表BOOK_PUBLISHERCREATETABLEBOOK_PUBLISHER(BOOK_IDNUMBER(10)NOTNULL,PUBLISHER_IDNUMBER(10)NOTNULL)TABLESPACEBOOK_DATA;CREATEUNIQUEINDEXBOOK_PUBLISHER_PKONBOOK_PUBLISHER(BOOK_ID,PUBLISHER_ID)LOGGINGTABLESPACEBOOK_DATA;ALTERTABLEBOOK_PUBLISHERADD(CONSTRAINTBOOK_PUBLISHER_PKPRIMARYKEY(BOOK_ID,PUBLISHER_ID)USINGINDEXTABLESPACEBOOK_DATA);ALTERTABLEBOOK_PUBLISHERADD(CONSTRAINTBOOK_PUBLISHER_R01FOREIGNKEY(BOOK_ID)REFERENCESBOOK_INFO(BOOK_ID));ALTERTABLEBOOK_PUBLISHERADD(CONSTRAINTBOOK_PUBLISHER_R02FOREIGNKEY(PUBLISHER_ID)REFERENCESPUBLISHER_INFO(PUBLISHER_ID));出版社分類參數(shù)信息表PUBLISHER_CLASS_INFO（實體表）CREATETABLEPUBLISHER_CLASS_INFO(PUBLISHER_CLASS_IDNUMBER(10)NOTNULL,PUBLISHER_CLASS_NAMEVARCHAR2(20CHAR)NOTNULL)TABLESPACEBOOK_DATA;CREATEUNIQUEINDEXPUBLISHER_CLASS_INFO_PKONPUBLISHER_CLASS_INFO(PUBLISHER_CLASS_ID)LOGGINGTABLESPACEBOOK_DATA;ALTERTABLEPUBLISHER_CLASS_INFOADD(CONSTRAINTPUBLISHER_CLASS_INFO_PKPRIMARYKEY(PUBLISHER_CLASS_ID)USINGINDEXTABLESPACEBOOK_DATA);出版社分類關(guān)系表PUBLISHER_CLASSCREATETABLEPUBLISHER_CLASS(PUBLISHER_IDNUMBER(10)NOTNULL,PUBLISHER_CLASS_IDNUMBER(10)NOTNULL)TABLESPACEBOOK_DATA;CREATEUNIQUEINDEXPUBLISHER_CLASS_PKONPUBLISHER_CLASS(PUBLISHER_ID,PUBLISHER_CLASS_ID)LOGGINGTABLESPACEBOOK_DATA;ALTERTABLEPUBLISHER_CLASSADD(CONSTRAINTPUBLISHER_CLASS_PKPRIMARYKEY(PUBLISHER_ID,PUBLISHER_CLASS_ID)USINGINDEXTABLESPACEBOOK_DATA);ALTERTABLEPUBLISHER_CLASSADD(CONSTRAINTPUBLISHER_CLASS_R02FOREIGNKEY(PUBLISHER_ID)REFERENCESPUBLISHER_INFO(PUBLISHER_ID));ALTERTABLEPUBLISHER_CLASSADD(CONSTRAINTPUBLISHER_CLASS_R01FOREIGNKEY(PUBLISHER_CLASS_ID)REFERENCESPUBLISHER_CLASS_INFO(PUBLISHER_CLASS_ID));圖書分類參數(shù)信息表BOOK_CLASS_INFO（實體表）CREATETABLEBOOK_CLASS_INFO(BOOK_CLASS_IDNUMBER(10)NOTNULL,BOOK_CLASS_NAMEVARCHAR2(20CHAR)NOTNULL)TABLESPACEBOOK_DATA;CREATEUNIQUEINDEXBOOK_CLASS_INFO_PKONBOOK_CLASS_INFO(BOOK_CLASS_ID)LOGGINGTABLESPACEBOOK_DATA;ALTERTABLEBOOK_CLASS_INFOADD(CONSTRAINTBOOK_CLASS_INFO_PKPRIMARYKEY(BOOK_CLASS_ID)USINGINDEXTABLESPACEBOOK_DATA);圖書分類關(guān)系表BOOK_CLASSCREATETABLEBOOK_CLASS(BOOK_IDNUMBER(10)NOTNULL,BOOK_CLASS_IDNUMBER(10)NOTNULL)TABLESPACEBOOK_DATA;CREATEUNIQUEINDEXBOOK_CLASS_PKONBOOK_CLASS(BOOK_ID,BOOK_CLASS_ID)LOGGINGTABLESPACEBOOK_DATA;ALTERTABLEBOOK_CLASSADD(CONSTRAINTBOOK_CLASS_PKPRIMARYKEY(BOOK_ID,BOOK_CLASS_ID)USINGINDEXTABLESPACEBOOK_DATA);ALTERTABLEBOOK_CLASSADD(CONSTRAINTBOOK_CLASS_R01FOREIGNKEY(BOOK_ID)REFERENCESBOOK_INFO(BOOK_ID));ALTERTABLEBOOK_CLASSADD(CONSTRAINTBOOK_CLASS_R02FOREIGNKEY(BOOK_CLASS_ID)REFERENCESBOOK_CLASS_INFO(BOOK_CLASS_ID));角色信息表ROLE_INFOCREATETABLEROLE_INFO(ROLE_IDNUMBER(10)NOTNULL,ROLE_NAMEVARCHAR2(10CHAR)NOTNULL,MAX_BORROW_COUNTNUMBER(2)NOTNULL,ADMINNUMBER(1)NOTNULL)TABLESPACEBOOK_DATA;CREATEUNIQUEINDEXROLE_INFO_PKONROLE_INFO(ROLE_ID)LOGGINGTABLESPACEBOOK_DATA;ALTERTABLEROLE_INFOADD(CONSTRAINTROLE_INFO_PKPRIMARYKEY(ROLE_ID)USINGINDEXTABLESPACEBOOK_DATA);用戶信息表USER_INFO（實體表，依賴角色表）CREATETABLEUSER_INFO(USER_IDNUMBER(10)NOTNULL,USER_NAMEVARCHAR2(15CHAR)NOTNULL,USER_REALNAMEVARCHAR2(20CHAR)NOTNULL,USER_PASSWORDVARCHAR2(15CHAR)NOTNULL,USER_SEXNUMBER(1),USER_EMAILVARCHAR2(20CHAR),USER_TELEPHONENUMBER(11),ROLE_IDNUMBER(5)NOTNULL,USER_BORROW_COUNTNUMBER(2))TABLESPACEBOOK_DATA;CREATEUNIQUEINDEXUSER_INFO_PKONUSER_INFO(USER_ID)LOGGINGTABLESPACEBOOK_DATA;ALTERTABLEUSER_INFOADD(CONSTRAINTUSER_INFO_PKPRIMARYKEY(USER_ID)USINGINDEXTABLESPACEBOOK_DATA);ALTERTABLEUSER_INFOADD(CONSTRAINTUSER_INFO_R01FOREIGNKEY(ROLE_ID)REFERENCESROLE_INFO(ROLE_ID));用戶借書關(guān)系表USER_BOOKCREATETABLEUSER_BOOK(USER_IDNUMBER(10)NOTNULL,BOOK_IDNUMBER(10)NOTNULL,BORROW_DATEDATE,RETURN_DATEDATE)TABLESPACEBOOK_DATA;CREATEUNIQUEINDEXUSER_BOOK_PKONUSER_BOOK(USER_ID,BOOK_ID)LOGGINGTABLESPACEBOOK_DATA;ALTERTABLEUSER_BOOKADD(CONSTRAINTUSER_BOOK_PKPRIMARYKEY(USER_ID,BOOK_ID)USINGINDEXTABLESPACEBOOK_DATA);ALTERTABLEUSER_BOOKADD(CONSTRAINTUSER_BOOK_R01FOREIGNKEY(USER_ID)REFERENCESUSER_INFO(USER_ID));ALTERTABLEUSER_BOOKADD(CONSTRAINTUSER_BOOK_R02FOREIGNKEY(BOOK_ID)REFERENCESBOOK_INFO(BOOK_ID));預(yù)定關(guān)系表USER_RESERVECREATETABLEUSER_RESERVE(USER_IDNUMBER(10)NOTNULL,BOOK_IDNUMBER(10)NOTNULL,RESERVE_DATEDATENOTNULL)TABLESPACEBOOK_DATA;CREATEUNIQUEINDEXRESERVE_PKONUSER_RESERVE(USER_ID,BOOK_ID)LOGGINGTABLESPACEBOOK_DATA;ALTERTABLEUSER_RESERVEADD(CONSTRAINTRESERVE_PKPRIMARYKEY(USER_ID,BOOK_ID)USINGINDEXTABLESPACEBOOK_DATA);ALTERTABLEUSER_RESERVEADD(CONSTRAINTRESERVE_R01FOREIGNKEY(USER_ID)REFERENCESUSER_INFO(USER_ID));ALTERTABLEUSER_RESERVEADD(CONSTRAINTRESERVE_R02FOREIGNKEY(BOOK_ID)REFERENCESBOOK_INFO(BOOK_ID));書本評論表BOOK_COMMENTCREATETABLEBOOK_COMMENT(COMMENT_IDNUMBER(12)NOTNULL,COMMENT_TEXTVARCHAR2(200CHAR),COMMENT_DATEDATE,BOOK_IDNUMBER(10)NOTNULL,USER_IDNUMBER(10)NOTNULL)TABLESPACEBOOK_DATA;CREATEUNIQUEINDEXBOOK_COMMENT_PKONBOOK_COMMENT(COMMENT_ID)LOGGINGTABLESPACEBOOK_DATA;ALTERTABLEBOOK_COMMENTADD(CONSTRAINTBOOK_COMMENT_PKPRIMARYKEY(COMMENT_ID)USINGINDEXTABLESPACEBOOK_DATA);ALTERTABLEBOOK_COMMENTADD(CONSTRAINTBOOK_COMMENT_R01FOREIGNKEY(BOOK_ID)REFERENCESBOOK_INFO(BOOK_ID));ALTERTABLEBOOK_COMMENTADD(CONSTRAINTBOOK_COMMENT_R02FOREIGNKEY(USER_ID)REFERENCESUSER_INFO(USER_ID));

實驗5－PL/SQL基礎(chǔ)按出版社查詢購書金額的平均值DECLARECURSORc_price_avgISSELECTc.publisher_name,AVG(book_price)FROMbook_infoa,book_publisherb,publisher_infocWHEREa.book_id=b.book_idANDb.publisher_id=c.publisher_idGROUPBYc.publisher_name;v_price_avgNUMBER(10,2);v_publisherVARCHAR2(30);BEGINOPENc_price_avg;LOOPFETCHc_price_avgINTOv_publisher,v_price_avg;EXITWHENc_price_avg%NOTFOUND;DBMS_OUTPUT.put_line(v_publisher||'的平均購書金額'||v_price_avg);ENDLOOP;CLOSEc_price_avg;END;運行結(jié)果如下：按出版社查詢購書金額的最高及最低購書金額DECLAREv_max_priceNUMBER(10,2);v_min_priceNUMBER(10,2);BEGINSELECTMAX(sum_book_price),MIN(sum_book_price)INTOv_max_price,v_min_priceFROM(SELECTc.publisher_nameASpublisher_name,SUM(book_price)ASsum_book_priceFROMbook_infoa,book_publisherb,publisher_infocWHEREa.book_id=b.book_idANDb.publisher_id=c.publisher_idGROUPBYc.publisher_name);DBMS_OUTPUT.put_line('最高購書金額：'||v_max_price);DBMS_OUTPUT.put_line('最低購書金額：'||v_min_price);END;運行結(jié)果如下：查詢出版社購書金額超過平均值的出版社數(shù)DECLAREv_excess_price_avgNUMBER(10,2);BEGINSELECTCOUNT(*)INTOv_excess_price_avgFROM(SELECTc.publisher_name,AVG(book_price)ASprice_avgFROMbook_infoa,book_publisherb,publisher_infocWHEREa.book_id=b.book_idANDb.publisher_id=c.publisher_idGROUPBYc.publisher_name)aWHEREa.price_avg>(SELECTAVG(price_avg)FROM(SELECTc.publisher_name,AVG(book_price)ASprice_avgFROMbook_infoa,book_publisherb,publisher_infocWHEREa.book_id=b.book_idANDb.publisher_id=c.publisher_idGROUPBYc.publisher_name));DBMS_OUTPUT.put_line('超過平均值的出版社數(shù):'||v_excess_price_avg);END;運行結(jié)果如下:查詢購書金額高于1萬元的出版社數(shù)DECLAREv_excess_priceNUMBER(10,2);BEGINSELECTCOUNT(*)INTOv_excess_priceFROM(SELECTc.publisher_name,SUM(book_price)ASsum_priceFROMbook_infoa,book_publisherb,publisher_infocWHEREa.book_id=b.book_idANDb.publisher_id=c.publisher_idGROUPBYc.publisher_name)aWHEREa.sum_price>10000;DBMS_OUTPUT.put_line('購書金額高于1萬元的出版社數(shù):'||v_excess_price);END;運行結(jié)果如下：查詢本月借出書的數(shù)量查詢本月借出書的數(shù)量，如果高于100本時，顯示‘本月借出書大于100本’，否則顯示‘本月借出書小于100本’，代碼如下:DECLAREv_month_numberNUMBER;BEGINSELECTCOUNT(*)INTOv_month_numberFROMuser_bookWHEREborrow_dateBETWEENTO_DATE('20071201','yyyymmdd')ANDTO_DATE('20071231','yyyymmdd');DBMS_OUTPUT.put_line('12月借出圖書數(shù)為：'||v_month_number);IFv_month_number>100THENDBMS_OUTPUT.put_line('本月借出圖書數(shù)大于100本！');ELSEDBMS_OUTPUT.put_line('本月借出圖書數(shù)小于100本！');ENDIF;END;運行結(jié)果如下：

實驗6—基表數(shù)據(jù)操作在TOAD中插入記錄在TOAD中使用可視化工具插入數(shù)據(jù)：點擊insertrecord已經(jīng)插入書本信息表的記錄：插入書本信息表的記錄SQLPLUS在基表中插入記錄插入出版社類別關(guān)系表（前提：已經(jīng)有publisher_info,publisher_class_info）insertintopublisher_classselectpublisher_id,1frompublisher_infowherepublisher_idlike'1%'insertintopublisher_classselectpublisher_id,2frompublisher_infowherepublisher_idlike'2%'commit插入書本出版社關(guān)系表（前提：book_publisher_temp，publisher_info）insertintobook_publisherselectbook_id,publisher_idfrombook_publisher_tempa,publisher_infobwherea.publisher_name=b.publisher_namecommit一年內(nèi)無借閱記錄的圖書在備注欄中注明updatebook_infosetbook_remark='2007年無借閱記錄'wherebook_idin(selectbook_idfrombook_infowherebook_idnotin(selectdistinctbook_idfromuser_bookwhereborrow_datebetweento_date('20070101','yyyymmdd')andto_date('20071231','yyyymmdd'))orderbybook_id);commit;刪除圖書借閱信息中超過兩年且已歸還的記錄deletefromuser_bookwheremonths_between(sysdate,borrow_date)>24andreturn_dateisnotnull;commit;

實驗7—索引和完整性建立出版社名稱不能重名的索引CREATEUNIQUEINDEXBOOK_NAMEONBOOK_INFO(BOOK_NAME)LOGGINGTABLESPACEBOOK_DATASTORAGE(INITIAL20KNEXT20KPCTINCREASE75)PCTFREE0;查詢創(chuàng)建結(jié)果：建立圖書借閱信息中書號加借閱日期加借書卡號的索引CREATEUNIQUEINDEXBOOK_BORROW_INDEXONUSER_BOOK(USER_ID,BOOK_ID,BORROW_DATE)LOGGINGTABLESPACEBOOK_DATASTORAGE(INITIAL20KNEXT20KPCTINCREASE75)PCTFREE0;查詢創(chuàng)建結(jié)果：完善域完整性、實體完整性和參照完整性完整性約束用于增強數(shù)據(jù)的完整性，Oracle提供了5種完整性約束：CheckNOTNULLUniquePrimaryForeignkey完整性約束是一種規(guī)則，不占用任何數(shù)據(jù)庫空間。完整性約束存在數(shù)據(jù)字典中，在執(zhí)行SQL或PL/SQL期間使用。用戶可以指明約束是啟用的還是禁用的，當(dāng)約束啟用時，他增強了數(shù)據(jù)的完整性，否則，則反之，但約束始終存在于數(shù)據(jù)字典中。在實驗4中，每個基表的建立的同時，都建立了相應(yīng)主鍵、外鍵，即域完橫性、實體完整性和參照完整性。詳見實驗4的基表創(chuàng)建腳本。以作者信息表為例子添加外鍵約束：ALTERTABLEAUTHOR_INFOADD(CONSTRAINTAUTHOR_INFO_PKPRIMARYKEY(AUTHOR_ID)USINGINDEXTABLESPACEBOOK_DATA);

實驗8—數(shù)據(jù)庫的查詢和視圖建立查詢數(shù)據(jù)視圖建立查詢以下數(shù)據(jù)的視圖：書號、書名、作者、出版社名稱、首版年月、單價、借閱日期、借書卡號、數(shù)量。要求條件為時間已超過一個月且未歸還的數(shù)據(jù)。CREATEORREPLACEVIEWone_month_not_repayASSELECTa.book_idAS"書號",book_nameAS"書名",a.author_nameAS"作者",b.publisher_nameAS"出版社名稱",book_pubdateAS"首版年月",book_priceAS"單價",c.borrow_dateAS"借閱日期",user_idAS"借書卡號",numAS"數(shù)量"FROM(SELECTa.book_id,author_nameFROMbook_infoa,book_authorb,author_infocWHEREa.book_id=b.book_idANDb.author_id=c.author_id)a,(SELECTa.book_id,publisher_name,book_name,TO_CHAR(a.book_pubdate,'yyyy-mm-dd')ASbook_pubdate,a.book_priceFROMbook_infoa,book_publisherb,publisher_infocWHEREa.book_id=b.book_idANDb.publisher_id=c.publisher_id)b,(SELECTa.book_id,TO_CHAR(borrow_date,'yyyy-mm-dd')ASborrow_date,user_idFROMbook_infoa,user_bookbWHEREa.book_id=b.book_id)c,(SELECTbook_id,COUNT(*)ASnumFROMuser_bookWHERESYSDATE-TO_DATE(return_date,'yyyymmdd')>=30GROUPBYbook_id)d實現(xiàn)對各基表及多表的數(shù)據(jù)查詢基于多表的數(shù)據(jù)查詢，例如：查詢圖書編號為100016的出版社名稱和借閱次數(shù)。SELECTa.book_name,a.publisher_name,b.borrow_numberFROM(SELECTa.book_idASa_book_id,a.book_name,publisher_nameFROMbook_infoa,book_publisherb,publisher_infocWHEREa.book_id=b.book_idANDb.publisher_id=c.publisher_id)a,(SELECTa.book_idASb_book_id,COUNT(*)ASborrow_numberFROMbook_infoa,user_bookbWHEREa.book_id=b.book_idGROUPBYa.book_id)bWHEREa.a_book_id=b.b_book_idANDa.a_book_id='100016';查詢結(jié)果如下圖：

實驗9—PL/SQL編程統(tǒng)計本月圖書借出總數(shù)量、總金額代碼如下：DECLAREv_book_numberNUMBER(10);v_book_sum_priceNUMBER(10);BEGINSELECTCOUNT(b.book_id),SUM(book_price)INTOv_book_number,v_book_sum_priceFROMbook_infoa,user_bookbWHEREa.book_id=b.book_idANDborrow_dateBETWEENTO_DATE('20071201','yyyymmdd')ANDTO_DATE('20071231','yyyymmdd');DBMS_OUTPUT.put_line('本月圖書借出總數(shù)量：'||v_book_number);DBMS_OUTPUT.put_line('本月圖書借出總金額:'||v_book_sum_price);IFv_book_number>100THENDBMS_OUTPUT.put_line('本月圖書借出數(shù)量超過100本!');ENDIF;END;運行結(jié)果如下：使用循環(huán)結(jié)構(gòu)求和使用循環(huán)結(jié)構(gòu)求（1+2）*（2+3）*（3+4）*……*（9+10）的和，代碼如下：DECLAREiNUMBER(2);tNUMBER(20);RESULTNUMBER(20);BEGINt:=1;FORiIN1..9LOOPRESULT:=i+(i+1);RESULT:=RESULT*t;t:=RESULT;ENDLOOP;DBMS_OUTPUT.put_line('計算結(jié)果為:'||RESULT);END;運行結(jié)果如下：使用CASE語句查圖書的所屬種類代碼如下：DECLAREv_book_nameVARCHAR2(30);v_class_idNUMBER;BEGINSELECTbook_class_idINTOv_class_idFROMbook_infoa,book_classbWHEREa.book_id=b.book_idANDbook_name='&v_book_name';CASEv_class_idWHEN10THENDBMS_OUTPUT.put_line('該圖書屬于計算機類');WHEN20THENDBMS_OUTPUT.put_line('該圖書屬于管理類');WHEN30THENDBMS_OUTPUT.put_line('該圖書屬于工具書類');ELSEDBMS_OUTPUT.put_line('該圖書屬于其他類');ENDCASE;END;運行結(jié)果下圖：使用游標(biāo)統(tǒng)計當(dāng)前未歸還圖書的總金額代碼如下：DECLAREsum_priceNUMBER(10);CURSORc_book_infoISSELECTa.book_id,book_priceFROMbook_infoa,user_bookbWHEREa.book_id=b.book_idANDreturn_date>SYSDATE;v_book_idNUMBER(10);v_book_priceNUMBER(10);BEGINsum_price:=0;OPENc_book_info;LOOPFETCHc_book_infoINTOv_book_id,v_book_price;sum_price:=sum_price+v_book_price;EXITWHENc_book_info%NOTFOUND;ENDLOOP;CLOSEc_book_info;DBMS_OUTPUT.put_line('未歸還圖書的總金額為:'||sum_price);END;運行結(jié)果如下：

實驗10—存儲過程與觸發(fā)器存儲過程使用存儲過程統(tǒng)計從每個出版社購入書的總數(shù)量，并將數(shù)據(jù)更新到出版社信息表的‘購書數(shù)量’域。注意:該存儲過程執(zhí)行時,對應(yīng)的一個觸發(fā)器tr_update_publisher_info必須DISABLE才能正確執(zhí)行，因為該存儲過程的作用與觸發(fā)器tr_update_publisher_info的作用相同，代碼如下。CREATEORREPLACEPROCEDUREpubliser_sum_book_numberISiNUMBER(10);CURSORc_publisherISSELECTc.publisher_id,SUM(book_quantity)FROMbook_infoa,book_publisherb,publisher_infocWHEREa.book_id=b.book_idANDb.publisher_id=c.publisher_idGROUPBYc.publisher_id;v_publisher_idpublisher_info.publisher_id%TYPE;v_publisher_book_numberpublisher_info.publisher_book_number%TYPE;BEGINi:=0;OPENc_publisher;LOOPFETCHc_publisherINTOv_publisher_id,v_publisher_book_number;UPDATEpublisher_infoSETpublisher_book_number=v_publisher_book_numberWHEREpublisher_id=v_publisher_id;EXITWHENc_publisher%NOTFOUND;COMMIT;i:=i+1;ENDLOOP;DBMS_OUTPUT.put_line('共更新'||i||'記錄');CLOSEc_publisher;END;驗證成功修改出版社信息表的‘購書數(shù)量’域，可以先增加一條圖書信息insertintobook_infovalues(100031,'精通JAVAWEB',to_date('20071101','yyyymmdd'),59.2,23,'解讀JavaWeb的各類主流應(yīng)用，深入闡釋JSP2.0在JavaEE中的高級應(yīng)用',0);因該書對應(yīng)的出版社已經(jīng)存在，只需要添加關(guān)系表insertintobook_publishervalues(100031,2041);commit;然后執(zhí)行存儲過程,并查看見結(jié)果，最后的結(jié)果顯示更新的記錄數(shù)。execpubliser_sum_book_number;觸發(fā)器BOOK_INFO刪除操作審計BOOK_INFO表很重要，不允許對該表進(jìn)行刪除操作，建立觸發(fā)器對刪除操作進(jìn)行審計。createorreplacetriggertr_audit_delete_book_infobeforedeleteonbook_infobegincasewhendeletingthenraise_application_error(-20001,'不能刪除圖書信息');endcase;end;驗證對表BOOK_INFO的刪除操作的審計，如下圖：自動更新出版社購書數(shù)量觸發(fā)器使用觸發(fā)器實現(xiàn)當(dāng)圖書信息數(shù)據(jù)增加時，即BOOK_INFO表的數(shù)據(jù)有變動時,自動增加出版社信息表中每個出版社的‘購書數(shù)量’。CREATEORREPLACETRIGGERtr_update_book_infoAFTERINSERTORUPDATEONBOOK_INFODECLARECURSORc_publisherISSELECTc.publisher_id,SUM(book_quantity)aspublisher_book_numberFROMbook_infoa,book_publisherb,publisher_infocWHEREa.book_id=b.book_idANDb.publisher_id=c.publisher_idGROUPBYc.publisher_id;v_publisher_idnumber(10);v_publisher_book_numbernumber(10);BEGINOPENc_publisher;LOOPFETCHc_publisherINTOv_publisher_id,v_publisher_book_number;UPDATEpublisher_infoSETpublisher_book_number=v_publisher_book_numberWHEREpublisher_id=v_publisher_id;EXITWHENc_publisher%NOTFOUND;ENDLOOP;CLOSEc_publisher;END;驗證觸發(fā)器的作用：查詢要修改的圖書及其對應(yīng)的出版社。修改BOOK_INFO表的圖書的數(shù)量。查詢PUBLISHER_INFO表中的對應(yīng)的出版社的圖書數(shù)量是否隨之改變。如下圖：

實驗11—函數(shù)與包函數(shù)使用函數(shù)實現(xiàn)從圖書借閱信息表中統(tǒng)計各年月的借出圖書數(shù)與歸還圖書數(shù)，要求輸入?yún)?shù)年月后，返回該年月的借出圖書數(shù)NUM1與歸還圖書數(shù)入庫金額NUM2。并實現(xiàn)調(diào)用：查2004年8月份的借出圖書數(shù)與歸還圖書數(shù)。借出圖書數(shù)CREATEORREPLACEfunctionBOOK.get_book_borrow_number(query_dateinnumber)returnnumberisnum1number(10);beginselectcount(book_id)intonum1fromuser_bookwhereborrow_date=to_date(query_date,'yyyymm');returnnum1;exceptionwhenNO_DATA_FOUNDTHENraise_application_error(-200000,'本月無借出圖書。');end;在命令行中執(zhí)行函數(shù)SQL>varnum1numberSQL>exec:num1:=get_book_borrow_number(200712)SQL>printnum1運行結(jié)果如下圖：歸還圖書數(shù)CREATEORREPLACEfunctionBOOK.get_book_return_number(query_dateinnumber)returnnumberisnum2number(10);beginselectcount(book_id)intonum2fromuser_bookwherereturn_date=to_date(query_date,'yyyymm');re