存储过程与用户自定义函数（SQL SERVER）

联系
二者本质上没有什么区别。当存储过程和函数被执行的时候，SQL Manager会到procedure cache中去取相应的查询语句，如果在procedure cache里没有相应的查询语句，SQL Manager就会对存储过程和函数进行编译。Procedure cache 中保存的是执行计划，当编译好之后就执行procedure cache中的execution plan，之后SQL SERVER会根据每个execution plan的实际情况来考虑是否要在cache中保存这个plan，评判的标准一个是这个execution plan可能被使用的频率；其次是生成这个plan的代价，也就是编译的耗时。保存在cache中的plan在下次执行时就不用再编译了。
二者的区别就是 1） 函数返回的整数值将可充当一个有意义的数据，而存储过程返回值用来显示执行成功还是失败。2） 可以在查询中执行嵌入式函数，但存储过程却不能。

存储过程
存储过程（Store Procedure，简称SP）和函数类似，都是SQL面向过程的一种数据库对象。存储过程实际上就是一段独立的SQL命令，它存储于数据库中而不是单独的文件中，它有输入参数、输出参数和返回值。
存储过程是以特定顺序排列的T-SQL语句序列，可为其指定名称，加以编译并保存到SQL SERVER上。一旦由DBMS编译并保存，用户可使用应用程序或者其他SQL脚本调用并执行存储过程中的语句。其类似于应用程序中的程序调用子程序。存储过程与程序有很多相似之处，具体如下所述：
存储过程可以接受输入参数，最终以输出参数或者输出结果的格式向调用该存储过程的其他存储过程或T-SQL批处理返回值。
存储过程可以执行复杂的逻辑操作和运算，可以调用其他存储过程。
存储过程不像函数那样将值返回，其向调用过程或批处理返回的是状态值，以指明成功或者失败（以及失败的原因）。
可以使用T-SQL的EXECUTE（简写为EXEC）语句来运行存储过程。在实际应用开发过程中，推荐在SQL SERVER中使用存储过程而不使用T-SQL，因为使用存储过程有以下好处：
1. 存储过程作为一个数据库对象已在服务器注册。
2. 存储过程具有安全特性（例如权限）和所有权链接，以及可以附加它们的证书。用户可以被授予权限来执行存储过程，而不必直接对存储过程中引用对象有权限。
3. 存储过程可以加强应用程序的安全性。使用参数化存储过程有助于保护应用程序，降低SQL注入攻击的可能性。
4. 存储过程允许模块化程序设计。存储过程与调用该存储过程的程序相分离，减少了应用程序与数据库之间的耦合。
5. 存储过程在编译后将把执行计划进行存储，在以后的调用中就可以不用像T-SQL语句一样每次进行编译然后再执行。由于减少了编译的过程，从而提高了执行的效率。
6. 存储过程可以减少网络通信流量。一个需要数百行T-SQL代码的操作可以通过将T-SQL代码中的逻辑写在存储过程当中，然后由应用程序执行存储过程即可，而不需要在网络中发送数百行代码。
7. 存储过程可以进行加密，包含其中的处理逻辑不被其他用户获取。

创建存储过程

CREATE PROCEDURE|PROC <sp name>[ { @parameter [ type_schema_name. ] data_type }    [ VARYING ] [ = default ] [ OUT [ PUT ] ] [ ,...n ]    [ WITH [ RECOMPILE ] [ ,ENCRYPTION ] ]    [ FOR REPLICATION ]AS<code>

存储过程名后跟的是参数表。参数表是可选项，有些存储过程可以不带参数，有些带一到多个参数。参数的声明是由参数名、数据类型、默认值和方向4部分构成。当然在声明参数时并不需要必须都要将这4部分写出。一般的参数只有参数名和数据类型两部分。
参数名必须以@符号开始，参数名的命名规则与其他数据库对象的命名规则类似，只是参数名中不能有空格。
数据类型是在声明变量时必须指出的，并且必须是有效的SQL SERVER数据类型。
默认值是参数与变量存在分歧的地方。通常变量将会被初始化为NULL，而参数则不是。如果需要定义一个没有提供默认值的参数，那么就需要在调用存储过程时提供一个初始值。
方向是指数据传输的方向，在没有指定的情况下默认为传入。若声明OUTPUT或者简写为OUT，则表示数据是从存储过程中传出的。

   --创建不带参数的存储过程   CREATE PROC GetStudent   AS   SELECT *   FROM Student   --执行存储过程   EXEC GetStudent   --创建带参数的存储过程   CREATE PROC GetStudent   @ssex varchar(2) = '男'   AS   SELECT *   FROM Student   WHERE sex = @ssex   --在没有参数赋值的情况下调用存储过程，存储过程将使用参数的默认值   EXEC GetStudent   --在存储过程名后给出参数的值，参数的值顺序必须与存储过程中定义的参数顺序相同   EXEC GetStudent '女'   --在存储过程名后以“@参数名=参数值”的形式给出参数值。参数顺序可以与存储过程中定义的顺序不同   EXEC GetStudent @ssex = '女'   --修改存储过程   ALTER PROC GetStudent   @ssex varchar(2) --去掉默认值，调用该存储过程必须传入参数   AS   SELECT *   FROM Student   ORDER BY sid --按学号排序   --删除存储过程   --DROP PROCEDURE|PROC <sp name>   DROP PROC GetStudent 

存储过程除了可以被其他存储过程调用外，更多的情况是作为数据库与应用程序的接口，被外部应用程序调用。除了使用SELECT命令返回表集外，还可以使用OUTPUT参数返回数据以及存储过程的RETURN值的功能。
如果在过程定义中为参数指定OUTPUT关键字，则存储过程在退出时可将该参数的当前值返回至调用程序。若要用变量保存参数值以便在调用程序中使用，则调用程序必须在执行存储过程时使用OUTPUT关键字。

--使用OUTPUT参数的存储过程--存储过程calc实现两个数相加并将结果传给OUTPUT参数。CREATE PROC@a int,@b int,@c int outASSET @c = @a + @b  --输出参数为输入2个参数之和GODECLARE @ans int EXEC @ans 1, 2, @ans out --执行存储过程PRINT @ansGODROP PROC calc

存储过程可以返回一个整数值（称为“返回代码”），指示过程的执行状态。与OUTPUT参数一样，执行存储过程时必须将返回代码保存到变量中，才能在调用程序时使用返回代码值。

--使用RETURN返回值的存储过程CREATE PROC compareNumber@a int,@b intASIF( @a >= @b )    RETURN 100ELSE    RETURN 200GODECLARE @ans intEXEC compareNumber 123, 234 --执行存储过程PRINT @ansGODROP PROC compareNumber/*--返回代码通常用在存储过程内的控制流块中，为每种可能的错误情况设置返回代码值。可以在T-SQL语句后使用@@ERROR函数，来检测该语句在执行过程中是否有错误发生。*/

用户定义函数
用户定义函数（User Defined Function，简称UDF）同存储过程类似，是一组有序的被预先优化的T-SQL语句，并且能够作为一个独立工作单元被调用。UDF与存储过程的主要区别在于如何返回结果。由于支持不同类型的返回值，所以UDF得限制比存储过程要多一些。
使用存储过程可以输入参数也可以得到返回的参数值。存储过程可以返回一个整数值，用于表示成功或失败而不是返回数据。存储过程也可以返回一个结果集，但是如果没有将这些结果集插入到某类表（表变量或临时表）中，则不能使用这些结果集。
但是在UDF中只能使用输入参数，而不能使用输出参数。输出参数的概念已经被一个更强的返回值所替代。UDF中返回值不像存储过程一样只能是整型，相反，UDF不仅可以返回SQL SERVER中的大部分数据类型（除了BLOB、游标、和时间戳），也能够返回一张表。返回标量值得函数叫标量值函数，返回表的函数叫做表值函数。
用户定义函数有以下优点：
1. 允许模块化程序设计。用户只需创建一次函数并将其存储在数据库中，以后便可在T-SQL语句、存储过程或其他函数中调用任意次。由于用户定义函数以数据库对象的形式存储在数据库中，所以可以独立于程序源代码进行修改，从而降低了程序与数据库之间的耦合。
2. 执行速度更快。与存储过程相似，用户定义函数在编译以后其执行计划便会被缓存，通过缓存计划并在重复执行时重用它可以降低使用T-SQL语句的编译开销。
3. 减少网络流量。在定义复杂约束或复杂WHERE条件时，可以使用函数来表示，以减少发送至客户端的数字或行数。

创建标量值函数

--语法：CREATE FUNCTION [ schema_name. ] function_name(    [ { @parameter_name [AS][type_schema_name. ] parameter_data_type     [ = default ][ READONLY ] }    [ ,...n ]    ])RETURNS return_data_type    [ WITH <function_option> [ ,...n ] ]    [ AS ]    BEGIN        function_body        RETURN scalar_expression    END

语法
function_name：用户定义函数名称。函数名称必须符合有关标示符的规则，并且在数据库中以及对其架构来说是唯一的。
@parameter_name：用户定义函数中的参数。可以声明一个或多个参数。
return_data_type：标量用户定义函数的返回值。对于T-SQL函数，可以使用除timestamp数据类型之外的所有数据类型（包含CLR用户定义类型）。对于CLR函数，允许使用除text、ntext和timestamp数据类型之外的所有数据类型（包含CLR用户定义类型）。不能将非标量类型cursor和table指定为T-SQL函数或CLR函数中的返回数据类型。
function_body：指定一系列定义函数值的T-SQL语句。这些语句在一起使用不会产生负面影响（例如修改表）。function_body仅用于标量函数和多语句表值函数。在标量函数中，function_body是一系列T-SQL语句，这些语句一起使用的计算结果为标量值。在多语句表值函数中，function_body是一系列T-SQL语句。这些语句将填充TABLE返回变量。
scalar_expression：指定标量函数返回的标量值。

/*一个函数最多可以有1024个参数。执行函数时，如果未定义参数的默认值，则用户必须提供每个已声明参数的值。*/--创建标量值函数--求人的年龄CREATE FUNCTION dbo.CalcAge( @birthday datetime )RETURNS int --返回类型ASBEGIN    RETURN year( getdate() ) - year( @birthday )    --用当前年份减去出生年份就是年龄END--执行PRINT dbo.CalcAge( '1989-12-02' )/*注意：与存储过程等其他数据库对象不同，在调用用户定义函数时必须使用schema_name.function_name的形式，如果只使用函数名，写成PRINT  CalcAge( '1989-12-02' )，则系统抛出错误：'CalcAge'不是可以识别的内置函数名称。*/--创建的UDF也可以嵌入到查询中，查询学生的名字和年龄SELECT s.sname , dbo.Calc( s.birthday ) AS ageFROM StudentORDER BY age

创建表值函数

--返回值是表CREATE FUNCTION [ schema_name. ] function_name(    [ { @parameter_name [AS][type_schema_name. ] parameter_data_type     [ = default ][ READONLY ] }    [ ,...n ]    ])RETURNS TABLE    [ WITH <function_option> [ ,...n ] ]    [ AS ]    RETURN [ ( ] select_stmt [ ) ]

语法
RETURNS子句为函数返回的表定义局部返回变量名。RETURNS子句还定义表的格式。局部返回变量名的作用域位于函数内。
函数体中的T-SQL语句生成行并将其插入RETURNS子句的返回值中。
当执行RETURN语句时，插入变量的行为将作为函数的表格输出返回。RETURN语句不能有参数。

CREATE FUNCTION GetStudentInfo() --定义函数名和参数RETURNS TABLE --返回的是一个表ASRETURN(    SELECT * FROM Student)--若要使用该表值函数，可以将该表值函数当表一样进行查询，--唯一不同的是在使用函数时必须为“架构.函数名”的形式。--修改与存储过程一样用alter--删除用drop