存储过程与函数

存储过程与函数：
 本质上没区别。只是函数有如：只能返回一个变量的限制。而存储过程可以返回多个。而函数是可以嵌入在sql中使用的,可以在select中调用，而存储过程不行。
 但函数限制比较多，比如不能用临时表，只能用表变量，且不能执行数据表操作（增，删，改）．还有一些函数都不可用等等．而存储过程的限制相对就比较少
      1.    一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。
      2.    对于存储过程来说可以返回参数，而函数只能返回值或者表对象。
      3.    存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。
      4.    当存储过程和函数被执行的时候，SQL Manager会到procedure cache中去取相应的查询语句，如果在procedure cache里没有相应的查询语句，SQL Manager就会对存储过程和函数进行编译。
      Procedure cache中保存的是执行计划 (execution plan) ，当编译好之后就执行procedure cache中的execution plan，之后SQL SERVER会根据每个execution plan的实际情况来考虑是否要在cache中保存这个plan，评判的标准一个是这个execution plan可能被使用的频率；其次是生成这个plan的代价，也就是编译的耗时。保存在cache中的plan在下次执行时就不用再编译了。

存储过程和用户自定义函数具体的区别

概念：

 存储过程可以使得对数据库的管理、以及显示关于数据库及其用户信息的工作容易得多。存储过程是 SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。
存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。
可以出于任何使用 SQL 语句的目的来使用存储过程，它具有以下优点：
可以在单个存储过程中执行一系列 SQL 语句。
可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。
存储过程在创建时即在服务器上进行编译，所以执行起来比单个 SQL 语句快。
 函数是由一个或多个 Transact-SQL 语句组成的子程序，可用于封装代码以便重新使用。Microsoft? SQL Server? 2000 并不将用户限制在定义为 Transact-SQL 语言一部分的内置函数上，而是允许用户创建自己的用户定义函数。
每个完全合法的用户定义函数名 (database_name.owner_name.function_name) 必须唯一。
必须被授予 CREATE FUNCTION 权限才能创建、修改或除去用户定义函数。不是所有者的用户在 Transact-SQL 语句中使用某个函数之前，必须先给此用户授予该函数的适当权限。若要创建或更改在 CHECK 约束、DEFAULT 子句或计算列定义中引用用户定义函数的表，还必须具有函数的 REFERENCES 权限。
在函数中，区别处理导致删除语句并且继续在诸如触发器或存储过程等模式中的下一语句的 Transact-SQL 错误。在函数中，上述错误会导致停止执行函数。接下来该操作导致停止唤醒调用该函数的语句。

用户定义函数的使用：
用户定义函数采用零个或更多的输入参数并返回标量值或表。函数最多可以有 1024 个输入参数。当函数的参数有默认值时，调用该函数时必须指定默认 DEFAULT 关键字才能获取默认值。该行为不同于在存储过程中含有默认值的参数，而在这些存储过程中省略该函数也意味着省略默认值。用户定义函数不支持输出参数。
 1.标量函数 ：返回在 RETURNS 子句中定义的类型的单个数据值。可以使用所有标量数据类型，包括 bigint 和 sql_variant。不支持 timestamp 数据类型、用户定义数据类型和非标量类型（如 table 或 cursor）。在 BEGIN...END 块中定义的函数主体包含返回该值的 Transact-SQL 语句系列。返回类型可以是除 text、ntext、image、cursor 和 timestamp 之外的任何数据类型。
  --CREATE FUNCTION function_name
  --(@parameter_name_list)
  --RETURNS @return_data_type
  --AS
  --BEGIN
  -- function_body
  -- RETURN scalar_expression
  --END
 2.内嵌表值函数 ：表值函数返回 table。对于内嵌表值函数，没有函数主体；表是单个 SELECT 语句的结果集。
  --CREATE FUNCTION function_name
  --(@parameter_name_list)
  --RETURNS TALBE
  --AS
  --RETURN (select_statement) 
 3.多语句表值函数 ：在 BEGIN...END 块中定义的函数主体包含 TRANSACT-SQL 语句，这些语句可生成行并将行插入将返回的表中。BEGIN...END 块中的语句不能有任何副作用。函数副作用是指对具有函数外作用域（例如数据库表的修改）的资源状态的任何永久性更改。函数中的语句唯一能做的更改是对函数上的局部对象（如局部游标或局部变量）的更改。不能在函数中执行的操作包括：对数据库表的修改，对不在函数上的局部游标进行操作，发送电子邮件，尝试修改目录，以及生成返回至用户的结果集。
  --CREATE FUNCTION function_name
  --(@parameter_name_list)
  --RETURNS @return_variable TABLE(table_type_definition)
  --AS
  --BEGIN
  -- function_body
  -- RETURN
  --END

 
函数中的有效语句类型包括：

1.DECLARE 语句，该语句可用于定义函数局部的数据变量和游标。
2.为函数局部对象赋值，如使用 SET 给标量和表局部变量赋值。
3.游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。不允许使用 FETCH 语句将数据返回到客户端。仅允许使用 FETCH 语句通过 INTO 子句给局部变量赋值。
4.控制流语句,
5.SELECT 语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。
6.INSERT、UPDATE 和 DELETE 语句，这些语句修改函数的局部 table 变量。
7.EXECUTE 语句，该语句调用扩展存储过程。
在查询中指定的函数的实际执行次数在优化器生成的执行计划间可能不同。示例为 WHERE 子句中的子查询唤醒调用的函数。子查询及其函数执行的次数会因优化器选择的访问路径而异。

用户定义函数中不允许使用会对每个调用返回不同数据的内置函数。用户定义函数中不允许使用以下内置函数：
@@CONNECTIONS @@PACK_SENT GETDATE
@@CPU_BUSY @@PACKET_ERRORS GetUTCDate
@@IDLE @@TIMETICKS NEWID
@@IO_BUSY @@TOTAL_ERRORS RAND
@@MAX_CONNECTIONS @@TOTAL_READ TEXTPTR
@@PACK_RECEIVED @@TOTAL_WRITE 

 

架构绑定函数
CREATE FUNCTION 支持 SCHEMABINDING 子句，后者可将函数绑定到它引用的任何对象（如表、视图和其它用户定义函数）的架构。尝试对架构绑定函数所引用的任何对象执行 ALTER 或 DROP 都将失败。
必须满足以下条件才能在 CREATE FUNCTION 中指定 SCHEMABINDING：
该函数所引用的所有视图和用户定义函数必须是绑定到架构的。
该函数所引用的所有对象必须与函数位于同一数据库中。必须使用由一部分或两部分构成的名称来引用对象。
必须具有对该函数中引用的所有对象（表、视图和用户定义函数）的 REFERENCES 权限。
可使用 ALTER FUNCTION 删除架构绑定。ALTER FUNCTION 语句将通过不带 WITH SCHEMABINDING 指定函数来重新定义函数。

调用用户定义函数
当调用标量用户定义函数时，必须提供至少由两部分组成的名称：
SELECT *, MyUser.MyScalarFunction()
FROM MyTable
可以使用一个部分构成的名称调用表值函数：
SELECT *
FROM MyTableFunction()
然而，当调用返回表的 SQL Server 内置函数时，必须将前缀 :: 添加至函数名：
SELECT * FROM ::fn_helpcollations()