存储过程、触发器和函数

存储过程

存储过程可以提高引用程序的设计效率和增强系统的安全性.这一小节将全面介绍存储过程的特点、类型、创建、执行等内容.

存储过程的特点和类型

存储过程是一个可重用的代码模块,可以高效率地完成指定操作.SQL server中既可以用T-SQL语言写存储过程,也可以用CLR方式写,后者是和微软的.NET框架紧密结合的方式,我们这里用T-SQL语言写存储过程.

存储过程主要有以下三种:

• 用户定义的存储过程: 是主要的存储过程类型,封装了可重用代码模块或例程.
• 扩展存储过程: 指用某种外部语言(如C)创建的外部例程,是可以在SQL server中动态加载和运行的DLL,但是为了推广.NET生态,微软宣称将会使用CLR代替外部语言实现外部例程.
• 系统存储过程: 用来完成和管理SQL server中许多管理活动的特殊存储过程,带有sp_前缀,存在于每个数据库的sys架构中.

创建存储过程的规则

虽然说存储过程中可以包含不限数量和类型的T-SQL语句,但是某些特殊的语句是不能包含在存储过程内的,这类语句如下:

• CREATE AGGREGATE.
• CREATE DEFAULT.
• CREATE FUNCTION.
• CREATE PROCEDURE.
• CREATE RULE.
• CREATE SCHEMA.
• CREATE TRIGGER.
• CREATE VIEW.
• CERATE PARSEONLY.
• SET SHOWPLAN_TEXT.
• SET SHOWPLAN_ALL.
• SHOWPLAN_XML.
• USE db_name.

除了上面列出的CREATE语句之外,其他的数据库对象都可以在存储过程中创建.只要引用时,该对象已经存在即可.也可以在存储过程中引用临时表.如果在存储过程中创建了临时表,那么该临时表仅在存储过程运行时存活,一旦存储过程运行结束,该临时表就自动销毁了.如果存储过程中调用其他存储过程,那么后调用的存储过程可以使用主调存储过程创建的一切资源,包括临时表.存储过程可以带有参数,但是不能超过2100个.

创建存储过程

使用CREATE PROCEDURE可以创建存储过程.必须具有CREATE PROCEDURE权限才可以创建存储过程.存储过程作用对象是架构作用域中的对象,只能在本地数据库创建存储过程.

在创建存储过程时,应指定所有的输入参数、执行数据库操作的语句、返回至调用存储过程或批处理表明成功或失败的状态值、捕捉和处理异常的错误处理语句,CREATE PROCEDURE的基本语法如下:

CREATE PROCEDURE procedure_name
parameter_name data_type, ...
WITH <procedure_option>
AS
sql_statement;
GO

/**
 *	<procedure_option> ::=
 *	[ ENCRYPTION ]  --加密该存储过程的定义文本,类比视图的WITH ENCRYPTION选项;
 *	[ RECOMPILE ]		--每一次执行该存储过程时都重新编译;
 *	[ EXECUTE AS Clause ]  --指定在其中执行过程的安全上下文;
 */

其中<procedure_option>表示存储过程选项,已在注释中指明,具体作用待后续解释.下面我们试着创建一个存储过程.

创建一个存储过程,用于从HumanResources.vEmployeeDepartment视图中检索员工姓名和所属部门:

USE AdventureWorks2017;
GO

CREATE PROCEDURE HumanResources.uspGetEmployeeFullNameAndDepartment
AS
SELECT FirstName+''+MiddleName+''+LastName,Department
FROM HumanResources.vEmployeeDepartment
GO

运行结果如下:

接下来我们再创建一个带参数的存储过程:

USE AdventureWorks2017;
GO

CREATE PROCEDURE HumanResources.uspGetEmployeeInfo
	@FirstName VARCHAR(50),				--以下三列是参数,执行存储过程时要求输入的变量;
	@MiddleName VARCHAR(50),
	@LastName VARCHAR(50)
AS
SELECT FirstName,MiddleName,LastName,JobTitle,Department
FROM HumanResources.vEmployeeDepartment
WHERE FirstName = @FirstName AND MiddleName = @MiddleName AND LastName = @LastName;
GO

运行结果如下:

在使用存储过程中,对于那些作返回值的参数,需要使用OUTPUT关键字明确指定,存储过程的返回值比函数有一个优势->可以同时指定多个返回值!只要每个后面都用OUTPUT指定即可.如下例(只有一个返回值):

USE AdventureWorks2017;
GO

CREATE PROCEDURE dbo.uspComputePlus
	@FirstPara DECIMAL(10,2),
	@SecondPara DECIMAL(10,2),
	@Result DECIMAL(10,2) OUTPUT
AS
SET @Result = @FirstPara + @SecondPara;
GO

以上存储过程计算两个实数的和,创建完成后结果如下:

执行存储过程

创建了存储过程,就可以执行了.在SQL server中,需要使用EXECUTE语句来执行,或者简写成EXEC.下面讨论几种不同的情况.

1. 执行无参的存储过程

对于此类存储过程,直接用EXEC语句执行即可,如:

USE AdventureWorks2017;
GO

EXEC HumanResources.uspGetEmployeeFullNameAndDepartment;
GO

运行结果如下:

2. 执行含参的存储过程

• 直接提供参数: 使用参数对应类型的常量作为参数,不通过变量直接指定,如:

USE AdventureWorks2017;
GO

EXEC HumanResources.uspGetEmployeeInfo 'Syed','E','Abbas';
GO

• • 运行结果如下:

• 间接提供参数: 通过变量为存储过程提供参数,如:

USE AdventureWorks2017;
GO

DECLARE @FirstName VARCHAR(50);
DECLARE @MiddleName VARCHAR(50);
DECLARE @LastName VARCHAR(50);
SET @FirstName = 'Michael';
SET @MiddleName = 'G';
SET @LastName = 'Blythe';

EXEC HumanResources.uspGetEmployeeInfo @FirstName,@MiddleName,@LastName
GO

• • 运行结果如下:

3. 执行带有返回值的存储过程

对于此类存储过程,需要设置临时变量并使用OUTPUT关键字来接收返回值,注意变量的个数和数据类型应和返回值一一对应,如下:

USE AdventureWorks2017;
GO

DECLARE @result DECIMAL(10,2);

EXEC dbo.uspComputePlus 222,666, @result OUTPUT;
PRINT @result;
GO

运行结果如下:

修改/删除存储过程

在SQL server中,可以使用ALTER PROCEDURE来修改存储过程.其目的是为了对存储过程进行修改,而保留原有的权限.其中ALTER PROCEDURE语句和CREATE PROCEDURE能定义的东西一样,如:

ALTER PROCEDURE procedure_name
parameter_name data_type, ...
WITH <procedure_option>
AS
sql_statement;
GO

/**
 *	<procedure_option> ::=
 *	[ ENCRYPTION ]  --加密该存储过程的定义文本,类比视图的WITH ENCRYPTION选项;
 *	[ RECOMPILE ]		--每一次执行该存储过程时都重新编译;
 *	[ EXECUTE AS Clause ]  --指定在其中执行过程的安全上下文;
 */

也可以使用DROP PROCEDURE来删除存储过程,使用删除重建的方式需要重新定义存储过程对数据库对象的访问权限,其语法如下:

DROP PROCEDURE procedure_name;
GO

删除一个存储过程脚本如下:

USE AdventureWorks2017;
GO

DROP PROCEDURE dbo.uspComputePlus;
GO

运行结果如下:

存储过程的执行过程

存储过程创建后,第一次执行需要经过以下四个阶段:

• 语法分析: 创建存储过程时,系统会检查其创建语句的语法正确性.若碰到语法错误,则创建失败.通过语法检查后,系统将存储过程存入sys.sql_modules目录视图中.
• 解析阶段: 存储过程执行前,系统从sys.sql_modules中读取目标存储过程,并检查该存储过程引用的对象是否存在,该阶段又称为延迟名称解析阶段.即在该阶段存储过程引用的对象可以不存在,只要运行时存在即可.注意: 只有被存储过程引用的表可以延迟解析,其他对象必须已经存在(如引用具体的表列).
• 编译阶段: 分析存储过程并生成存储过程执行计划.执行计划是描述存储过程执行最快的方法,查询优化器在分析完存储过程后,将生成的执行计划置于内存中.
• 执行阶段: 从内存中执行存储过程执行计划的过程.

第一次执行后,内存中将会驻留存储过程执行计划,以后的执行就不需要经过前面三步,从而加快执行速度.但是,若存储过程发生了更改,则需要重新创建存储过程执行计划.当存储过程引用的基本表发生结构变化时,存储过程的执行计划会自动优化,但是如果在表中添加索引或者更改了索引中的数据后,该执行计划不会自动优化,此时,应当重新编译存储过程.可以使用三种重编译方式:

• 使用sp_recompile 'object_name’系统存储过程.
• 在CREATE PROCEDURE中使用WITH RECOMPILE子句.
• 在EXECUTE语句中使用WITH RECOMPILE子句.

查看存储过程

可以使用系统存储过程和目录视图来查看有关存储过程的信息,具体如下:

1. 查看存储过程的定义信息

系统存储过程/目录视图名	描述
sys.sql_modules	为每个SQL语言定义的对象模块返回一行,其包括本机编译标量用户定义信息.
OBJECT_DEFINITION(object_id)	为指定的对象id,返回其定义文本信息
sp_helptext ‘object_name’	显示指定对象的用户定义文本

其中OBJECT_DEFINITION(object_id)可以配合OBJECT_ID一起使用(获取对象id),其用法如下:

OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ]   
  object_name' [ ,'object_type' ] )

如使用OBFECT_DEFINITION查看一个AdventureWorks2017自带的存储过程定义文本,如下:

USE AdventureWorks2017;
GO

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.uspLogError'));
GO

运行结果如下:

2. 查看存储过程的依赖信息

系统存储过程名/目录视图名	描述
sys.sql_dependencies	返回被引用实体的依赖关系
sp_denpends ‘object_name’	显示有关数据库对象的依赖关系

3. 查看存储过程名称、参数等其他参数

目录视图名	描述
sys.objects	查看数据库对象的信息
sys.procedures	查看数据库上的存储过程信息
sys.parameters	对象的每个参数在表中对应一行.如果对象是标量函数,则另有一行说明返回值
sys.numbered_procedures	为数据库中所有存储过程编上号,并显示出来

触发器

触发器的概念和类型

触发器被认为是一种特殊的存储过程,它包含大量的T-SQL语句,和存储过程不同的是,它不能被用户主动调用,是在满足条件时系统自动触发的,因此才被称为触发器.按照触发事件的不同,SQL server可以把触发器分为两类: DML触发器和DDL触发器.

• DML触发器: 当数据库中发生数据操纵语言(INSERT、UPDATE、DELETE)事件时,将调用DML触发器.
• DDL触发器: 当数据库中发生数据定义语言(CREATE、ALTER、DROP)事件时,将调用DDL触发器.

除此之外,SQL server还可以创建CLR触发器,CLR触发器既可以是DML触发器也可以是DDL触发器.

DML触发器的类型

由于DML有三类基本操作,因此DML触发器可以分为三类,即INSERT、UPDATE、DELETE类DML触发器,分别表示在INSERT、UPDATE、DELETE动作发生后触发对应的触发器.而这三大类中的触发动作又可以分为两小类,即FOR/AFTER、INSTEAD OF类,其中:

• FOR/AFTER类触发器表示在对于/执行完某种DML操作时,进行某种动作的触发.
• INSTEAD OF类触发器表示不进行该种DML操作,而是以某种操作替代的触发.

总之,就是下表这六种类型(其中FOR和AFTER完全等效,故写在一起):

DML操作分类	触发动作分类	触发器类型
INSERT	FOR/AFTER	FOR/AFTER INSERT
INSERT	INSTEAD OF	INSTEAD OF INSERT
UPDATE	FOR/AFTER	FOR/AFTER UPDATE
UPDATE	INSTEAD OF	INSTEAD OF UPDATE
DELETE	FOR/AFTER	FOR/AFTER DELETE
DELETE	INSTEAD OF	INSTEAD OF DELETE

下一节将会详细介绍上表的这些类型的触发器的工作原理.

DML触发器的工作原理

1. INSERT类DML触发器工作原理

INSERT类触发器的工作原理是: 当检测到触发器所引用的基本表发生了INSERT类操作时,触发器就会工作,具体的过程如下:

• 系统截取INSERT的插入数据,并将其放入基本表和inserted表(inserted是一个逻辑表,用来存储插入数据的备份).
• 触发器检查inserted表,确定触发器动作是否执行以及怎样执行.
• 若触发器动作执行,则将inserted表的经过触发器动作得到的结果进行指定的处理(例如: 插入到日志表中等操作).

---

2. DELETE类触发器工作原理

DELETE类触发器的工作原理是: 当检测到触发器所引用的基本表发生了DELETE类操作时,触发器就会工作,具体的过程如下:

• 系统将DELETE的指定数据,从基本表转移到(此时数据不在基本表)deleted表(deleted是一个逻辑表,用来存储删除数据的备份).
• 触发器检查deleted表,确定触发器动作是否执行以及怎样执行.
• 若触发器动作执行,则将deleteed表的经过触发器动作得到的结果进行指定的处理(例如: 插入到日志表中等操作).

注意DELETE类触发器使用时应考虑的因素:

• deleted表和触发器引用的基本表没有共同的数据.
• deleted表总是放在内存中,这样可以提高性能.
• DELETE触发器中,不能包括TRUNCATE TABLE语句,因为这样的操作不计入日志.

---

3. UPDATE类触发器工作原理

UPDATE操作可以看成是先进行DELETE操作,再进行INSERT操作,因此它的工作原理就不在这里赘述,我们只需要知道一点即可:

不存在一个updated逻辑表,UPDATE操作先检查deleted表,再检查inserted表.

---

4. 关于触发器嵌套,应该注意的两点:

• 默认情况下,触发器不支持迭代调用,即,触发器不能自己调用自己.
• 触发器操作是一个事务,因此在嵌套调用触发器时,若有一个失败,则整个操作都会回滚,失败.
• 建议在触发器中增加输出语句,以便提示用户在执行什么操作时出现了错误.

创建DML触发器

创建DML触发器的CREATE TRIGGER语句基本语法如下:

CREATE TRIGGER trigger_name
ON tb_name_or_view_name
[WITH ENCRYPTION]
{ FOR | AFTER | INSTEAD OF } { [DELETE] [,] [INSERT] [,] [UPDATE] }
AS statement;
GO

其中,WITH ENCRYPTION操作是对触发器定义文本加密,FOR、AFTER、INSTEAD OF三选一指定(FOR和AFTER等效),三个DML操作既可以在触发器中指定一个,也可以指定多个.下面我们来进行一组触发器操作:

Step 1: 建立两个表,一个是账目表AccountData一个是审计表AuditAccountData,这两个表的结构分别如下:

AccountData表的结构

列名	数据类型	是否允许空值	描述
accountID	INT	NOT NULL	账户的流水号,主键,默认值IDENTITY
operateType	CHAR(16)	NOT NULL	业务操作的类型
operateAmount	MONEY	NOT NULL	业务操作金额

AuditAccountData表的结构

列名	数据类型	是否允许空值	默认值	描述
logID	UNIQUEIDENTIFIER	NOT NULL	NEWID	日志编号,主键
loginName	VARCHAR(128)	NOT NULL	SYSTEM USER	登录账户名
loginUsername	VARCHAR(128)	NOT NULL	CURRENT USER	数据库用户名
operateType	CHAR(16)	NOT NULL		业务操作类型
operateAmount	MONEY	NOT NULL		操作金额
operateTime	DATETIME	NOT NULL	GETDATE	操作时间

创建这两个表,如下:

USE ElecTravelCom;
GO

CREATE TABLE AccountData (
	accountID INT NOT NULL IDENTITY PRIMARY KEY,
	operateType CHAR(16) NOT NULL,
	operateAmount MONEY NOT NULL
)

CREATE TABLE AuditAccountData (
	logID UNIQUEIDENTIFIER NOT NULL DEFAULT  NEWID() PRIMARY KEY,
	loginName VARCHAR(128) NOT NULL DEFAULT SYSTEM_USER,
	loginUsername VARCHAR(128) NOT NULL DEFAULT CURRENT_USER,
	operateType CHAR(16) NOT NULL,
	operateAmount MONEY NOT NULL,
	operateTime DATETIME NOT NULL DEFAULT GETDATE()
)
GO

Step 2: 分析应处理的问题,设计触发器

既然是审计账户的流水信息,那么只有在财务人员进行操作后才能触发,因此确定触发器的动作为AFTER,财务人员操作的方式有三种,显然就是INSERT、UPDATE、DELETE.所以可以分别为该表创建三个类型的触发器.根据审计表的列,我们显然要对账户流水记录一个日志,该日志的内容包括: 操作数据库的登录名和用户名,操作类型,操作金额,操作时间.再参照账目表,就好设计触发器了.

Step 3: 实现触发器

为了防止触发器定义信息被无关用户查看,我们对其进行定义文本加密:

USE ElecTravelCom;
GO

CREATE TRIGGER dmlAccountDataInsert
ON dbo.AccountData
WITH ENCRYPTION
FOR INSERT
AS
DECLARE @insertedAmount MONEY
DECLARE @operateType CHAR(16)
SELECT @insertedAmount = operateAmount,@operateType = operateType
FROM inserted
INSERT INTO dbo.AuditAccountData(operateType,operateAmount)	--将存入的数额和存入操作插入到审计表;
VALUES (@operateType,@insertedAmount);
GO

CREATE TRIGGER dmlAccountDataDelete
ON dbo.AccountData
WITH ENCRYPTION
FOR DELETE
AS
DECLARE @deletedAmount MONEY
SELECT @deletedAmount = operateAmount
FROM deleted
INSERT INTO dbo.AuditAccountData(operateType,operateAmount)	--将删除的金额插入到审计表;
VALUES ('Delete',@deletedAmount);
GO

只建立了INSERT和DELETE触发器,读者可自建UPDATE触发器,运行结果如下:

接下来我们做一组插入操作,再看看审计表AuditAccountData的变化:

USE ElecTravelCom;
GO

INSERT INTO dbo.AccountData VALUES ('存款',2800);
INSERT INTO dbo.AccountData VALUES ('支票',399);
INSERT INTO dbo.AccountData VALUES ('存款',3500);
INSERT INTO dbo.AccountData VALUES ('支票',8848);
INSERT INTO dbo.AccountData VALUES ('支票',1679.55);
INSERT INTO dbo.AccountData VALUES ('存款',213000);
GO

SELECT * FROM dbo.AccountData;
SELECT * FROM dbo.AuditAccountData;
GO

运行结果如下:

可见审计表插入了6条数据,明确记录了账户流水的明细日志,此时我们再删除AccountData的一些数据,再看看审计表的情况:

USE ElecTravelCom;
GO

DELETE FROM dbo.AccountData WHERE accountID = 1;
DELETE FROM dbo.AccountData WHERE accountID = 2;
DELETE FROM dbo.AccountData WHERE accountID = 3;
DELETE FROM dbo.AccountData WHERE accountID = 4;
GO

SELECT * FROM dbo.AccountData;
SELECT * FROM dbo.AuditAccountData;
GO

运行结果如下:

可见审计表AuditAccountData中又多了4条记录,显然是把DELETE操作记录在内了.

通过以上的例子,我们知道,使用触发器实现一些自动化管理的任务是非常方便的,尤其是在日志记录方面.

---

虽然DML触发器中可以有非常多的T-SQL语句,但是还是有一些语句不允许出现在其中,如:

• CREATE DATABASE、ALTER DATABASE、DROP DATABASE、RESTORE DATABASE、RESTORE LOG等.
• 不允许对基本表进行修改和删除操作.
• CREATE INDEX、ALTER INDEX、DROP INDEX等语句.
• RECONFIGUE语句.

DDL触发器

DDL触发器和DML触发器有很多相似之处.但是它的触发条件是CREATE、ALTER、DROP、GRANT、DENY、REVOKE等语句.并且触发条件只有AFTER/FOR,没有INSTEAD OF.一般地,DDL触发器主要用于一下这些操作:

• 防止对数据库架构进行更改.
• 希望数据库中发生某种情况以便实现相应数据库架构中的更改.
• 记录数据库架构中的更改或事件.

DDL触发器的创建基本语法如下:

CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[WITH ENCRYPTION]
{ FOR | AFTER } {event_type}
AS statement;
GO

其中ALL SERVER表示触发器作用域是整个服务器,DATABASE表示触发器作用域是整个数据库,event_type指DDL操作,分为服务器级事件和数据库级事件,统称为DDL事件,请参照这里,DDL触发器的行为还可以是DDL事件组,请参照这里.

下面举一个DDL触发器应用的例子:

USE ElecTravelCom;
GO

CREATE TRIGGER ddlSecureProtection
ON DATABASE
WITH ENCRYPTION
FOR DROP_TABLE,ALTER_TABLE
AS
PRINT N'禁止删除/修改当前数据库的表';
ROLLBACK
GO

此时再尝试删除ElecTravelCom数据库里的表:

USE ElecTravelCom;
GO

DROP TABLE dbo.books;
GO

运行结果如下:

可见DDL触发器起了作用.

---

通过以上的例子,我们知道:

• DDL触发器的作用范围远大于DML触发器,是立足于整个数据库乃至服务器层面进行操作的.
• DML触发器在记录日志时非常快捷有效,平常用户使用最多的就是DML触发器.

查看/删除触发器

创建触发器时,有关触发器的信息就存在sys.triggers对象目录视图、sys.trigger_events对象目录视图以及sys.sql_modules目录视图里.使用sp_helptext可以查看触发器的定义信息,如下示例:

USE AdventureWorks2017;
GO

SELECT * FROM sys.triggers;
GO

EXEC sp_helptext 'AdventureWorks2017.HumanResources.dEmployee';
GO

运行结果如下:

删除触发器很简单,只需一行脚本:

DROP TRIGGER [ IF EXISTS ] [schema_name.]trigger_name [ ,...n ]

删除DDL触发器更复杂一些,如:

DROP TRIGGER [ IF EXISTS ] trigger_name [ ,...n ]   
ON { DATABASE | ALL SERVER }

如下演示删除DDL触发器:

USE ElecTravelCom;
DROP TRIGGER ddlSecureProtection
ON DATABASE;
GO

运行结果如下:

函数

函数又分为系统函数和用户自定义函数,在这一节我们把用户自定义函数简称为函数,来说说如何使用用户自定义函数.

函数的结构和特点

函数可以带来许多优势,如模块化编程,创建一次调用多次等.函数可以独立与应用程序源码进行修改,执行速度更快(原理同存储过程),减少网络流量,基于某种无法用单一标量表达式表示的复杂约束来过滤数据的操作,可以表示为函数.然后,该函数可以在WHERE子句中调用,以减少发送至客户端的数字或行数.

在SQL server中,函数由两部分组成: 标题和正文.

• 标题可以定义以下内容:

-> 具有可选架构/所有者名称的函数名称.

-> 输入参数名称和数据类型.

-> 可用于输入参数的选项.

-> 返回参数数据类型和可选名称.

-> 可用于返回参数的选项.

• 正文定义了函数要执行的操作,这些操作可以是若干个T-SQL语句,也可以是.NET程序合集.

SQL server中,函数又可以分为两类: 用户定义标量函数和用户定义表值函数.标量函数在RETURN语句中定义单个返回值.表值函数在BEGIN…END块内定义若干个返回值,这些值构成一个表.无论是标量函数还是表值函数,返回值类型可以是除TEXT、NTEXT、IMAGE、CURSOR、TIMESTAMP以外的任意类型.

---

创建函数时,应考虑如下因素:

• 每个完全限定用户的函数名称(schema_name.function_name),必须唯一.
• 函数的BEGIN…END语句不能有任何副作用,即不能对函数作用范围外的资源状态进行任何永久性更改,仅可更改局部变量.
• 函数内不能进行的操作: 修改数据库表、修改不在函数上的局部游标、发送电子邮件、修改目录、生成返回至用户的结果集.

在函数中,可以包括的语句类型如下:

• DECLARE语句,用于声明局部数据变量/游标.
• SET语句,用于为标量和表局部变量赋值.
• 游标操作,该操作引用在函数中声明、打开、关闭和释放的局部游标,不允许使用FETCH将数据返回到客户端,仅允许使用FETCH…INTO给局部变量赋值.
• 除了TRY…CATCH之外的控制流语句.
• SELECT语句,该语句包含具有为函数局部变量赋值的表达时选择列表.
• INSERT、UPDATE、DELETE语句,可以修改函数的局部变量.
• EXECUTE语句,该语句调用存储过程.

SQL server中,确定性内置函数可以在函数中使用,大多数不确定性内置函数也可以在函数中使用,如: GETDATE、CURRENT_TIMESTAMP、@@MAX_CONNECTIONS等,但是如NEWID则不可以在函数中使用.

使用函数

使用CREATE FUNCTION可以创建函数,其中可以创建标量函数、内联表值函数、多语句表值函数,下面就针对这些情况一一介绍.

创建标量函数

使用CREATE FUNCTION创建标量函数语法如下:

CREATE FUNCTION func_name (@parameter_name_list)
RETURNS return_data_type
AS
BEGIN
	function_body
	RETURN scalar_expression
END

可见定义函数时先使用RETURNS关键字指定返回值类型,在函数体中再是使用了RETURNS正式定义了返回表达式,类型应要匹配.

创建一个标量函数,功能是将指定的日期换算成标准的周序号,如下:

USE ElecTravelCom;
GO

CREATE FUNCTION dbo.ISOWeek (@DATE DATETIME)
RETURNS INT
AS
BEGIN
	DECLARE @ISOWeek INT
	SET @ISOWeek = DATEPART(WK,@DATE)+1-DATEPART(WK,CAST(DATEPART(YY,@DATE) AS CHAR(4))+'0104');
	-- 特殊情况：1月份的1-3日可能属于前一年;
	IF(@ISOWeek = 0)
		SET @ISOWeek = dbo.ISOWeek(CAST(DATEPART(YY,@DATE)-1
			AS CHAR(4))+'12'+CAST(24+DATEPART(DAY,@DATE) AS char(2)))+1
	-- 特殊情况：12月份的29-31日可能属于下一年;
	IF(DATEPART(MM,@DATE) = 12 ) AND ((DATEPART(DD,@DATE)-DATEPART(DW,@DATE) >= 28))
		SET @ISOWeek = 1
	RETURN (@ISOWeek)
END
GO

测试该标量函数,如下:

USE ElecTravelCom;
GO

SELECT dbo.ISOWeek(CONVERT(DATETIME,'08/08/2008',101)) AS N'标准周序号';
GO

运行结果如下:

创建内联表值函数

使用CREATE FUNCTION创建内联表值函数语法如下:

CREATE FUNCTION func_name (@parameter_name_list)
RETURNS TABLE
AS
RETURN (SELECT statement);

上面的函数体只有一个RETURN语句,其内容是一个SELECT语句的结果集,也就是一个表.内联表值函数往往用作视图,但比视图灵活,因为该函数可以在WHERE子句中使用变量.

下面创建一个内联表值函数,可以返回指定商店销售产品代号、产品名称和年销售总额,如下:

CREATE FUNCTION Sales.func_SalesByStore (@storeID INT)
RETURNS TABLE
AS
RETURN (
	SELECT P.ProductID,P.Name,SUM(SD.LineTotal) AS N'年销售总额'
	FROM Production.Product AS P 
		JOIN Sales.SalesOrderDetail AS SD
			ON SD.ProductID = P.ProductID
		JOIN Sales.SalesOrderHeader AS SH
			ON SH.SalesOrderID = SD.SalesOrderDetailID
	WHERE SH.CustomerID = @storeID
	GROUP BY P.ProductID,P.Name
)
GO

使用内联表值函数:

USE AdventureWorks2017;
GO

SELECT * FROM Sales.func_SalesByStore(30052);
GO

运行结果如下:

创建多语句表值函数

使用CREATE FUNCTION创建多语句表值函数语法如下:

CREATE FUNCTION func_name (@parameter_name_list)
RETURNS @return_variable TABLE(tb_type_definition)
AS
BEGIN
	function_body
	RETURN
END

多语句表值函数和内联表值函数创建语法有4处不同:

• 多语句表值函数创建语句中,RETURNS后面可以接收将要返回的结果集的标的定义,内联表值函数后只有一个TABLE关键字.
• 多语句表值函数使用BEGIN…END模块,内联表值函数只有一个RETURN语句.
• 多语句表值函数有函数体,内联表值函数只有一个RETURN语句.
• 多语句表值函数的RETURN语句是空的,而内联表值函数RETURN语句返回一个SELECT结果集.

下面创建一个多语句表值函数,用于获取指定员工代号的工作汇报路径清单:

USE AdventureWorks2017;
GO

CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)  
RETURNS @retFindReports TABLE (  
    EmployeeID int primary key NOT NULL,  
    FirstName nvarchar(255) NOT NULL,  
    LastName nvarchar(255) NOT NULL,  
    JobTitle nvarchar(50) NOT NULL,  
    RecursionLevel int NOT NULL  
)  
--Returns a result set that lists all the employees who report to the   
--specific employee directly or indirectly.
AS  
BEGIN  
	WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns  
		AS (  
			-- Get the initial list of Employees for Manager n
			SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0   
			FROM HumanResources.Employee e INNER JOIN Person.Person p   
				ON p.BusinessEntityID = e.BusinessEntityID  
			WHERE e.BusinessEntityID = @InEmpID  
			UNION ALL  
			-- Join recursive member to anchor
			SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1   
			FROM HumanResources.Employee e   
				INNER JOIN EMP_cte  
				ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode  
				INNER JOIN Person.Person p   
				ON p.BusinessEntityID = e.BusinessEntityID  
        )  
	-- copy the required columns to the result of the function   
	INSERT @retFindReports  
	SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel  
	FROM EMP_cte   
	RETURN  
END;  
GO  

使用多语句表值函数,如下:

USE AdventureWorks2017;
GO
 
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel  
FROM dbo.ufn_FindReports(10);
GO

运行结果如下:

修改/删除函数

使用ALTER FUNCTION来修改函数定义,这种修改不影响函数的权限.修改和创建能使用的语句是一致的,相当于保留权限重写函数.但是要注意,不能将标量函数修改为表值函数,也不能将内联表值函数修改为多语句表值函数,反之亦然.

如果某个函数不需要了,可以通过DROP FUNCTION function_name删除该函数.

查看函数

以下是一些查看函数的系统存储过程和目录视图,大部分都已经介绍过了,如下:

• sys.sql_modules: 查看sql的模块化对象信息(存储过程/触发器/函数等).
• OBJECT_DEFINITION: 查看对象的定义信息.
• sp_helptext: 查看对象的详细信息.
• sys.objects: 系统对象视图.
• sys.parameter: 系统参数视图.
• sp_help: 查看系统的各种对象.
• sys.sql_dependencies、sp_depends: 查看对象的依赖关系.

上一篇: 视图
下一篇: 备份和还原