第9章 Transact-SQL程序设计.ppt

1、第9章 Transact-SQL程序设计,本章内容,9.1 数据与表达式 9.2 函 数 9.3 程序控制流语句 9.4 游标管理与应用,9.1 数据与表达式,9.1.1 用户定义数据类型 9.1.2 常量与变量 9.1.3 运算符与表达式,9.1 数据与表达式,9.1.1 用户定义数据类型,1使用系统存储过程来创建用户定义数据类型,命令格式如下： sp_addtype typename= type, phystype = system_data_type , nulltype = null_type , owner = owner_name ,9.1.1 用户定义数据类型,例如，为Sales

2、数据库创建个不允许为NULL值的test_add用户定义数据类型。,USE Sales GO EXEC sp_addtype test_add,Varchar(10),NOT NULL GO 此后，test_add可用为数据列或变量的数据类型。,9.1.1 用户定义数据类型,2使用企业管理器创建用户定义数据类型,在企业管理器中，为Sales数据库创建个不允许NULL值的test_add用户定义数据类型，操作步骤如下。 (1)选择Sales数据库。 (2)在右窗格中选择“用户定义的数据类型”项，单击鼠标右键，在出现的快捷菜单中选择“新建用户定义数据类型”命令。 (3)在“用户定义的数据类型属性”

3、对话框中的文本框内输入test_add。 (4)在“数据类型”下拉列表框中，选择char。 (5)在“长度”文本框中输入10。 (6)选中“允许NULL值”复选框。 (7)单击“确定”按钮完成创建用户自定义数据类型。,9.1 数据与表达式,9.1.2 常量与变量,在程序运行中保持常值的数据，即程序本身不能改变其值的数据，称为常量，在程序中经常直接使用文字符号表示。 相应地，在程序运行过程中可以改变其值的数据，称为变量。,9.1.2 常量与变量,1常量,常量是表示特定数据值的符号，其格式取决于其数据类型 (1)字符串和二进制常量 字符串常量括在单引号内并包含字母数字字符(a-z、A-Z 和 0-

4、9)以及特殊字符，如感叹号(!)、at 符()和数字号(#)。 例如：Cincinnati、OBrien、Process X is 50% complete.、“OBrien”为字符串常量。 二进制常量具有前辍0 x并且是十六进制数字字符串，它们不使用引号。例如0 xAE、0 x12Ef、0 x69048AEFDD010E、0 x(空串)为二进制常量。,(2)日期/时间常量,datetime常量使用特定格式的字符日期值表示，用单引号括起来。 输入时，可以使用“/”、“.”、“-”作日期/时间常量的分隔符。,9.1.2 常量与变量,(3)数值常量,整型常量由没有用引号括起来且不含小数点的一串数字

5、表示。例如，1894、2为整型常量。 浮点常量主要采用科学记数法表示，例如，101.5E5、0.5E-2为浮点常量。 精确数值常量由没有用引号括起来且包含小数点的一串数字表示。例如，1894.1204、2.0为精确数值常量。 货币常量是以“$”为前缀的一个整型或实型常量数据，不使用引号。例如，$12.5、$542023.14为货币常量。 uniqueidentifier常量是表示全局惟一标识符GUID值的字符串。可以使用字符或二进制字符串格式指定。,9.1.2 常量与变量,逻辑数据常量使用数字0或1表示，并且不使用引号。非0的数字当作1处理。 (5)空值 在数据列定义之后，还需确定该列是否允许

6、空值(NULL)。允许空值意味着用户在向表中插入数据时可以忽略该列值。空值可以表示整型、实型、字符型数据。,(4)逻辑数据常量,9.1.2 常量与变量,变量用于临时存放数据，变量中的数据随着程序的运行而变化，变量有名字与数据类型两个属性。 变量的命名使用常规标识符，即以字母、下划线(_)、at符号()、数字符号(#)开头，后续字母、数字、at符号、美元符号($)、下划线的字符序列。不允许嵌入空格或其他特殊字符。,2变量,9.1.2 常量与变量,全局变量由系统定义并维护，通过在名称前面加“”符号 局部变量的首字母为单个“”。,全局变量和局部变量,9.1.2 常量与变量,(1)局部变量,局部变量使

7、用DECLARE语句定义 DECLARE local_variable data_type ,.n 变量名最大长度为30个字符。一条DECLARE语句可以定义多个变量，各变量之间使用逗号隔开。 例如 DECLARE name varchar(30),type int,9.1.2 常量与变量,局部变量的赋值,用SELECT为局部变量赋值 SELECT variable_name=expression , n FROM WHERE 例如 DECLARE int_var int SELECT int_var =12 /*给int_var赋值*/ SELECT int_var /*将int_var的值

8、输出到屏幕上*/,9.1.2 常量与变量,在一条语句中可以同时对几个变量进行赋值,例如 DECLARE LastName char(8),Firstname char(8),BirthDate datetime SELECT LastName=Smith,Firstname=David,BirthDate=1985-2-20 SELECT LastName,Firstname,BirthDate 局部变量没有被赋值前，其值是NULL，若要在程序中引用它，必须先赋值。,9.1.2 常量与变量,例9-1 使用SELECT语句从customer表中检索出顾客编号为“C0002”的行，再将顾客的名字赋

9、给变量customer。,DECLARE customer varchar(40),curdate datetime SELECT customer=customer_name,curdate=getdate() FROM customer WHERE customer_id=C0002,9.1.2 常量与变量,利用UPDATE为局部变量赋值,例9-2 将sell_order表中的transporter_id列值为“T001”、goods_id列值为“G00003”的order_num列的值赋给局部变量order_num。 DECLARE order_num float UPDATE sell

10、_order SET order_num=order_num*2 WHERE transporter_id=T001 AND goods_id=G00003,9.1.2 常量与变量, 用SET给局部变量赋值,SET语句格式为： SET local_variable=expression 使用SET初始化变量的方法与SELECT语句相同，但一个SET语句只能为一个变量赋值。 例9-3 计算employee表的记录数并赋值给局部变量rows。 DECLARE rows int SET rows=(SELECT COUNT(*) FROM employee) SELECT rows,9.1.2 常量

11、与变量,(2)全局变量,全局变量通常被服务器用来跟踪服务器范围和特定会话期间的信息，不能显式地被赋值或声明。 全局变量不能由用户定义，也不能被应用程序用来在处理器之间交叉传递信息。,9.1.2 常量与变量, rowcount,rowcount存储前一条命令影响到的记录总数，除了DECLARE语句之外，其他任何语句都可以影响rowcount的值。 例如 DECLARE rows int SELECT rows=rowcount,9.1.2 常量与变量, error,如果error为非0值，则表明执行过程中产生了错误，此时应当在程序中采取相应的措施加以处理。 error的值与rowcount一样，

12、会随着每一条SQL Server语句的变化而改变。 例9-4 使服务器产生服务，并用显示错误号。 raiserror(miscellaneous error message,16,1) /*产生一个错误*/ if error0 SELECT error as last error 运行结果： 服务器: 消息 50000，级别 16，状态 1，行 1 miscellaneous error message last error 0,9.1.2 常量与变量,例9-5 捕捉例9-4中服务器产生的错误号，并显示出来。,DECLARE my_error int RAISERROR(miscellaneo

13、us error message,16,1) SELECT my_error=error IF my_error0 SELECT my_error as last error 运行结果： 服务器: 消息 50000，级别 16，状态 1，行 2 miscellaneous error message last error 50000,9.1.2 常量与变量, trancount,trancount记录当前的事务数量，当某个事务当前并没有结束会话过程时，trancount的值大于0。 version version的值代表服务器的当前版本和当前操作系统版本，是SQL Server中一项较实用的技

14、术支持，通常对识别网络中某个未命名的服务器时非常有用。 spid spid返回当前用户进程的服务器进程ID，可以用来识别sp_who输出中的当前用户进程。,9.1.2 常量与变量,例9-6 使用spid返回当前用户进程的ID。,SELECT spid as ID,SYSTEM_USER AS Login Name,USER AS User Name 运行结果： ID Login Name User Name 52 sa dbo,9.1.2 常量与变量,9.1 数据与表达式,9.1.3 运算符与表达式,运算符用来执行数据列之间的数学运算或比较操作。 表达式是符号与运算符的组合。 简单的表达式可以

15、是一个常量、变量、列或函数，复杂表达式是由运算符连接一个或多个简单表达式。,9.1.3 运算符与表达式,1. 算术运算符与表达式,算术运算符用于数值型列或变量间的算术运算。算术运算符包括加(+)、减(-)、乘(*)、除(/)和取模(%)运算等。 例9-9 使用“+”将goods表中高于9000的商品价格增加15元： SELECT goods_name,unit_price,(unit_price+15) AS nowprice FROM goods WHERE unit_price9000 运行结果如图所示。,9.1.3 运算符与表达式,2. 位运算符与表达式,位运算符用以对数据进行按位与()

16、、或(|)、异或()、求反()等运算。 & 运算只有当两个表达式中的两个位值都为1时，结果中的位才被设置为1，否则结果中的位被设置为0。 | 运算时，如果在两个表达式的任一位为1或者两个位均为1，那么结果的对应位被设置为1；如果表达式中的两个位都不为1，则结果中该位的值被设置为0。 运算时，如果在两个表达式中，只有一位的值为1，则结果中位的值被设置为1；如果两个位的值都为0或者都为1，则结果中该位的值被清除为0。,9.1.3 运算符与表达式,例如，170与75进行&运算,先将170和75转换为二进制数0000 0000 1010 1010和0000 0000 0100 1011，再进行&运算的

17、结果是0000 0000 0000 1010，即十进制数10。 同样，表达式52，1，5|2的运算结果为：7，0，7。,9.1.3 运算符与表达式,3. 比较运算符与表达式,比较运算符用来比较两个表达式的值是否相同，可用于字符、数字或日期数据。 SQL Server中的比较运算符有大于()、小于(=)、小于等于(=)和不等于(!=)等，比较运算返回布尔值，通常出现在条件表达式中。 比较运算符的结果为布尔数据类型，其值为TRUE、FALSE及UNKNOWN。 例如，表达式2=3的运算结果为FALSE。,9.1.3 运算符与表达式,4. 逻辑运算符与表达式,逻辑运算符与(AND)、或(OR)、非(

18、NOT)等，用于对某个条件进行测试，以获得其真实情况。 逻辑运算符和比较运算符一样，返回TRUE或FALSE的布尔数据值。,9.1.3 运算符与表达式,表9-5 逻辑运算符,9.1.3 运算符与表达式,逻辑运算符通常和比较运算一起构成更为复杂的表达式。逻辑运算符的操作数都只能是布尔型数据。 例如，在表employee中查找1973年以前与1980年以后出生的男员工的表达式为： (year(birth_date)1980) AND sex=男,9.1.3 运算符与表达式,LIKE运算符,确定给定的字符串是否与指定的模式匹配，通常只限于字符数据类型。LIKE的通配符如下表,9.1.3 运算符与表达

19、式,例如,查找所有姓“钱”的员工及住址,SELECT employee_name, address FROM employee WHERE employee_name LIKE 钱%,9.1.3 运算符与表达式,4连接运算符与表达式,连接运算符(+)用于两个字符串数据的连接，通常也称为字符串运算符。 在SQL Server中，对字符串的其他操作通过字符串函数进行。字符串连接运算符的操作数类型有char、varchar和text等。 例如， Dr.+Computer的运算结果:Dr. Computer,9.1.3 运算符与表达式,5运算符的优先级别,SQL Server中各种运算符的优先顺序如下

20、： () | *、/、% +、- NOT AND OR 排在前面的运算符的优先级高于其后的运算符。在一个表达式中，先计算优先级较高的运算，后计算优先级低的运算，相同优先级的运算按自左向右的顺序依次进行。,9.2 函 数,9.2.1 常用函数 9.2.2 用户定义函数,9.2 函 数,9.2.1 常用函数,函数是组编译好的Transact-SQL语句，它们可以带一个或一组数值做参数，也可不带参数，它返回一个数值、数值集合，或执行一些操作。 函数能够重复执行一些操作，从而避免不断重写代码。 SQL Server 2000支持两种函数类型： (1) 内置函数:是一组预定义的函数，是Transact-

21、SQL语言的一部分，按Transact-SQL参考中定义的方式运行且不能修改。 (2) 用户定义函数:由用户定义的Transact-SQL函数。它将频繁执行的功能语句块封装到一个命名实体中，该实体可以由Transact-SQL语句调用。,9.2.1 常用函数,1字符串函数,字符串函数用来实现对字符型数据的转换、查找、分析等操作，通常用做字符串表达式的一部分。表9-7中列出了SQL Server的常用字符串函数。,9.2.1 常用函数,(1) 使用datalength和Len函数,datalength函数主要用于判断可变长字符串的长度，对于定长字符串将返回该列的长度。要得到字符串的真实长度，通常

22、需要使用rtrim函数截去字符串尾部的空格。 Len函数可以获取字符串的字符个数，而不是字节数，也不包含尾随空格。,9.2.1 常用函数,例9-10 从表department中读取manger列的各记录的实际长度。,SELECT Datalength(rtrim(manger) AS DATALENGTH, Len(rtrim(manger) AS LEN FROM department 运行结果如下： DATALENGTH LEN 4 2 6 3 6 3 6 3,9.2.1 常用函数,(2) 使用Soundex函数,soundex函数将char_expr转换为4个字符的声音码，其中第一个码为

23、原字符串的第一个字符，第24个字符为数字，是该字符串的声音字母所对应的数字，但忽略了除首字母外的串中的所有元音。 Soundex函数可用来查找声音相似的字符串，但它对数字和汉字均只返回0值。 例如 SELECT soundex(1),soundex(a), soundex(计算机), soundex(abc), soundex (abcd), soundex(a12c), soundex(a数字) 返回值为： 0000 A000 0000 A120 A120 A000 A000,9.2.1 常用函数,(4) 使用Charindex函数实现串内搜索,charindex函数主要用于在串内找出与指定

24、串匹配的串，如果找到的话，charindex函数返回第一个匹配的位置。格式： Charindex(expr1, expr2, start_location) expr1是待查找的字符串 expr2是用来搜索expr1的字符表达式， start_location是在expr2中查找expr1的开始位置，如果此值省略、为负或为0，均从起始位置开始查找。,9.2.1 常用函数,例如,SELECT charindex(, red,white,blue) 该查询确定了字符串red,white,blue中第一个逗号的位置。,9.2.1 常用函数,(5) 使用Patindex函数,patindex函数返回在

25、指定表达式中模式第一次出现的起始位置，如果模式没有则返回0。格式： Patindex(%pattern%, expression) pattern是字符串，%字符必须出现在模式的开头和结尾。expression通常是搜索指定子串的表达式或列。 例如： SELECT patindex(%abc%,abc123), patindex(123,abc123) 子串“abc”和“123”在字符串“abc123”中出现的起始位置分别为：1和0。因为子串“123”不是以%开头和结尾。,9.2.1 常用函数,2数学函数,数学函数用来实现各种数学运算，如指数运算、对数运算、三角运算等，其操作数为数值型数据，如

26、int、float、real、money等 表9-8列出了SQL Server的数学函数。,9.2.1 常用函数,例9-11 在同一表达式中使用sin、atan、rand、pi、sign函数。,SELECT sin(23.45), atan(1.234), rand(), pi(), sign(-2.34) 运行结果如下： -0.99374071017265964 0.88976244895918932 0.1975661765616786 3.1415926535897931 -1.00,9.2.1 常用函数,例9-12 用ceiling和floor函数返回大于或等于指定值的最小整数值和小于

27、或等于指定值的最大整数值。,SELECT ceiling(123),floor(321), ceiling(12.3), ceiling(-32.1),floor(-32.1) 运行结果如下： 12332113-32-33,9.2.1 常用函数,SELECT round(12.34512,3), round(12.34567,3), round(12.345,-2), round(54.321,-2) 运行结果如下： 12.34500 12.34600 .000 100.000 Round(numeric_expr, int_expr) 的int_expr为负数时，将小数点左边第int_exp

28、r位四舍五入。,例9-13 round函数的使用。,9.2.1 常用函数,3日期函数,日期函数用来操作datetime和smalldatetime类型的数据，执行算术运算。与其他函数一样，可以在SELECT语句和WHERE子句以及表达式中使用日期函数。,9.2.1 常用函数,表9-9 SQL Server的日期函数,9.2.1 常用函数,表9-10 SQL Server的日期部分,9.2.1 常用函数,例9-14 使用datediff函数来确定货物是否按时送给客户。,SELECT goods_id,datediff(dd,send_date,arrival_date) FROM purchas

29、e_order 为了从datediff中得到一个正值，应注意把较早的日期放在前面,9.2.1 常用函数,例9-15 使用datename函数返回员工的出生日期的月份(mm)名称。,SELECT employee_name,datename(mm,birth_date) FROM employee 运行结果如下： 钱达理 December 东方牧 April 郭文斌 March 肖海燕 July 张明华 August,9.2.1 常用函数,4系统函数,系统函数用于获取有关计算机系统、用户、数据库和数据库对象的信息。与其他函数一样，可以在SELECT和WHERE子句以及表达式中使用系统函数。 表9

30、-11列出了SQL Server的系统函数。,9.2.1 常用函数,例9-16 使用object_name函数返回已知ID号的对象名。,SELECT object_name(469576711) 运行结果如下： Employee,9.2.1 常用函数,例9-17 利用object_id函数，根据表名返回该表的ID号。,SELECT name FROM sysindexes WHERE id=object_id(customer) 运行结果如下： name customer _WA_Sys_customer_id_75D7831F,9.2 函 数,9.2.2 用户定义函数,根据函数返回值形式的不

31、同将用户定义函数分为3种类型。 (1) 标量函数 标量函数返回一个确定类型的标量值，其函数值类型为SQL Server的系统数据类型（除text、ntext、image、cursor、timestamp、table类型外）。函数体语句定义在BEGINEND语句内。 (2) 内嵌表值函数 内嵌表值函数返回的函数值为一个表。内嵌表值函数的函数体不使用BEGINEND语句，其返回的表是RETURN子句中的SELECT命令查询的结果集，其功能相当于一个参数化的视图。 (3) 多语句表值函数 多语句表值函数可以看作标量函数和内嵌表值函数的结合体。其函数值也是一个表，但函数体也用BEGINEND语句定义，

32、返回值的表中的数据由函数体中的语句插入。,9.2.2 用户定义函数,1. 创建用户定义函数,(1) 使用CREATE FUNCTION创建用户定义函数 标量函数的语法格式： CREATE FUNCTION owner_name. function_name ( parameter_name AS scalar_parameter_data_type=default ,.n ) RETURNS scalar_return_data_type WITH , .n AS BEGIN function_body RETURN scalar_expression END,9.2.2 用户定义函数,内嵌表

33、值函数的语法格式：,CREATE FUNCTION owner_name. function_name ( parameter_name AS scalar_parameter_data_type =default ,.n ) RETURNS TABLE WITH , .n AS RETURN ( select_stmt ) ,9.2.2 用户定义函数,多语句表值函数的语法格式：,CREATE FUNCTION owner_name. function_name ( parameter_name AS scalar_parameter_data_type = default ,.n ) RET

34、URNS return_variable TABLE WITH , .n AS BEGIN function_body RETURN END,9.2.2 用户定义函数,例9-18 创建一个用户定义函数DatetoQuarter，将输入的日期数据转换为该日期对应的季度值。如输入2006-8-5，返回3Q2006，表示2006年3季度。,CREATE FUNCTION DatetoQuarter(dqdate datetime) RETURNS char(6) AS BEGIN RETURN(datename(q,dqdate)+Q+datename(yyyy,dqdate) END,9.2.2

35、用户定义函数,例9-19 创建用户定义函数goodsq，返回输入商品编号的商品名称和库存量。,CREATE FUNCTION goodsq(goods_id varchar(30) RETURNS TABLE AS RETURN(SELECT goods_name,stock_quantity FROM goods WHERE goods_id =goods_id),9.2.2 用户定义函数,例9-20 根据输入的订单编号，返回该订单对应商品的编号、名称、类别编号、类别名称。,CREATE FUNCTION good_info(in_o_id varchar(10) RETURNS goodi

36、nfo TABLE (o_id char(6), g_id char(6), g_name varchar(50), c_id char(6), c_name varchar(20) AS BEGIN DECLARE g_id varchar(10),g_name varchar(30) DECLARE c_id varchar(10),c_name varchar(30) SELECT g_id=goods_id FROM sell_order WHERE order_id1=in_o_id SELECT g_name=goods_name,c_id=classification_id FR

37、OM goods WHERE goods_id=g_id SELECT c_name=classification_name FROM goods_classification WHERE c_id=classification_id INSERT goodinfo VALUES(in_o_id,g_id,g_name,c_id,c_name) RETURN END,9.2.2 用户定义函数, 在企业管理器中选择要创建用户定义函数的数据库（如Sales），在数据库对象“用户定义函数”项上单击右键，从弹出的快捷菜单中选择“新建用户定义的函数.”选项，出现如图所示的“用户定义函数属性”对话框。,(

38、2) 使用企业管理器创建用户定义函数,9.2.2 用户定义函数, 在“用户定义函数属性”对话框的文本框中指定函数名称（如numtostr），编写函数的代码。 单击“检查语法”按钮，出现“语法检查成功”消息框后，单击“确定”按钮，将用户定义函数对象添加到数据库中。,9.2.2 用户定义函数,2. 执行用户定义函数,使用函数需要指出函数所有者，即为函数加上所有者权限作为前缀。其语法格式如下： database_name.owner_name.function_name (argument_expr , . ),9.2.2 用户定义函数,例如，调用例9-18创建的用户定义函数DatetoQuarte

39、r,SELECT dbo.DatetoQuarter (2006-8-5) 运行结果为： 3Q2006 调用例9-20创建的用户定义函数good_info，使用以下语句： SELECT * FROM dbo.good_info(S00002) 运行结果为表的记录，如图9-3所示。,9.2.2 用户定义函数,3. 修改和删除用户定义函数,用企业管理器修改用户定义函数，选择要修改函数，单击右键，从快捷菜单中选择“属性”选项，打开图9-2所示的“用户定义函数属性”对话框。在该对话框中可以修改用户定义函数的函数体、参数等。从快捷菜单中选择“删除”选项，则可删除用户定义函数。 用ALTER FUNCTI

40、ON命令也可以修改用户定义函数。此命令的语法与CREAT FUNCTION相同，使用ALTER FUNCTION命令相当于重建一个同名的函数。 使用DROP FUNCTION命令删除用户定义函数，其语法如下： DROP FUNCTION owner_name . function_name ,.n 其中，function_name是要删除的用户定义的函数名称。,9.2.2 用户定义函数,例如，删除例9-18创建的用户定义函数,DROP FUNCTON DatetoQuarter 删除用户定义函数时，可以不加所有者前缀。,9.3 程序控制流语句,9.3.1 语句块和注释 9.3.2 选择控制 9

41、.3.3 循环控制 9.3.4 批处理,9.3 程序控制流语句,9.3.1 语句块和注释,Transact-SQL提供了控制流语言的特殊关键字和用于编写过程性代码的语法结构，可进行顺序、分支、循环、存储过程、触发器等程序设计，编写结构化的模块代码，并放置到数据库服务器上。,9.3.1 语句块和注释,BEGIN.END用来设定一个语句块，将在BEGIN.END内的所有语句视为一个逻辑单元执行。 语句块BEGIN.END的语法格式为： BEGIN sql_statement | statement_block END,1. 语句块BEGIN.END,9.3.1 语句块和注释,USE Sales G

42、O DECLARE linkman_name char(8) BEGIN SELECT linkman_name=(SELECT linkman_name FROM customer WHERE customer_id LIKE C0001) SELECT linkman_name END,例9-21 显示Sales数据库中customer表的编号为C0001的联系人姓名。,9.3.1 语句块和注释,在BEGIN.END中可嵌套另外的BEGIN.END来定义另一程序块。,例9-22 语句块嵌套举例。 DECLARE errorcode int,nowdate dateTIME BEGIN SE

43、T nowdate=getdate() INSERT sell_order(order_date,send_date,arriver_date,custom_id) VALUES(nowdate,nowdate+5,nowdate+10,C0002) SELECT errorcode=error IF errorcode0 BEGIN RAISERROR(当表sell_order插入数据时发生错误!,16,1) RETURN END,9.3.1 语句块和注释,2. 注释,有两种方法来声明注释： 单行注释 多行注释。,9.3.1 语句块和注释,（1）单行注释,在语句中，使用两个连字符“-”开头，

44、则从此开始的整行或者行的一部分就成为了注释，注释在行的末尾结束。 -This is a comment.Whole line will be ignored. SELECT employee_name, address -查询所有姓钱的员工 FROM employee WHERE employee_name LIKE 钱% 注释的部分不会被SQL Server执行。,9.3.1 语句块和注释,（2）多行注释,多行注释方法是SQL Server自带特性，可以注释大块跨越多行的代码，它必须用一对分隔符“/* */”将余下的其他代码分隔开。 /* This is a commnet All thes

45、e lines will be ignored. */ /* List all employees.*/ SELECT * FROM employee 注释并没有长度限制。SQL Server文档禁止嵌套多行注释，但单行注释可以嵌套在多行注释中。 /* -List all employees. SELECT * FROM employee */,9.3 程序控制流语句,9.3.2 选择控制,1. 条件执行语句IFELSE 必须IF.ELSE结构根据条件表达式的值，以决定执行哪些语句。 IF.ELSE的语法格式为： IF Boolean_expression sql_statement | st

46、atement_block -条件表达式为真时执行 ELSE sql_statement | statement_block -条件表达式为假时执行,9.3.2 选择控制,IF (SELECT avg(unit_price) FROM goods WHERE supplier_id=S001)$9799.0 SELECT supplier_id为S001的商品的平均单价比9799大 ELSE SELECT supplier_id为S001的商品的平均单价比9799小 运行结果如下： supplier_id为S001的商品的平均单价比9799大,例9-23 判断表goods中supplier_i

47、d为“S001”的商品的平均单价是否大于9799。,9.3.2 选择控制,DECLARE lname varchar(40),msg varchar(255) SELECT lname=陈晓兵 IF EXISTS(SELECT * FROM department WHERE manager=lname) BEGIN SELECT msg=有人名为+lname SELECT msg END ELSE BEGIN SELECT msg=没有人名为+lname SELECT msg END 运行结果为： 有人名为陈晓兵,例9-24 用EXISTS确定表department中是否存在“陈晓兵”。,9.

48、3.2 选择控制,例9-25 嵌套IF.ELSE语句的使用。,IF (SELECT SUM(order_num) FROM sell_order)50 PRINT 他们是最佳的客户 ELSE IF (SELECT SUM(order_num) FROM sell_order)30 PRINT 必须与他们保持联络 ELSE PRINT 再想想办法吧!,9.3.2 选择控制,2. CASE函数,CASE函数计算多个条件并为每个条件返回单个值。 (1) 简单CASE函数：将某个表达式与一组简单表达式进行比较以确定结果。 CASE input_expression WHEN when_expressi

49、on THEN result_expression .n ELSE else_result_expression END (2) CASE搜索函数，CASE计算一组逻辑表达式以确定结果。 CASE WHEN Boolean_expression THEN result_expression . n ELSE else_result_expression END,9.3.2 选择控制,例9-26 使用简单CASE函数将goods表中的商品分类重命名，以使之更易理解。,SELECT CASE classification_id WHEN P001 THEN 笔记本计算机 WHEN P002 THE

50、N 激光打印机 WHEN P003 THEN 喷墨打印机 WHEN P004 THEN 交换机 ELSE 没有这种品牌 END AS Classification, goods_name AS Goods Name, unit_price AS Price FROM goods WHERE unit_price IS NOT NULL,9.3.2 选择控制,SELECT goods_name AS 商品名称, CASE WHEN stock_quantity-order_quantity3 and stock_quantity-order_quantity10 THEN 货物充足 END AS

51、 进货判断 FROM goods,例9-27 根据goods表中库存货物数量与订货量之差，使用CASE搜索函数判断该商品是否进货。,9.3.2 选择控制,3. 跳转语句GOTO,GOTO语句将允许程序的执行转移到标签处,GOTO语句的语法格式如下： GOTO label 其中，label为GOTO语句处理的起点。label必须符合标识符规则。,9.3.2 选择控制,例9-28 使用GOTO语句改变程序流程。,DECLARE x int SELECT x=1 label_1: SELECT x SELECT x=x+1 WHILE x6 GOTO label_1,9.3.2 选择控制,4. RE

52、TURN语句,RETURN语句可使程序从批处理、存储过程或触发器中无条件退出，不再执行本语句之后的任何语句。 RETURN语句的语法格式为： RETURN integer_expression ,9.3.2 选择控制,例9-29 RETURN语句应用示例。,DECLARE x int,y int SELECT x=1,y=2 IF xy RETURN ELSE RETURN,9.3 程序控制流语句,9.3.3 循环控制,WHILE语句根据条件表达式设置Transact-SQL语句或语句块重复执行的次数。如果所设置的条件为真（TRUE）时，在WHILE循环体内的Transact-SQL语句会一直

53、重复执行，直到条件为假（FALSE）为止。 WHILE循环语句的语法格式如下： WHILE boolean_expression sql_statement | statement_block BREAK sql_statement | statement_block CONTINUE ,9.3 程序控制流语句,例9-30 将goods表中库存数最大的商品每次订购2件，计算如此需要多少次订购才能使库存数不够一次订购。,DECLARE count int,maxstockid char(6),maxstock float SET count=0 SET maxstock=(SELECT max(

54、stock_quantity) FROM goods) SET maxstockid=(SELECT goods_id FROM goods WHERE stock_quantity=maxstock) SELECT maxstockid,maxstock WHILE (maxstock (SELECT order_quantity FROM goods WHERE goods_id=maxstockid) BEGIN UPDATE goods SET order_quantity=order_quantity+2 WHERE goods_id=maxstockid SET count=cou

55、nt+1 END SELECT count 运行结果如下： 8,9.3 程序控制流语句,BREAK语句让程序跳出循环，CONTINUE语句让程序跳过CONTINUE命令之后的语句，回到WHILE循环的第一行命令，重新开始循环。,BREAK或CONTINUE语句,9.3 程序控制流语句,例9-31 对于goods表，如果平均库存少于12，WHILE循环就将各记录库存增加5%，再判断最高库存是否少于或等于25，是则WHILE循环重新启动并再次将各记录库存增加5%。当循环不断地将库存增加直到最高库存超过25时，然后退出WHILE循环。,/*执行循环，直到库存平均值超过12*/ WHILE(SELEC

56、T avg(stock_quantity) FROM goods)25 BEGIN PRINT 库存太多了 BREAK END ELSE CONTINUE END,9.3 程序控制流语句,DECLARE s int,n int,t int,c int SET S=0 SET n=1 WHILE n=10 BEGIN SET c=1 SET t=1 WHILE c=n BEGIN SET t=t*c SET c=c+1 END SET s=s+t SET n=n+1 END SELECT s,n,例9-32 计算s=1!+2!+10!。,9.3 程序控制流语句,9.3.4 批处理,一个批处理是条

57、或多条Transact-SQL语句的集合。 SQL Server服务器对批处理的处理分为四个阶段： 分析阶段，服务器检查命令的语法，验证表和列的名字的合法性 优化阶段，服务器确定完成一个查询的最有效的方法； 编译阶段，生成该批处理的执行计划； 运行阶段，条一条地执行该批处理中的语句。 批处理最重要的特征就是它作为一个不可分的实体在服务器上解释和执行。在一些情况下批处理被隐式地设定。例如，用查询分析器来执行一组Transact-SQL语句，这组语句将被视为一个批处理来对待。,9.3 程序控制流语句,SQL Server有以下几种指定批处理的方法。 (1) 应用程序作为一个执行单元发出的所有SQL

58、语句构成一个批处理，并生成单个执行计划。 (2) 存储过程或触发器内的所有语句构成一个批处理。每个存储过程或触发器都编译为一个执行计划。 (3) 由EXECUTE语句执行的字符串是一个批处理，并编译为一个执行计划。例如， EXEC (SELECT * FROM employee) (4) 由sp_executesql系统存储过程执行的字符串是一个批处理，并编译为一个执行计划。例如， execute sp_executesql NSELECT * from Sales.dbo.employee,1. 批处理的指定,9.3 程序控制流语句,GO是批处理的结束标志。当编译器执行到GO时会把GO前面的

59、所有语句当成一个批处理来执行。 GO命令和Transact-SQL语句不可处在同一行上。但在GO命令行中可以包含注释。 在批处理的第一条语句后执行任何存储过程必须包含EXECUTE关键字。局部(用户定义)变量的作用域限制在一个批处理中，不可在GO命令后引用。 RETURN可在任何时候从批处理中退出，而不执行位于RETURN之后的语句。,2. 批处理的结束与退出,9.3 程序控制流语句,USE Sales GO - 批处理结束标志 CREATE VIEW employee_info AS SELECT * FROM employee GO - CREATE VIEW语句与其他语句隔离 SELECT * FROM employee_info GO,例9-33 创建一个视图，使用GO命令将CREATE VIEW语句与批处理中的其他语句(如USE、SELECT语句等)隔离。,9.4 游标管理与应用,9.4.1 游标概述 9.4.2 声明游标 9.4.3 使用游标 9.4.4 游标的应用 9.4.5 使用系统存储过程管理游标,9.