[SQL]SQL Server的存储过程和用户自定义函数

baacloud免费翻墙vpn注册使用

一.创建存储过程

 

二.更改存储过程

     Alter PROC 会保留任何在为存储过程所建立的许可。它在系统对象中保留相同的对象ID,同时也允许保留其依赖性。譬如,如果存储过程A调用存储过程B,而又删除和重建 了存储过程B,这时就不再能看到这两者之间的依赖性。如果使用Alter修改存储过程B,其依赖性就仍然是存在的。

     Alter PROC 保留了任何其他需要调用该被修改存储过程对象上的依赖信息。如果执行Drop再执行Create,可以得到同Alter PROC几乎相同的结果,只除了一个较大的差别,如果先执行Drop再Create,你需要重新建立许可,以确定谁能访问该存储过程,谁不能访问该存储过 程。另外,再删除它之前,SQL SERVER会丢失对依赖它的任何过程、视图、触发器、函数的依赖性信息的追踪。     

三.参数

     1.参数声明

          语法:@参数名 [AS] 数据类型 [ = 默认值 | NULL] [VARYING] [OUTPUT | OUT]

          注:在声明CURSOR类型的参数时,必须同时使用VARYING和OUTPUT选项。

          变量总是被初始化为NULL,而参数不同。如果没有为参数指定默认值,则在调用存储过程时要求必须提供参数的初始值。

     2.输出参数:OUTPUT

          从存储过程中往调用该存储过程的地方传递非记录集形式的信息。

四.返回值

     1.正确的使用方式:用来确认存储过程的执行状态。

     2.RETURN:实际上,无论是否指定返回值,程序都会返回一个值。默认时,在存储过程完成时,SQL SERVER会自动返回一个值0。

五.错误处理

 

六.存储过程能提供什么

     1.创建可调用的存储过程

          存储过程可以调用其他存储过程(嵌套)。在SQL SERVER 2005 中,最多可以进行32层嵌套。

     2.安全

          像视图一样,可以创建一个返回记录集的存储过程,而不需要为用户提供访问该数据表的授权。

          允许某人有权执行存储过程,意味着他们可以在存储过程中进行任何操作,所提供的操作是在存储过程的上下文环境中被执行的。

     3.性能

          存储过程被创建后,第一次执行时,存储过程被优化,查询计划被编译并被缓存到系统中。当再次执行该存储过程时,存储过程如果在 内存中,则使用内存中的查询计划;如果不在,则重新编译,并添加到内存中。这意味着,当多次执行存储过程的时候,只需一次优化和编译的过程。

          (1)好的存储过程变坏时

          存储过程最重要的事情之一是:除非你手工干涉(使用WITH RECOMPILE选项),它们将基于或者是第一次运行,或者是查询中所包含的表的统计状态被更新时而优化。

          当一个存储过程包含的查询超过一条,并且依赖于参数的值来选择正确的查询来运行时,“一次优化,多次使用”的策略就会导致存储 过程性能降低的情况。当第一次执行存储过程时,它走过一些IF…ELSE…语句,并找到正确的查询来执行。不幸的是,这只是针对该特定时刻的正确 查询,只是它未知时刻的百分之几。在第一次之后的任何时候,存储过程选择不同的查询来运行,然而,它还是会使用基于第一次运行时刻的查询计划。这时,存储 过程的性能就被降低了。

          (2)WITH RECOMPILE

          仍然使用存储过程的安全性和代码区域化带来的好处,而同时可以忽略预编译代码方面的事情。这可以使你避过使用不正确的查询计划的问题,你可以针对当前的运行,确定创建新的计划。方法就是使用WITH RECOMPILE选项。

          首先,可以在运行时刻包括WITH RECOMPILE选项,如:EXEC [PROC] WITH RECOMPILE。这告诉SQL SERVER将现有的查询计划抛开,并创建一个新的计划,但是只针对这一次——就是说,只对执行该存储过程的这次使用WITH RECOMPILE选项。

          其次,也可以通过在存储过程中添加WITH RECOMPILE选项,让配置变成永久的,在Create或Alter PROC语句中的AS语句前直接添加该WITH RECOMPILE选项。这时,该存储过程在每次被运行的时候都被编译,而不管在运行时刻所选择的其他选项。

七.用户定义函数(UDF)

     1.返回标量值的UDF

         (1)返回值类型:除了BLOB、游标(cursor)和时间戳(timestamp)之外,它可以返回任何有效的SQL Server数据类型(包括用户定义的数据类型!)。

         (2)与存储过程相比的好处:

               a.不像存储过程,返回值的所有目的就是提供有用的数据块——对于存储过程,返回值是其执行成功或者失败的标志,并且,在失败的时候,针对失败的种类提供某些特定的信息。

               b.可以在查询中内联执行函数(例如,可以将它作为Select语句的一部分包含进来),而存储过程却不可以。

     2.返回表的UDF

          (1)在大多数情况下,返回的表可以像其他表一样使用。可以在其上执行JOIN,甚至可以对结果应用Where条件。

          (2)确定性。用户定义的函数可以是确定的,也可以是非确定的。如果给定一系列指定的输入函数每次都可以返回准确的相同的值,那么该函数就被称作确定的。反之,则被称为非确定的。

               只有当视图或计算列的结果可以被可靠地确定时,才允许在视图或计算列上构建索引。

               可以使用OBJECTPROPERTY函数来进行这个检查。返回0代表是非确定的,返回1代表是确定的。

               Select OBJECTPROPERTY(OBJECT_ID('[FUNCTIONNAME]', 'IsDeterministic')

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏