數(shù)據(jù)庫課程設(shè)計之圖書管理系統(tǒng)-文庫吧
2025-01-01 16:09 本頁面
【正文】 r(20) NULL,bookpress varchar(20)NOT NULL,bookcontent varchar(50)NULL,bookprice varchar(10)NOT NULL,noborrow SMALLINT CHECK(noborrow=0),haveborrow SMALLINT CHECK(haveborrow=0))CREATE TABLE 還書(readerid varchar(9) NOT NULL,bookid varchar(9) NOT NULL,booknum SMALLINT CHECK(booknum=0 AND booknum=5),inputdate datetime ,FOREIGN KEY (readerid) REFERENCES 讀者(readerid),FOREIGN KEY (bookid) REFERENCES 圖書(bookid))CREATE TABLE 借書(readerid varchar(9) NOT NULL,bookid varchar(9) NOT NULL,bookname varchar(40) NOT NULL,booknum SMALLINT CHECK(booknum=0 AND booknum=5),outputdate datetime NULL,shoulddate datetime NULL,FOREIGN KEY(readerid) REFERENCES 讀者(readerid),FOREIGN KEY(bookid) REFERENCES 圖書(bookid))第四章 功能模塊的創(chuàng)建 創(chuàng)建向讀者表插入數(shù)據(jù)的存儲過程/*******創(chuàng)建存儲過程向讀者表中插入數(shù)據(jù)************/USE bookmgGO CREATE PROCEDURE reader_insert@elem1 varchar(9),@elem2 varchar(20),@elem3 varchar(10),@elem4 smallintASBEGIN insert into 讀者(readerid,readername,readersex,borrownum)values (@elem1,@elem2,@elem3,@elem4)END/********讀者表中插入實例**********/USE bookmgGOEXEC reader_insert 39。11100713139。,39。吳書敏39。,39。男39。,39。039。圖9讀者表中插入實例 創(chuàng)建向圖書表中插入數(shù)據(jù)的存儲過程/***************通過存儲過程向圖書表中插入數(shù)據(jù)******************/USE bookmgGO CREATE PROCEDURE book_insert@elem1 varchar(9),@elem2 varchar(40),@elem3 varchar(20),@elem4 varchar(20),@elem5 varchar(20),@elem6 varchar(20),@elem7 varchar(50),@elem8 varchar(10),@elem9 smallint,@elem10 smallintASBEGIN insert into 圖書(bookid,bookname,booktype,bookauther,booktrslator,bookpress,bookcontent,bookprice,noborrow,haveborrow)values (@elem1,@elem2,@elem3,@elem4,@elem5,@elem6,@elem7,@elem8,@elem9,@elem10)END 創(chuàng)建實現(xiàn)還書的存儲過程/********************************創(chuàng)建還書存儲*******************************/USE bookmgGOCREATE PROCEDURE return_insert@elem1 varchar(9),@elem2 varchar(9),@elem3 smallintASINSERT INTO 還書(readerid,bookid,booknum)values (@elem1,@elem2,@elem3)BEGINUPDATE 還書SET inputdate=GETDATE()WHERE readerid=@elem1。UPDATE 圖書SET noborrow=noborrow+@elem3WHERE bookid=@elem2。UPDATE 圖書SET haveborrow=haveborrow@elem3WHERE bookid=@elem2。UPDATE 讀者SET borrownum=borrownum@elem3SELECT 39。還書成功39。ENDGO/***********還書實例*************/USE bookmgGO EXEC return_insert 39。11100713139。,39。54054190339。,1GO圖 10還書實例 創(chuàng)建實現(xiàn)借書的存儲過程/**************************創(chuàng)建借書存儲*****************/USE bookmgGOCREATE PROCEDURE borrow_insert@elem1 varchar(9),@elem2 varchar(9),@elem3 smallintASinsert into 借書(readerid,bookid,booknum)values(@elem1,@elem2,@elem3)BEGIN/*這里用了判定是否超期函數(shù)judgedate，將返回值賦予判斷變量judgement*/DECLARE @judgement intSET @judgement=(SELECT max(overdays)FROM judgedate(@elem1)) /*判定該讀者是否有圖書超期*/IF (@judgement=0)BEGINSELECT 39。借書成功39。UPDATE 借書SET outputdate=GETDATE()WHERE readerid=@elem1 AND bookid=@elem2。UPDATE 借書
環(huán)評公示相關(guān)推薦
鄂ICP備17016276號-1