第 10 章 SQL Server 程序设计

第10章 SQL Server程序设计 本章要点 • 领会批处理、脚本、事务 • 学会变量的使用方法 • 掌握程序控制语句的特点及使用方法 • 学会编写简单的SQL程序

10.1 程序中的批处理、脚本、注释 SQL srever的编程语言就是T-SQL 语言，是一种非过程化的语言。不论是普通的客户/服务器应用程序，还是web应用程序，都必须通过服务器发送T-SQL语言才能实现与SQL srever的通信。

一、批处理 批处理就是一个或多个T_SQL语句，两个GO之间的SQL语句作为一个批处理。在一个批处理中可以包含一条或多条Transact-SQL语句，成为一个语句组。这样的语句组从应用程序一次性地发送到SQL Server服务器进行执行。SQL Server服务器将批处理编译成一个可执行单元，称为执行计划。

批处理的特点 1、一次发给数据库服务器的所有的SQL语句，由GO作为结束标志。 2、编译成一个可执行单元——执行计划。 3、执行时，每次执行计划中的一条语句。注意：如果在一个批处理中，某条语句存在语法错误，SQL Server将不执行批处理中的任何语句。

二、脚本 脚本是存储在文件中的一系列SQL 语句，是一系列按顺序提交的批处理。使用脚本可以将创建和维护数据库的操作步骤保存为磁盘文件，其扩展名为.sql。查询分析器是建立、编辑和使用脚本的一个最好的环境。

三、注释 在SQL Server中，有两种类型的注释字符，用来说明程序内容的语句，不能执行且不参与程序的编译。单行注释（行内）：使用两个连在一起的减号“- -”作为注释符；多行注释（块）：使用“/* */”作为注释符，用来标志多行。

例：利用查询分析器执行两个批处理，用来显示学生表中的信息及学生记录个数。例：利用查询分析器执行两个批处理，用来显示学生表中的信息及学生记录个数。 use student go print ‘学生表包含信息如下:’ select * from 学生 print ‘学生表记录个数为：’ SELECT COUNT(*) FROM 学生 go --PRINT语句用于显示char、varchar类型 /*或可自动转换为字符串类型的数据*/

10.2 SQL Server变量 变量是SQL Server用来在语句之间传递数据的方式之一,是一种语言中必不可少的组成部分。Transact-SQL语言中有两种形式的变量，一种是用户自己定义的局部变量，以@字符开始；另外一种是系统提供的全局变量，以@@字符开始。

一、全局变量 全局变量是SQL Server系统提供并赋值的变量，是SQL Server系统内部使用的变量。全局变量其作用范围并不仅仅局限于某一程序，而是任何程序均可以随时调用。全局变量通常存储一些SQL Server的配置设定值和统计数据。用户可以在程序中用全局变量来测试系统的设定值或者是Transact-SQL命令执行后的状态值。

SQL Server提供的全局变量共有33个，在使用全局变量(是一组特殊的函数)时应该注意以下几点： • 全局变量是在服务器级定义的。 • 用户只能使用预先定义的全局变量。 • 引用全局变量时，必须以标记符“@@”开头。 • 全局变量对用户来说是只读的。 • 局部变量的名称不能与全局变量的名称相同。

例：利用全局变量查看sql server的版本、当前所使用的sql server服务器的名称以及所使用的服务器的服务名称等信息。代码如下： print ‘目前所用sql server的版本信息’ print @@version 版本 print ’目前所用sql server服务器的名称:‘+ @@servername 服务器的名称 print ’目前所用服务器的服务名称:‘+ @@servicename 服务名称 go

二、局部变量 变量：在程序运行过程中其值是变化的量。局部变量是一个能够拥有特定数据类型的对象，它的作用范围仅限制在程序内部。局部变量可以作为计数器来计算循环执行的次数，或是控制循环执行的次数。另外，利用局部变量还可以保存数据值，以供控制流语句测试以及保存由存储过程返回的数据值等。局部变量被引用时要在其名称前加上标志“＠”，而且必须先由用户自己定义和赋值后才可以使用。

局部变量的定义: DECLARE @局部变量名数据类型 […n] 局部变量的赋值方法 : SET @局部变量 =表达式或者 SELECT @局部变量=表达式 [ ,...n ]

局部变量（以@开头;使用DECLARE声明） 例： DECLARE @var1 int,@var2 money SELECT @var1=50,@var2=$29.95 SELECT @var1,@var2 全局变量（也称配置函数，是系统定义的变量，以@@开头；用户只能使用）例如：@@version @@rowcount

例：声明两个局部变量，并对它们进行赋值， 然后将变量的值显示出来。代码如下： declare @nowdate char(6),@disp varchar(30) set @nowdate=getdate() set @disp=‘现在的日期为’： print @disp+@nowdate

10.3 程序流程控制语言 流程控制语句是指那些用来控制程序执行和流程分支的命令，在SQL Server 中，流程控制语句主要用来控制SQL语句、语句块或者存储过程的执行流程。

一、 BEGIN…END语句 其语法形式为： BEGIN 语句1 语句2 END 语句序列

说明： BEGIN…END语句能够将多个Transact-SQL语句组合成一个语句块，并将它们视为一个单元处理。在条件语句和循环等控制流程语句中，当符合特定条件便要执行两个或者多个语句时，就需要使用BEGIN…END语句,将多个T-sql语句组合成一个语句块。

二、 IF…ELSE语句 IF 条件表达式语句1 ELSE 语句2

说明： IF…ELSE语句是条件判断语句，其中，ELSE 子句是可选的，最简单的IF语句没有ELSE子句部分。 IF…ELSE语句用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。SQL Server 允许嵌套使用IF…ELSE语句，而且嵌套层数没有限制。

例：Use market if (select 库存量 from goods where 货品名称=‘彩电’)<30 Begin Declare @number1 int Set @number1=(select 库存量 from goods where 货品名称=‘彩电’) -- 将彩电的库存量赋给内存变量 Print ‘彩电的库存量为:’+cast(@number1 as char(3)) Print ‘该商品库存量已不足，提醒进货!’ end /*cast函数用于将某种数据类型的表达式转换为另一种数据类型。*/

三、CASE表达式 CASE表达式能够实现多重选择的计算，并将其中一个符合条件的结果表达式返回。CASE表达式按照使用形式的不同，可以分为简单CASE表达式和搜索CASE表达式。

1.简单 CASE语句 语法形式为： CASE 测试表达式 WHEN 测试值1 THEN 结果表达式1 WHEN 测试值2 THEN 结果表达式2 [ ELSE 结果表达式n+1] END 2.搜索CASE语句语法形式为：CASE WHEN 表达式1 THEN 结果表达式1 WHEN 表达式2 THEN 结果表达式2 [ ELSE 结果表达式n+1] END

使用简单CASE表达式示例 USE MARKET Select 货品名称，供应商，库存量，状态= CASE 状态 when 0 then ’库存无’ when 1 then ’库存有’ END FROM goods

使用搜索CASE表达式示例： 根据商品价格判别商品档次。代码如下： USE MARKET Select 货品名称，供应商，价格评价= CASE when（价格>=5000）then’高档商品’ when（价格>=1500）then’中档商品’ when（价格>=900）then’中档商品’ when（价格>=100）then’低档商品’ ElSE’小商品’ END FROM goods

四、 WAITFOR语句 WAITFOR语句用于暂时停止执行SQL语句、语句块或者存储过程等，直到所设定的时间已过或者所设定的时间已到才继续执行。 WAITFOR语句的语法形式为： WAITFOR DELAY 'time' | TIME 'time' 其中：DELAY用于指定时间间隔，TIME用于指定某一时刻，其数据类型为datetime，在datetime 数据中不允许有日期部分，即采用‘hh:mm:ss’格式。

本例演示了WAITFOR语句的语法 SELECT’执行waitfor之前，秒数为：’DATEPART（SECOND，GETDATE（）），’执行waitfor之前，时间为：’= GETDATE（） go Waitfor delay’00:00:05’延时5秒 Select ’执行waifor之后，秒数为：‘=DATEPART(SEXOND,GETDATE()),’执行waitfor之后，时间为：‘=GETDATE（）

五、PRINT语句 PRINT语句的作用是向用户端返回用户定义的消息。语法格式： PRINT ‘字符串’|局部变量|全局变量

六、WHILE语句 在程序中当需要多次重复处理某项工作时，就需使用WHILE循环语句。WHILE语句通过布尔表达式来设置一个循环条件，当条件为真时，重复执行一个SQL语句或语句块，否则退出循环，继续执行后面语句。While语句的语法格式为：

语法格式如下： WHILE 布尔表达式 Begin 语句序列1 [ BREAK ] 语句序列2 [ CONTINUE ] 语句序列3 End

DECLARE @i INT,@e INT SET @i=1 SET @e=0 WHILE @i<=100 -- 循环条件恒为真 BEGIN IF @i%7=0 /*如果@I能够被7整除*/ begin SET @e= @e+1 SET @i= @i+1 CONTINUE --使WHILE循环重新执行 END IF @ i>=100 - -当i>=100时结束整个循环 BREAK ELSE SET @ i=@ i+1 END PRINT’1~100之间能被7整除的数共有‘+ CONVERT（VARCHAR, @ e)+’ 个‘

七、RETURN语句 RETURN语句用于无条件地终止一个查询、存储过程或者批处理，此时位于RETURN语句之后的程序将不会被执行。 RETURN语句的语法形式为： RETURN 表达式

10.4.2用户自定义函数 在SQL Server中，用户不仅可以使用标准的内置函数，也可以使用自己定义的函数来实现一些特殊的功能。在创建时需要注意：函数名在数据库中必须唯一，其可以有参数，也可以没有参数，其参数只能是输入参数，最多可以有1024参数。 • 标量函数：返回单个数据值。 • 表值函数：返回值是一个记录集合——表。在此函数中，return语句包含一条单独的select语句。

10.4.2用户自定义函数 1、使用CREATE FUNCTION语句创建用户自定义函数语法格式如下： CREATE FUNCTION 函数名 ( [ { @变量数据类型} [ ,...n ] ] ) RETURNS 返回值的数据类型 [ AS ] BEGIN 语句 RETURN 返回值表达式 END

例：在studenet库中创建一个用户自定义标量值函数xuefen，该函数通过输入成绩来判断是否取得学分，当成绩大于等于60时，返回取得学分，否则，返回未取得学分。例：在studenet库中创建一个用户自定义标量值函数xuefen，该函数通过输入成绩来判断是否取得学分，当成绩大于等于60时，返回取得学分，否则，返回未取得学分。 CREATE FUNCTION xuefen(@cj int) RETURNS nvarchar(10) BEGIN declare @str nvarchar(10) if @cj >=60 set @str='取得学分' else set @str='未取得学分' return @str END GO

使用刚才定义的xuefen函数来查看课程号为“0004”使用刚才定义的xuefen函数来查看课程号为“0004” 的课程，学生获得学分的情况。在查询编辑器中输入如下代码： USE studenet GO SELECT 学号,成绩,dbo.xuefen(成绩) AS 学分情况 FROM 课程注册 WHERE 课程号='0004' GO

2.表值函数 表值函数遵循的原则： • RETURNS子句仅包含关键字table。不必定义返回变量的格式，因为它由RETURN 子句中的 SELECT 语句的结果集的格式设置。 • 语句体不由BEGIN和END分隔。 • RETURN子句在括号中包含单个SELECT语句。SELECT语句的结果集构成函数所返回的表。

例：在STUDENT库中创建一个内嵌表值函数XSH，该函数可以根据输入的系部代码返回该系学生的基本信息。其代码如下：例：在STUDENT库中创建一个内嵌表值函数XSH，该函数可以根据输入的系部代码返回该系学生的基本信息。其代码如下： CREATE FUNCTION XSH (@zymc char(4)) RETURNS table AS RETURN ( SELECT 学号, 姓名, 入学时间 FROM 学生 WHERE 专业=@zymc) GO

建立好该内嵌表值函数后，就可以象使用表或视图一样来使用它：建立好该内嵌表值函数后，就可以象使用表或视图一样来使用它： SELECT * FROM DBO.XSH (‘计算机网络') GO

10.4.3查看、修改和删除自定义函数 1.查看用户自定义函数的属性语法格式为： sp_helptext 用户自定义函数名使用sp_help查看用户自定义函数的一般信息，其语法格式为： sp_help 用户自定义函数名

10.4.3查看、修改和删除自定义函数 例：使用有关系统过程查看STUDENT数据库中名为XUEFEN的用户自定义函数的文本信息。其程序代码如下： USE STUDENT GO SP_HELPTEXT XUEFEN GO

10.4.3 查看、修改和删除自定义函数 使用T-SQL命令删除用户自定义函数使用DROP命令可以一次删除多个用户自定义函数，其语法格式为： DROP FUNCTION 函数名称[,…n]

10.5 程序中的事务 1. 概述 1）事务事务是最小的工作单元。这个工作单元要么成功完成所有操作，要么就是失败，并将所做的一切复原。 2）事务特性原子性（Atomic）一致性（ConDemoltent）独立性（Isolated）持久性（Durable）

10.5 程序中的事务 3）事务类型 ※显式事务：显示事务是手工配置的事务。用保留字标识显式事务的开始和结束。开始显式事务，首先输入关键词BEGIN TRAN。结束显示事务，使用COMMIT TRAN。取消事务，使用ROLL BACK TRAN命令。

10.6 游标 游标是处理数据的一种方法，可以看作是一个表中的记录的指针，作用于SELECT 语句生成的记录集，能够实现在记录集中逐行向前或者向后访问数据。使用游标，可以在记录集中的任意位置显示、修改和删除当前记录的数据。 1.游标的基本操作包含5部分内容：声明游标、打开游标、提取数据、关闭游标和释放游标。

1）声明游标 游标在使用之前需要声明，以建立游标。声明游标的语法格式为： DECLARE 游标名 CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] FOR select语句 [ FOR UPDATE [ OF 列名 [ ,...n ] ] ]

2）打开游标 创建游标之后，使用之前需要打开游标，才能从游标中提取数据。打开游标的语法格式为： OPEN 游标名游标在打开状态下，不能再被打开，也就是OPEN 命令只能打开已声明但尚未打开的游标。打开一个游标以后，可以使用全局变量@@ERROR判断打开操作是否成功，如果返回值为0，表示游标打开成功，否则表示打开失败。当游标被成功打开时，游标位置指向记录集的第一行之前。游标打开成功后，可以使用全局变量@@CURSOR_ROWS返回游标中的记录数。

3）提取数据 游标被成功打开后，就可以使用FETCH命令从中检索特定的数据。提取游标中数据的语法格式为： FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n}|RELATIVE{n}]FROM] 游标名 [ INTO @Variable_name [ ,...n ] ]

4）关闭游标 游标使用完毕后，应该关闭游标，释放当前结果集，以便释放游标所占用的系统资源。关闭游标语法格式为： CLOSE 游标名关闭游标后，系统删除了游标中所有的数据，所以不能再从游标中提取数据。但是，可以再使用OPEN命令重新打开游标使用。在一个批处理中，可以多次打开和关闭游标。

5）释放游标 如果一个游标确定不再使用时，可以将其删除，彻底释放游标所占系统资源。释放游标的语法格式为： DEALLOCATE 游标名释放游标即将其删除，如果想重新使用游标就必须重新声明一个新的游标。

第 10 章 SQL Server 程序设计