SQL Server 事务 游标 基本语法操作实例 全SQL语句

USE master GO IF EXISTS(SELECT * FROM sysDatabases WHERE name='TransDB') DROP DATABASE TransDB GO CREATE DATABASE TransDB GO USE TransDB GO IF EXISTS(SELECT * FROM sysObjects WHERE name='BankInfo') DROP TABLE BankInfo GO CREATE TABLE BankInfo ( ID int NOT NULL IDENTITY PRIMARY KEY, Username nvarchar(10) NOT NULL, Balance money NOT NULL Check(Balance>0) ) -- INSERT INTO BankInfo VALUES ('小敏',800) INSERT INTO BankInfo VALUES ('吉吉',1000) -- SELECT * FROM BankInfo --模拟转换：小敏转300给吉吉 UPDATE BankInfo SET Balance=Balance-300 WHERE ID=1 UPDATE BankInfo SET Balance=Balance+300 WHERE ID=2 GO SELECT * FROM BankInfo --再执行转换2次 ----开启事务 BEGIN TRAN --以下代码任意执行N次 UPDATE BankInfo SET Balance=Balance-300 WHERE ID=1 UPDATE BankInfo SET Balance=Balance+300 WHERE ID=2 --查看 SELECT * FROM BankInfo --回滚 ROLLBACK TRAN --编写完整的转帐代码 BEGIN TRAN DECLARE @err int --累加每条语句执行后的错误号，如果是0表示全部正确，不是0就表示有错 UPDATE BankInfo SET Balance=Balance-300 WHERE ID=1 SET @err = @@ERROR UPDATE BankInfo SET Balance=Balance+300 WHERE ID=2 SET @err = @err + @@ERROR IF @err = 0 BEGIN PRINT '转账成功' COMMIT TRAN SELECT * FROM BankInfo END ELSE BEGIN PRINT '转账失败' ROLLBACK TRAN END GO SELECT * FROM BankInfo --练习（书P106第1题）：创建一个Students的数据库，有表StuInfo(StuID,StuName,StuAge)，无约束。 -- 要求编写一个事务，当插入的年龄大于100或小于0就回滚 USE master GO IF EXISTS(SELECT * FROM sysDatabases WHERE name='Students') DROP DATABASE Students GO CREATE DATABASE Students GO USE Students GO ----------------------学生信息表 IF EXISTS(SELECT * FROM sysObjects WHERE name='StuInfo') DROP TABLE StuInfo GO CREATE TABLE StuInfo ( StuID int IDENTITY PRIMARY KEY, StuName nvarchar(5) NOT NULL, StuAge int NOT NULL ) GO -- BEGIN TRAN --插入语句 INSERT INTO StuInfo VALUES('李四',19) INSERT INTO StuInfo VALUES('王五',29) INSERT INTO StuInfo VALUES('赵六',219) --判断有无年龄不合法的记录 IF EXISTS(SELECT * FROM StuInfo WHERE StuAge>100 OR StuAge<0) BEGIN PRINT '年龄不合法，回滚！' ROLLBACK TRAN END ELSE BEGIN PRINT '添加数据成功！' COMMIT TRAN END GO SELECT * FROM StuInfo