數(shù)據(jù)庫(kù)課程設(shè)計(jì)之圖書管理系統(tǒng)-資料下載頁(yè)
2025-01-16 16:09本頁(yè)面
【正文】 row=haveborrow@elem3WHERE bookid=@elem2。UPDATE 讀者SET borrownum=borrownum@elem3SELECT 39。還書成功39。ENDGO/***********還書實(shí)例*************/USE bookmgGO EXEC return_insert 39。11100713139。,39。54054190339。,1GO/**************************創(chuàng)建借書存儲(chǔ)*****************/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 借書SET shoulddate=DATEADD(day,30,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 noborrow=noborrow@elem3WHERE bookid=@elem2。UPDATE 圖書SET haveborrow=haveborrow+@elem3WHERE bookid=@elem2。UPDATE 讀者SET borrownum=borrownum+@elem3WHERE readerid=@elem1。ENDELSEBEGIN SELECT 39。借書失敗，有超期未還圖書39。ENDEND/************判斷是否超期表值函數(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/*借書實(shí)例*/USE bookmgGO EXEC borrow_insert 39。11100713139。,39。54054190339。,2GO/*****************圖書分類查詢和分類統(tǒng)計(jì)**************/USE bookmgGOCREATE PROCEDURE type_select@booktype varchar(20)ASBEGINSELECT booktypeFROM 圖書WHERE booktype=@booktype。DECLARE @booknum intSET @booknum=(select sum(noborrow+haveborrow)FROM 圖書WHERE booktype=@booktype)SELECT @booktype AS 39。類型39。 ,@booknum AS 39。本39。 END/****圖書分類查詢實(shí)例*********/USE bookmgGO EXEC type_select 39。漫畫39。 GO/*******************圖書按書名查詢和統(tǒng)計(jì)******************/USE bookmgGOCREATE PROCEDURE bookname_select@bookname varchar(40)ASBEGINSELECT *FROM 圖書WHERE bookname=@bookname。DECLARE @booknum intSET @booknum=(select sum(noborrow+haveborrow)FROM 圖書WHERE bookname=@bookname)SELECT @bookname AS 39。書名39。 ,@booknum AS 39。本39。 END/****圖書書名查詢實(shí)例*********/USE bookmgGO EXEC bookname_select 39。子不語(yǔ)壹39。 GO/**********************按作者查詢和統(tǒng)計(jì)**************************/USE bookmgGOCREATE PROCEDURE bookauthor_select@bookauthor varchar(20)ASBEGINSELECT *FROM 圖書WHERE bookauthor=@bookauthor。DECLARE @booknum intSET @booknum=(select sum(noborrow+haveborrow)FROM 圖書WHERE bookauthor=@bookauthor)SELECT @bookauthor AS 39。作者39。 ,@booknum AS 39。本39。 END/***圖書作者查詢實(shí)例******/USE bookmgGO EXEC bookauthor_select 39。夏達(dá)39。GO2
環(huán)評(píng)公示相關(guān)推薦
鄂ICP備17016276號(hào)-1