山西财贸职业技术学院

1、数据库原理与SQL Server 第5章 Transact-SQL编程1第5章 Transact-SQL编程5.1 进一步了解编程语言 Transact-SQL5.2 设计程序流程控制语句 5.3 分行处理程序游标实训 Transact-SQL语言编程25.1 进一步了解编程语言 Transact-SQL5.1.1 定义批处理语句 批处理是一个或多个SQL语句的集合，从程序一次性发送到SQL Server 2000并编译为一个可执行单元，一次性执行。如果一个批处理中任何一条语句有语法错误，则整个批处理将不能编译和执行。 语法格式： GO35.1.2 变量SQL Server将变量分为局部变量和

2、全局变量。1.局部变量 局部变量是用户定义，必须以开头，在程序内声 明，并只能在该程序内使用。 (1)局部变量的声明 DECLATE ,n (2)局部变量的赋值 SET|SELECT = 4例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(ch

3、ar(10),var1,102)例5-4 用SET语句将查询结果赋给局部变量并用SELECT语句显示局部变量的值。 declare date_var datetime set date_var=(select min(birthday) from s) select date_var as min_birthday5.1.2 变量52.全局变量 全局变量是SQL Server系统内部使用的变量，以开头。 例5-5 用全局变量查看SQL Server的版本、当前所使用的SQL Server服务器的名称以及所使用的服务名称等信息。 脚本：print 目前所用SQL Server的版本信息如下:pr

4、int VERSIONprint 目前SQL Server服务器名称为：+SERVERNAMEprint 目前所用服务器为：+SERVICENAME5.1.2 变量65.1.3 注释语句 注释语句是对程序代码的说明或暂时禁用，是程序代码中不编译执行的语句。 语法格式： - 或 /* */75.1.4 输出语句 需要查看程序结果时，可以使用输出语句。 语法格式： PRINT 例5-6 输出变量的值。 脚本： declare date_var datetime set date_var=(select min(birthday) from s) print date_var 85.1.5 定义语句

5、块语句 在控制流程中需要执行两条或两条以上的语句，应该将这些语句定义为一个语句块（称为复合语句）。 语法格式： BEGIN | END 95.2 设计程序流程控制语句 一、选择结构二、循环结构三、转移语句四、等待语句五、返回语句105.2.1 选择结构选择结构可以使用条件语句来实现。语法格式： IF | ELSE |11例5-7 查询学号为1001的学生。脚本： if exists(select sno from s where sno=0001) print 找到 else print 未找到例5-8 条件语句的嵌套。脚本：5.2.1 选择结构125.2.2 循环结构 循环结构可以使用循环语

6、句来实现。 语法格式： WHILE | 中断语句：BREAK 短路语句：CONTINUE 13例5-9 求110之间的素数和。脚本：例5-10 求100200之间的全部素数。脚本：5.2.2 循环结构145.2.3 转移语句 转移语句将程序的执行流程无条件转移到指定的标号处。 语法格式： GOTO 定义标号时，应在标号名后面加上冒号。 GOTO语句常用在循环语句和条件语句内，使程序跳出循环或进行分支处理。15例5-11 求10的阶乘。脚本：DECLARE s int,times int set s=1set times=1label1:set s=s*timesset times=times+

7、1if times=10 goto label1print 结果为:+str(s)165.2.4 等待语句 等待语句挂起一个程序中语句的执行，直到指定的某一时间点到来或在一定的时间间断之后才继续执行。 语法格式： WAITFOR DELAY |TIME 其中，时间间隔以及时间均为datetime类型，格式为“hh:mm:ss”，分别说明等待的时间长度和时间点，在time内不能指定日期。 17例5-12 设置等待一小时后执行查询。脚本： begin waitfor delay 1:00:00 select * from s end 例5-13 设置到十点整执行查询。脚本： begin waitf

8、or time 10:00:00 select * from s end5.2.4 等待语句185.2.5 返回语句 返回语句结束执行，使程序无条件返回，其后面的语句不再执行。 语法格式： RETURN 存储过程可以使用RETURN语句向调用者返回一个整数值。在SQL Server 2000中，存储过程返回值为0时，表示存储过程成功执行。 195.3 分行处理程序游标 在数据库开发过程中，使用SELECT语句查询，得到一个结果集，对这个结果集中的不同数据行，可能要做不同的处理。也就是要逐一处理每一个数据行。游标提供了一种比较好的解决方案。205.3.1 游标概述 1.概念 游标是一种能从包括多

9、条数据记录的结果集中每次提取一条记录的机制。2.种类 Transact-SQL游标、 API游标、客户游标。 211. Transact-SQL游标 Transact-SQL游标是由DECLARE CURSOR语句定义的，主要用在Transact-SQL脚本、存储过程和触发器中。Transact-SQL游标主要用在服务器上，由从客户端发送给服务器的Transact-SQL 语句或是批处理、存储过程、触发器中的Transact-SQL进行管理。Transact-SQL游标不支持提取数据块或多行数据。 222. API游标 API游标支持在OLE DB、ODBC以及DB_library中使用游标函

10、数，主要用在服务器上。每一次客户端应用程序调用API游标函数，SQL Server的OLE DB提供者、ODBC驱动程序或DB_library的动态链接库DLL，都会将这些客户请求传送给服务器，以对API游标进行处理。 233. 客户游标 客户游标主要用在当需要在客户机上缓存结果集时使用。在客户游标中有一个默认的结果集，被用来在客户机上缓存整个结果集。客户游标仅支持静态游标而非动态游标，由于服务器游标并不支持所有Transact-SQL语句或批处理，所以客户游标常常仅被用作服务器游标的辅助。因为在一般情况下，服务器游标能支持绝大多数的游标操作。245.3.2 使用游标操作步骤:1. 声明游标（

11、变量）。2. 打开游标。3. 从一个游标中提取信息。4. 关闭（释放）游标。251. 声明游标语法格式：DECLARE INSENSITIVE SCROLL CURSORFOR FOR READ ONLY | UPDATE OF ,.n 26例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

12、 cur_c cursor for select cno,cname,credit from c for update 1. 声明游标272. 声明游标变量语法格式： 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 =

13、CURSOR FOR SELECT * FROM s当游标变量和游标关联后，就可用游标变量代替游标名称。 283. 打开游标 游标声明后，如果要从游标中读取数据，必须打开游标。 语法格式： OPEN GLOBAL |其中，GLOBAL表示要打开的是一个全局游标。294. 读取游标中的数据 当游标被打开后，就可以从游标中逐行地读取数据。 语法格式： FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|nvar |RELATIVE n|nvar FROM GLOBAL | INTO ,.n 305. 关闭游标使用CLOSE命令关闭游标(2) 自动关闭游标31(1) 使用C

14、LOSE命令关闭游标 处理完游标中数据后，必须关闭游标来释放数据结果集和定位于数据记录上的锁。 语法格式: CLOSE GLOBAL | CLOSE语句可以关闭游标，但不释放游标的数据结构。如果要再次使用游标，可用OPEN命令重新打开。32(2) 自动关闭游标 如果在声明游标与释放游标之间使用了事务结构，则在结束事务时游标会自动关闭。 避免自动关闭的方法是使用语句： SET CURSOR_CLOSE_ON_COMMIT OFF 336. 释放游标 用CLOSE命令关闭游标并没有释放游标占用的数据结构。使用DEALLOCATE命令将释放游标占用的数据结构，游标使用的任何资源也随之释放。 语法格式

15、： DEALLOCATE GLOBAL | 游标的关闭指释放游标的结果集所占用的资源，游标的释放指释放游标占用的所有资源，当然也包括结果集占用的资源。 34例5-17 DEALLOCATE的作用。 脚本： 6. 释放游标355.3.3 游标应用实例 1. 用于游标的UPDATE语句的语法格式： UPDATE SET = |NULL|,n WHERE CURRENT OF 2. 用于游标的DELETE语句的语法格式： DELETE FROM WHERE CURRENT OF 36例5-18 游标的定位修改。 脚本： 例5-19 游标的定位删除。 脚本：例5-20 将课程号为“c001”的课程成绩上浮5%。 脚本： 5.3.3