sql 13
来源:互联网 发布:mac chrome 书签同步 编辑:程序博客网 时间:2024/05/11 00:22
【例13.1】
(1)
USE Xk
GO
--开始事务
BEGIN TRANSACTION
INSERT StuCou(StuNo,COuNo,WillOrder) VALUES ('00000025','001',1)
INSERT StuCou(StuNo,COuNo,WillOrder) VALUES ('00000025','002',2)
INSERT StuCou(StuNo,COuNo,WillOrder) VALUES ('00000025','003',3)
--提交事务
COMMIT TRANSACTION
(2)
SELECT * FROM StuCou WHERE StuNo='00000025'
(3)
DELETE FROM StuCou WHERE StuNo='00000025'
【例13.2】
(1)
USE Xk
GO
--开始事务
BEGIN TRANSACTION
INSERT StuCou(StuNo,COuNo,WillOrder) VALUES ('00000025','001',1)
INSERT StuCou(StuNo,COuNo,WillOrder) VALUES ('00000025','002',2)
INSERT StuCou(StuNo,COuNo,WillOrder) VALUES ('00000025','003',3)
--撤销事务
ROLLBACK TRANSACTION
(2)
SELECT * FROM StuCou WHERE StuNo='00000025'
【例13.3】
(1)
USE Xk
GO
BEGIN TRANSACTION
--报3门课程
INSERT StuCou(StuNo,COuNo,WillOrder) VALUES ('00000025','001',1)
INSERT StuCou(StuNo,COuNo,WillOrder) VALUES ('00000025','002',2)
INSERT StuCou(StuNo,COuNo,WillOrder) VALUES ('00000025','003',3)
DECLARE @CountNum INT
SET @CountNum= (SELECT COUNT(*) FROM StuCou WHERE StuNo='00000025')
IF @CountNum>4
BEGIN
ROLLBACK TRANSACTION
PRINT '超过报名门数!'
END
ELSE
BEGIN
COMMIT TRANSACTION
PRINT '报名成功!'
END
(2)
SELECT * FROM StuCou WHERE StuNo='00000025'
(3)
DELETE FROM StuCou WHERE StuNo='00000025'
(4)
USE Xk
GO
BEGIN TRANSACTION
--报3门课程
INSERT StuCou(StuNo,COuNo,WillOrder) VALUES ('00000025','001',1)
INSERT StuCou(StuNo,COuNo,WillOrder) VALUES ('00000025','002',2)
INSERT StuCou(StuNo,COuNo,WillOrder) VALUES ('00000025','003',3)
INSERT StuCou(StuNo,COuNo,WillOrder) VALUES ('00000025','004',4)
INSERT StuCou(StuNo,COuNo,WillOrder) VALUES ('00000025','005',5)
DECLARE @CountNum INT
SET @CountNum= (SELECT COUNT(*) FROM StuCou WHERE StuNo='00000025')
IF @CountNum>4
BEGIN
ROLLBACK TRANSACTION
PRINT '超过报名门数!'
END
ELSE
BEGIN
COMMIT TRANSACTION
PRINT '报名成功!'
END
(5)
SELECT * FROM StuCou WHERE StuNo='00000025'
【例13.4】
(1)
CREATE TABLE TestTran (A INT, B CHAR(3))
GO
(2)
BEGIN TRANSACTION Tran1
GO
INSERT INTO TestTran VALUES (1, 'aaa')
GO
(3)
BEGIN TRANSACTION Tran2
GO
INSERT INTO TestTran VALUES (2, 'bbb')
GO
(4)
BEGIN TRANSACTION Tran3
GO
INSERT INTO TestTran VALUES (3, 'ccc')
GO
(5)
COMMIT TRANSACTION Tran3
GO
(6)
COMMIT TRANSACTION Tran2
GO
(7)
COMMIT TRANSACTION Tran1
GO
(8)
DROP TABLE TestTran
【例13.5】
(1)
CREATE TABLE TestTran (A INT, B CHAR(3))
GO
(2)
BEGIN TRANSACTION Tran1
GO
INSERT INTO TestTran VALUES (1, 'aaa')
GO
(3)
BEGIN TRANSACTION Tran2
GO
INSERT INTO TestTran VALUES (2, 'bbb')
GO
(4)
BEGIN TRANSACTION Tran3
GO
INSERT INTO TestTran VALUES (3, 'ccc')
GO
(5)
ROLLBACK TRANSACTION Tran3
(6)
ROLLBACK TRANSACTION Tran1
--或
ROLLBACK TRANSACTION
(7)
DROP TABLE TestTran
【例13.7】
USE master
GO
EXEC sp_lock
GO
【例13.8】
(1)
USE Xk
SET DEADLOCK_PRIORITY LOW
BEGIN TRANSACTION
--第一个事务中,系统自动为Student 表中StuNo='00000001'的记录加锁
UPDATE Student SET Pwd='1111111' WHERE StuNo='00000001'
(2)
BEGIN TRANSACTION
--第二个事务中,系统自动为Course 表中CouNo='002'的记录加锁
UPDATE Course SET Credit=3 WHERE CouNo='002'
(3)
--第一个事务中,系统自动为Course 表中CouNo='002'的记录加锁
UPDATE Course SET Credit=4 WHERE CouNo='002'
(4)
--第二个事务中,系统自动为Student 表中StuNo='00000001'的记录加锁
UPDATE Student SET Pwd='99999999' WHERE StuNo='00000001'
- sql 13
- SQL 基础 总结(13) SQL语句
- 2009.11.13---sql语句
- SQL 8-13章
- 13常用sql语句
- 2017/4/13 SQL
- SQL
- SQL
- SQL
- sql
- sql
- SQL
- sql
- SQL (-)
- sql
- SQL
- SQL
- SQL
- 日期控件的问题!!!!!!!!!!!!!!!
- CSS光标属性一览表
- 修改iis的request限制值
- sql 实例
- sql 语句 查询
- sql 13
- sql 例子
- Java中使用BigDecimal进行浮点数精确计算、超大整数、浮点数等计算,没有数位限制
- CMMI 介绍
- 第一篇博客
- Gridview利用DataFormatString属性设置数据格式
- 轉摘老公的blog-蜜月桂林(一)
- vi 命令
- linux log analysis 日志分析