当前位置：首页 > news >正文

存储过程、用户定义函数、触发器和游标

news 2026/8/10 11:45:53

创建、执行和删除存储过程

存储过程是调用执行的、存储在服务器端的代码段。
利用存储过程可以提高数据操作性能以及数据的安全性，可以进行模块化程序设计。

创建存储过程

创建存储过程的 SQL 语句为 CREATE PROCEDURE。其语法格式为：

CREATE|PROC|PROCEDURE|[schema_name.]procedure_name[[@parameter[type_schema_name.]data_type][=default][OUT|OUTPUT]][,...n][WITHRECOMPILE]AS|<sql_statement>[;][,...n]|[;]<sql_stalement>::=|[BEGIN]statements[END]|

一个存储过程最多可以有2100个参数，所有数据类型都可以用作存储过程的参数。

执行存储过程

执行存储过程可以使用 T-SQL 的 EXECUTE 语句。如果存储过程是批处理中的第一条语句，那么不使用 EXECUTE 关键字也可以执行存储过程。
EXECUTE 语句的语法格式为：

[{EXEC|EXECUTE}]{[@return_status=]{proc_name}[[@parameter_name=]{value|@variable[OUTPUT]|[DEFAULT]}][,...n][WITHRECOMPILE]}[;]

执行有多个输入参数的存储过程时，参数的传递方式有两种：

按参数位置传递值。
按参数位置传递值指执行存储过程的 EXEC 语句中的实参的排列顺序必须与定义存储过程时定义的参数的顺序一致。
按参数名传递值。
按参数名传递值指的是执行存储过程的 EXEC 语句中要指明定义存储过程时指定的参数的名字以及此参数的值，而不关心参数的定义顺序。

SQL Server支持按位置传参和按名字传参两种方式，但不能在同一条语句中混合使用。
如果在定义存储过程时为参数指定了默认值，则在执行存储过程时可以不为有默认值的参数提供值。

例：

Ⅱ中的参数赋值采用按参数位置传值，必须从左到右赋值，即不能跳过左边的某个默认参数而传递某个值。

删除存储过程

删除存储过程使用DROP PROCEDURE语句实现，该语句可从当期数据库中删除一个或多个存储过程。其语法格式为：DROP {PROC | PROCEDURE} {[schema_name.]procedure [,...n]

用户定义函数

用户定义函数与编程语言中的函数类似，其结构与存储过程类似，但函数必须有一个 RETURN 子句，用于返回函数值。函数说明要指定函数名、结果值的类型、以及参数类型等。 SQL Server 2008 支持两类用户定义函数：标量函数和表值函数，标量函数只返回单个数据值，表值函数将返回一个表。表值函数又分为内联表值函数和语句表值函数。

创建和调用标量函数

标量函数是返回单个数据值的函数。

定义标量函数

定义标量函数的语法格式为：

CREATEFUNCTION[schema_name.]function_name([{@parameter_name[AS][type_schema_name.]parameter_data_type[=default]}[,...n]])RETURNSreturn_data_type[AS]BRGIN function_bodyRETURNscalar_expressionEND[;]

调用标量函数

当调用标量函数时，必须提供至少由两部分组成的名称：函数拥有者名和函数名。可在任何允许出现表达式的 SQL 语句中调用标量函数，只要类型一致。

创建和调用内联表值函数

内联表值函數的返回值是一个表，该表的内容是一个在查询语句的结果。

创建內联表值函数

定义内联表值函数的语法为：

CREATEFUNCTION[schema_name.]function_name([{@parameter_name[AS][type_schema_name.]parameter_data_type[=default]}[,...n]])RETURNSTABLE[AS]RETURN[(]select_stmt[)][;]

其中，select_stmt 是定义内联表值函数返回值的单个 SELECT 语句。其他各参数含义同标量函数。
在内联表值函数中，通过单个 SELECT 语句定义 TABLE 返回值。内联表值函数没有相关联返回变量。也没有函数体。

调用内联表值函数

对内联表值函数的使用与视图非常类似，需要放置在查询语句的 FROM 子句部分，它的作用很像是带参数的视图。

创建和调用多语句表值函数

多语句表值函数的功能是视图和存储过程的组合，可以利用多语句表值函数返回一个表，表中的内容可由复杂的逻辑和多条 SQL语句构建（类似于存储过程）。可以在 SELECT 语句的 FROM 子句中使用多语句表值函数（同视图）。

创建多语句表值函数

定义多语句表值函数的语法：

CREATEFUNCTION[schema_name.]function_name([{@parameter_name[AS][type_schema_name.]parameter_data_type[=default]}[,...n]]}RETURNS@return_variableTABLE<table_type_definition>[AS]BEGINfunction_bodyRETURNEND[;]<table_type_definition>::=({<column_definition><column_constraint>|<computed_column_definition>}[<table_constraint>][,...n])

调用多语句表值函数

多语句表值函数的返回值是一个表，因此对多语句表值函数的使用也是放在 SELECT 语句的 FROM 子句部分。

删除用户自定义函数

删除函数使用 DROP FUNCTION 语句实现，它从当前数据库中删除一个或多个用户定义函数。其语法格式为：DROP FUNCTION {[schema_name.]function_name} [,...n ]

触发器

触发器是一种特殊的存储过程，其特殊性在于它不需要由用户来直接调用，而是在对表中做据进行 UPDATE INSERT 或 DELETE 操作时自动触发执行的。触发器通常用于保证业务规则和数据完整性。其主要优点是用户可以用编程的方法来实现复杂的处理逻辑和商业规则，增强了数据完整性约束的功能。

SQL Server 2008 支持三种类型的触发器：DML、DDL 和登录触发器。如果用户要通过数据操作语言 (DML) 事件编辑数据，则执行 DML 触发器。 DML 事件是针对表或视图的INSERT、UPDATE或 DELETE 语句。DDL 触发器用于响应各种数据定义语言（DDL）事件，这些事件主要对应 T-SQL 中的CREATE、ALTER 和 DROP 语句，以及执行类似 DDL 操作的某些系统存储过程。登录触发器在遇到 LOGON 事件时触发。LOGON 事件是在建立用户会话时引发的。这里只介绍最常用的 DML 触发器。

创建触发器

建立 DML 触发器的 SQL 语句为 CREATE TRIGGER，其语法格式为：

CREATETRIGGER[schema_name.]trigger_nameON{table|view} {FOR|AFTER|INSTEADOF} {[INSERT][,][UPDATE][,][DELETE]}AS{sql_statement}[;]

不能在视图上定义AFTER触发器。
在一个表上可以建立多个名称不同、类型各异的触发器，每个触发器可由所有 3 个操作（INSERT、UPDATE 或 DELETE）来引发。对于 AFTER 型的触发器，可以在同一种操作上建立多个触发器；对于 INSTEAD OF 型的触发器，在同一种操作上只能建立一个触发器。
在触发器语句中可以使用两个特殊的临时工作表：INSERTED 表和 DELETED 表。这两个表是在用户执行数据的更改操作时，SQL Server 自动创建和管理的，这两个表驻留在内存中。
DELETED 表用于存储 DELETE 和 UPDATE 语句所影响的行的副本。在执行 DELETE 时，被删除的数据被保存到 DELETED 表中。在执行 UPDATE 操作时，对被修改操作影响的所有数据行，将更改前的数据（按行进行）保存到 DELETED 表中。
INSERTED 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在执行 INSERT 操作时。新插入的数据同时被保存到 INSERTED 表中。在执行 UPDATE 操作时，对被修改操作影响的所有数据行，将更改后的数据（按行进行）保存到 INSERTED 表中。
触发器可以实现不同表中的列之间的相互取值约束。

创建后触发型触发器

使用 FOR 或 AFTER 选项定义的触发器为后触发型触发器，即只有在引发触发器执行的语句中的操作都已成功执行，并且所有的约束检查也成功完成后，才执行触发器。

创建前触发型触发器

使用 INSTEAD OF 选项定义的触发器为前触发型触发器。在这种模式的触发器中，指定执行触发器而不是执行引发触发器执行的 SQL 语句，从而替代引发语句的操作。
对于定义了前触发型触发器的操作，系统并不执行引发触发器执行的数据操作语句。因此，如果数据操作满足完整性约束的要求，则在触发器中必须重新执行这些数据操作语句。
在表或视图上，每个 INSERT、UPDATE 或 DELETE 语句最多可以定义一个 INSTEAD OF 触发器。

对于前触发器，在一个表上针对同一个数据操作（INSERT、UPDATE或 DELETE）只能定义一个前触发器；
对于后触发器，可以在同一种操作上建立多个触发器。

删除触发器

删除触发器使用DROP TRIGGER语句实现，它从当期数据库中删除一个或多个触发器。其语法格式为：DROP TRIGGER schema_name.trigger_name [,...n][;]

游标

关系数据库中的操作是基于集合的操作，即对整个行集产生影响，由 SELECT 语句返回的行集包括所有满足条件子句的行，这一完整的行集被称为结果集。一般在使用 SELECT 语句进行查询时，就可以得到这个结果集，但有时用户需要对结果集中的每一行或部分行进行单独的处理，这在 SELECT 的结果集中是无法实现的。游标就是提供这种机制的结果集扩展，它使人们可以逐行处理结果集。

游标的组成

游标（Cursor) 包括两部分内容：

游标结果集：指定义游标的 SELECT 语句返回的结果的集合。
游标当前行指针：指向该结果集中的某一行的指针。

游标具有如下特点：

允许定位结果集中的特定行。
允许从结果集的当前位置检索一行或多行。
支持对结果集中当前行的数据进行修改。
为由其他用户对显示在结果集中的数据所做的更改提供不同级别的可见性支持。

使用游标

声明游标

声明游标实际是定义服务器端游标的特性，例如游标的滚动行为和用于生成游标结果集的查询语句。SQL Server支持两种格式的声明游标语句：一种是基于 ISO 标准的语法，另一种是使用 T-SQL 扩展的语法。这里只介绍 ISO 标准语法的声明游标语句。
ISO 声明游标的简化语法格式如下：

DECLAREcursor_name[INSENSITIVE][SCROLL]CURSORFORselect_statement[FOR{READONLY|UPDATE[OFcolumn_name[,...n]]}]

如果在声明游标时未指定 INSENSITIVE 选项，则已提交的（任何用户）对基本表的删除和更新都会反映在后面的提取操作中。
每个游标都有一个当前行指针，当游标打开后，当前行指针自动指向结果集的第一行数据。

打开游标

打开游标的语句是 OPEN，其语法格式为：OPEN cursor_name，其中 cursor_name 为游标名。

提取数据

游标被声明和打开之后，游标的当前行指针就位于结果集中的第一行位置，可以使用 FETCH 语句从游标结果集中按行提取数据。其语法格式如下：

FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE nFROM]cursor_name[INTO@variable_name[,...n]]

NEXT：返回紧跟在当前行之后的数据行，并且当前行递增为结果行。如果 FETCH NEXT 是对游标的第一次提取操作，则返回结果集中的第一行。NEXT 为默认选项。
PRIOR：返回紧临当前行前面的数据行，并且当前行递减为结果行。如果 FETCH PRIOR 为对游标的第一次提取操作，则不返回任何结果并将游标当前行置于第一行之前。
FIRST：返回游标中的第一行并将其作为当前行。
LAST：返回游标中的最后一行并将其作为当前行。

在对游标数据进行提取的过程中，可以使用@@FETCH_STATUS 全局变量判断数据提取的状态。 @@FETCH_STATUS 返回 FETCH 语句执行后的游标最终状态。
@@FETCH_STATUS的数值和含义如表：

返回值
0	FETCH语句成功
-1	FETCH语句失败或此行不在结果集中
-2	提取的行不存在

@@ FETCH_STATUS 对于在一个连接上的所有游标是全局性的，不管是对哪个游标，只要执行一次 FETCH 语句，系统都会对@@FETCH_STATUS 赋一次值，以表明该 FETCH 语句的情況。因此，在每次执行完一条 FETCH 语句后，都应该测试一下@@FETCH_STATUS 全局变量的值，以观测当前提取游标数据语句的执行情况。
在对游标进行提取操作前，@@FETCH_STATUS的值没有定义。