№05 存储过程§2

最新推荐文章于 2024-06-20 18:09:36 发布

vicanary

最新推荐文章于 2024-06-20 18:09:36 发布

阅读量570

点赞数

分类专栏： MSSQL2008R2

本文链接：https://blog.csdn.net/longjuesi/article/details/37566863

版权

MSSQL2008R2 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

存储过程常用技术

存储过程一直是sql编程的精华所在，这一篇主要通过实践来创建复杂的存储过程，以及说明一些辅助的SQL Server功能，这些功能完全可以放在其它地方来讲，因为它不是存储过程的核心概念，而是T-SQL语法和一些系统常用对象，考虑到梳理到这里，就一概而论。

检测存储过程是否存在

规范的创建脚本之前一般有如下一段代码（这是关于一个名叫t1的表）：

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('dbo.t1') AND type in ('U'))
DROP TABLE dbo.t1
GO

谁都知道这段代码是要 检查模式dbo下的t1表是否存在，如果存在则删除。但是细节像我这种人从来没有注意过，T-SQL的IF语句和EXISTS，SELECT，DROP就不说了，关键是sys.object视图和OBJECT_ID方法，它们都是系统已定义的对象，其功能实现并不复杂，完全可以自己写，前提是你对系统表以及SQL Server的运行方式足够了解。

sys.object：返回一个数据库中所有用户定义的，模式范围下（你猜的没错，有些用户创建的对象不属于模式范围中：比如数据库对象，它是模式的上一层）的对象：表，函数，存储过程，视图，主键外键（这种键关系也是对象）……这些对象都有一个唯一的object_id，但是从sys.object中检索数据时，习惯上也要指定对象的type，type用字符串表示，有很多类别：U表示用户定义表，F表示外键关系，FN表示标量值函数，V表示视图……完整的集合请参阅官方文档：http://msdn.microsoft.com/en-us/library/ms190324%28v=sql.105%29.aspx
OBJECT_ID：这是一个系统定义的函数，调用语法为OBJECT_ID(object_name,object_type)，返回对象的object_id，其object_type和sys.object中的type取值域一样。

如果你装了AdventureWorks2008R2，在SQL Servermanagement Studio中新建查询运行下面的代码：

SELECT OBJECT_ID('HumanResources.Department')

你会看到这样的输出：

所有对象的检测基本一样，除了这个通用的sys.object视图，还有sys.procedure，sys.tables等，所以检测存储过程是否存在，也可以这么写：

IF (OBJECT_ID('my_pro','P') IS NOT NULL) 
DROP PROCEDURE my_pro

关于 NULL的判断不用 ‘=’ 号，我就不说什么了。。。

示例表

下面是一个T-SQL脚本，用来创建两个表并插入一些数据，本文后面所有的示例都是基于这个两个表：

IF OBJECT_ID('wifes','U') IS NOT NULL
DROP TABLE wifes;
GO
IF OBJECT_ID('husband','U') IS NOT NULL
DROP TABLE husband;
GO
CREATE TABLE husband(
id VARCHAR(10) PRIMARY KEY,
name VARCHAR(20)
)

CREATE TABLE wifes(
id VARCHAR(10) PRIMARY KEY,
name VARCHAR(20),
hid VARCHAR(10) FOREIGN KEY REFERENCES husband--FK
)

INSERT husband(id,name)VALUES('1','Sun')
INSERT husband(id,name)VALUES('2','Sam')

INSERT wifes(id,name,hid)VALUES('1','Lily','1')
INSERT wifes(id,name,hid)VALUES('2','Lucy','1')
INSERT wifes(id,name,hid)VALUES('3','Dorothy','1')

事务的使用

在此之前，先介绍 SET XACT_ABORT { ON | OFF } 语句，它指示当事务发生运行时错误时，事务是否全部回滚：当 SET XACT_ABORT ON时，事务中发生Run-time Error，会将整个事务回滚，事务具有原子性；当SET XACT_ABORT OFF时，只回滚错误行，但是其他的语句都会被执行提交，包括错误行之前的和之后的。

下面这个例子演示存储过程中使用事务

IF OBJECT_ID('InsertTest','P') IS NOT NULL
DROP PROC InsertTest
GO
CREATE PROC InsertTest
AS 
BEGIN
	SET XACT_ABORT ON
	BEGIN TRAN
		INSERT wifes(id,name,hid)VALUES('4','Ketty','1')--外键存在
		INSERT wifes(id,name,hid)VALUES('5','Nikko','9')--不存在
		INSERT wifes(id,name,hid)VALUES('6','Aima','1')--存在
		INSERT wifes(id,name,hid)VALUES('7','Numi','1')--存在
	COMMIT TRAN	
END

到DB中看看表，第二个INSERT出错，整个事务块被回滚，数据库中没有插入任何数据。

现在将上面代码中的SET XACT_ABORT ON 改成 OFF试试，可以看到wifes.id为4，6，7的数据成功插入。

关于事务和锁更高级的应用我会在后续的T-SQL编程中述写。

处理异常

在上面的例子中你可能已经注意到了，在 ServerManagement Studio 中执行有错的语句时抛出了异常信息：

消息 547，级别 16，状态 0，过程 InsertTest，第 6 行
INSERT 语句与 FOREIGN KEY 约束"FK__wifes__hid__1AF3F935"冲突。该冲突发生于数据库"AdventureWorks2008R2"，表"dbo.husband", column 'id'。

这种错误在SQL2008中已经可以像C#和JAVA中那么简单地被捕捉，它的语法也很简单：BEGIN TRY ... END TRY ，BEGIN CATCH ... END CATCH。

依然是上面的例子，我稍微改动了它：

IF OBJECT_ID('InsertTest','P') IS NOT NULL
DROP PROC InsertTest
GO
CREATE PROC InsertTest
AS 
BEGIN
	SET XACT_ABORT ON
	BEGIN TRY
	BEGIN TRAN
		INSERT wifes(id,name,hid)VALUES('4','Ketty','1')--外键存在
		INSERT wifes(id,name,hid)VALUES('5','Nikko','9')--不存在
		INSERT wifes(id,name,hid)VALUES('6','Aima','1')--存在
		INSERT wifes(id,name,hid)VALUES('7','Numi','1')--存在
	COMMIT TRAN	
	END TRY
	BEGIN CATCH
		ROLLBACK TRAN 
		SELECT ERROR_MESSAGE()
	END CATCH
END

用 EXEC InsertTest 调用（在执行这个存储过程时，请执行示例表中的初始化脚本，以使此次的执行环境一样，得出没有偏差的结果。），结果如下：