SQL数据库应用(第七章)

1、第七章 Transact-SQL程序设计学时：6目标学习T-SQL要素，掌握其使用方法，掌握游标的使用重点T-SQL程序设计难点命令的使用SQL数据库应用第七章 Transact-SQL程序设计7.1 Transact-SQL基础7.2 Transact-SQL要素7.3 使用游标Transact-SQL程序设计使用Transact-SQL进行程序设计是SQL Server的主要应用形式之一。不论是普通的客户机/服务器应用程序，还是Web应用程序，都应该对涉及数据库中数据进行的处理描述成Transact-SQL语句，并通过向服务器端发送Transact-SQL语句才能实现与SQL Server

2、的通信。本章将首先介绍Transact-SQL的基本知识，然后在此基础上介绍Transact-SQL基本要素、流程控制语句和游标的使用。Transact-SQL程序设计视图是一个虚拟表，其结构和数据是建立在对表的查询基础上的。和表一样，视图也包括几个被定义的数据列和多个数据行，但就本质而言这些数据列和数据行来源于其所引用的表，所以视图不是真实存在的基表，而是一张虚表。视图所对应的数据并不以实际视图结构存储在数据库中，而是基表中数据的一个映射。7.1 基础 Transact-SQL（简称T-SQL）语言是SQL Server使用的一种数据库查询和编程语言，是结构化查询语言SQL的增强版本，增加了

3、一些非标准的SQL语句，使其功能更强大。 使用T-SQL语句可建立、修改、查询和管理关系数据库，也可以把T-SQL语句嵌入到某种高级程序设计语言（如VB、VC、DELPHI）中。7.1 基础 T-SQL的基本成分是语句，由一个或多个语句可以构成一个批处理，由一个或多个批处理可以构成一个查询脚本（以sql作为文件扩展名）并保存到磁盘文件中，供以后需要时使用。使用语句数据定义语言 (DDL) 语句数据操作语言 (DML) 语句数据控制语言 (DCL) 语句7.2 要素 批处理就是单个或多个T-SQL语句的集合，由应用程序一次性发送给SQL Server解释并执行批处理内的所有语句指令。使用GO命令

4、和使用EXECUTE命令可以将批处理发送给SQL Server。1、go命令一个批处理的结束标志。2、exec命令用于执行用户定义的函数以及存储过程。7.2.1 批处理7.2 要素注释是程序代码中不执行的文本字符串。它起到注解说明代码或暂时禁用正在进行诊断调试的部分语句和批处理的作用。1、行内注释 -注释文本2、块注释 /* 注释文本 */7.2.2 注释语句7.2 要素 标识符是用户编程时使用的名字。我们指定某个东西、人，都要用到它，他或她的名字；在数学中解方程时，我们也常常用到这样或那样的变量名或函数名。同样的道理，在电脑语言中，对于变量，常量，函数，语句块也有名字，我们统统称之为标识符。

5、7.2.3 标识符7.2 要素1、标准标识符 标准标识符也称为常规标识符，它包含1128个字符，以字母（az或AZ）、下划线（_）、或#开头，后续字符可以是ASCII字符、Unicode字符、符号（_、$、或#），但不能全为下划线（_）、或#。2、分隔标识符 分隔标识符是包含在双引号（）或中括号（）内的标准标识符或不符合标准标识符规则的标识符。7.2.3 标识符7.2 要素1、全局变量 全局变量以开头，由系统定义和维护，不能由用户创建，对用户来说是只读的，大部分的全局变量记录了SQL Server服务器的当前状态信息。全局变量是不可以赋值的。2、局部变量 局部变量以开头，由用户定义和赋值，指在

6、T-SQL批处理和脚本中用来保存数据值的对象。此外，还允许用table数据类型的局部变量来代替临时表。但不能指定局部变量为text、ntext、image数据类型7.2.4 全局变量与局部变量7.2 要素1、全局变量【例7.5】 使用ERROR变量在一个UPDATE语句中检测限制检查冲突（错误代码为#547）。USE SalesGO-将编号为1001的员工编号更新为1100UPDATE GoodsSET 进货员工编号=1100WHERE 进货员工编号=1001-检查是否出现限制检查冲突IF ERROR=547 PRINT 出现限制检查冲突，请检查需要更新的数据限制GO7.2.4 全局变量与局部

7、变量（1）局部变量的声明DECLARE 局部变量名 数据类型,n（2）局部变量的赋值。使用SET语句赋值的语法格式为： SET 局部变量名=表达式,n使用SELECT语句赋值的语法格式为： SELECT 局部变量名=表达式,n【例7.9】 声明一个名为now的局部变量并赋值，用此变量返回当前系统的日期和时间。-声明两个局部变量DECLARE now datetime-对局部变量赋值SET now=GETDATE()-显示局部变量的值SELECT now7.2.4 全局变量与局部变量【例7.10】 本例演示了使用查询给变量赋值的方法。USE SalesGODECLARE cnt intSET c

8、nt=(SELECT count(编号) FROM employees) /*使用查询给变量赋值，注意这里的查询语句只能在返回单个值的情况下才能赋值成功*/SELECT cnt AS 公司员工总数 /*将员工总数值输出到屏幕*/GO7.2.4 全局变量与局部变量7.2 要素1运算符运算符是执行数学运算、字符串连接以及比较操作的一种符号。（1）算术运算符（2）比较运算符（3）逻辑运算符 （4）字符串串联运算符 （5）按位运算符 （6）赋值运算符 （7）一元运算符7.2.5 运算符和表达式7.2 要素1运算符 在SQL Server 2005中，运算符的优先等级从高到低如下所示。7.2.5 运算符

9、和表达式运 算 符优 先 级 别+（正）、（负）、（按位NOT）一元运算符1*（乘）、/（除）、%（取模）算术运算符2+（加）、（减）算数运算符3=(等于)、=、=、!=、!、!0 PRINT 还有货ELSE PRINT 无库存，请尽快进货!7.2.5 运算符和表达式【例7.14】 本例演示逻辑运算符ALL的使用。查询单笔商品销售量高于王峰最高销售量的员工姓名、所销售的商品名称、售出时间及销售量。USE SalesGOSELECT 姓名,商品名称,售出时间,Sell.数量FROM Employees JOIN Sell ON Employees.编号=Sell.售货员工编号 JOIN Good

10、s ON Goods.商品编号=Sell.商品编号WHERE Sell.数量ALL(SELECT Sell.数量 FROM Employees JOIN Sell ON Employees.编号=Sell.售货员工编号 JOIN Goods ON Goods.商品编号=Sell.商品编号 WHERE 姓名=王峰)【例7.15】 本例演示多个字符串的串联。USE SalesGOSELECT 姓名+ : 电话 AS 姓名及电话FROM Employees7.2 要素 使用T-SQL编程的时候，常常要利用各种流程控制语句去进行顺序、分支控制转移、循环等操作。T-SQL提供了一组流程控制语句条件控制语

11、句无条件控制语句循环语句返回状态值给调用例程的语句7.2.6 流程控制语句7.2 要素7.2.6 流程控制语句语 句说 明BEGINEND定义一个语句块IFELSE如果条件成立，执行一个分支，否则执行另一个分支WHILE基本循环语句，指定条件为真时重复执行一条语句或语句块BREAK退出最内层的WHILE循环CONTINUE退出本次WHILE循环，重新开始一个WHILE循环CASE(表达式)允许表达式按照条件返回不同的值GOTO转到指定标签语句处继续执行RETURN无条件退出语句。可以给调用的过程或应用程序返回整型值TRYCATCH错误处理语句WAITFOR为语句执行设置时间。时间可以是一个延时

12、，也可以是一天中的某个时间点【例7.16】 在WHILE循环中，包含两条语句，需要BEGINEND语句将这两条语句封闭起来组成一个语句块。DECLARE counter intSELECT counter=0WHILE counter10BEGIN SELECT counter=counter+1 -计数器循环累加 PRINT counter -将计数器的值显示出屏幕来END7.2.6 流程控制语句【例7.17】 测试Sales数据库的Goods表中是否有“HP喷墨打印机photosmart 7268”这种商品，有的话显示该商品的信息，否则显示“该商品无进货记录！”USE SalesGODEC

13、LARE Goodname varchar(20)SET Goodname=HP喷墨打印机photosmart 7268IF EXISTS(SELECT * FROM Goods WHERE 商品名称=Goodname) BEGIN PRINT Goodname+商品的信息如下： SELECT * FROM Goods WHERE 商品名称=Goodname ENDELSE BEGIN PRINT 该商品无进货记录！ ENDGO【例7.18】 判断Sales数据库是否有商品的库存量少于10，如果有，则将每一商品都入货50，直到所有商品的库存量都多于10或者有商品的库存量超过200。USE Sa

14、lesGOWHILE EXISTS(SELECT * FROM Goods WHERE 数量200 BREAK ENDGO7.2.6 流程控制语句【例7.19】 在Sales数据库中查询每个员工在2005年1月的销售商品数量并发布奖罚信息，销售商品数量低于30的为不合格员工，扣除当月提成；30到60之间的，为合格员工；高于60的，为优秀员工，凭多出的销量获取奖金；其他情况视为无销量记录。USE SalesGOSELECT 售货员工编号,姓名,奖罚信息= CASE WHEN sum(数量)=30 AND sum(数量)=60 THEN 优秀员工，凭多出的销量获取奖金 ELSE 无销售记录 END

15、 FROM Sell JOIN Employees ON Employees.编号=Sell.售货员工编号 WHERE YEAR(售出时间)=2005 AND MONTH(售出时间)=1 GROUP BY 售货员工编号,姓名GO【例7.20】 在sales数据库中，删除编号是“1301”的员工信息。USE salesGOBEGIN TRY DELETE FROM employees WHERE 编号=1301END TRYBEGIN CATCH PRINT 出错信息为：+error_message()End catch7.2.6 流程控制语句【例7.21】（1）使用DELAY关键字指定在执行S

16、ELECT语句之前等待5秒。USE SalesGOWAITFOR DELAY 00:00:05SELECT 商品名称,生产厂商,进货价FROM GoodsWHERE 进货时间=2005-1-1GO（2）使用TIME关键字指定在9时25分50秒执行SELECT语句。USE SalesGOWAITFOR TIME 9:25:50SELECT 商品名称,生产厂商,进货价FROM GoodsWHERE 进货时间=2005-1-1GO7.3 使用游标 游标（cursor）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字。用户可以用SQL语句逐一从游标中获取记录，并赋给主

17、变量，交由主语言进一步处理。 在数据库开发过程中，当你检索的数据只是一条记录时，你所编写的事务语句代码往往使用SELECT INSERT 语句。但是我们常常会遇到这样情况，即从某一结果集中逐一地读取一条记录。那么如何解决这种问题呢？游标为我们提供了一种极为优秀的解决方案。 7.3 使用游标游标的概念游标提供了一种从表中检索数据并进行操作的灵活手段，游标主要用在服务器上，处理由客户端发送给服务器端的SQL语句，或是批处理、存储过程、触发器中的数据处理请求。游标的优点在于它可以定位到结果集中的某一行，并可以对该行数据执行特定操作，为用户在处理数据的过程中提供了很大方便。一个完整的游标由5部分组成，

18、并且这5个部分应符合下面的顺序。（1）声明游标。（2）打开游标。（3）从一个游标中查找信息。（4）关闭游标。（5）释放游标。7.3 使用游标 1SQL-92标准的游标声明 基于SQL-92标准的游标声明语句的语法格式为： DECLARE 游标名称 INSENSITIVE SCROLL CURSOR FOR SELECT语句 FOR READ ONLY | UPDATEOF 字段名,n【例7.22】 本例是符合SQL-92标准的游标声明语句。声明一个游标，用于访问Sales数据库中的所有进货商品信息。USE SalesGO-声明游标DECLARE Goods_cursor CURSOR FOR

19、SELECT * FROM Goods FOR READ ONLY7.3.1 游标声明7.3 使用游标2T-SQL扩展标准的游标声明基于T-SQL标准的游标声明语句的语法格式为：DECLARE 游标名称 CURSORLOCAL|GLOBAL -指定游标的作用范围FORWARD_ONLY|SCROLL -指定游标的移动方向STATIC|KEYSET|DYNAMIC|FAST_FORWARD -指定游标的4种类型READ_ONLY|SCROLL_LOCKS|OPTIMISTIC -指定游标或基表的访问属性TYPE_WARNING -指定如果游标从所请求的类型隐形转换为另一种类型， 则给客户端发送警

20、告信息FOR SELECT语句 -定义游标的结果集FOR UPDATE OF 字段名,n -指定游标中可以更新的字段7.3.1 游标声明7.3 使用游标【例7.23】 声明一个全局滚动动态游标sales_cursor，它用于获取所有员工销售“9”号商品的信息，其中包括员工姓名、销售数量和售出时间3列。USE SalesGODECLARE sales_cursor CURSORGLOBAL SCROLL DYNAMICFOR SELECT 姓名,数量,售出时间 FROM Employees JOIN Sell ON Employees.编号=Sell.售货员工编号 WHERE 商品编号=97.3.1 游标声明7.3 使用游标1打开游标OPEN global 游标名称2读取数据FETCH NEXT|FRIOR|FIRST|LAST|ABSOLUTEn|var|RELATIVEn|varFROMGLOBAL游标名称INTO 变量名，n7.3.2 打开和读取游标7.3 使用游标从游标中读取数据SQL语句如下：USE 销售管理系统 -引入数据库DECLARE ReadCursor CURSOR FOR -声明一个游标SELECT 操作员编号,操作员姓名,操作员性别,操作员住址FROM 操作员信息表OPEN ReadCursor -打开游标FETCH NEXT FRO