函数游标过称

函数游标过称第一页，共七十页，编辑于2023年，星期日学习目标：通过本章学习，你能够学会：在进行SQLServer程序设计时涉及的批处理、事务、局部变量、全局变量的概念及使用方法在进行SQLServer程序设计时涉及到的程序控制语句的使用方法在进行SQLServer程序设计时还会用到游标，在本章中还可以学到游标的使用方法在进行SQLServer程序设计时为使编程简洁清晰还会用到用户自定义函数，在本章将会学到用户如何定义自己的函数第二页，共七十页，编辑于2023年，星期日11．1程序注释语句

前面介绍的Transact-SQL语句以及一些编程的方法和技巧在处理一些简单的查询和实际应用时还可以完全胜任，但是，在有些时候，这些工具无法满足一些特殊要求，例如，要实现与用户的交互操作等。那么，了解和掌握如何使用Transact-SQL语句进行程序设计是非常必要的。第三页，共七十页，编辑于2023年，星期日在程序设计的过程中，注释语句的主要作用是帮助他人了解程序的具体内容，同时也便于我们对程序结构有所了解和掌握。试想如果你编了一段100行左右的程序，如果一个月后在来浏览，在没有注释的情况下，即使记忆力再好的程序员，也得花一番功夫才能把这段程序侧底弄清楚。因此经常书写程序注释语句是一种非常好的编程习惯。第四页，共七十页，编辑于2023年，星期日[实例11.1]下面是一个关于创建学生表的程序，请大家注意注释语句的书写操作步骤：通过开始菜单打开查询分析器。在查询分析器的编辑窗口输入程序设计语句，如下图所示第五页，共七十页，编辑于2023年，星期日批处理是一个以GO为结束标志的SQL语句组。批中的所有SQL语句作为一个整体编译成一个执行单元后一次提交给SQLServer服务器执行。有多个批时,系统按批地顺序依次执行。11．2批处理

第六页，共七十页，编辑于2023年，星期日[实例11.2]使用批处理选择Pubs数据库，然后创建一个视图，最后使用视图进行查询。操作步骤： 通过开始菜单打开查询分析器。在查询分析器中输入如下语句执行结果如图11.2所示第七页，共七十页，编辑于2023年，星期日图11.2批处理执行结果第八页，共七十页，编辑于2023年，星期日由上面的例子可以看出来，所有的批处理命令都使用GO作为结束标志。当编译器读到GO时，它就会把GO前面所有的语句当作一个批处理，而打包成一个数据包发送到服务器。GO本身并不是Transact-SQL语句组部分，它只是一个用于表示批处理结束的前端指令。当有多个批时,系统按批地顺序依次执行,关于批中有如下几点需要注意：

(1)一个批处理中若有多个存储过程,则从第二个存储过程开始,每个存储过程的前面都要加关键字EXECUTE或EXEC.(2)一般地，一个CREATE语句必须独自占用一个批处理，且必须以CREATE语句开始。

(3)若使用ALTERTABLE语句修改表的结构，则不能在同一个批中引用新定义或修改的列。第九页，共七十页，编辑于2023年，星期日执行一个批时，若其中有一个语句出现编译错误，则SQLServer将取消该批内所有语句的执行。但若一个批通过了编译而在运行时出现错误，则批内发生错误之前的语句将被执行，发生错误的语句不被执行，发生错误之后的语句视具体情况可能被执行或不被执行。因此，对于仅执行了一部分的批，可能引起整个过程发生错误或产生数据垃圾。第十页，共七十页，编辑于2023年，星期日为防止这类情况出现，可以在SQLServer编程中引入“事务”（将在下节讲到），将批处理放在一个事务中，并设置批处理发生错误时引发事务回滚，使所有尚未提交的操作都回滚到发生错误之前的状态。第十一页，共七十页，编辑于2023年，星期日事务是SQLServer的一个工作单元，事务通常以BEGINTRANSACTION开始，以COMMITTRANSACTION结束。如果在一个事务模块中有一个SQL语句执行失败或发生不该有的结果，可以通过设置回滚命令ROLLBACKTRANSACTION取消该语句导致的错误操作结果(例如取消已插入的数据或恢复已被修改的数据)，使数据库恢复到该错误语句执行前的状态。11．3事务

第十二页，共七十页，编辑于2023年，星期日[实例11.3]利用事务在第4章中的学生库的学生信息表中插入两行数据，在插入第2行数据后回滚到两次插入数据之间，然后查询。步骤：启动“查询分析器”，输入SQL语句：得到如下执行结果如图11.3所示11.3利用事务执行语句第十三页，共七十页，编辑于2023年，星期日然后对“课程”表进行查询，执行结果如下，如图11.4所示。图11.4事务执行后的查询结果第十四页，共七十页，编辑于2023年，星期日这个例子一共执行了两次插入操作，第一次操作完成后，设置了一个保存点，第二次插入操作执行完后，程序执行了事务回滚，使第二次的插入操作被取消，但是第一次插入操作依然有效，这样查询后就只能得到一行插入数据。如果不设置保存点，则默认回滚整个事务，两次插入操作都将被取消。第十五页，共七十页，编辑于2023年，星期日从上面的例子也可以看出，在一个事务中可以包含多个批处理，当然一个批处理也可以包含多个事务。两者的区别是，批语句的组合发生在编译阶段，而事务语句的组合发生在执行阶段。一个批若在编译阶段发生语法错误，则在运行阶段整个批都不能执行。若编译通过而在运行时发生错误，如违反主键惟一性约束等，则有两种情况：若没有设置事物回滚，默认时系统仅回滚发生错误的语句；若设置了事务回滚，则系统回滚事务。第十六页，共七十页，编辑于2023年，星期日由上面的例子我们可以看出，建立一个事务包含四个语句，即开始事务、设置保存点、回滚事务、提交事务。

(1)开始(或启动)一个事务单元。命令格式：

BEGINTRANSACTION单元名称

(2)在事务中设置一个保存点。保存点的设置可以使得回滚事务语句回滚到该保存点，以便取消该事务中的部分操作对数据库所引起的变动。命令格式：

SAVETRANSACTION保存点名称第十七页，共七十页，编辑于2023年，星期日(3)回滚一个事务单元。该语句用于取消事务中的部分或全部操作，使得这些操作对数据库所作的改动恢复为改动前的状态。命令格式：

ROLLBACKTRANSACTION单元名称|保存点名称

(4)提交(或完成)一个事务单元。提交一个事物后，在该事物中对数据库所做的变动被保存到数据库中。命令格式：

COMMITTRANSACTION单元名称第十八页，共七十页，编辑于2023年，星期日注意，一旦执行了COMMITTRANSACTION语句，则事务被提交，此时将不再能够回滚事务。在一个事务内允许有重复的保存点名称，但若在ROLLBACKTRANSACTION语句中使用重复的保存点名称，则事物只回滚到离它最近的使用该保存点名称的SAVETRANSACTION语句处。第十九页，共七十页，编辑于2023年，星期日

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

第二十页，共七十页，编辑于2023年，星期日局部变量是在批处理（也可以是存储过程或触发器）内由用户定义并使用的变量。用户在使用局部变量前必须事先声明，而它的使用范围也仅限于声明它的批处理（也可以是存储过程或触发器）以内。[实例11.4]

利用局部变量在数据库学生库的学生信息表中查询学生“和平”的年龄。步骤：启动查询分析器，输入如下SQL语句：按“F5”键或单击工具栏“执行查询”图标执行。执行结果如图11.5所示11.4.1局部变量

第二十一页，共七十页，编辑于2023年，星期日图11.5使用局部变量查询的结果第二十二页，共七十页，编辑于2023年，星期日从上面的例子，我们可以看出，通过声明局部变量可以查询学生信息表中任意的学生信息，只要修改赋给局部变量的值就可以。在使用局部变量查询需要做到两点：一是声明局部变量，二是给局部变量赋值。第二十三页，共七十页，编辑于2023年，星期日（1）声明局部变量命令格式：

DECLARE(@局部变量名数据类型[，….n])

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

SELECT@局部变量名=表达式

[FROM表名[,….n]WHEREclause][+·n)

或SET@局部变量名=表达式第二十四页，共七十页，编辑于2023年，星期日命令说明：表达式可以是一个具体的数据如数字、字符串等，也可以是一个表达式或另一个局部变量或全局变量，还可以是从一个查询语句中查询出来的数据。FROM子句用于向变量所赋的值源于由一个表中查询所得数据的情形。一个SELECT语句可以为多个变量赋值，但一个SET语句仅能为一个变量赋值。若使用SELECT语句为变量赋值，则不能与其查询功能同时使用。若使用SELECT语句从表中取数为变量赋值，则其返回的数据必须惟一，否则仅将最后一个数据赋给变量。第二十五页，共七十页，编辑于2023年，星期日下面的例子是使用SELECT语句进行赋值，从表格中查询出一条数据赋值给已经声明的变量：

USEpubsGODECLARE@authorvarchar(20)SELECT@author=au_idFROMauthorsWHEREstate=’MI’GO第二十六页，共七十页，编辑于2023年，星期日11.4.2全局变量

在SQLServer中全局变量作为一种特殊函数由系统预先定义，因此有的教材把全局变量放到函数部分介绍，称为SQLServer的内置函数。常用的全局变量有三十多个，用于记录数据库服务器的工作信息及性能数据。全局变量的作用范围是整个系统，通常利用全局变量来检测系统的设置值或执行查询命令后的状态值。第二十七页，共七十页，编辑于2023年，星期日[实例11.5]

使用全局变量来查询SQLServer实例当前使用的语言和当前所使用语言的标识符。第二十八页，共七十页，编辑于2023年，星期日

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

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

11．5程序控制语句

第三十页，共七十页，编辑于2023年，星期日11．5．1BEGIN…END语句

BEGIN…END语句相当于其他计算机语言中的大括号或复合语句，它将多条T-SQL语句封装成为一个整体的语句块，T-SQL中允许使用嵌套的BEGIN…END语句。通常把BEGIN…END语句放在语句IF…ELSE或者WHILE中，使其中的语句作为一个整体来执行。第三十一页，共七十页，编辑于2023年，星期日[实例11.6]在学生成绩表期中成绩中检查全体学生某门课程(课程编号为1001)的期中成绩。如果某个学生的成绩低于60分，则显示文本：“1001科目的分数低于60分”，并显示所有未及格学生的学号、姓名与期中成绩分数。第三十二页，共七十页，编辑于2023年，星期日命令格式：

BEGINT-SQL语句块

END

命令说明：BEGIN、END分别表示语句块的开始和结束。T-SQL语句块可以是一条或一条以上的T-SQL语句。第三十三页，共七十页，编辑于2023年，星期日11．5．2IF…ELSE语句

若希望在满足某种条件的情况下执行一些语句，而在该条件不满足的情况下执行另外一些语句。在这种情况下，我们就需要使用条件语句来在执行T-SQL语句时强加条件，来控制语句执行的顺序。第三十四页，共七十页，编辑于2023年，星期日[实例11.7]在学生信息表中统计学生的平均年龄，如果平均年龄小于20则输出年龄组合正常，否则输出大于平均年龄的学生的信息。第三十五页，共七十页，编辑于2023年，星期日由上例我们可归纳出IF...ELSE语句的命令格式：

IF返回布尔表达式{T-SQL语句|BEGIN…END语句块}[ELSE{T-SQL语句|BEGIN…END语句块}]命令说明：返回“真”(TRUE)或“伪”(FALSE)的布尔表达式。如果布尔表达式中含有SELECT语句，则必须用圆括号将SELECT语句括起来。T-SQL语句|BEGIN…END语句块：只能是一条。第三十六页，共七十页，编辑于2023年，星期日11.5.3CASE语句

当有多个选择方案时，如果使用IF…ELSE语句进行选择判断，则由于需要层层嵌套而非常不便，此时可以在SELECT语句中采用CASE结构。CASE语句有两种格式：简单CASE函数与CASE搜索函数。前者将某个表达式与一组简单表达式进行比较以确定结果；后者则需计算一组布尔表达式以确定结果。下面用实例加以说明。第三十七页，共七十页，编辑于2023年，星期日[实例11.8]在pubs数据库中根据作者来自的州的名字，打印出作者的姓名和州的全名第三十八页，共七十页，编辑于2023年，星期日命令格式：CASE输入表达式

WHEN比较的表达式THEN结果表达式[…n][ELSE返回结果表达式]END

命令说明；CASE后面的输入表达式，用于进行比较。WHEN后面给出用于进行比较的表达式的数据类型必须相同，或者能够隐性转换。[…n)表示可以有多个WHEN…THEN结构。第三十九页，共七十页，编辑于2023年，星期日[实例11.9]

显示学生成绩中的期中成绩分数。第四十页，共七十页，编辑于2023年，星期日由上例可见，CASE搜索函数的WHEN后表达式是布尔表达式，这是与简单CASE函数不同之处。命令格式；CASEWHEN逻辑表达式THEN[ELSE结果表达式]END第四十一页，共七十页，编辑于2023年，星期日11．5．4WHILE语句

WHILE语句是一种条件循环语句，在满足条件时执行循环体，不满足条件时跳出循环结构。WHILE语句可以嵌套执行，嵌套时首先运行内层循环，内层循环结束之后再运行外层循环。第四十二页，共七十页，编辑于2023年，星期日[实例11.10]在pubs数据库中，将书价小于$30的书提高50%，如果提高后的书价超过$50，则输出书价太贵。第四十三页，共七十页，编辑于2023年，星期日由此，我们可给出命令格式：

WHILE逻辑表达式BEGIN

表达式

[BREAK][CONTINUE]END

命令说明：当逻辑表达式为真时，服务器将重复执行SQL语句组。BREAK的作用是在某些情况发生时，立即无条件地跳出循环，并开始执行紧接在END后面的语句。CONTINUE的作用是在某些情况发生时，跳出本次循环，开始执行下一次循环。第四十四页，共七十页，编辑于2023年，星期日11．5．5RETURN语句

RETURN语句用于从当前过程、批处理或语句块中无条件退出，并不再执行位于RETURN之后的语句。命令格式：

RETURN整型表达式命令说明：RETURN一般用于从存储过程给调用过程或应用程序返回整型值。除非特别指明，所有系统存储过程返回0值表示成功，返回非0值则表示失败。第四十五页，共七十页，编辑于2023年，星期日11.6游标

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

[实例11.11]

定义游标，打开学生信息表。第四十七页，共七十页，编辑于2023年，星期日上面的例子首先通过DECLARE关键字来定义游标，然后选择一个表，打开游标，就可以查询到当前表中记录的个数。其中@@cursor_row表示当前游标记录数目是一个全局变量。由此，我们给出打开游标的命令格式：

DECLARE游标名称[INSENSITIVE][SCROLL]CURSORFORSELECT语句

[FORREADONLY|UPDATE[OF列名1，列名2，列名3…]]第四十八页，共七十页，编辑于2023年，星期日命令说明：游标名：为声明游标所起的名字。INSENSITIVE：使用INSENSITIVE定义的游标，把提取出来的数据放入一个在tempdb数据库里创建的临时表里。任何通过这个游标进行的操作，都在这个临时表里进行。所以所有对基本表的改动都不会在用游标进行的操作中体现出来。SCROLL：使用SCROLL关键字定义的游标，具有包括如下所示的所有取数功能：FIRST：取第一行数据；LAST：取最后一行数据；PRIOR：取前一行数据；NEXT：取最后一行数据；RELATIVE：按相对位置取数据；ABSOLUTE：按绝对位置取数据。如果没有在声明时使用SCROLL关键字，那么所声明的游标只具有默认的NEXT功能。第四十九页，共七十页，编辑于2023年，星期日SELECT语句：一些标准的SELECT语句，主要用来定义游标所要进行处理的结果集。在声明游标的SELECT语句中，不允许使用如COMPUTE，COMPUTEBY和INTO等关键字。FORREADONLY：声明只读光标。不允许通过只读光标进行数据的更新。UPDATE[OF列名1，列名2，列名3…]：定义在这个游标里可以更新的列。如果定义了[OF列名1，列名2，列名3…]，那么只有列在表中的列可以被更新；如果没有定义[OF列名1，列名2，列名3…]，那么游标里的所有列都可以被更新。第五十页，共七十页，编辑于2023年，星期日11.6.2打开游标

在可以使用游标之前，必须首先打开游标。开游标的语法如下：

OPENcursor_name使用关键字OPEN来打开游标，cursor_name为打开游标的名字。具体例子可参考[实例11.11]第五十一页，共七十页，编辑于2023年，星期日11．6．3关闭与释放游标

在打开游标后，SQLServer服务器会专门为游标开辟一定的内存空间存放游标操作的数据结果集，同时游标的使用也会根据具体情况对某些数据进行封锁。所以，在不使用游标的时候，一定要关闭游标，以通知服务器释放游标所占用的资源。关闭游标的语法：

CLOSEcursor_name第五十二页，共七十页，编辑于2023年，星期日关闭游标后可以再次打开游标，在一个批处理中也可以多次打开和关闭游标。而游标本身也会占用一定的计算机资源，所以在使用完游标后，为了回收被游标占用的资源，应该将游标释放。释放游标的语法是：

DEALLOCATEcursor_name

当释放完游标后，如果要重新使用这个游标必须重新执行声明游标的语句。第五十三页，共七十页，编辑于2023年，星期日11．6．4使用游标

在打开游标以后，就可以打开游标通过FETCH语句提取数据了。[实例11.12]根据例11.11使用绝对位置以及相对位置来提取信息。第五十四页，共七十页，编辑于2023年，星期日

FETCH语句的目的是从T-SQL服务器游标中检索特定的记录。使用游标提取数据的语法如下：

FETCH[[NEXT∣PRIOR∣FIRST∣LAST∣ABSOLUTE{n∣@nvar}∣IRELATIVE{n∣@nvar}FROM]{{游标名称INTO变量第五十五页，共七十页，编辑于2023年，星期日命令说明：NEXT：从当前记录立即返回结果集合以及对返回的记录增加当前记录。如果FETCINEXT是通过游标第一次提取，返回结果集合的第一条记录。NEXT是提取游标选项的默认选项。在处理当前记录之前立即返回记录以及减少返回记录的当前记录数目。PRIOR：读取当前游标指针所指向行的上一行数据，并将游标指向该上一行。如果FETCHPRIOR是通过游标第一次提取，没有记录返回并且光标停留在第一行。FIRST：返回第一条记录并且作为当前记录。LAST：返回最后一条记录并作为当前记录。ABSOLUTE{n∣@nva}:如果n或者@nva正数，返回从前面游标起的第n记录，并将其作为当前记录；如果为负数，那么返回当前游标前面的第n条记录；如果是0，则没有记录返回。RELATIVE{n∣@nvar}：相对当前游标位置。第五十六页，共七十页，编辑于2023年，星期日[实例11.13]

修改第5行的班级内容：第五十七页，共七十页，编辑于2023年，星期日11．7用户自定义函数

在SQLServer2000中，为了使逻辑编程更为简洁清晰，设计了函数的功能。有两类函数，系统内置函数和用户自定义函数。系统内置函数包括数学函数(如max、min、count等)、字符串函数(如len、left等)、日期函数(如year、getdate等)、系统函数(如user)，这些系统内置函数。第五十八页，共七十页，编辑于2023年，星期日除了可以使用系统提供的内置函数外，用户可以定义自己的函数，来补充和扩展系统支持的内置函数。用户自定义函数可以像系统函数一样在查询或存储过程中使用，也可以想存储过程一样通过EXECUTE命令来执行。第五十九页，共七十页，编辑于2023年，星期日11．7．1创建和调用用户自定义函数

[实例11.14]创建一个函数，根据是否为团员返回不同的数据，如果输入为1，即为团员则返回该团员的信息，否则返回非团员的信息。第六十页，共七十页，编辑于2023年，星期日语句如下：

CREATEFUNCTIONee(@yongBIT)RETURNS@infoTABLE/*定义返回的表结构*/(学号char(7),

姓名char(8),

期末成绩int)ASBEGINIF@yong=1INSERT@info/*返回数据*/SELECT学生信息.学号,姓名,期末成绩

FROM学生信息,学生成绩

WHERE是否团员=1and学生信息.学号=学生成绩.学号ELSEINSERT@info/*返回数据*/SELECT学生信息.学号,姓名,期末成绩

FROM学生信息,学生成绩

WHERE是否团员=0and学生信息.学号=学生成绩.学号RETURNENDGOUSE学生库SELECT*FROMdbo.ee(1)GO第六十一页，共七十页，编辑于2023年，星期日我们可以给出创建函数的命令格式：

CREATEFUNCTION[所有者]函数的名称

([{自定义函数的参数[AS]定义函数参数的数据类型])<function_option>：RETURNS函数返回值的数据类型

[WITH<函数选项>[[，]．．．n]][AS]BEGIN

函数体

RETURN标量值表达式

END：={ENCRYPTION|SCHEMABINDING}第六十二页，共七十页，编辑于2023年，星期日11．7．2修改和删除用户自定义函数

删除用户自定义的函数的语法是：

DROPFUNCTION{[owner_name.]function_name}[,...n]命令说明：

(1)DROPFUNCTION：删除函数命令的关键字。

(2)owner_name：删除表的权力仅属于表的拥有者，且不能转移。

(3)function_name：函数的名称。第六十三页，共七十页，编辑于2023年，星期日[实例11.15]将对前面定义的ee函数进行修改，修改为如果为团员则输出期末成绩大于80的学生的信息。语句如下：ALTERFUNCTIONee(@yongBIT)/*修改函数ee*/RETURNS@infoTABLE/*定义返回的表结构*/(学号char(7),

姓名char(8),

期末成绩int)AS第六十四页，共七十页，编辑于2023年，星期日BEGINIF@yong=1INSERT@info/*返回数据*/SELECT学生信息.学号,姓名,期末成绩

FROM学生信息,学生成绩

WHERE是否团员=1and学生信息.学号=学生成绩.学号and期末成绩>80ELSEINSERT@info/*返回数据*/SELECT学生信息.学号,姓名,期末成绩

FROM学生信息,学生成绩

WHERE是否团员=0and学生信息.学号=学生成绩.学号RETURNENDGO第六十五页，共七十页，编辑于2023年，星期日11.8本章总结

SQLServer支持结构化的编程方法，可以进行逻辑编程，处理各种结构的编程，从而完成较为复杂的作业。在本章中主要讲述了批处理、事务、变量等概念，目的是让大家在进行程序设计时使用这些SQLServer本身特有的方法以达到进行复杂程序设计的目的。另外还介绍了5种程序控制语句，游标的使用方法以及用户自定义函数。大家应该对SQLServer的编程逻辑有清楚的认识，能够胜任复杂的数据查询和修改工作。第六十六页，共七十页，编辑于2023年，星期日11.9思考与实训练习

11.9.1思考题1．什么叫批处理，什么叫事务，它们之间的关系是什么？2．执行一个批处理时，如果批处理内有一个语句错误，那么批内的其他语句是否还执行？3．执行一个事务时，如果其内部发生错误，但又没与设置回滚，是否这些错误会被提交？4．SQLServer