在SQL Server中编写函数和存储过程

SQL语句的集合包含在存储过程和函数中，数据库对象用于执行某些任务（或者也可能用于数据科学）。两者在很多方面都有区别。

在这篇文章中，我们将详细讨论函数和存储过程，以及它们的区别。

让我们从存储过程开始 −

SQL中的存储过程

简单写好的SQL代码保存起来多次重复使用就构成了一个存储过程。如果你能想到一个你经常写的查询，你可以把它保存为一个存储过程，然后调用存储过程来运行你保存为存储过程一部分的SQL代码。这将使你不必重复写同一个问题。

你可以重复执行相同的SQL代码，也可以向存储过程提供参数。根据需要，存储过程将根据提供的参数值作出适当的反应。

性能也可以通过存储过程来提高。一组SQL语句被用来实现几个任务。根据最初的SQL语句和条件逻辑的结果，运行以下哪些SQL语句。这些SQL语句和它们所包含的条件逻辑可以通过把它们写进存储过程而在服务器上组合成一个执行计划。由于所有的工作都是在服务器上进行的，所以条件逻辑的执行可以不向客户提供结果。

存储过程的优点

编译和执行

每个存储过程都由SQL Server编译一次，然后重复使用执行计划。当存储过程经常被调用时，其性能的提高是巨大的。

客户端/服务器流量减少

如果你的环境中的网络带宽是一个问题，你会知道存储过程可以将冗长的SQL搜索浓缩成一行，可以通过电线传输。

有效的代码重用和编程抽象化

众多的用户和客户端应用程序可以使用存储过程。如果你以有计划的方法使用它们，完成开发周期所需的时间会更少。

强化安全措施

与底层表的权限无关，你可以为用户提供运行存储过程的权限。

SQL中的函数

有2种类型的函数是SQL Server支持的

内置函数

内置函数按照Transact-SQL参考的定义进行操作，是不可改变的。只有遵循Transact-SQL参考的既定语法的Transact-SQL语句可以使用这些函数作为参考。

该系统已经定义了这些函数。它被分成两类 −

在本教程示例中，我们将使用以下表格 −

ID	Name	Marks	Age
1	Harsh	90	19
2	Suresh	50	20
3	Pratik	80	21
4	Dhanraj	95	19
5	Ram	85	18

标量函数

这些操作将一个值作为输入并输出。常见系统标量函数包括 −

round() − 将一个数字四舍五入到最接近的三位数。例如，round(28.64851)将输出28.649。

SELECT ROUND(MARKS,0) FROM students;

upper() − upper(“ENGLISH”)返回ENGLISH，lower(“ENGLISH”)返回english。

SELECT upper(NAME) FROM Students;

输出

HARSH
SURESH
PRATIK
DHANRAJ
RAM

rand() − 使用函数rand()，将返回一个范围内的随机数。例如，Rand(8)，返回0.71372242401或任何其他随机生成的数字。

系统聚合函数

这些函数返回一个单一的值，它们与输入参数的集合一起工作。例子包括 −

Avg() 将为所有提供的输入提供平均值。

示例

SELECT AVG(MARKS) FROM Students;

输出

Count() 这个函数将返回符合给定条件的行数。

示例

SELECT COUNT(*) FROM Students;

输出

Max() 和 min() 函数max()和min()将返回所提供参数中的最高值和最低值。

示例

SELECT MAX(AGE) FROM Students

输出

示例

SELECT MIN(AGE) FROM Students;

输出

用户定义的函数

使用CREATE FUNCTION命令来创建自定义Transact-SQL函数。用户定义的函数提供一个单一的值，需要零到多个输入参数。一个单一的数据值，如十进制数字、char或int，是某些用户定义的函数（UDFs）的返回值。

标量操作

用户定义的标量函数为函数的每一步操作输出一个单一的值。函数的任何数据类型的值都会被返回。

表值函数

内联函数

具有用户定义值的内联表函数操作并将结果作为一个表返回。没有一个BEGIN/END主体。为了得到结果，只需使用一个SELECT语句。

多重声明函数

如果一个用户定义的函数包含一个不能修改的SELECT语句或包含几个SELECT语句，那么该函数给出的结果就不会改变。我们必须明确指定表变量，并描述可能从各种SQL查询中获取的值。

用户定义函数的优点

支持模块化编程。
函数可以被创建一次，保存在数据库中，然后在你的软件中随意使用多次。应用程序的源代码不需要改变来改变用户定义的函数。
它们能够更快地执行。
Transact-SQL用户定义函数，就像存储过程一样，通过缓存计划并在多次执行中重用它们来降低编译成本。这使得执行时间大大加快，因为用户定义的函数不需要在每次使用时重新解析和优化。
对于计算工作负载、商业逻辑和字符串操作，CLR函数的表现明显优于Transact-SQL函数。数据访问密集型逻辑更适合于Transact-SQL操作。
它们可能会减少网络活动。
一个函数可以用来表示一个动作，该动作根据复杂的约束条件过滤信息，而这种约束条件不能用一个单一的数字表达式来表述。为了减少给客户的行数，该函数可以在WHERE子句中使用。

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

下表强调了SQL中用户定义的函数和存储过程之间的主要区别 −

标准	用户定义的函数	存储过程
返回值	单一值	单一、多个或甚至是零值
参数	输入值	输入和输出值
数据库	不能修改	可以修改
语句	只有SELECT语句	既有SELECT也有DML语句
调用	从函数中调用	不能从函数中调用
编译和执行	每次都需要编译	只需要编译一次
事务管理	不可能	不可能