數(shù)據(jù)庫課程設(shè)計(jì)之圖書管理系統(tǒng)-在線瀏覽
2025-03-05 16:09本頁面
【正文】 turn_insert 39。,39。,1GO圖 10還書實(shí)例 創(chuàng)建實(shí)現(xiàn)借書的存儲(chǔ)過程/**************************創(chuàng)建借書存儲(chǔ)*****************/USE bookmgGOCREATE PROCEDURE borrow_insertelem1 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。UPDATE 借書SET outputdate=GETDATE()WHERE readerid=elem1 AND bookid=elem2。/*聲明一個(gè)變量bookname從圖書表里讀取圖書名到借書表里*/DECLARE bookname varchar(40)SET bookname=(SELECT bookname FROM 圖書WHERE bookid=elem2)UPDATE 借書SET bookname=booknameWHERE readerid=elem1 AND bookid=elem2。UPDATE 圖書SET haveborrow=haveborrow+elem3WHERE bookid=elem2。ENDELSEBEGIN SELECT 39。ENDEND/*借書實(shí)例*/USE bookmgGO EXEC borrow_insert 39。,39。,2GO圖11借書實(shí)例 建立是否超期表值函數(shù)USE bookmgGOCREATE FUNCTION judgedate(elem1 as varchar(9))RETURNS overdate TABLE(overdays int)ASBEGININSERT overdateSELECT day(GETDATE()ISNULL(shoulddate,0))FROM 借書WHERE readerid=elem1RETURNEND /*****************圖書分類查詢和分類統(tǒng)計(jì)**************/USE bookmgGOCREATE PROCEDURE type_selectbooktype varchar(20)ASBEGINSELECT booktypeFROM 圖書WHERE booktype=booktype。類型39。本39。漫畫39。DECLARE booknum intSET booknum=(select sum(noborrow+haveborrow)FROM 圖書WHERE bookname=bookname)SELECT bookname AS 39。 ,booknum AS 39。 END/****圖書書名查詢實(shí)例*********/USE bookmgGO EXEC bookname_select 39。 GO圖13圖書書名查詢實(shí)例/**********************按作者查詢和統(tǒng)計(jì)**************************/USE bookmgGOCREATE PROCEDURE bookauthor_selectbookauthor varchar(20)ASBEGINSELECT *FROM 圖書WHERE bookauthor=bookauthor。作者39。本39。夏達(dá)39。通過一個(gè)月的努力，我自主完成了課題要求的所有功能。很多知識(shí)，老師并沒有講過，甚至使用的教材里也沒有提到。通過，本次課程設(shè)計(jì)我收獲很大，認(rèn)識(shí)到當(dāng)遇到困難的時(shí)候要多和人溝通，并且要冷靜得去思考去分析問題出在哪了，怎樣才能解決。參考文獻(xiàn)[1] 數(shù)據(jù)庫原理和技術(shù)（SQL Server 2005）申事凱 戴祖誠(chéng) 余玉梅 主編[2] 王珊，薩師煊 數(shù)據(jù)庫系統(tǒng)概論[第四版] 高等教育出版社附錄：（附上關(guān)鍵的源程序，510頁）/************創(chuàng)建各個(gè)表*************************/CREATE TABLE 讀者(readerid varchar(9) NOT NULL PRIMARY KEY,readername varchar(20) NOT NULL,readersex varchar(10) NOT NULL,booknum SMALLINT CHECK(borrownum=0 AND borrownum=5))CREATE TABLE 圖書(bookid varchar(9) NOT NULL PRIMARY KEY,bookname varchar(40)NOT NULL,booktype varchar(20)NOT NULL,bookauther varchar(20) NOT NULL,booktrslator varchar(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
環(huán)評(píng)公示相關(guān)推薦
鄂ICP備17016276號(hào)-1