查看: 248|回复: 0

[SQLServer] SQL Server 存储过程的运用 2017-11-20 09:37 by pursuer.chen, ... 阅读, ... 评论, 收藏, 编辑

发表于 2017-12-7 08:00:01
概述

最近因为业务的需求写了一段时间存储过程,发现之前写的存储过程存在一些不严谨的地方,特别是TRY...CATCH中嵌套事务的写法;虽然之前写的并没有错,但是还是埋藏着很大的隐患在里面。希望这篇文章能给大家一些参考;文章内容有点长还望耐心阅读。

1.插入测试数据
  1. ----创建表
  2. DROP TABLE score
  3. GO
  4. CREATE TABLE [dbo].[score](
  5. id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
  6. name VARCHAR(50) NOT NULL,
  7. score INT NOT NULL CHECK (score>=0),
  8. months INT NOT NULL,
  9. createtime DATETIME NOT NULL DEFAULT GETDATE()
  10. )
  11. ---根据姓名月份查询分数
  12. CREATE INDEX IX_score_name ON score(name,months) include(score)
  13. ---根据月份查询最高分数
  14. CREATE INDEX IX_score_months ON score(months) include(name,score)
  15. ---创建姓名和月份组合的唯一索引
  16. CREATE UNIQUE INDEX IX_score_months_name ON score(months,name)
  17. ------插入测试数据
  18. TRUNCATE TABLE score
  19. INSERT INTO score(name,score,months)
  20. VALUES('li',50,10),('chen',70,10),('zhang',80,10),('wang',90,10),('li',50,11),('chen',70,11),('zhang',80,11),('wang',90,11)
  21. SELECT * FROM score;
复制代码

2.THROW

THROW是在2012版本中引入的,在有些场景当中,应用程序端不做一些合法性的验证,这些验证会被放在数据库端来验证。当数据库端验证输入的信息不合法时需要主动抛出异常来中断代码的执行。

THROW既可以接收错误信息抛错提示,同时也可以手动抛出错误到CATCH中。语法如下:

  1. ;THROW
  2. THROW [ { error_number | @local_variable },
  3. { message | @local_variable },
  4. { state | @local_variable } ]
  5. [ ; ]
  6. 参数
  7. error_number
  8. 表示异常的常量或变量。 error_number是int并且必须为大于或等于 50000 且小于或等于 2147483647,如果CATCH中使用RAISERROR来接收错误信息那么指定的error_number必须在sys.messages 中存在;如果使用CATCH来接收则不需要。
  9. 消息
  10. 描述异常的字符串或变量。 消息是nvarchar(2048)。
  11. 状态
  12. 在 0 到 255 之间的常量或变量,指示与消息关联的状态。 状态是tinyint。
复制代码

注意:

1.THROW代码前必须要用分号,因为THROW会中断代码的执行,所以如果将THROW放在CATCH中时必须放在ROLLBACK TRAN之后,否则不会回滚事务导致对象一直处于提交状态被锁。

2.THROW放CATCH中可以达到RAISERROR一样的效果,同时还简便了代码。

3. THROW能返回正确的错误代码行号,而RAISERROR没办法

参考:https://docs.microsoft.com/zh-cn/sql/t-sql/language-elements/throw-transact-sql

3.sp_addmessage

自定义错误号

  1. EXEC sp_addmessage
  2. @msgnum = 60000,
  3. @severity = 16,
  4. @msgtext = N'Manual cast wrong ',
  5. @lang = 'us_english';
  6. EXEC sp_addmessage
  7. @msgnum = 60000,
  8. @severity = 16,
  9. @msgtext = N'手动抛错',
  10. @lang = '简体中文';
复制代码

注意:自定义错误号必须大于50000

二、调用存储过程 1.查询存储过程
  1. ----查询存储过程
  2. CREATE PROCEDURE Pro_score
  3. (@Option VARCHAR(50),
  4. @name VARCHAR(50)='',
  5. @months INT=''
  6. )
  7. AS
  8. BEGIN
  9. ---查询指定人分数
  10. IF @Option='GetScore'
  11. BEGIN
  12. SELECT name,
  13. score
  14. FROM score
  15. WHERE name=@name
  16. END
  17. ----查询指定月份最高分数
  18. IF @Option='MonthMaxScore'
  19. BEGIN
  20. SELECT Top 1
  21. name,
  22. score
  23. FROM score
  24. WHERE months=@months
  25. ORDER BY score
  26. END
  27. END
复制代码

调用存储过程:

  1. EXEC Pro_score @Option='GetScore',@name='li'
  2. EXEC Pro_score @Option='MonthMaxScore',@months=11
复制代码

3.修改存储过程
  1. 1 CREATE PROCEDURE [dbo].[Pro_Insert_score]
  2. 2 (@Option VARCHAR(50),
  3. 3 @name VARCHAR(50)='',
  4. 4 @months INT=0,
  5. 5 @score INT=0
  6. 6 )
  7. 7 AS
  8. 8 BEGIN
  9. 9 DECLARE @ErrorNum INT,@ErrorSeverity INT,@ErrorState INT,@ErrorLine INT,@ErrorPro VARCHAR(200),@ErrorMessage NVARCHAR(4000);
  10. 10 IF @Option='InsertScore'
  11. 11 BEGIN
  12. 12
  13. 13 -----使用事务
  14. 14 BEGIN TRY
  15. 15 BEGIN TRAN
  16. 16 INSERT INTO score(name,score,months)
  17. 17 VALUES(@name,@score,@months)
  18. 18
  19. 19 ----插入重复值报错事务回滚
  20. 20 INSERT INTO score(name,score,months)
  21. 21 VALUES(@name,@score,@months)
  22. 22
  23. 23 COMMIT TRAN
  24. 24
  25. 25 END TRY
  26. 26 BEGIN CATCH
  27. 27 SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
  28. 28 RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) ;
  29. 29 ROLLBACK TRAN
  30. 30 ;THROW
  31. 31 ----执行失败
  32. 32 RETURN 1
  33. 33 END CATCH
  34. 34
  35. 35 ----执行成功
  36. 36 RETURN 0
  37. 37 END
  38. 38
  39. 39 END
复制代码

调用存储过程

  1. ----调用存储过程2
  2. DECLARE @status INT
  3. EXEC @status=Pro_Insert_score @Option='InsertScore',@name='chen',@months=12,@score=90
  4. SELECT @status
复制代码

可以发现使用RAISERROR抛错出来的行号和消息号都是错误的,50000这个消息号其实是不存在的,它是保留的一个统一的消息号。

可以通过查询sys.message查询对应的消息号

  1. SELECT * FROM score WHERE name='chen'
  2. SELECT * FROM sys.messages WHERE message_id=2601 and language_id=2052
复制代码

4.手动抛错中断

手动抛错也是这篇文章主要要讲的一个知识点,在有一些业务场景当中有一些验证操作需要在数据库中进行,甚至必须在更新之后进行但是又是正常的提交操作,在这种情况下就需要手动进行验证是否需要执行下面的代码。,见过很多程序员写存储过程喜欢在每一个判断的地方加上RETURN操作,目的是为了不执行后面的代码,同时又在RETURN前加上ROLLBACK操作。这虽然是一个办法,但是在事务中运用RETURN是一个很危险的操作,弄不好会导致事务一直处于打开操作导致表一直被锁住,在生成环境是很危险的操作。

建议使用THROW来手动进行抛错,THROW抛错会相当于触发一个11-19级别的错误,这样会跳到CATCH中做ROLLBACK操作。

注意:THROW前必须以分号开头,如果THROW前有代码以分号结尾也可以。

  1. CREATE PROCEDURE [dbo].[Pro_score_throw]
  2. (@Option VARCHAR(50),
  3. @name VARCHAR(50)='',
  4. @months INT=0,
  5. @score INT=0
  6. )
  7. AS
  8. BEGIN
  9. DECLARE @ErrorNum INT,@ErrorSeverity INT,@ErrorState INT,@ErrorLine INT,@ErrorPro VARCHAR(200),@ErrorMessage NVARCHAR(4000);
  10. IF @Option='UpdateScore'
  11. BEGIN
  12. -----使用事务
  13. BEGIN TRY
  14. BEGIN TRAN
  15. UPDATE score
  16. SET score=score+@score
  17. WHERE name=@name AND months=@months
  18. ----在有些业务场景有些判断必须等操作完了才能去做判断是否能继续执行下去
  19. IF (SELECT score FROM score WHERE name=@name AND months=@months)>100
  20. BEGIN
  21. ;THROW 60000,'分数不能大于100',111
  22. END
  23. COMMIT TRAN
  24. END TRY
  25. BEGIN CATCH <br /> <br /> ROLLBACK TRAN
  26. ;THROW
  27. END CATCH
  28. ----执行成功
  29. RETURN 0
  30. END
  31. END
复制代码

调用存储过程

  1. DECLARE @status INT
  2. EXEC @status=Pro_score_throw @Option='UpdateScore',@name='chen',@months=10,@score=40
  3. SELECT @status
复制代码

5.存储过程调用存储过程

  1. CREATE PROCEDURE [dbo].[Pro_score_ProcToProc]
  2. (@Option VARCHAR(50),
  3. @name VARCHAR(50)='',
  4. @months INT=0,
  5. @score INT=0
  6. )
  7. AS
  8. BEGIN
  9. DECLARE @ErrorNum INT,@ErrorSeverity INT,@ErrorState INT,@ErrorLine INT,@ErrorPro VARCHAR(200),@ErrorMessage NVARCHAR(4000);
  10. IF @Option='Update'
  11. BEGIN
  12. ----判断修改的人是否存在
  13. IF NOT EXISTS(SELECT * FROM score WHERE name=@name)
  14. BEGIN
  15. ---修改人不存在
  16. RETURN 2
  17. END
  18. ELSE
  19. BEGIN
  20. -----使用事务
  21. BEGIN TRY
  22. BEGIN TRAN
  23. UPDATE score
  24. SET createtime='1900-01-01 00:00:000'
  25. WHERE name=@name AND months=@months
  26. SELECT name,months,createtime,score FROM score WHERE name=@name AND months=@months
  27. ---定义事务保存点
  28. ---SAVE TRAN TRAN1
  29. ----调用别的存储过程
  30. EXEC Pro_score_ProcToProc @Option='UpdateScore',@name=@name,@months=@months,@score=@score
  31. COMMIT TRAN
  32. END TRY
  33. BEGIN CATCH
  34. SELECT name,months,createtime,score FROM score WHERE name=@name AND months=@months
  35. IF @@TRANCOUNT > 0
  36. ROLLBACK TRAN ;
  37. SELECT name,months,createtime,score FROM score WHERE name=@name AND months=@months
  38. ;THROW
  39. END CATCH
  40. END
  41. ----执行成功
  42. RETURN 0
  43. END
  44. IF @Option='UpdateScore'
  45. BEGIN
  46. ---使用事务
  47. BEGIN TRY
  48. BEGIN TRAN
  49. UPDATE score
  50. SET score=score+@score
  51. WHERE name=@name AND months=@months
  52. ----在有些业务场景有些判断必须等操作完了才能去做判断是否能继续执行下去
  53. IF (SELECT score FROM score WHERE name=@name AND months=@months)>100
  54. BEGIN
  55. ;THROW 60000,'分数不能大于100',111
  56. END
  57. COMMIT TRAN
  58. END TRY
  59. BEGIN CATCH
  60. ----回滚到指定保存点
  61. ----ROLLBACK TRAN TRAN1
  62. --回滚事务
  63. ROLLBACK TRAN
  64. ----执行失败
  65. ;THROW
  66. END CATCH
  67. END
  68. END
复制代码

存储过程调用存储过程事务的三种处理方法:

1.内部存储过程不要包含事务,因为内部ROLLBACK会直接回滚到外部的BEGIN TRAN导致外部的ROLLBACK没有对应的COMMIT;

2.还有一种方法是在调用内部存储过程之前使用保存点“SAVE TRAN TRAN1”,同时内部存储过程的ROLLBACK TRAN必须指定事务保存点,例如“ROLLBACK TRAN TRAN1”,这样内部存储过程回滚就只会回滚到保持点.

3.在外部存储过程的CATCH块的ROLLBACK前加上IF @@TRANCOUNT > 0判断条件

事务嵌套事务的理解

  1. ---事务1
  2. BEGIN TRAN
  3. ---事务2
  4. BEGIN TRAN
  5. COMMIT TRAN /ROLLBACK TRAN
  6. COMMIT TRAN /ROLLBACK TRAN
复制代码

对于事务嵌套事务,事务2的ROLLBACK操作会直接回滚到事务1的BEGIN TRAN,会导致事务1的ROLLBACK没有对应的BEGIN TRAN。处理方法可以在调用事务2之前定义一个事务保存点或者在事务1的ROLLBACK前加上IF @@TRANCOUNT > 0判断条件是否存在事务需要回滚。

SET XACT_ABORT ON

并不是所有的错误都能被CATCH所接收。对于严重级别为0-10(信息性消息)和20-25(致命的消息)是不能被CATCH所接收的,这时如果在事务中遇到了这类的报错那么通用会导致事务处理打开状态,这时就需要开启XACT_ABORT。当开启XACT_ABORT后只要代码中存在报错就会执行回滚操作,而不管错误的级别。例如:

  1. CREATE TABLE [dbo].[AA](
  2. [id] [int] NULL
  3. ) ON [PRIMARY]
  4. GO
  5. CREATE PROC Pro_bb
  6. (@Option VARCHAR(50))
  7. AS
  8. BEGIN
  9. IF @OPTION='a'
  10. BEGIN
  11. TRUNCATE TABLE AA;
  12. SELECT * FROM AA;
  13. ----事务1
  14. BEGIN TRY
  15. BEGIN TRAN
  16. INSERT INTO AA SELECT 2
  17. SELECT * FROM AA;
  18. INSERT INTO #BB SELECT 1
  19. COMMIT TRAN;
  20. END TRY
  21. BEGIN CATCH
  22. IF @@TRANCOUNT > 0
  23. ROLLBACK TRAN;
  24. ;THROW
  25. END CATCH
  26. END
  27. END
复制代码

由于临时表#BB不存在,导致插入报错,但是严重级别又小于11导致CATCH接收不到错误,这时查看发现事务处于打开状态,而且表AA也被锁住。

  1. EXEC Pro_bb @OPTION='a';
  2. DBCC OPENTRAN;
复制代码

加上事务前加上 SET XACT_ABORT ON

  1. ALTER TABLE [dbo].[AA](
  2. [id] [int] NULL
  3. ) ON [PRIMARY]
  4. GO
  5. CREATE PROC Pro_bb
  6. (@Option VARCHAR(50))
  7. AS
  8. BEGIN
  9. IF @OPTION='a'
  10. BEGIN
  11. SET XACT_ABORT ON
  12. TRUNCATE TABLE AA;
  13. SELECT * FROM AA;
  14. ----事务1
  15. BEGIN TRY
  16. BEGIN TRAN
  17. INSERT INTO AA SELECT 2
  18. SELECT * FROM AA;
  19. INSERT INTO #BB SELECT 1
  20. COMMIT TRAN;
  21. END TRY
  22. BEGIN CATCH
  23. IF @@TRANCOUNT > 0
  24. ROLLBACK TRAN;
  25. ;THROW
  26. END CATCH
  27. END
  28. END
复制代码

再次执行

  1. EXEC Pro_bb @OPTION='a';
  2. DBCC OPENTRAN;
复制代码

没有处于打开的事务而且事务也执行了回滚操作。

总结

1.建议2012以后版本所有的接收抛错改成使用THROW,不要使用THROW抛错又使用RAISERROR来介绍错误,在事务嵌套事务的写法中如果内部事务使用RAISERROR来接收THROW返回的报错不会执行后面的ROLLBACK。

2.建议在ROLLBACK前统一加上IF @@TRANCOUNT > 0判断条件,这样可以避免因为内部的ROLLBACK回滚或者RETURN操作导致ROLLBACK没有对应的COMMIT。

3.建议不要在事务内使用RETURN返回代码错误位置,RETURN会跳出事务导致提示ROLLBACK没有对应的COMMIT,严重的会导致事务一直处于打开不提交,THROW也可以指定错误位置。

4.CATCH只是用来处理TRY报错之后的逻辑,不要认为代码执行到了CATCH的ROLLBACK就会结束处理,除非是在ROLLBACK后加入了RETURN或者THROW之类的中断代码执行的命令,否则代码还将继续执行ROLLBACK之后的代码甚至END CATCH之后的代码(如果存在)。

备注:

作者:pursuer.chen

博客:http://www.cnblogs.com/chenmh

本站点所有随笔都是原创,欢迎大家转载;但转载时必须注明文章来源,且在文章开头明显处给明链接,否则保留追究责任的权利。

《欢迎交流讨论》



回复

使用道具 举报

关闭

站长推荐上一条 /1 下一条