數(shù)據(jù)庫(kù)第六次作業(yè)

實(shí)驗(yàn)十一存儲(chǔ)過(guò)程實(shí)驗(yàn)十二觸發(fā)器一：實(shí)驗(yàn)?zāi)康暮鸵螅赫莆沾鎯?chǔ)過(guò)程的創(chuàng)立及調(diào)用。理解觸發(fā)器的工作原理，掌握如何使用inserted表和deleted表及如何創(chuàng)立：INSERT、UPDATE、DELETE觸發(fā)器。二：實(shí)驗(yàn)類(lèi)型：設(shè)計(jì)驗(yàn)證三：實(shí)驗(yàn)內(nèi)容：一：有學(xué)員成績(jī)數(shù)據(jù)庫(kù)stu，其中學(xué)員信息表stuinfo和學(xué)生成績(jī)表stumarks如下CREATEDATABASESTUINFOUSESTUCREATETABLESTUINFO(STUNAMEVARCHAR(50),STUNOVARCHAR(50)PRIMARYKEY,STUSEXVARCHAR(50),STUAGEVARCHAR(50),STUADDRESSVARCHAR(50),)CREATETABLESTUMARKS(EXAMNOVARCHAR(10)PRIMARYKEY,STUNOVARCHAR(20),WRITTENEXAMINT,LABEXAMINT,FOREIGNKEY(STUNO)REFERENCESSTUINFO(STUNO),)輸入數(shù)據(jù)USESTUINSERTINTOSTUINFOVALUES('張秋麗','s25301','男','18','北京')INSERTINTOSTUINFOVALUES('李文才','s25302','男','28','地址不詳')INSERTINTOSTUINFOVALUES('李斯文','s25303','女','22','河南')INSERTINTOSTUINFOVALUES('歐陽(yáng)俊雄','s25304','女','34','新疆')INSERTINTOSTUINFOVALUES('梅超風(fēng)','s25305','女','23','地址不詳')USESTUINSERTINTOSTUMARKSVALUES('s271811','s25303','93','59')INSERTINTOSTUMARKSVALUES('s271813','s25302','63','91')INSERTINTOSTUMARKSVALUES('s271816','s25301','90','83')INSERTINTOSTUMARKSVALUES('s271818','s25305','63','53')1.請(qǐng)創(chuàng)立存儲(chǔ)過(guò)程，查看本次考試平均分以及未通過(guò)考試的學(xué)員名單CREATEPROCEDURESTUAS DECLARE@AINT,@BINT,@CVARCHAR(20),@DVARCHAR(20),@EVARCHAR(20),@FINT,@GINT SELECT@A=AVG(WRITTENEXAM),@B=AVG(LABEXAM)FROMSTUMARKSBEGIN IF@A>70AND@B>70 SET@C=’成績(jī)優(yōu)秀’ ELSE SET@C=’本班成績(jī)較差’ ENDPRINT‘筆試平均分為:’+CONVERT(VARCHAR,@A)PRINT‘機(jī)試平均分為:’+CONVERT(VARCHAR,@B)PRINT’本班考試成績(jī):’+CONVERT(VARCHAR,@C)PRINT’****************************’PRINT’參加考試沒(méi)有通過(guò)的學(xué)生’PRINT’*************************’PRINT’STUNAMESTUNOWRITTENEXAMLABEXAM’PRINT’*************************’DECLARESTUDECURSORFORSELECTSTUNAME,STUMARKS.STUNO,WRITTENEXAM,LABEXAMFROMSTUINFO,STUMARKSWHERESTUINFO.STUNO=STUMARKS.STUNOAND(WRITTENEXAM<60ORLABEXAM<60)OPENSTUDEFETCHNEXTFROMSTUDEINTO@D,@E,@F,@GWHILE@@FETCH_STATUS=0BEGIN PRINT @D+’’+@E+’’+CONVERT(VARCHAR(5),@F)+’’+CONVERT(VARCHAR(5),@G) FETCHNEXTFROMSTUDE INTO@D,@E,@F,@GENDCLOSESTUDEDEALLOCATESTUDEEXECSTU2.修改上題：由于每次考試的難易程度不一樣，每次筆試和機(jī)試的及格線可能隨時(shí)變化〔不再是60分〕，這導(dǎo)致考試的評(píng)判結(jié)果也相應(yīng)變化?！蔡崾荆菏褂脦л斎?yún)?shù)的存儲(chǔ)過(guò)程〕DROPPROCSTUDCREATEPROCEDURESTUD@PINTOUTPUT,@QINTOUTPUTASDECLARE@AINT,@BINT,@CVARCHAR(20),@DVARCHAR(20),@EVARCHAR(20),@FINT,@GINT SELECT@A=AVG(WRITTENEXAM),@B=AVG(LABEXAM)FROMSTUMARKSBEGIN IF@A>@QAND@B>@Q SET@C=’成績(jī)優(yōu)秀’ ELSE SET@C=’本班成績(jī)較差’ ENDPRINT‘筆試平均分為:’+CONVERT(VARCHAR,@A)PRINT‘機(jī)試平均分為:’+CONVERT(VARCHAR,@B)PRINT’本班考試成績(jī):’+CONVERT(VARCHAR,@C)PRINT’****************************’PRINT’參加考試沒(méi)有通過(guò)的學(xué)生’PRINT’*************************’PRINT’STUNAMESTUNOWRITTENEXAMLABEXAM’PRINT’*************************’DECLARESTUDECURSORFORSELECTSTUNAME,STUMARKS.STUNO,WRITTENEXAM,LABEXAMFROMSTUINFO,STUMARKSWHERESTUINFO.STUNO=STUMARKS.STUNO AND(WRITTENEXAM<@PORLABEXAM<@P)OPENSTUDEFETCHNEXTFROMSTUDEINTO@D,@E,@F,@GWHILE@@FETCH_STATUS=0BEGIN PRINT @D+’’+@E+’’+CONVERT(VARCHAR(5),@F)+’’+CONVERT(VARCHAR(5),@G) FETCHNEXTFROMSTUDE INTO@D,@E,@F,@GENDCLOSESTUDEDEALLOCATESTUDEEXECSTU(‘70’,’753、修改上題：返回未通過(guò)考試的學(xué)員人數(shù)?！蔡崾荆菏褂脦л敵鰠?shù)的存儲(chǔ)過(guò)程〕DROPPROCSTUDENTCREATEPROCEDURESTUDENT@NINTOUTPUTASSELECT@N=COUNT(*)FROMSTUMARKSWHEREWRITTENEXAM<60ORLABEXAM<60PRINT(’結(jié)果’)DECLARE@NINT EXECSTUDENT@NOUTPUT PRINT(’沒(méi)通過(guò)的人數(shù)為:’+CONVERT(VARCHAR(10),@N)二：建立銀行業(yè)務(wù)數(shù)據(jù)庫(kù)bankdb，其中，帳戶(hù)信息表(bank)存放帳戶(hù)的信息，交易信息表(transInfo)存放每次的交易信息。CREATETABLEBANK(customernamevarchar(20),

cardIDvarchar(10),

currentMoneyfloat(4))CREATETABLETRANSINFO(transDatedatetime,

cardIDVARCHAR(50),transTypevarchar(20),check

(transType='支取'or

transType='存入'),

transMoneyfloat(4),)usebankdbINSERTINTOBANKVALUES('張三','10010001','1000.0000')INSERTINTOBANKVALUES('李四','100110002','1.0000')USEBANKDBINSERTINTOTRANSINFOVALUES('2005-10-1111:30:46.623','10010001','支取','200.0000')1、當(dāng)向交易信息表(transInfo)中插入一條交易信息時(shí)，自動(dòng)更新對(duì)應(yīng)帳戶(hù)的余額。2、當(dāng)刪除交易信息表時(shí)，要求自動(dòng)備份被刪除的數(shù)據(jù)到表backupTable中。3、跟蹤用戶(hù)的交易，交易金額超過(guò)20000元，那么取消交易，并給出錯(cuò)誤提示。

IFEXISTS(SELECTnameFROMsysobjects

WHEREname='transInfo_in'ANDtype='TR')

DROPTRIGGERtransInfo_in

GO

CREATETRIGGERtransInfo_in

ONtransInfo

FORINSERT

ASDECLARE

@i_transDatedatetime,

@i_cardidint,

@i_transtypevarchar(20),

@i_transmoneyfloat(4)

if

exists

(select

1

from

inserted)

and

not

exists

(select

1

from

deleted)

begin

--查詢(xún)插入信息

SELECT

@i_cardid

=cardID,

@i_transtype=transType,

@i_transmoney=transMoney

FROM

inserted--判斷是否有插入信息

IF(isnull(@i_cardid,0)<>0)

BEGIN

if(@i_transmoney>20000)

--判斷大于20000給出錯(cuò)誤提示，并回滾

begin

RAISERROR('交易金額大于20000元！',16,1)

ROLLBACKTRANSACTION

end

else

begin

updatebanksetcurrentMoney=currentMoney+(case@i_transtypewhen'支取'then-@i_transmoneywhen'存入'then@i_transmoneyelse0end)wherecardID=@i_cardid

end

END

end

IFEXISTS(SELECTnameFROMsysobjects

WHEREname='transInfo_del'ANDtype='TR')

DROPTRIGGERtransInfo_del

GO

CREATETRIGGERtransInfo_del

ONtransInfo

forDELETE

as

DECLARE

@i_transDatedatetime,

@i_cardidint,

@i_transtypevarchar(20),

@i_transmoneyfloat(4)

set@i_cardid=0if

not

exists

(select

1

from

inserted)

and

exists

(select

1

from

deleted)

begin

--查詢(xún)刪除信息

SELECT@i_transDate=transDate,

@i_cardid

=cardID,

@i_transtype=transType,

@i_transmoney=transMoney

FROM

deleted

IF(isnull(@i_cardid,0)<>0)--查詢(xún)是否有刪除信息

BEGIN

--更新主表信息

updatebanksetcurrentMoney=currentMoney+(case@i_transtypewhen'支取'th