设计一个事务,给所有在册的非新生学生增加1岁。要求:50人作为一个事务提交一次
BEGIN TRANSACTION
USE JZLjiaoxuedb
GO
DECLARE i_cursor CURSOR KEYSET FOR SELECT * FROM student
DECLARE @step int
OPEN i_cursor
DECLARE @sno char(6),@sn char(8),@sex char(2),@age tinyint,@dept char(10),@Native char(40),@id_sc int
SET @step=0
FETCH NEXT FROM i_cursor INTO @sno,@sn,@sex,@age,@dept,@Native,@id_sc
SELECT @sno AS Sno,@sn AS Sname,@sex AS Sex,@age AS Age,@dept AS Dept,@Native AS native,@id_sc AS ID_SC
WHILE @@FETCH_STATUS=0
BEGIN
SET @step=@step+1
UPDATE student SET Age=Age+1 WHERE @sno=Sno AND Age>18
IF(@@ERROR<>0) ROLLBACK TRANSACTION
IF(@step=50)
BEGIN
COMMIT
SET @step=0
END
FETCH NEXT FROM i_cursor INTO @sno,@sn,@sex,@age,@dept,@Native,@id_sc
END
GO
CLOSE i_cursor
DEALLOCATE i_cursor