文档结构  
翻译进度:已翻译     翻译赏金:0 元 (?)    ¥ 我要打赏

1. 简介

Sqlserver存储过程是可执行的程序,它作为一个对象存储在数据库中。从客户端应用可以执行这些存储过程以获得更好的性能。它们可以被其他脚本甚至其他存储过程调用。

SqlServer存储过程和客户端应用中的通讯是通过参数和返回值进行的。如下图所示:


图 1: SqlServer存储过程和客户端应用的通信

客户端应用传递些数据给存储过程(这是可选的),存储过程在此数据上做些处理。在存储过程完成其操作后,它把返回值传给客户端应用(同样是可选的),主要通过三种技术,比如:

  1. 通过输出参数类型传递数据
  2. 通过返回语句传递数据
  3. 通过选择语句传递数据

我们会在此文中看到这些。

第 1 段(可获 2 积分)

2. 在Sql中创建和执行存储过程

你可以在 SQL Server Management Studio中创建存储过程。一旦存储过程被创建,在其对应数据库下的 Programmability 结点下会列出此存储过程。要执行此存储过程,只有用存储过程名称后的 “EXEC” 就可以了。

存储过程的结构如图2所示。创建时,先写存储过程名称,然后写一个或多个传递参数,参数是可选的。参数后面,开始写执行指定操作的函数体。在函数体里可以定义局部变量,这些变量只是存储过程的变量,或者说这些变量只能在存储过程体里可见。

第 2 段(可获 2 积分)


图 2: SQL Server存储过程的基本框架

下面的图3展示了简单的存储过程,略过了存储过程所有可选的部分。要创建一个存储过程,需要存储过程名字以及只少一个语句作为存储过程的主体。注意在创建和执行存储过程时,最好带有所属的schema名称。在下面的例子里,shema名称是dbo.


图3: 一个简单的存储过程的例子

存储过程主体可以包含任何SQL语言如创建数据表,在数据表中添加一行或多行内容,设置数据库行为,等等。但是某些操作在存储过程主体部分是被限制的。这些重要约束如下所示:

第 3 段(可获 2 积分)
  1. 存储过程主体不能创建其他的存储过程
  2. 存储过程不能创建视图
  3. 存储过程不能创建任何触发器

下面的录像展示了在SQL Server Management Studio中怎样创建和执行一个简单的存储过程。

视频1: 创建一个简单的存储过程

3. 在存储过程主体里声明变量

你可以在存储过程主体里声明一个或多个局部变量,这些变量只在存储过程主体里可见。你可以存储过程主体里的任何地方声明变量。但是,最好是在函数主体的开头声明变量,如图2所示。看看下面的简单的存储过程示例:

第 4 段(可获 2 积分)

图4. 在存储过程中定义变量

在上面的存储过程中,如你所见变量Name1和Name2(标为1定义在主体开始部分。注意到多个变量定义在一行里,且每个变量由逗号相隔。并且看到变量有个前缀@。在主体的任何地方你都可以定义变量,变量@Name3就定义在存储过程主体后部。为了给定义变量赋值,在上述例子中用了“Set”关键字(标记为3)。不像一行可以同时定义多个变量,set语句一行只能出现一次。执行上述存储过程的结果如下图所示:

第 5 段(可获 2 积分)

图5:执行存储过程SayHello2

图6.在一句语句中给多个变量赋值

4. SQL 存储过程参数

在上述例子中,我们创建并执行了一个简单的存储过程。存储过程可以接收参数,调用者会传值给存储过程(但不总是这样,稍后我们会发现)。基于所得参数值,存储过程在过程主体做相应的操作。比如,我们创建一个存储过程,它会接收城市和国家这两个参数,然后算出在这个城市和国家里有多少作者。存储过程会查询Pubs数据库的Authors数据表,然后计算出作者数量。你可以通过谷歌,或者在SQL2005页面下载对应的SQL Script. 存储过程示例如下所示:

第 6 段(可获 2 积分)

图7. 执行带参的存储过程

在上述存储过程的例子中,他接收了@State和@City 两个参数(在上面的截图中标为1)。为了方便,数据类型和表中的数据类型保持一致。存储过程主体里有内部变量@TotalAuthors ,用来显示作者的数量。参数通过select语句中的where子句进行传递,从而算出作者数量。这在截图中标记为2。最后,通过Print语句将计算值打印出来,输出内容标记为5.

第 7 段(可获 2 积分)

有两种方式执行存储过程。第一种方式(标记为3),在存储过程名称后用逗号间隔参数。在例子中我们传递 CA, Berkeley两个值,并用逗号相隔离。这两个值被存在变量@State 和@City里。在这种方法中,参数传递的顺序极为重要,这种技术被称为依次传参。第二种方式(标记为4),在存储过程名称后参数直接被赋值,参数顺序并不重要,第二种技术被称为传递明明参数,如下面视频所述:

第 8 段(可获 2 积分)

视频2:执行带参的存储过程

创建和执行带默认参数的存储过程如下图所示。除了参数位置变化,存储过程和上述截图的一样。默认是参数@City放在前面,@State参数放在后面。在下面的截图中标记为1。看看存储过程的执行(标记为2,3),在第一个执行中,我们把两个参数都传了(标记为3)。在这种情况下,被传参数‘UT'代替了默认值’CA‘(标记为4)。在第二个执行中,只传了一个参数 ‘Covelo’ 给@City,@State采用默认值 ‘CA’。在参数列表后赋予默认值是很好的做法。否则,标记为2的执行是不可行的,你需要通过命名参数名称传参。

第 9 段(可获 2 积分)

图8: 带默认参数的SQL存储过程

5. 带返回值的SQL存储过程

存储过程传递返回值的三种方式如下:1)通过RETURN语句的存储过程2)带OutPut参数的存储过程3)存储过程的Select语句。我们一个个讲。

5.1 含RETURN语句的SQL存储过程

通过这种方法,存储过程可以赋值给局部变量,然后返回其值。当然存储过程也可以返回一个常量。在下面的例子中,我们创建的存储过程可以返回作者的总数量。当你将其与之前的存储过程相比较,你发现Print语句被替为了retrun语句。

第 10 段(可获 2 积分)


图 9: 含return语句的SQL存储过程

让我们看下怎么执行存储过程,打印出存储过程的返回值。存储过程执行需要声明一个变量,并在执行后打印它。注意你可以使用类似“”Select @RetValue as OutputValue”的Select语句替代print语句。下面的代码段显示了检查存储过程返回值的两种方式。


图10: 执行含return语句的存储过程

5.2 含OUTPUT 参数的SQL存储过程

在前面的例子中,我们看到return语句可以用来返回一个单独变量。而用Output参数可以传递给调用者一个或多个变量。在创建存储过程时,输出参数用关键字“Output”标识。一个参数被标为输出参数时,在存储过程主体里需要给它赋值。看一下下面这个存储过程的例子:

第 11 段(可获 2 积分)

图11: 含Output参数类型的存储过程。

在上面的存储过程里,参数列表定义了两个输出参数@TotalAuthors和@TotalNoContract。这些变量在存储过程主体内部被赋值。在用output参数时,调用者发现其值在过程主体被赋予。执行此存储过程,并通过output参数获取值,如下图所示:


图12: 执行带output参数的存储过程

在上面的脚本中,给存储过程声明了两个output参数变量(标记为1)。赋予正常的参数值 CA 和Berkeley以执行存储过程。由于第三和第四个参数是Output参数,因而声明的变量@OutputVar1, @OutputVar2会传给存储过程(标记为2)。注意当传递参数时,关键字output也要在这里指定。一旦存储过程过程执行成功,返回值通过output参数成功打印到消息窗口(标记为3).

第 12 段(可获 2 积分)

5.3 含有 Select from 语句的存储过程

Select...From 技术是用来以数据表的形式(记录集)返回给存储过程的调用者一组值。在下面的例子中,存储过程接受了名为@AuthID的参数,并用此参数过滤 Authors表的记录。如下图标记为1的Select语句,它决定了哪些数值会返回给调用者。当执行存储过程时,将Author Id值作为参数(标记为2)。执行结果标为3。这边的例子要么返回一条记录,要么不返回。然而存储过程对返回值的行数没有限制,可以返回多于一条的记录。图6展示了通过select语句返回多行值。

第 13 段(可获 2 积分)


图13:含Select from语句的存储过程

6. 结论

相比有同样作用的函数而言,存储过程是很强大的。这是因为它可以在服务器上就能被调用,对于服务器和客户端存在大量数据交换(对于特定的计算)的情况可以避免。本文没有演示混合的数值返回技术(比如在存储过程中同时使用return语句和output参数返回数值),不过你可以自学这些。我们以后文章再见。

第 14 段(可获 2 积分)

文章评论