SQL Server 中的功能与存储过程

我学习函数和存储过程已经有一段时间了,但是我不知道为什么以及何时应该使用函数或存储过程。在我看来,它们看起来一样,也许是因为我对此有些新手。

有人可以告诉我为什么吗?

答案

函数是计算值,不能对 SQL Server 进行永久的环境更改(即,不允许 INSERT 或 UPDATE 语句)。

如果函数返回标量值,则可以在 SQL 语句中内联使用;如果返回结果集,则可以将其联接。

注释中值得指出的一点,这些注释总结了答案。感谢 @Sean K Anderson:

函数遵循计算机科学的定义,因为它们必须返回一个值,并且不能更改作为参数(自变量)接收的数据。函数不允许进行任何更改,必须具有至少一个参数,并且它们必须返回一个值。存储的 proc 不必具有参数,可以更改数据库对象,也不必返回值。

SP 和 UDF 之间的区别如下:

+---------------------------------+----------------------------------------+
| Stored Procedure (SP)           | Function (UDF - User Defined           |
|                                 | Function)                              |
+---------------------------------+----------------------------------------+
| SP can return zero , single or  | Function must return a single value    |
| multiple values.                | (which may be a scalar or a table).    |
+---------------------------------+----------------------------------------+
| We can use transaction in SP.   | We can't use transaction in UDF.       |
+---------------------------------+----------------------------------------+
| SP can have input/output        | Only input parameter.                  |
| parameter.                      |                                        |
+---------------------------------+----------------------------------------+
| We can call function from SP.   | We can't call SP from function.        |
+---------------------------------+----------------------------------------+
| We can't use SP in SELECT/      | We can use UDF in SELECT/ WHERE/       |
| WHERE/ HAVING statement.        | HAVING statement.                      |
+---------------------------------+----------------------------------------+
| We can use exception handling   | We can't use Try-Catch block in UDF.   |
| using Try-Catch block in SP.    |                                        |
+---------------------------------+----------------------------------------+

函数和存储过程用于不同的目的。尽管这不是最好的类比,但可以将函数按字面意义看成您在任何编程语言中都会使用的任何其他函数,但是存储的 procs 更像是单个程序或批处理脚本。

函数通常具有输出和可选的输入。然后可以将输出用作另一个函数(SQL Server 内置的 DATEDIFF,LEN 等)的输入,或用作 SQL 查询的谓词 - 例如SELECT a, b, dbo.MyFunction(c) FROM tableSELECT a, b, c FROM table WHERE a = dbo.MyFunc(c)

存储的 proc 用于在事务中将 SQL 查询绑定在一起,并与外界交互。诸如 ADO.NET 等框架无法直接调用函数,但是它们可以直接调用存储的 proc。

但是,函数的确存在隐患:它们可能会被滥用并导致相当讨厌的性能问题:请考虑以下查询:

SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2)

MyFunction 声明为:

CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGER
AS
BEGIN
   DECLARE @retval INTEGER

   SELECT localValue 
      FROM dbo.localToNationalMapTable
      WHERE nationalValue = @someValue

   RETURN @retval
END

此处发生的是,对表 MyTable 中的每一行都调用了函数 MyFunction。如果 MyTable 有 1000 行,那么这是针对数据库的另外 1000 个临时查询。同样,如果在列规范中指定时调用了该函数,则将为 SELECT 返回的每一行调用该函数。

因此,您确实需要谨慎编写函数。如果从函数中的表中执行 SELECT,则需要问自己:使用父存储过程中的 JOIN 或其他一些 SQL 构造(例如 CASE ... WHEN ... ELSE ... 结束)。

存储过程和用户定义函数之间的区别:

  • 存储过程不能在 Select 语句中使用。
  • 存储过程支持延迟名称解析。
  • 存储过程通常用于执行业务逻辑。
  • 存储过程可以返回任何数据类型。
  • 与用户定义的函数相比,存储过程可以接受更多数量的输入参数。存储过程最多可具有 21,000 个输入参数。
  • 存储过程可以执行动态 SQL。
  • 存储过程支持错误处理。
  • 在存储过程中可以使用非确定性函数。

  • 用户定义的函数可以在 Select 语句中使用。
  • 用户定义的函数不支持 “延迟名称解析”。
  • 用户定义的函数通常用于计算。
  • 用户定义的函数应返回一个值。
  • 用户定义的函数无法返回图像。
  • 与存储过程相比,用户定义函数接受的输入参数数量更少。 UDF 最多可以具有 1,023 个输入参数。
  • 临时表不能在用户定义的函数中使用。
  • 用户定义的函数无法执行 Dynamic SQL。
  • 用户定义的函数不支持错误处理。 UDF 中不允许出现RAISEERROR@@ERROR
  • 非确定性函数不能在 UDF 中使用。例如,不能在 UDF 中使用GETDATE()

当您要计算并返回一个值以供其他 SQL 语句使用时,编写一个用户定义的函数;当您想要对一组可能复杂的 SQL 语句进行分组时,编写一个存储过程。毕竟,这是两个截然不同的用例!

STORE PROCEDURE                 FUNCTION (USER DEFINED FUNCTION)    
 * Procedure can return 0, single or   | * Function can return only single value   
   multiple values.                    |
                                       |
 * Procedure can have input, output    | * Function  can have only input 
   parameters.                         |   parameters.         
                                       |
 * Procedure cannot be called from     | * Functions can be called from 
   function.                           |   procedure.
                                       |
 * Procedure allows select as well as  | * Function allows only select statement 
   DML statement in it.                |   in it.
                                       |
 * Exception can be handled by         | * Try-catch block cannot be used in a 
   try-catch block in a procedure.     |   function.
                                       |
 * We can go for transaction management| * We can't go for transaction 
   in procedure.                       |   management in function.
                                       |
 * Procedure cannot be utilized in a   | * Function can be embedded in a select 
   select statement                    |   statement.
                                       |
 * Procedure can affect the state      | * Function can not affect the state 
   of database means it can perform    |   of database means it can not    
   CRUD operation on database.         |   perform CRUD operation on 
                                       |   database. 
                                       |
 * Procedure can use temporary tables. | * Function can not use 
                                       |   temporary tables. 
                                       |
 * Procedure can alter the server      | * Function can not alter the  
   environment parameters.             |   environment parameters.
                                       |   
 * Procedure can use when we want      | * Function can use when we want
   instead is to group a possibly-     |   to compute and return a value
   complex set of SQL statements.      |   for use in other SQL 
                                       |   statements.

基本差异

函数必须返回一个值,但是在存储过程中它是可选的(过程可以返回零个或 n 个值)。

函数只能具有输入参数,而过程可以具有输入 / 输出参数。

函数采用一个输入参数,这是强制性的,但存储过程可能采用 o 到 n 个输入参数。

可以从过程中调用函数,而不能从函数中调用过程。

提前差异

过程允许 SELECT 以及 DML(INSERT / UPDATE / DELETE)语句,而 Function 则仅允许 SELECT 语句。

不能在 SELECT 语句中使用过程,而可以在 SELECT 语句中嵌入函数。

在 WHERE / HAVING / SELECT 部分的任何地方都不能在 SQL 语句中使用存储过程,而可以使用 Function。

返回表的函数可以视为另一个行集。可以在与其他表的 JOIN 中使用。

内联函数可以作为带有参数的视图,并且可以在 JOIN 和其他行集操作中使用。

异常可以由 Procedure 中的 try-catch 块处理,而 try-catch 块不能在 Function 中使用。

我们可以在过程中进行事务管理,而不能在功能中进行事务管理。

资源

用户定义函数是 SQL Server 程序员可用的重要工具。您可以像这样在 SQL 语句中内联使用它

SELECT a, lookupValue(b), c FROM customers

其中lookupValue将是 UDF。使用存储过程时,这种功能是不可能的。同时,您不能在 UDF 中执行某些操作。这里要记住的基本内容是 UDF:

  • 无法创建永久更改
  • 无法更改数据

存储过程可以完成那些事情。

对我来说,UDF 的内联用法是 UDF 的最重要用法。

存储过程 用作脚本 。它们为您运行一系列命令,您可以安排它们在特定时间运行。

函数 用作方法。您将其传递给它,它将返回结果。应该小而快速 - 即时进行。

存储过程:

  • 就像 SQL Server 中的微型程序。
  • 可以像选择语句一样简单,也可以像从数据库中多个表中添加,删除,更新和 / 或读取数据的长脚本一样复杂。
  • (可以实现循环和游标,它们都允许您使用较小的结果或对数据进行逐行操作。)
  • 应该使用EXECEXECUTE语句调用。
  • 返回表变量,但是我们不能使用OUT参数。
  • 支持交易。

功能:

  • 不能用于更新,删除或向数据库添加记录。
  • 只需返回一个值或一个表值。
  • 只能用于选择记录。但是,可以从标准 SQL 中很容易地调用它,例如:

    SELECT dbo.functionname('Parameter1')

    要么

    SELECT Name, dbo.Functionname('Parameter1') FROM sysObjects
  • 对于简单的可重用选择操作,函数可以简化代码。只是要小心在函数中使用JOIN子句。如果您的函数具有JOIN子句,并且您从另一个返回多个结果的 select 语句调用它,则该函数调用将针对结果集中返回的每一行将这些表JOIN在一起。因此,尽管它们可以帮助简化某些逻辑,但是如果使用不当,它们也可能成为性能瓶颈。

  • 使用OUT参数返回值。
  • 不支持交易。