第20講 事務(wù)與鎖實驗步驟.doc

-【第20講 事務(wù)與鎖】-【顯示事務(wù)】-【回滾事務(wù)】begin transactioninsert into S values(lily,女,18,軟件工程0914班)insert into S values(lucy,女,18,軟件工程0914班)rollback transaction -回滾事務(wù)-驗證 select * from S-【提交事務(wù)】begin transactioninsert into S values(lily,女,18,軟件工程0914班)insert into S values(lucy,女,18,軟件工程0914班)commit transaction -提交事務(wù)(可省略)-驗證 select * from S-begin transactioninsert into S values(lucy2,女,18,軟件工程0914班)insert into S values(lucy3,女,18,軟件工程0914班)rollback transaction -回滾事務(wù)到begin處，此事務(wù)已結(jié)束commit transaction -由于沒有配對的begin transaction，所以報錯-驗證 select * from S-【回滾事務(wù)到保存點】begin transactioninsert into S values(lucy4,女,18,軟件工程0914班)insert into S values(lucy5,女,18,軟件工程0914班)save transaction point1 -設(shè)置保存點point1insert into S values(lucy6,女,18,軟件工程0914班)insert into S values(lucy7,女,18,軟件工程0914班)rollback transaction point1 -回滾事務(wù)到保存點point1處，此事務(wù)還未結(jié)束commit transaction -提交事務(wù)-驗證 select * from S-【總結(jié)-顯示事務(wù)的使用】：- begin transaction . rollback transaction 表示事務(wù)中的操作又還原到操作前的狀態(tài)；- begin transaction . commit transaction 表示事務(wù)中的操作執(zhí)行成功；- begin transaction 必須和 rollback transaction 或者 commit transaction配對使用，否則表示事務(wù)未結(jié)束；- 以上三個關(guān)鍵語句不可同時在一個事務(wù)中使用。除非事務(wù)中設(shè)置有保存點，rollback transaction回滾到保存點處-【隱式事務(wù)】-set implicit_transactions on -開啟隱式事務(wù)insert into S values(Tom,男,18,軟件工程0914班)insert into S values(John,男,18,軟件工程0914班)rollback transactioninsert into S values(Alice,女,18,軟件工程0914班)insert into S values(Mary,女,18,軟件工程0914班)commit transactionset implicit_transactions off -關(guān)閉隱式事務(wù)-【總結(jié)-隱式事務(wù)的使用】：。-【銀行轉(zhuǎn)賬事務(wù)】-create table account(accountID varchar(20), -賬號accountBalance money, -賬戶余額)insert into account values(11,10000)insert into account values(22,1000)-create proc pro_turnAccountaccountID1 varchar(20), -本賬戶accountID2 varchar(20), -轉(zhuǎn)賬的賬戶turnAccount money -轉(zhuǎn)賬的金額asbeginbegin transactionupdate account set accountBalance = accountBalance - turnAccount where accountID = accountID1update account set accountBalance = accountBalance + turnAccount where accountID = accountID2if (select accountBalance from account where accountID = accountID1) 60beginprint 班級人數(shù)不得大于60rollback -回滾endelseprint 添加成功endgo-驗證insert into S values(Michael,男,19,軟件工程0914班)-寫法二：alter trigger T_insert on Sinstead of insertasbegindeclare count int -班級人數(shù)select count = count(*) from S where className = (select className from inserted)if count 60beginprint 班級人數(shù)不得大于60rollback -回滾endelsebegininsert into S(Sname,Ssex,Sage,ClassName) select Sname,Ssex,Sage,ClassName from insertedprint 添加成功endendgo-驗證insert into S values(Michael,男,19,軟件工程0914班)- 【上次作業(yè)】 -6.模擬一張表完成提款機功能：-1) 創(chuàng)建一個觸發(fā)器，對ATM表中寫入內(nèi)容時判斷該用戶帳戶是否是合法的，是則成功寫入輸出“成功完成”,否則提示錯誤信息“用戶名不正確”。create trigger T_06_01 on ATMfor insertasbeginif exists(select * from userInfo where uid = (select uid from inserted)print 成功完成elsebeginprint 用戶名不正確rollbackendendgo-驗證insert into ATM values(1001,400)-2) 創(chuàng)建一個觸發(fā)器，當(dāng)向ATM表中寫入內(nèi)容即用戶取款時自動更新他的余額。（提示：當(dāng)余額小于取款金額時提示報錯信息余額不足，并取消當(dāng)有操作，否則成功取款）。create trigger T_06_02 on ATMfor insertasbegindeclare getmoney money,uid varchar(10),savemoney moneyselect getmoney = getmoney,uid = uid from insertedselect savemoney = savemoney from userInfo where uid = uidif getmoney savemoneybeginprint 余額不足，操作無法執(zhí)行rollbackendelseupdate userInfo set savemoney = savemoney - getmoney where uid = uiddeclare a moneyselect a = savemoney from userInfo where uid = uidprint 當(dāng)前余額為： + cast(a as varchar(10)endgo-驗證insert into ATM values(1002,1000)-3) 創(chuàng)建一個存儲過程，修改userInfo表，添加一個字段flag (int型)。利用update命令直接批量更新flag值，當(dāng)金額小于值為，否則為。alter table userInfoadd flag int-create proc pro_06_03asbeginupdate userInfo set flag = (casewhen savemoney 100 then 0else 1end)end-exec pro_06_03select * from userInfo-4）創(chuàng)建一個觸發(fā)器，當(dāng)userInfo表中金額發(fā)生變化時，狀態(tài)列會自動更新為相應(yīng)狀態(tài)。create trigger T_06_04 on userInfo