【正文】 RINT 39。用戶登陸成功^_^39。 END ELSE PRINT 39。此用戶不存在，請(qǐng)您重新輸入！39。 ENDGO2添加客房USE 酒店客房管理系統(tǒng) GO IF EXISTS(SELECT * FROM SYSOBJECTSWHERE name = 39。添加客房39。) DROP PROCEDURE 添加客房GOCREATE PROCEDURE 添加客房@roomid varchar(20),@rcategoryid varchar(20),@rposition varchar(50),@description varchar(50)AS BEGIN DECLARE @returnvalue int SET @returnvalue = 1 IF EXISTS(SELECT RoomId FROM RoomsInfo WHERE RoomId = @roomid) PRINT 39。此房間已存在，請(qǐng)您重新添加！39。 ELSE BEGIN IF EXISTS(SELECT RCategoryId FROM RoomCategory WHERE RCategoryId = @rcategoryid) BEGIN INSERT INTO RoomsInfo VALUES(@roomid,@rcategoryid,@rposition,@description) INSERT INTO RoomStatus VALUES(@roomid,0) SET @returnvalue = 0 PRINT 39。客房添加成功^_^39。 END ELSE PRINT 39?？头款愋筒淮嬖?，請(qǐng)您重新添加！39。 END ENDGO3客戶訂房USE 酒店客房管理系統(tǒng) GO IF EXISTS(SELECT * FROM SYSOBJECTSWHERE name = 39?？蛻粲喎?9。) DROP PROCEDURE 客戶訂房GOCREATE PROCEDURE 客戶訂房@begintime date,@roomid varchar(20),@cidentityid varchar(30),@ame varchar(20),@cphone varchar(20)ASBEGINDECLARE @rstatus int SELECT @rstatus = RStatus FROM RoomStatus WHERE RoomId = @roomid IF(@rstatus = 1) PRINT 39。此客房正在使用，請(qǐng)您選擇別的客房！39。 ELSE BEGIN UPDATE RoomStatus SET RStatus = 1 WHERE RoomId = @roomid IF EXISTS(SELECT Cidentityid FROM CustomersInfo WHERE Cidentityid = @cidentityid) BEGIN UPDATE CustomersInfo SET Cname = @ame,Cphone = @cphone WHERE Cidentityid = @cidentityid UPDATE History SET Cname = @ame,Cphone = @cphone WHERE Cidentityid = @cidentityid INSERT INTO RoomService VALUES(@roomid,@cidentityid) INSERT INTO History VALUES(@begintime,null,@roomid,0,@cidentityid,@ame,@cphone) PRINT 39。訂房成功^_^39。 END ELSE BEGIN INSERT INTO CustomersInfo VALUES(@cidentityid,@ame,@cphone) INSERT INTO RoomService VALUES(@roomid,@cidentityid) INSERT INTO History VALUES(@begintime,null,@roomid,0,@cidentityid,@ame,@cphone) PRINT 39。訂房成功^_^39。 END ENDENDGO4客戶退房USE 酒店客房管理系統(tǒng)GOIF EXISTS(SELECT * FROM SYSOBJECTSWHERE name = 39?？蛻敉朔?9。) DROP PROCEDURE 客戶退房GOCREATE PROCEDURE 客戶退房@roomid varchar(20),@cidentityid varchar(20),@endtime dateASBEGIN DECLARE @price numeric(10,2), @totalprice numeric(10,2), @begintime date SELECT @price = RoomCategory .Price FROM RoomCategory,RoomsInfo WHERE = AND = @roomid SELECT @begintime = FROM History WHERE = @roomid AND = @cidentityid SET @totalprice = @price * DATEDIFF (DAY,@begintime,@endtime) IF(@totalprice = 0) SET @totalprice = @price UPDATE History SET EndTime = @endtime,TotalPrice = @totalprice WHERE RoomId = @roomid AND CIdentityId = @cidentityid AND BeginTime = @begintime DELETE FROM RoomService WHERE RoomId = @roomid UPDATE RoomStatus SET RStatus = 0 WHERE RoomId = @roomid PRINT 39。退房成功^_^39。ENDGO5客戶信息修改USE 酒店客房管理系統(tǒng)GOIF EXISTS(SELECT * FROM SYSOBJECTSWHERE name = 39?？蛻粜畔⑿薷?9。) DROP PROCEDURE 客戶信息修改GOCREATE PROCEDURE 客戶信息修改@cidentityid varchar(30),@ame varchar(20),@cphone varchar(20)ASBEGIN UPDATE CustomersInfo SET Cname = @ame,Cphone = @cphone WHERE CIdentityId = @cidentityid UPDATE History SET Cname = @ame,Cphone = @cphone WHERE CIdentityId = @cidentityid PRINT 39?？蛻粜畔⑿薷某晒_^39。ENDGO 6密碼修改USE 酒店客房管理系統(tǒng)GOIF EXISTS(SELECT * FROM SYSOBJECTSWHERE name = 39。密碼修改39。) DROP PROCEDURE 密碼修改GOCREATE PROCEDURE 密碼修改@userid varchar(20),@password varchar(20),@npassword varchar(20)AS BEGIN IF EXISTS(SELECT UseId,UPassword FROM UsersInfo WHERE UseId = @userid AND UPassword = @password) UPDATE UsersInfo SET UPassword = @npassword WHERE UseId = @userid ELSE PRINT 39。這個(gè)用戶不存在，請(qǐng)您重新輸入！39。ENDGO7客房刪除USE 酒店客房管理系統(tǒng)GOIF EXISTS(SELECT * FROM SYSOBJECTSWHERE name = 39?？头縿h除39。) DROP PROCEDURE 客房刪除GOCREATE PROCEDURE 客房刪除@roomid varchar(20)ASBEGIN DECLARE @rstatus int IF EXISTS(SELECT * FROM RoomsInfo WHERE RoomId = @roomid) BEGIN SELECT @rstatus = Rstatus FROM RoomStatus WHERE RoomId = @roomid IF(@rstatus = 1) PRINT 39。此客房正在被使用，現(xiàn)在無(wú)法刪除！39。 ELSE BEGIN UPDATE History SET RoomId = 39。D39。+ @roomid WHERE RoomId = @roomid DELETE FROM RoomStatus WHERE RoomId = @roomid DELETE FROM RoomsInfo WHERE RoomId = @roomid PRINT 39。恭喜您客房刪除成功^_^39。 END END ELSE PRINT 39。您刪除的客房不存在!39。ENDGO8客房修改USE 酒店客房管理系統(tǒng)GO IF EXISTS(SELECT * FROM SYSOBJECTSWHERE name = 39?？头啃薷?9。) DROP PROCEDURE 客房修改GOCREATE PROCEDURE 客房修改@roomid varchar(20),@rcategoryid varchar(20),@rpostition varchar(50),@description varchar(50)ASBEGIN IF EXISTS(SELECT RCategoryId FROM RoomCategory WHERE RCategoryId = @rcategoryid) BEGIN UPDATE RoomsInfo SET RCategoryId = @rcategoryid,RPostition = @rpostition,Eescription = @description WHERE RoomId = @roomid PRINT 39?？头啃薷某晒_^39。 END ELSE PRINT 39?？头款愋筒淮嬖?，請(qǐng)您重新修改！39。ENDGO（6）使用T—SQL語(yǔ)句創(chuàng)建庫(kù)存觸發(fā)器。USE 酒店客房管理系統(tǒng)GOIF EXISTS(SELECT * FROM SYSOBJECTSWHERE name = 39。TGI_業(yè)務(wù)信息添加39。) DROP TRIGGER TGI_業(yè)務(wù)信息添加GOCREATE TRIGGER TGI_業(yè)務(wù)信息添加ON RoomServiceFOR INSERTAS DECLARE @roomid varchar(20) SELECT @roomid = Roomid FROM INSERTED UPDATE RoomStatus SET Rstatus = 1 WHERE Roomid = @roomidGOUSE 酒店客房管理系統(tǒng)GOIF EXISTS(SELECT * FROM SYSOBJECTSWHERE name = 39。TGI_業(yè)務(wù)信息刪除39。) DROP TRIGGER TGI_業(yè)務(wù)信息刪除GOCREATE TRIGGER TGI_業(yè)務(wù)信息刪除ON RoomServiceFOR DELETEAS DECLARE @roomid varchar(20) SELECT @roomid = Roomid FROM DELETED UPDATE RoomStatus SET Rstatus = 0 WHERE Roomid = @roomid GO指導(dǎo)教師評(píng)語(yǔ)及設(shè)計(jì)成績(jī) 評(píng) 語(yǔ) 課程設(shè)計(jì)成績(jī)： 指導(dǎo)教師： 日期： 年 月 日