数据库原理与 SQL Server

数据库原理与SQL Server 第5章 Transact-SQL编程

第5章 Transact-SQL编程 • 5.1 进一步了解编程语言 —Transact-SQL • 5.2 设计程序—流程控制语句 • 5.3 分行处理程序—游标实训Transact-SQL语言编程

5.1 进一步了解编程语言—Transact-SQL 5.1.1 定义批处理语句批处理是一个或多个SQL语句的集合，从程序一次性发送到SQL Server 2000并编译为一个可执行单元，一次性执行。如果一个批处理中任何一条语句有语法错误，则整个批处理将不能编译和执行。语法格式： GO

5.1.2 变量 SQL Server将变量分为局部变量和全局变量。 1.局部变量局部变量是用户定义，必须以@开头，在程序内声明，并只能在该程序内使用。 (1)局部变量的声明 DECLATE @<局部变量名> <数据类型>[,…n] (2)局部变量的赋值 SET|SELECT @<局部变量名>=<表达式>

5.1.2 变量 例5-1 声明一个datetime类型的局部变量。 DECLARE @date_var datetime 例5-2 声明两个局部变量。 DECLARE @var1 int , @var2 money 例5-3 用SET语句和SELECT语句为局部变量赋值。 DECLARE @var1 datetime,@var2 char(10) SET @var1 = getdate() SELECT @var2 = convert(char(10),@var1,102) 例5-4 用SET语句将查询结果赋给局部变量并用SELECT语句显示局部变量的值。 declare @date_var datetime set @date_var=(select min(birthday) from s) select @date_var as min_birthday

5.1.2 变量 2.全局变量全局变量是SQL Server系统内部使用的变量，以@@开头。例5-5用全局变量查看SQL Server的版本、当前所使用的SQL Server服务器的名称以及所使用的服务名称等信息。脚本： print '目前所用SQL Server的版本信息如下:' print @@VERSION print '目前SQL Server服务器名称为：'+@@SERVERNAME print '目前所用服务器为：'+@@SERVICENAME

5.1.3 注释语句 注释语句是对程序代码的说明或暂时禁用，是程序代码中不编译执行的语句。语法格式： --<注释文本> 或 /* <注释文本> … */

5.1.4 输出语句 需要查看程序结果时，可以使用输出语句。语法格式： PRINT <表达式> 例5-6输出变量的值。脚本： declare @date_var datetime set @date_var=(select min(birthday) from s) print @date_var

5.1.5 定义语句块语句 在控制流程中需要执行两条或两条以上的语句，应该将这些语句定义为一个语句块（称为复合语句）。语法格式： BEGIN <SQL语句>|<语句块> END

5.2 设计程序—流程控制语句 一、选择结构二、循环结构三、转移语句四、等待语句五、返回语句

5.2.1 选择结构 选择结构可以使用条件语句来实现。语法格式： IF <布尔表达式> <SQL语句>|<语句块> [ELSE <SQL语句>|<语句块>]

5.2.1 选择结构 例5-7 查询学号为1001的学生。脚本： if exists(select sno from s where sno='0001') print '找到' else print '未找到' 例5-8 条件语句的嵌套。脚本：

5.2.2 循环结构 循环结构可以使用循环语句来实现。语法格式： WHILE <布尔表达式> <SQL语句>|<语句块> 中断语句：BREAK 短路语句：CONTINUE

5.2.2 循环结构 例5-9 求1~10之间的素数和。脚本：例5-10 求100~200之间的全部素数。脚本：

5.2.3 转移语句 转移语句将程序的执行流程无条件转移到指定的标号处。语法格式： GOTO <标号> 定义标号时，应在标号名后面加上冒号。 GOTO语句常用在循环语句和条件语句内，使程序跳出循环或进行分支处理。

例5-11 求10的阶乘。 脚本： DECLARE @s int,@times int set @s=1 set @times=1 label1: set @s=@s*@times set @times=@times+1 if @times<=10 goto label1 print '结果为:'+str(@s)

5.2.4 等待语句 等待语句挂起一个程序中语句的执行，直到指定的某一时间点到来或在一定的时间间断之后才继续执行。语法格式： WAITFOR DELAY '<时间间隔>'|TIME '<时间>' 其中，时间间隔以及时间均为datetime类型，格式为“hh:mm:ss”，分别说明等待的时间长度和时间点，在time内不能指定日期。

5.2.4 等待语句 例5-12 设置等待一小时后执行查询。脚本： begin waitfor delay '1:00:00' select * from s end 例5-13 设置到十点整执行查询。脚本： begin waitfor time '10:00:00' select * from s end

5.2.5 返回语句 返回语句结束执行，使程序无条件返回，其后面的语句不再执行。语法格式： RETURN [<整数表达式>] 存储过程可以使用RETURN语句向调用者返回一个整数值。在SQL Server 2000中，存储过程返回值为0时，表示存储过程成功执行。

5.3 分行处理程序—游标 在数据库开发过程中，使用SELECT语句查询，得到一个结果集，对这个结果集中的不同数据行，可能要做不同的处理。也就是要逐一处理每一个数据行。游标提供了一种比较好的解决方案。

5.3.1 游标概述 1.概念游标是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。 2.种类 Transact-SQL游标、 API游标、客户游标。

1. Transact-SQL游标 Transact-SQL游标是由DECLARE CURSOR语句定义的，主要用在Transact-SQL脚本、存储过程和触发器中。Transact-SQL游标主要用在服务器上，由从客户端发送给服务器的Transact-SQL 语句或是批处理、存储过程、触发器中的Transact-SQL进行管理。Transact-SQL游标不支持提取数据块或多行数据。

2. API游标 API游标支持在OLE DB、ODBC以及DB_library中使用游标函数，主要用在服务器上。每一次客户端应用程序调用API游标函数，SQL Server的OLE DB提供者、ODBC驱动程序或DB_library的动态链接库DLL，都会将这些客户请求传送给服务器，以对API游标进行处理。

3. 客户游标 客户游标主要用在当需要在客户机上缓存结果集时使用。在客户游标中有一个默认的结果集，被用来在客户机上缓存整个结果集。客户游标仅支持静态游标而非动态游标，由于服务器游标并不支持所有Transact-SQL语句或批处理，所以客户游标常常仅被用作服务器游标的辅助。因为在一般情况下，服务器游标能支持绝大多数的游标操作。

5.3.2 使用游标 操作步骤: 1. 声明游标（变量）。 2. 打开游标。 3. 从一个游标中提取信息。 4. 关闭（释放）游标。

1. 声明游标 语法格式： DECLARE <游标名> [INSENSITIVE] [SCROLL] CURSOR FOR <SELECT语句> [FOR {READ ONLY | UPDATE [OF <列名> [,...n]]}]

1. 声明游标 例5-14 声明标准游标。脚本：declare cur_c cursor for select cno,cname,credit from c 例5-15 声明只读游标。脚本：declare cur_c cursor for select cno,cname,credit from c for read only 例5-16 声明更新游标。脚本：declare cur_c cursor for select cno,cname,credit from c for update

2. 声明游标变量 语法格式： DECLARE @<变量名> CURSOR 建立游标变量与游标之间的关联。方法1：先声明游标和游标变量，然后用SET语句将游标赋给游标变量。例：DECLARE c1 CURSOR FOR SELECT * FROM s DECLARE @cur_var CURSOR SET @cur_var = c1 方法2：不声明游标，直接用SET语句将游标定义信息赋给游标变量。例： DECLARE @cur_var CURSOR SET @cur_var = CURSOR FOR SELECT * FROM s 当游标变量和游标关联后，就可用游标变量代替游标名称。

3. 打开游标 游标声明后，如果要从游标中读取数据，必须打开游标。语法格式： OPEN [GLOBAL] <游标名>|<游标变量名> 其中，GLOBAL表示要打开的是一个全局游标。

4. 读取游标中的数据 当游标被打开后，就可以从游标中逐行地读取数据。语法格式： FETCH [[NEXT|PRIOR|FIRST|LAST|ABSOLUTE {n|@nvar} |RELATIVE {n|@nvar}] FROM ] {{[GLOBAL] <游标名>}|<@游标变量>} [INTO @<变量名>[,...n]]

5. 关闭游标 • 使用CLOSE命令关闭游标 (2) 自动关闭游标

(1) 使用CLOSE命令关闭游标 处理完游标中数据后，必须关闭游标来释放数据结果集和定位于数据记录上的锁。语法格式: CLOSE [GLOBAL] <游标名>|@<游标变量> CLOSE语句可以关闭游标，但不释放游标的数据结构。如果要再次使用游标，可用OPEN命令重新打开。

(2) 自动关闭游标 如果在声明游标与释放游标之间使用了事务结构，则在结束事务时游标会自动关闭。避免自动关闭的方法是使用语句： SET CURSOR_CLOSE_ON_COMMIT OFF

6. 释放游标 用CLOSE命令关闭游标并没有释放游标占用的数据结构。使用DEALLOCATE命令将释放游标占用的数据结构，游标使用的任何资源也随之释放。语法格式： DEALLOCATE [GLOBAL] <游标名>|@<游标变量> 游标的关闭指释放游标的结果集所占用的资源，游标的释放指释放游标占用的所有资源，当然也包括结果集占用的资源。

6. 释放游标 例5-17 DEALLOCATE的作用。脚本：

5.3.3 游标应用实例 1. 用于游标的UPDATE语句的语法格式： UPDATE <表名> SET <列名> = {<表达式>|NULL|<select子句>}[,…n] WHERE CURRENT OF <游标名> 2. 用于游标的DELETE语句的语法格式： DELETE FROM <表名> WHERE CURRENT OF <游标名>

5.3.3 游标应用实例 例5-18 游标的定位修改。脚本：例5-19 游标的定位删除。脚本：例5-20 将课程号为“c001”的课程成绩上浮5%。脚本：

实训 Transact-SQL语言编程 实验名称：Transact-SQL语言编程目的要求：掌握用Transact-SQL语句编写程序的方法操作步骤： (1) 启动查询分析器。 (2) 用SQL语句编写程序，求前多少个自然数之和不超过5000，并存储为脚本文件。 (3) 用SQL语句编写程序，输出所有的水仙花数，并存储为脚本文件。所谓水仙花数是指一个三位数，其各位数字的立方和等于该数本身。 (4) 使用游标将学号为“1001”的学生的所有课程成绩减5分。

数据库原理与 SQL Server