mysql生产环境存储过程_SQL Server 在生产环境中这样写存储过程的坑都避免了吗？...

于 2021-01-20 00:37:12 发布
阅读量73 收藏
点赞数
文章标签： mysql生产环境存储过程
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42499067/article/details/113293805
版权

概述

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

1.插入测试数据

----创建表

DROP TABLE score

GO

CREATE TABLE [dbo].[score](

id INT NOT NULL PRIMARY KEY IDENTITY(1,1),

name VARCHAR(50) NOT NULL,

score INT NOT NULL CHECK (score>=0),

months INT NOT NULL,

createtime DATETIME NOT NULL DEFAULT GETDATE()

)

---根据姓名月份查询分数

CREATE INDEX IX_score_name ON score(name,months) include(score)

---根据月份查询最高分数

CREATE INDEX IX_score_months ON score(months) include(name,score)

---创建姓名和月份组合的唯一索引

CREATE UNIQUE INDEX IX_score_months_name ON score(months,name)

------插入测试数据

TRUNCATE TABLE score

INSERT INTO score(name,score,months)

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)

SELECT * FROM score;

cff97f3bad9c2b6eb09745c964c604b9.png

2.THROW

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

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

;THROW

THROW [ { error_number | @local_variable },

{ message | @local_variable },

{ state | @local_variable } ]

[ ; ]

参数

error_number

表示异常的常量或变量。 error_number是int并且必须为大于或等于 50000 且小于或等于 2147483647，如果CATCH中使用RAISERROR来接收错误信息那么指定的error_number必须在sys.messages 中存在；如果使用CATCH来接收则不需要。

消息

描述异常的字符串或变量。 消息是nvarchar(2048)。

状态

在 0 到 255 之间的常量或变量，指示与消息关联的状态。 状态是tinyint。

注意：

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

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

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

3.sp_addmessage

自定义错误号

EXEC sp_addmessage

@msgnum = 60000,

@severity = 16,

@msgtext = N'Manual cast wrong ',

@lang = 'us_english';

EXEC sp_addmessage

@msgnum = 60000,

@severity = 16,

@msgtext = N'手动抛错',

@lang = '简体中文';

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

调用存储过程

1.查询存储过程

----查询存储过程

CREATE PROCEDURE Pro_score

(@Option VARCHAR(50),

@name VARCHAR(50)='',

@months INT=''

)

AS

BEGIN

---查询指定人分数

IF @Option='GetScore'

BEGIN

SELECT name,

score

FROM score

WHERE name=@name

END

----查询指定月份最高分数

IF @Option='MonthMaxScore'

BEGIN

SELECT Top 1

name,

score

FROM score

WHERE months=@months

ORDER BY score

END

END

调用存储过程：

EXEC Pro_score @Option='GetScore',@name='li'

EXEC Pro_score @Option='MonthMaxScore',@months=11

e82e863580f587f0ea08f04837f48954.png

3.修改存储过程

CREATE PROCEDURE [dbo].[Pro_Insert_score]

(@Option VARCHAR(50),

@name VARCHAR(50)='',

@months INT=0,

@score INT=0

)

AS

BEGIN

DECLARE @ErrorMessage NVARCHAR(4000)

DECLARE @ErrorSeverity INT

DECLARE @ErrorState INT

IF @Option='InsertScore'

BEGIN

-----使用事务

BEGIN TRY

BEGIN TRAN

INSERT INTO score(name,score,months)

VALUES(@name,@score,@months)

----插入重复值报错事务回滚

INSERT INTO score(name,score,months)

VALUES(@name,@score,@months)

COMMIT TRAN

----执行成功

RETURN 0

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0

ROLLBACK TRAN

SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) ;

----执行失败

RETURN 1

END CATCH

END

END

调用存储过程

----调用存储过程2

DECLARE @status INT

EXEC @status=Pro_Insert_score @Option='InsertScore',@name='chen',@months=12,@score=90

SELECT @status

2a4e8787c2e98138c7799910c920dcc0.png

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

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

SELECT * FROM score WHERE name='chen'

SELECT * FROM sys.messages WHERE message_id=2601 and language_id=2052

0537c24260f9c6572c50cbcecb4ecc60.png

4.手动抛错中断

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

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

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

CREATE PROCEDURE [dbo].[Pro_score_throw]

(@Option VARCHAR(50),

@name VARCHAR(50)='',

@months INT=0,

@score INT=0

)

AS

BEGIN

DECLARE @ErrorNum INT,@ErrorSeverity INT,@ErrorState INT,@ErrorLine INT,@ErrorPro VARCHAR(200),@ErrorMessage NVARCHAR(4000);

IF @Option='UpdateScore'

BEGIN

-----使用事务

BEGIN TRY

BEGIN TRAN

UPDATE score

SET score=score+@score

WHERE name=@name AND months=@months

----在有些业务场景有些判断必须等操作完了才能去做判断是否能继续执行下去

IF (SELECT score FROM score WHERE name=@name AND months=@months)>100

BEGIN

;THROW 60000,'分数不能大于100',111

END

COMMIT TRAN

END TRY

BEGIN CATCH

ROLLBACK TRAN

;THROW

END CATCH

----执行成功

RETURN 0

END

END

调用存储过程

DECLARE @status INT

EXEC @status=Pro_score_throw @Option='UpdateScore',@name='chen',@months=10,@score=40

SELECT @status

967ea0c7cdab1744431fb7ea2c7be58b.png

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

CREATE TABLE Tbtran

(Id INT NOT NULL PRIMARY KEY,

Name VARCHAR(10)

)

INSERT INTO Tbtran(Id,Name) VALUES(1,'a'),(2,'b')

创建存储过程

CREATE PROC SpChangeTranCall

(@Option VARCHAR(50),

@Id INT =NULL,

@Name VARCHAR(50)=NULL OUTPUT

)

AS

DECLARE @ErrorMessage NVARCHAR(4000)

DECLARE @ErrorSeverity INT

DECLARE @ErrorState INT

DECLARE @ReturnNum INT ---Return返回值

DECLARE @OutputName VARCHAR(50)

BEGIN

SET XACT_ABORT ON

IF @OPTION='a'

BEGIN

SELECT

@Name=Name

FROM Tbtran

WHERE Id=@Id

IF @Name='a'

BEGIN

RETURN 2

END

IF @Name='b'

BEGIN

RETURN 3

END

INSERT INTO Tbtran(Id) VALUES(1)

END

IF @OPTION='b'

BEGIN

BEGIN TRY

BEGIN TRAN

INSERT INTO Tbtran(Id,Name) VALUES(3,'c');

----调用存储过程,内部存储过程不使用事务

EXEC @ReturnNum=SpChangeTranCall @OPTION='a',@Id=@Id,@Name=@OutputName OUTPUT

---接收内部存储过程OUTPUT返回值

SELECT @OutputName

---判断调用存储过程是否执行成功

IF @ReturnNum>0

BEGIN

ROLLBACK TRAN

RETURN @ReturnNum

END

COMMIT TRAN

---成功标志,放在COMMIT后

RETURN 0

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0

ROLLBACK TRAN

SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState)

---失败标志,放在最后

RETURN 1

END CATCH

END

END

调用存储过程

DECLARE @a INT

SET @a=-2

EXEC @a=SpChangeTranCall @OPTION='b',@Id=1

SELECT @a AS ReturnNum

9d23f41848a586868b951b89a97fc758.png

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

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

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

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

如果不需要使用RETURN 1来标志执行失败，可以使用THROW来替代RAISERROR来接受返回的错误值

ALTER PROC SpChangeTranCall

(@Option VARCHAR(50),

@Id INT =NULL,

@Name VARCHAR(50)=NULL OUTPUT

)

AS

DECLARE @ReturnNum INT ---Return返回值

DECLARE @OutputName VARCHAR(50)

BEGIN

SET XACT_ABORT ON

IF @OPTION='a'

BEGIN

SELECT

@Name=Name

FROM Tbtran

WHERE Id=@Id

IF @Name='a'

BEGIN

RETURN 2

END

IF @Name='b'

BEGIN

RETURN 3

END

INSERT INTO Tbtran(Id) VALUES(1)

END

IF @OPTION='b'

BEGIN

BEGIN TRY

BEGIN TRAN

INSERT INTO Tbtran(Id,Name) VALUES(3,'c');

----调用存储过程,内部存储过程不使用事务

EXEC @ReturnNum=SpChangeTranCall @OPTION='a',@Id=@Id,@Name=@OutputName OUTPUT

---接收内部存储过程OUTPUT返回值

SELECT @OutputName

---判断调用存储过程是否执行成功

IF @ReturnNum>0

BEGIN

ROLLBACK TRAN

RETURN @ReturnNum

END

COMMIT TRAN

---成功标志,放在COMMIT后

RETURN 0

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0

ROLLBACK TRAN

;THROW

END CATCH

END

END

调用存储过程

DECLARE @a INT

SET @a=-2

EXEC @a=SpChangeTranCall @OPTION='b',@Id=3

SELECT @a AS ReturnNum

2b9dfc1c3fba99c1b52370c6c39ad6d1.png

注意：THROW接收的返回错误行是准确的错误行，而RAISERROR返回的错误行是不正确的。

6.事务嵌套事务的理解

---事务1

BEGIN TRAN

---事务2

BEGIN TRAN

COMMIT TRAN /ROLLBACK TRAN

COMMIT TRAN /ROLLBACK TRAN

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

7.SET XACT_ABORT ON

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

CREATE TABLE [dbo].[AA](

[id] [int] NULL

) ON [PRIMARY]

GO

CREATE PROC Pro_bb

(@Option VARCHAR(50))

AS

BEGIN

IF @OPTION='a'

BEGIN

TRUNCATE TABLE AA;

SELECT * FROM AA;

----事务1

BEGIN TRY

BEGIN TRAN

INSERT INTO AA SELECT 2

SELECT * FROM AA;

INSERT INTO #BB SELECT 1

COMMIT TRAN;

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0

ROLLBACK TRAN;

;THROW

END CATCH

END

END

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

EXEC Pro_bb @OPTION='a';

DBCC OPENTRAN;

ad43326b84a11ff8dd1d40e097d2f5e4.png

加上事务前加上 SET XACT_ABORT ON

ALTER TABLE [dbo].[AA](

[id] [int] NULL

) ON [PRIMARY]

GO

CREATE PROC Pro_bb

(@Option VARCHAR(50))

AS

BEGIN

IF @OPTION='a'

BEGIN

SET XACT_ABORT ON

TRUNCATE TABLE AA;

SELECT * FROM AA;

----事务1

BEGIN TRY

BEGIN TRAN

INSERT INTO AA SELECT 2

SELECT * FROM AA;

INSERT INTO #BB SELECT 1

COMMIT TRAN;

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0

ROLLBACK TRAN;

;THROW

END CATCH

END

END

再次执行

EXEC Pro_bb @OPTION='a';

DBCC OPENTRAN;

544a4665acf9e7280d45de595d695eae.png

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

总结

1.THROW后面的语句不会被执行；如果不需要Return 0和Return 1来作为成功失败的标志那么可以使用THROW来替代RAISERROR，否则使用RAISERROR。不要使用THROW抛错又使用RAISERROR来接收错误，在事务嵌套事务的写法中如果内部事务使用RAISERROR来接收THROW返回的报错不会执行后面的ROLLBACK。

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

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

4.如果存储过程调用存储过程，内部的存储过程不使用事务也不需要使用TRY来接收错误，可以参考上面的“5.存储过程调用存储过程”。

5.虽然OUTPUT也能用来接收存储过程的执行情况，但是RETURN的作用可以跳出存储过程不执行后面的代码，所以避免即使用RETURN作为执行成功失败的标志又使用OUTPUT来标志执行成功和失败的标记，OUTPUT更多用来返回内部存储过程执行结果的值而不是用来做成功失败的标志。

备注：

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

《欢迎交流讨论》

夜空中筱晴的星
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值