第 11 章 SQL Server 程序设计

1. 第11章 SQL Server程序设计 • 11．1程序注释语句 • 11．2批处理 • 11．3事务 • 11．4 变量 • 11．5 程序控制语句 • 11. 6 游标 • 11．7 用户自定义函数 • 11 . 8本章总结

2. 学习目标： 通过本章学习，你能够学会： • 在进行SQL Server程序设计时涉及的批处理、事务、局部变量、全局变量的概念及使用方法 • 在进行SQL Server程序设计时涉及到的程序控制语句的使用方法 • 在进行SQL Server程序设计时还会用到游标，在本章中还可以学到游标的使用方法 • 在进行SQL Server程序设计时为使编程简洁清晰还会用到用户自定义函数，在本章将会学到用户如何定义自己的函数

3. 11．1程序注释语句 前面介绍的Transact-SQL语句以及一些编程的方法和技巧在处理一些简单的查询和实际应用时还可以完全胜任，但是，在有些时候，这些工具无法满足一些特殊要求，例如，要实现与用户的交互操作等。那么，了解和掌握如何使用Transact-SQL语句进行程序设计是非常必要的。

4. 在程序设计的过程中，注释语句的主要作用是帮助他人了解程序的具体内容，同时也便于我们对程序结构有所了解和掌握。试想如果你编了一段100行左右的程序，如果一个月后在来浏览，在没有注释的情况下，即使记忆力再好的程序员，也得花一番功夫才能把这段程序侧底弄清楚。因此经常书写程序注释语句是一种非常好的编程习惯。在程序设计的过程中，注释语句的主要作用是帮助他人了解程序的具体内容，同时也便于我们对程序结构有所了解和掌握。试想如果你编了一段100行左右的程序，如果一个月后在来浏览，在没有注释的情况下，即使记忆力再好的程序员，也得花一番功夫才能把这段程序侧底弄清楚。因此经常书写程序注释语句是一种非常好的编程习惯。

5. [实例11.1]下面是一个关于创建学生表的程序，请大家注意注释语句的书写[实例11.1]下面是一个关于创建学生表的程序，请大家注意注释语句的书写 操作步骤： 通过开始菜单打开查询分析器。 在查询分析器的编辑窗口输入程序设计语句，如下图所示

6. 11．2批处理 • 批处理是一个以GO为结束标志的SQL语句组。批中的所有SQL语句作为一个整体编译成一个执行单元后一次提交给SQL Server服务器执行。有多个批时,系统按批地顺序依次执行。

7. [实例11.2]使用批处理选择Pubs数据库，然后创建一个视图，最后使用视图进行查询。[实例11.2]使用批处理选择Pubs数据库，然后创建一个视图，最后使用视图进行查询。 • 操作步骤： • 通过开始菜单打开查询分析器。 • 在查询分析器中输入如下语句 • 执行结果如图11.2所示

8. 图11.2批处理执行结果

9. 由上面的例子可以看出来，所有的批处理命令都使用GO作为结束标志。当编译器读到GO时，它就会把GO前面所有的语句当作一个批处理，而打包成一个数据包发送到服务器。GO本身并不是Transact-SQL语句组部分，它只是一个用于表示批处理结束的前端指令。当有多个批时,系统按批地顺序依次执行,关于批中有如下几点需要注意：由上面的例子可以看出来，所有的批处理命令都使用GO作为结束标志。当编译器读到GO时，它就会把GO前面所有的语句当作一个批处理，而打包成一个数据包发送到服务器。GO本身并不是Transact-SQL语句组部分，它只是一个用于表示批处理结束的前端指令。当有多个批时,系统按批地顺序依次执行,关于批中有如下几点需要注意： • (1)一个批处理中若有多个存储过程,则从第二个存储过程开始,每个存储过程的前面都要加关键字EXECUTE 或EXEC. • (2)一般地，一个CREATE语句必须独自占用一个批处理，且必须以CREATE语句开始。 • (3)若使用ALTERTABLE语句修改表的结构，则不能在同一个批中引用新定义或修改的列。

10. 执行一个批时，若其中有一个语句出现编译错误，则SQL Server将取消该批内所有语句的执行。但若一个批通过了编译而在运行时出现错误，则批内发生错误之前的语句将被执行，发生错误的语句不被执行，发生错误之后的语句视具体情况可能被执行或不被执行。因此，对于仅执行了一部分的批，可能引起整个过程发生错误或产生数据垃圾。

11. 为防止这类情况出现，可以在SQL Server编程中引入“事务”（将在下节讲到），将批处理放在一个事务中，并设置批处理发生错误时引发事务回滚，使所有尚未提交的操作都回滚到发生错误之前的状态。

12. 11．3事务 • 事务是SQL Server的一个工作单元，事务通常以BEGIN TRANSACTION开始，以COMMIT TRANSACTION结束。如果在一个事务模块中有一个SQL语句执行失败或发生不该有的结果，可以通过设置回滚命令ROLLBACK TRANSACTION取消该语句导致的错误操作结果(例如取消已插入的数据或恢复已被修改的数据)，使数据库恢复到该错误语句执行前的状态。

13. [实例11.3]利用事务在第4章中的学生库的学生信息表中插入两行数据，在插入第2行数据后回滚到两次插入数据之间，然后查询。[实例11.3]利用事务在第4章中的学生库的学生信息表中插入两行数据，在插入第2行数据后回滚到两次插入数据之间，然后查询。 步骤： 启动“查询分析器”，输入SQL语句： 得到如下执行结果如图11.3所示 11.3利用事务执行语句

14. 然后对“课程”表进行查询 ，执行结果如下，如图11.4所示。 图11.4 事务执行后的查询结果

15. 这个例子一共执行了两次插入操作，第一次操作完成后，设置了一个保存点，第二次插入操作执行完后，程序执行了事务回滚，使第二次的插入操作被取消，但是第一次插入操作依然有效，这样查询后就只能得到一行插入数据。这个例子一共执行了两次插入操作，第一次操作完成后，设置了一个保存点，第二次插入操作执行完后，程序执行了事务回滚，使第二次的插入操作被取消，但是第一次插入操作依然有效，这样查询后就只能得到一行插入数据。 如果不设置保存点，则默认回滚整个事务，两次插入操作都将被取消。

16. 从上面的例子也可以看出，在一个事务中可以包含多个批处理，当然一个批处理也可以包含多个事务。两者的区别是，批语句的组合发生在编译阶段，而事务语句的组合发生在执行阶段。一个批若在编译阶段发生语法错误，则在运行阶段整个批都不能执行。若编译通过而在运行时发生错误，如违反主键惟一性约束等，则有两种情况：若没有设置事物回滚，默认时系统仅回滚发生错误的语句；若设置了事务回滚，则系统回滚事务。从上面的例子也可以看出，在一个事务中可以包含多个批处理，当然一个批处理也可以包含多个事务。两者的区别是，批语句的组合发生在编译阶段，而事务语句的组合发生在执行阶段。一个批若在编译阶段发生语法错误，则在运行阶段整个批都不能执行。若编译通过而在运行时发生错误，如违反主键惟一性约束等，则有两种情况：若没有设置事物回滚，默认时系统仅回滚发生错误的语句；若设置了事务回滚，则系统回滚事务。

17. 由上面的例子我们可以看出，建立一个事务包含四个语句，即开始事务、设置保存点、回滚事务、提交事务。 • (1) 开始(或启动)一个事务单元。 • 命令格式： • BEGIN TRANSACTION 单元名称 • (2) 在事务中设置一个保存点。保存点的设置可以使得回滚事务语句回滚到该保存点，以便取消该事务中的部分操作对数据库所引起的变动。 • 命令格式： • SAVE TRANSACTION 保存点名称

18. (3) 回滚一个事务单元。该语句用于取消事务中的部分或全部操作，使得这些操作对数据库所作的改动恢复为改动前的状态。 命令格式： ROLLBACK TRANSACTION 单元名称 | 保存点名称 (4)提交(或完成)一个事务单元。提交一个事物后，在该事物中对数据库所做的变动被保存到数据库中。 命令格式： COMMIT TRANSACTION 单元名称

19. 注意，一旦执行了COMMIT TRANSACTION语句，则事务被提交，此时将不再能够回滚事务。在一个事务内允许有重复的保存点名称，但若在ROLLBACK TRANSACTION语句中使用重复的保存点名称，则事物只回滚到离它最近的使用该保存点名称的SAVE TRANSACTION语句处。

20. 11．4 变量 Transact-SQL语言中有两种形式的变量，一种是用户自己定义的局部变量，另外一种是系统提供的全局变量。在使用方法以及具体意义上，这两种变量都有着较大的区别，本节将分别介绍这两种变量的使用方法。

21. 11.4.1局部变量 • 局部变量是在批处理（也可以是存储过程或触发器）内由用户定义并使用的变量。用户在使用局部变量前必须事先声明，而它的使用范围也仅限于声明它的批处理（也可以是存储过程或触发器）以内。 • [实例11.4]利用局部变量在数据库学生库的学生信息表中查询学生“和平” 的年龄。 • 步骤： • 启动查询分析器，输入如下SQL语句： • 按“F5”键或单击工具栏“执行查询”图标执行。执行结果如图11.5所示

22. 图11.5使用局部变量查询的结果

23. 从上面的例子，我们可以看出，通过声明局部变量可以查询学生信息表中任意的学生信息，只要修改赋给局部变量的值就可以。在使用局部变量查询需要做到两点：一是声明局部变量，二是给局部变量赋值。

24. （1）声明局部变量 • 命令格式： • DECLARE ( @ 局部变量名 数据类型[，….n]) • 命令说明： • DECLARE：该关键字表示将要声明变量。 • 数据类型可以是除text、ntext和image外的数据类型或用户定义的数据类型。 • [， ….n ]：表示一个DECLARE语句中可以声明多个局部变量，这些变量用逗号相隔。 • (2) 局部变量的赋值 • 声明局部变量后，系统自动为变量赋初值NULL。若需要另外赋值，可以使用SET或SELECT语句。赋值语法如下： • 命令格式： • SELECT @局部变量名=表达式 • [FROM 表名[,….n ] WHERE clause] [+·n) • 或 SET @局部变量名=表达式

25. 命令说明： • 表达式可以是一个具体的数据如数字、字符串等，也可以是一个表达式或另一个局部变量或全局变量，还可以是从一个查询语句中查询出来的数据。 • FROM子句用于向变量所赋的值源于由一个表中查询所得数据的情形。 • 一个SELECT语句可以为多个变量赋值，但一个SET语句仅能为一个变量赋值。若使用SELECT语句为变量赋值，则不能与其查询功能同时使用。若使用SELECT语句从表中取数为变量赋值，则其返回的数据必须惟一，否则仅将最后一个数据赋给变量。

26. 下面的例子是使用SELECT语句进行赋值，从表格中查询出一条数据赋值给已经声明的变量：下面的例子是使用SELECT语句进行赋值，从表格中查询出一条数据赋值给已经声明的变量： • USE pubs • GO • DECLARE @author varchar(20) • SELECT @author=au_id • FROM authors • WHERE state=’MI’ • GO

27. 11 . 4 . 2 全局变量 • 在SQL Server中全局变量作为一种特殊函数由系统预先定义，因此有的教材把全局变量放到函数部分介绍，称为SQL Server的内置函数。常用的全局变量有三十多个，用于记录数据库服务器的工作信息及性能数据。全局变量的作用范围是整个系统，通常利用全局变量来检测系统的设置值或执行查询命令后的状态值。

28. [实例11.5]使用全局变量来查询SQL Server实例当前使用的语言和当前所使用语言的标识符。

29. 由上例可以看出全局变量以@@开头，无需定义直接使用即可。除了上面的全局变量外，还有如@@CONNECTIONS返回自上次启动SQL Server数据库服务器以来连接或试图连接的次数，@@ERROR返回最近一次执行T-SQL语句的错误代码，@@version表示SQL Server的版本号等等，其他全局变量可参见帮助。

30. 11．5 程序控制语句 SQL Server支持结构化的编程方法，可以进行顺序结构、选择结构、循环结构的编程。本节结合例题介绍SQLServer2000的程序控制语句。

31. 11．5．1 BEGIN…END语句 • BEGIN … END语句相当于其他计算机语言中的大括号或复合语句，它将多条T-SQL语句封装成为一个整体的语句块，T-SQL中允许使用嵌套的BEGIN … END语句。通常把BEGIN … END语句放在语句IF … ELSE或者WHILE中，使其中的语句作为一个整体来执行。

32. [实例11.6]在学生成绩表期中成绩中检查全体学生某门课程(课程编号为1001)的期中成绩。如果某个学生的成绩低于60分，则显示文本：“1001科目的分数低于60分”，并显示所有未及格学生的学号、姓名与期中成绩分数。[实例11.6]在学生成绩表期中成绩中检查全体学生某门课程(课程编号为1001)的期中成绩。如果某个学生的成绩低于60分，则显示文本：“1001科目的分数低于60分”，并显示所有未及格学生的学号、姓名与期中成绩分数。

33. 命令格式： • BEGIN • T-SQL语句块 • END • 命令说明： • BEGIN、END分别表示语句块的开始和结束。 • T-SQL语句块可以是一条或一条以上的T-SQL语句。

34. 11．5．2 IF…ELSE语句 • 若希望在满足某种条件的情况下执行一些语句，而在该条件不满足的情况下执行另外一些语句。在这种情况下，我们就需要使用条件语句来在执行T-SQL语句时强加条件，来控制语句执行的顺序。

35. [实例11.7]在学生信息表中统计学生的平均年龄，如果平均年龄小于20则输出年龄组合正常，否则输出大于平均年龄的学生的信息。[实例11.7]在学生信息表中统计学生的平均年龄，如果平均年龄小于20则输出年龄组合正常，否则输出大于平均年龄的学生的信息。

36. 由上例我们可归纳出IF...ELSE语句的命令格式： • IF 返回布尔表达式{T-SQL语句| BEGIN…END语句块} • [ELSE{ T-SQL语句| BEGIN…END语句块}] • 命令说明： • 返回“真”(TRUE)或“伪”(FALSE)的布尔表达式。如果布尔表达式中含有SELECT语句，则必须用圆括号将SELECT语句括起来。 • T-SQL语句| BEGIN…END语句块：只能是一条。

37. 11.5.3 CASE语句 • 当有多个选择方案时，如果使用IF… ELSE语句进行选择判断，则由于需要层层嵌套而非常不便，此时可以在SELECT语句中采用CASE结构。CASE语句有两种格式：简单CASE函数与CASE搜索函数。前者将某个表达式与一组简单表达式进行比较以确定结果；后者则需计算一组布尔表达式以确定结果。下面用实例加以说明。

38. [实例11.8]在pubs数据库中根据作者来自的州的名字，打印出作者的姓名和州的全名[实例11.8]在pubs数据库中根据作者来自的州的名字，打印出作者的姓名和州的全名

39. 命令格式： • CASE 输入表达式 • WHEN 比较的表达式 THEN 结果表达式 […n] • [ELSE 返回结果表达式] • END • 命令说明； • CASE后面的输入表达式，用于进行比较。 • WHEN后面给出用于进行比较的表达式的数据类型必须相同，或者能够隐性转换。 • […n)表示可以有多个WHEN…THEN结构。

40. [实例11.9]显示学生成绩中的期中成绩分数。

41. 由上例可见，CASE搜索函数的WHEN后表达式是布尔表达式，这是与简单CASE函数不同之处。由上例可见，CASE搜索函数的WHEN后表达式是布尔表达式，这是与简单CASE函数不同之处。 • 命令格式； • CASE • WHEN 逻辑表达式 THEN • [ELSE 结果表达式 ] • END

42. 11．5．4 WHILE语句 • WHILE语句是一种条件循环语句，在满足条件时执行循环体，不满足条件时跳出循环结构。WHILE语句可以嵌套执行，嵌套时首先运行内层循环，内层循环结束之后再运行外层循环。

43. [实例11.10]在pubs数据库中，将书价小于$30的书提高50%，如果提高后的书价超过$50，则输出书价太贵。[实例11.10]在pubs数据库中，将书价小于$30的书提高50%，如果提高后的书价超过$50，则输出书价太贵。

44. 由此，我们可给出命令格式： • WHILE 逻辑表达式 • BEGIN • 表达式 • [BREAK] • [CONTINUE] • END • 命令说明： • 当逻辑表达式为真时，服务器将重复执行SQL语句组。BREAK的作用是在某些情况发生时，立即无条件地跳出循环，并开始执行紧接在END后面的语句。CONTINUE的作用是在某些情况发生时，跳出本次循环，开始执行下一次循环。

45. 11．5．5 RETURN语句 • RETURN语句用于从当前过程、批处理或语句块中无条件退出，并不再执行位于RETURN之后的语句。 • 命令格式： • RETURN 整型表达式 • 命令说明： • RETURN一般用于从存储过程给调用过程或应用程序返回整型值。除非特别指明，所有系统存储过程返回0值表示成功，返回非0值则表示失败。

46. 11.6 游标 • 数据库的游标是类似于C语言指针一样的语言结构。SELECT语句是针对数据库中表的所有数据进行处理的，如果需要对某一行数据进行处理，使用SELECT语句就显得力不从心。游标正好弥补了这一缺陷，它类似于一个指针，可以定位于SELECT语句结果集合中的某指定行，从而对该行的数据进行操作。游标的使用过程依次为： • 声明游标、打开游标、使用游标、关闭游标、释放游标。

47. 11.6.1声明游标 • [实例11.11]定义游标，打开学生信息表。

48. 上面的例子首先通过DECLARE关键字来定义游标，然后选择一个表，打开游标，就可以查询到当前表中记录的个数 。其中@@cursor_row表示当前游标记录数目是一个全局变量。由此，我们给出打开游标的命令格式： • DECLARE 游标名称 [INSENSITIVE] [SCROLL] CURSOR • FOR SELECT语句 • [FOR READ ONLY | UPDATE [OF 列名1，列名2，列名3…]]

49. 命令说明： • 游标名：为声明游标所起的名字。 • INSENSITIVE：使用INSENSITIVE定义的游标，把提取出来的数据放入一个在tempdb数据库里创建的临时表里。任何通过这个游标进行的操作，都在这个临时表里进行。所以所有对基本表的改动都不会在用游标进行的操作中体现出来。 • SCROLL：使用SCROLL关键字定义的游标，具有包括如下所示的所有取数功能： • FIRST：取第一行数据； • LAST：取最后一行数据； • PRIOR：取前一行数据； • NEXT：取最后一行数据； • RELATIVE：按相对位置取数据； • ABSOLUTE：按绝对位置取数据。 • 如果没有在声明时使用SCROLL关键字，那么所声明的游标只具有默认的NEXT功能。

50. SELECT语句：一些标准的SELECT语句，主要用来定义游标所要进行处理的结果集。在声明游标的SELECT语句中，不允许使用如COMPUTE，COMPUTE BY和INTO等关键字。 • FOR READ ONLY：声明只读光标。不允许通过只读光标进行数据的更新。 • UPDATE [OF 列名1，列名2，列名3…]：定义在这个游标里可以更新的列。如果定义了[OF 列名1，列名2，列名3…]，那么只有列在表中的列可以被更新；如果没有定义[OF 列名1，列名2，列名3…]，那么游标里的所有列都可以被更新。