數(shù)據(jù)庫原理課程設(shè)計實驗【 創(chuàng)建存儲過程與觸發(fā)器】

1、管工學(xué)院計算機系數(shù)據(jù)庫原理課程設(shè)計實驗報告 存儲過程與觸發(fā)器實驗日期和時間： 2016年5月13日、星期五第節(jié)實驗室：DJ2-信息管理實驗室班級：學(xué)號：姓名：實驗環(huán)境：1. 硬件：筆記本電腦2. 軟件：SQL Server 2012實驗原理：存儲過程概念：存儲過程是事先編好的，存儲在數(shù)據(jù)庫中的一組被編譯了的T-SQL命令集合，這些命令用來完成對數(shù)據(jù)庫的指定操作。存儲過程可以接受用戶的輸入?yún)?shù)、向客戶端返回表格或標量結(jié)果和消息、調(diào)用數(shù)據(jù)定義語言（DDL）和數(shù)據(jù)操作語言（DML）語句，然后返回輸入?yún)?shù)。觸發(fā)器概念：觸發(fā)器（trigger）是SQL server 提供給程序員和數(shù)據(jù)分析員來保證數(shù)據(jù)完

2、整性的一種方法，它是與表事件相關(guān)的特殊的存儲過程，它的執(zhí)行不是由程序調(diào)用，也不是手工啟動，而是由事件來觸發(fā)，比如當(dāng)對一個表進行操作（ insert，delete， update）時就會激活它執(zhí)行。觸發(fā)器經(jīng)常用于加強數(shù)據(jù)的完整性約束和業(yè)務(wù)規(guī)則等。實驗任務(wù)：此作業(yè)成績得分根據(jù)你完成的任務(wù)的難度和數(shù)量評分，完成后在實驗室給老師演示驗收，課后提交電子版報告。如額外完成自擬題目應(yīng)當(dāng)事先將所擬題目提交給老師或在報告中明確標注題意。假定有學(xué)校的圖書館管理信息系統(tǒng)，可以用于日常管理書庫和同學(xué)們的借還書工作。以下列出參考的庫表情況：根據(jù)管理的業(yè)務(wù)需求來分析，該管理信息系統(tǒng)的數(shù)據(jù)庫應(yīng)至少包括如下數(shù)據(jù)表：（打號的是

3、必須有的表）1. 圖書現(xiàn)有庫存表。作用：記錄圖書的現(xiàn)有庫存情況。至少包括：書號、書名、作者、簡介、類別、價格、出版社、出版日期、現(xiàn)有庫存數(shù)量、最小庫存量、庫存總量、庫存位置等。2. 讀者信息表。作用：記錄讀者信息。至少包括：讀者編號、證件類型、證件號碼、姓名、性別、職業(yè)（可填寫教師、學(xué)生、教工、其它）、所屬單位、地址、聯(lián)系電話等。3. 借書記錄表。作用：記錄借書情況,以及是否歸還。至少包括：借閱ID（主鍵，可設(shè)置為自動編號）、書號、讀者編號、借閱數(shù)量、借閱日期、是否歸還、管理員編號等。4. 還書記錄表。作用：記錄還書情況。至少包括：還書ID（主鍵，可設(shè)置為自動編號）、書號、讀者編號、歸還數(shù)量、

4、歸還日期、是否超期（超過假設(shè)45天為超期）、超期天數(shù)、管理員編號等。（附：為簡化操作，續(xù)借可視為歸還后再借）。5. 管理員信息表。作用：記錄負責(zé)管理書庫和借書還書工作的管理員信息。至少包括：管理員編號、職工編號（在職工檔案表中的職工編號）、用戶名、密碼、管理員級別等。6. 職工檔案表表。作用：記錄職工檔案。至少包括: 職工編號、姓名、性別、單位、職稱、職務(wù)、出生日期、學(xué)歷、其它字段自擬。7. 入庫單表。作用：記錄圖書館購買圖書補充書庫圖書時的入庫書單。包括字段：入庫單ID、入庫日期、入庫書號、書名、入庫數(shù)量其它請根據(jù)圖書現(xiàn)有庫存表自擬經(jīng)手人編號等。實驗步驟1、設(shè)計并創(chuàng)建數(shù)據(jù)庫。) 設(shè)計數(shù)據(jù)庫，

5、繪制ERD設(shè)計圖。) 根據(jù)ERD創(chuàng)建數(shù)據(jù)庫中的所有表，并根據(jù)業(yè)務(wù)需求正確設(shè)置主鍵、外鍵、約束條件、默認值等。) 創(chuàng)建關(guān)系圖，建立表之間的聯(lián)系以保證參照完整性。注意，建表時，表中的外鍵的數(shù)據(jù)類型應(yīng)當(dāng)與其所參照的主表中的主鍵數(shù)據(jù)類型一致。（至少創(chuàng)建題目所需要的表）) 基本數(shù)據(jù)錄入。可以直接錄入，也可將其它格式的表中的數(shù)據(jù)導(dǎo)入，或查詢其它表中可利用的數(shù)據(jù)并插入到現(xiàn)有的表中。ERD設(shè)計圖原代碼：創(chuàng)建數(shù)據(jù)庫、所有表，并根據(jù)業(yè)務(wù)需求正確設(shè)置主鍵、外鍵、約束條件、默認值create database 圖書管理系統(tǒng);use 圖書管理系統(tǒng);create table 圖書現(xiàn)有庫存表(書號 char(10) pri

6、mary key,書名 char(10) not null,作者 char(10),簡介 varchar,類別 char(10),價格 money not null,出版社 char,出版日期 datetime,現(xiàn)有庫存數(shù) int,最小庫存量 int,庫存總量 int,庫存位置 varchar default 3F45);create table 讀者信息表(讀者編號 nchar(10) primary key,證件類型 nchar(4),證件號碼 nchar(13),姓名 char(10),性別 char(2) default 男,check(性別 in (男,女),職業(yè) char(4),所

7、屬單位 char(8),地址 char(10),聯(lián)系電話 char(11)create table 借書記錄表(借閱ID int identity(1,1) primary key,讀者編號 char(5),借閱數(shù)量 int,借閱日期 datetime default getdate(),是否歸還 char(2) default 否,書號 char(10), 管理員編號 char(10),foreign key(書號)references 圖書現(xiàn)有庫存表(書號), foreign key(管理員編號)references 管理員信息表(管理員編號),);create table 還書記錄表(還

8、書ID int identity(1,1) primary key,書號 char(10), foreign key(書號)references 圖書現(xiàn)有庫存表(書號),歸還數(shù)量 int ,歸還日期 datetime default getdate(),是否超期 char(2),超期天數(shù) char(5), 管理員編號 char(10), foreign key(管理員編號)references 管理員信息表(管理員編號),);create table 管理員信息表( 管理員編號 char(10) primary key, 職工編號 char(10), foreign key(職工編號)refe

9、rences 職工檔案表(職工編號),用戶名 char(10) not null,密碼 char(6) not null,管理員級別 char(4);create table 職工檔案表(職工編號 char(10) primary key,姓名 char(10),性別 char(2)default 男,check(性別=男or 性別=女),單位 char(10),職稱 char(4),職務(wù) char(10),出生日期 datetime,學(xué)歷 char(10);create table 入庫單表(入庫單ID char(10),入庫日期 datetime,入庫書號 char(10),書名 char

10、(10),入庫數(shù)量 int); 關(guān)系圖和數(shù)據(jù)錄入情況、其它測試方案及數(shù)據(jù)：實驗步驟2、創(chuàng)建存儲過程。（任選一題） 創(chuàng)建可以按“書號”（參數(shù)）進行圖書庫存信息查詢的存儲過程。 創(chuàng)建可以按“類別”（參數(shù)）進行某類圖書庫存信息查詢的存儲過程。 創(chuàng)建可以按“讀者編號”進行讀者信息查詢的存儲過程。 自擬題先在下面第一欄填寫自己選擇的題目和欲實現(xiàn)的功能，再在其余欄目中分別填寫自己的代碼以及執(zhí)行情況、測試方案和數(shù)據(jù)、測試結(jié)果等等。如果選做多個或全做或額外完成自擬題，請自己依照格式添加欄目，自擬題請寫清題意。我的存儲過程選題：（描述題目和欲實現(xiàn)的功能）1.創(chuàng)建可以按“書號”（參數(shù)）進行圖書庫存信息查詢的存儲過

11、程。能夠使得用戶輸入書號，就可以查詢這本書的信息原代碼：創(chuàng)建存儲過程按照書號進行查詢（ashcx），參數(shù)為shuhao 定義為char（20）create proc ashcx shuhao char(20)asbeginselect * from 圖書現(xiàn)有庫存表 where 書號=shuhaoend調(diào)用存儲過程，查詢書號為30001圖書的信息exec ashcx shuhao=30001測試方案及數(shù)據(jù)：創(chuàng)建存儲過程，并查詢書號為30001的圖書信息exec ashcx shuhao=30001測試結(jié)果：(文字說明、原代碼、結(jié)果貼圖)查詢書號為3001書的信息實驗步驟3、創(chuàng)建觸發(fā)器。（任選一題

12、） 創(chuàng)建“借書記錄”表的插入觸發(fā)器，每插入一條借閱記錄就自動根據(jù)借閱數(shù)量減少該圖書的“現(xiàn)有庫存數(shù)量”（圖書現(xiàn)有庫存表），圖書數(shù)量不足時可以報警，圖書數(shù)量為零時拒絕插入借閱記錄。 創(chuàng)建“還書記錄”表的插入觸發(fā)器，每當(dāng)有讀者歸還圖書時，插入一條還書記錄，同時計算是否超期并在“是否超期”和“超期天數(shù)”字段填寫結(jié)果。并根據(jù)還書時提供的讀者編號和書號，將“借書記錄”表中的對應(yīng)借閱記錄的 “是否歸還”中原來的“否”置為“是”（注意考慮特殊情況，比如一本書由同一個讀者反復(fù)多次借閱或一次借閱多本的）；根據(jù)“歸還數(shù)量”增加該圖書的“現(xiàn)有庫存數(shù)量”（圖書現(xiàn)有庫存表）。 創(chuàng)建“入庫單”表的插入觸發(fā)器。在該表中插入圖

13、書入庫記錄時，在填寫“圖書編號”和“入庫數(shù)量”時，通過觸發(fā)器的作用，能判斷在“ 圖書現(xiàn)有庫存表”是否存在該圖書的庫存記錄，如果有，則自動更新該圖書的現(xiàn)有庫存數(shù)量，如果現(xiàn)有庫存表中不存在該圖書的庫存記錄（有可能是原來沒有的新書），則在“圖書現(xiàn)有庫存表”中自動插入該圖書的庫存記錄。對于書庫中已經(jīng)存在的圖書（此次只是補充圖書數(shù)量）能通過觸發(fā)器的作用自動填寫入庫單該記錄中其它的未填的該書的對應(yīng)信息（提示，根據(jù)書號在圖書現(xiàn)有庫存表查詢）。 自擬題目。先在下面第一欄填寫自己選擇的題目和欲實現(xiàn)的功能，再在其余欄目中分別填寫自己的代碼以及執(zhí)行情況、測試方案和數(shù)據(jù)、測試結(jié)果等等。如果選做多個或全做或額外完成自擬

14、題，請自己依照格式添加欄目，自擬題請寫清題意。我的觸發(fā)器選題：（描述題目和欲實現(xiàn)的功能） 創(chuàng)建“借書記錄”表的插入觸發(fā)器，每插入一條借閱記錄就自動根據(jù)借閱數(shù)量減少該圖書的“現(xiàn)有庫存數(shù)量”（圖書現(xiàn)有庫存表），圖書數(shù)量不足時可以報警，圖書數(shù)量為零時拒絕插入借閱記錄。原代碼：/*創(chuàng)建“借書記錄”表的插入觸發(fā)器，每插入一條借閱記錄就自動根據(jù)借閱數(shù)量減少該圖書的“現(xiàn)有庫存數(shù)量”（圖書現(xiàn)有庫存表），圖書數(shù)量不足時可以報警，圖書數(shù)量為零時拒絕插入借閱記錄。*/-use 圖書管理系統(tǒng)alter trigger crcfqon 借書記錄表for insertas begin-聲明變量declare zdbh i

15、nt, sh char(10),dzbh char(5),jysl int,glybh char(10)declare xykcs int-查詢庫存數(shù)量select zdbh=inserted.借閱ID,sh=inserted.書號,dzbh=inserted.讀者編號,jysl=inserted.借閱數(shù)量,xykcs=圖書現(xiàn)有庫存表.現(xiàn)有庫存數(shù)from 圖書現(xiàn)有庫存表 join inserted on inserted.書號 =圖書現(xiàn)有庫存表.書號if(jysl=xykcs)begin-根據(jù)插入的銷售記錄的數(shù)量更新庫存量update 圖書現(xiàn)有庫存表 set 現(xiàn)有庫存數(shù)=現(xiàn)有庫存數(shù)-jysl

16、where sh=書號endelsebeginprint庫存量不足！rollback transactionendend 測試方案及數(shù)據(jù)：在借書記錄表中插入數(shù)據(jù)：借書記錄表(書號,讀者編號, 借閱數(shù)量 )values(30002,0001,1)在借書記錄表中插入數(shù)據(jù)：借書記錄表(書號,讀者編號, 借閱數(shù)量 )values(30002,0001,55)測試結(jié)果：(文字說明、原代碼、結(jié)果貼圖)第一次插入數(shù)據(jù)：借書記錄表(書號,讀者編號, 借閱數(shù)量 )values(30002,0001,1)use 圖書管理系統(tǒng)select * from 借書記錄表goselect * from 圖書現(xiàn)有庫存表 go

17、insert into 借書記錄表(書號,讀者編號, 借閱數(shù)量 )values(30002,0001,55)goselect * from 借書記錄表goselect * from 圖書現(xiàn)有庫存表 go第二次測試插入數(shù)據(jù)：借書記錄表(書號,讀者編號, 借閱數(shù)量 )values(30002,0001,55)輸出結(jié)果 “庫存量不足！”use 圖書管理系統(tǒng)select * from 借書記錄表goselect * from 圖書現(xiàn)有庫存表 goinsert into 借書記錄表(書號,讀者編號, 借閱數(shù)量 )values(30002,0001,55)goselect * from 借書記錄表gose

18、lect * from 圖書現(xiàn)有庫存表 go本實驗總結(jié)：1. 創(chuàng)建、修改、刪除、調(diào)用存儲過程的語法。創(chuàng)建存儲過程語法：CREATE PROC EDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n 修改存儲過程語法：ALTER PROC EDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n 刪除存儲過程語法：DROPPROCEDURE存儲過程名稱調(diào)用存儲過程的語法：EXECUTEProced

19、ure_Name22. 舉例說明存儲過程中參數(shù)的應(yīng)用方法。創(chuàng)建存儲過程時設(shè)置一個參數(shù)，調(diào)用時必須也要有參數(shù)create proc ashcx shuhao char(20)asbeginselect * from 圖書現(xiàn)有庫存表 where 書號=shuhaoend調(diào)用存儲過程exec ashcx shuhao=300013. 簡述觸發(fā)器的種類。For觸發(fā)器和After觸發(fā)器：要求只有執(zhí)行某一操作（INSERT UPDATE DELETE） 之后，觸發(fā)器才被觸發(fā)，且只能在表上定義。可以為針對表的同一操作定義多個觸發(fā)器；Instead of 觸發(fā)器：觸發(fā)器表示并不執(zhí)行其所定義的操作（INSERT

20、、 UPDATE、 DELETE），而僅是執(zhí)行觸發(fā)器本身。既可在表上定義INSTEAD OF 觸發(fā)器，也可以在視圖上定義INSTEAD OF 觸發(fā)器，但對同一操作只能定義一個INSTEAD OF 觸發(fā)器。4. 什么是幻表？舉例說明其用途?；帽砭褪窃趧?chuàng)建觸發(fā)器時自己生成臨時表，分別是deleted 和inserted，Inserted表就是放新的記錄，Delete表就是放舊的記錄，當(dāng)你插入時，要插入的記錄是新的，所以可以在Insert表中找到。當(dāng)你刪除時，要刪除的記錄是舊的，所以可以在Deleted表中找到。例如借書記錄表中的借閱觸發(fā)器，插入書號，讀者編號，借閱數(shù)量時，插入新的內(nèi)容就在inserted表中5. 創(chuàng)建、修改、刪除觸發(fā)器的語法。CreateTRIGGER trigger_nameON table | view WITH ENCRYPTION FOR |AFTER | INSTEAD OF Insert , Update WITH APPEND NOT FOR REPLICATION AS IF Update ( column ) AND | or Update ( column ) .n | I