第6章 Transact-SQL程序设计 6.1 注释 6.2 变量 6.3 运算符 6.4 函数 6.5 流程控制语句

掌握Transact-SQL的语法结构； 学会Transact-SQL变量和函数的用法； 了解Transact-SQL各种控制结构的区别 能力目标 　　掌握Transact-SQL的语法结构； 　　学会Transact-SQL变量和函数的用法； 　　了解Transact-SQL各种控制结构的区别 和用法。

态度目标 　　团队精神； 　　态度积极； 　　任务按时完成； 　　出勤。

掌握Transact-SQL的语法结构。 难点： 学会使用Transact-SQL语言编写程序。 重点难点 　　重点： 　　掌握Transact-SQL的语法结构。 　　难点： 　　学会使用Transact-SQL语言编写程序。

6.1 注释 注释是表示用户提供的说明内容。有两种格式： 单行注释：使用两个联在一起的减号“--”作为注 释内容标记，其后为注释内容。 6.1 注释 　　注释是表示用户提供的说明内容。有两种格式： 　　单行注释：使用两个联在一起的减号“--”作为注 释内容标记，其后为注释内容。 　　多行注释：使用“/*注释内容*/”格式。可以将注 释插入单独行中或嵌套（只限“--”）在 Transact-SQL 命令行的末端或者Transact-SQL语句中。服务器不对 注释进行解释。 　　注意：不要将GO命令包含在注释中。

6.2 变量 1. 局部变量定义 DECLAER {@变量名称 数据类型}|{@游标变量名称 CURSOR} {,…n} 6.2 变量 　　1. 局部变量定义 DECLAER {@变量名称 数据类型}|{@游标变量名称 CURSOR} {,…n} 　　2. 局部变量的赋值 　　使用SET命令或SELECT命令对局部变量赋值。 SET {@变量名称=表达式} 或 SELECT {@变量名称=表达式} [,…n] 　　3. 系统全局变量 　　全局变量是由系统提供且预先声明的变量，通过 在名称前保留两个@@符号区别于局部变量(@@IDENTITY , @@ERROR )

6.3 运算符 1. 算术运算符 +，-，*，/，%（模余） 2. 赋值运算符 = 3. 按位运算符 6.3 运算符 　　1. 算术运算符 　　+，-，*，/，%（模余） 　　2. 赋值运算符 　　= 　　3. 按位运算符 　　&（按位与运算）、|（按位或运算）和^（按位异或运算 　　4. 比较运算符 　　=、>、<、>=、<=、<>、!=、!<、!>

6.3 运算符 　　5. 逻辑运算符

6.3 运算符 6. 字符串串联运算符 字符串串联运算符允许通过加号（+）进行字 符串串联。 6.3 运算符 　　6. 字符串串联运算符 　　字符串串联运算符允许通过加号（+）进行字 符串串联。 　　例如：print ‘abc’+‘123’的输出结果是abc123。 　　7. 一元运算符 　　+（数值为正）、-（数值为负）和~（返回数 值的补数即按位NOT）

6.4 函数 1. 聚合函数 AVG([ ALL | DISTINCT ] expression) ——返回平均值 6.4 函数 　　1. 聚合函数 AVG([ ALL | DISTINCT ] expression) ——返回平均值 COUNT ( { [ ALL | DISTINCT ] expression } | * ) ) ——返回统计记录行数 MAX|MIN( [ ALL | DISTINCT ] expression ) ——返回最大|最小值 SUM ( [ ALL | DISTINCT ] expression ) ——返回汇总和

6.4 函数 2. 标量函数 GETDATE ( ) ——获取服务器系统日期时间 3. 数学函数 6.4 函数 　　2. 标量函数 GETDATE ( ) ——获取服务器系统日期时间 　　3. 数学函数 ROUND( numeric_expression , length [ , function ] ) 　　4. 字符串函数 SUBSTRING ( expression , start , length )

6.4 函数 5. 系统函数 CAST ( expression AS data_type ) ——类型转换 6.4 函数 　　5. 系统函数 CAST ( expression AS data_type ) ——类型转换 CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) CURRENT_USER ——返回当前数据库用户 HOST_NAME() ——返回工作站名称

6.4 函数 【案例6.1】数据类型转换函数CAST和CONVERT 的用法，将整数转换为字符串。 ——使用CAST 6.4 函数 【案例6.1】数据类型转换函数CAST和CONVERT 的用法，将整数转换为字符串。 ——使用CAST SELECT SUBSTRING(emp_title, 1, 10) AS 职位, emp_salary AS 薪水 FROM employees WHERE CAST(emp_salary AS char(8)) LIKE '3%‘ ——使用 CONVERT WHERE CONVERT(char(20), emp_salary) LIKE '3%'

6.4 函数 6. 用户自定义函数 语法格式1：标量函数（Scalar Functions） 6.4 函数 　　6. 用户自定义函数 语法格式1：标量函数（Scalar Functions） CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ]  parameter_data_type     [ = default ] }     [ ,...n ]  ]) RETURNS return_data_type     [ WITH <function_option> [ ,...n ] ]     [ AS ]     BEGIN                 function_body         RETURN scalar_expression     END[ ; ]

6.4 函数 6. 用户自定义函数 语法格式2：内嵌表值函数 （Inline Table-Valued Functions） 6.4 函数 　　6. 用户自定义函数 语法格式2：内嵌表值函数 （Inline Table-Valued Functions） CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ]  parameter_data_type    [ = default ] }    [ ,...n ]  ]) RETURNS TABLE     [ WITH <function_option> [ ,...n ] ]     [ AS ]     RETURN [ ( ] select_stmt [ ) ] [ ; ]

6.4 函数 6. 用户自定义函数 语法格式3：多语句表值函数 6.4 函数 　　6. 用户自定义函数 语法格式3：多语句表值函数 （Multistatement Table-Valued Functions） REATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ]  parameter_data_type   [ = default ] }    [ ,...n ]  ]) RETURNS @return_variable TABLE < table_type_definition >     [ WITH <function_option> [ ,...n ] ]     [ AS ]     BEGIN                 function_body         RETURN     END[ ; ]

6.5 流程控制语句 1. BEGIN...END语句 【案例6.2】 从 employees 表中， 判断员工平均工资是否大 6.5 流程控制语句 　　1. BEGIN...END语句 【案例6.2】 　　从 employees 表中， 判断员工平均工资是否大 于2800，并显示工资大于 2800的员工姓名和实际工 资数量。 IF (SELECT AVG(emp_salary) FROM employees) >= 2800 BEGIN PRINT '下面列出的是工资大于2800的客户名称和实际工资数量。' PRINT ' ' SELECT emp_name AS 员工姓名, emp_salary AS 员工工资 FROM employees WHERE emp_salary >= 2800 END ELSE PRINT '员工平均工资不到2800元。 BEGIN { sql_statement | statement_block } END

6.5 流程控制语句 2. IF…ELSE语句 【案例6.3】 使用多个 IF ... ELSE 块，对产品型号为 6.5 流程控制语句 　　2. IF…ELSE语句 【案例6.3】 　　使用多个 IF ... ELSE 块，对产品型号为 “MAC重型机械”的产品平均价格和 2800000 比较，分别显示在 2800000上下的该类产品 的名称。 　　IF Boolean_expression 　　 { sql_statement | statement_block } 　　[ ELSE 　　{ sql_statement | statement_block } ]

6.5 流程控制语句 　　2. IF…ELSE语句 IF (SELECT AVG(unitprice) FROM products WHERE ProType = 'MAC重型机械') < 2800000 BEGIN PRINT '下面列出平均价低于280万元的重型机械产品：' PRINT ' ' SELECT product AS 产品名称 FROM products WHERE ProType = 'MAC重型机械' END ELSE IF (SELECT AVG(unitprice) FROM products WHERE unittype = ' MAC重型机械') > 2800000 PRINT '下面列出的是平均价高于280万元的重型机械产品：' PRINT '平均价等于280万元的重型机械产品。'

6.5 流程控制语句 3. CASE语句 格式1：简单CASE函数 格式2：CASE搜索函数 CASE input_expression 6.5 流程控制语句 　　3. CASE语句 格式1：简单CASE函数 CASE input_expression      WHEN when_expression THEN result_expression [ ...n ]      [ELSE else_result_expression  ] END 格式2：CASE搜索函数 CASE    WHEN Boolean_expression THEN result_expression　[ ...n ]    [ELSE else_result_expression ]

6.5 流程控制语句 4. WHILE语句 【案例6.4】 如果产品平均价格少于30元，WHILE循环就将 6.5 流程控制语句 　　4. WHILE语句 【案例6.4】 　　如果产品平均价格少于30元，WHILE循环就将 价格加倍，然后选择最高价。如果最高价少于或等 于 50 元，WHILE 循环重新启动并再次将价格加倍。 该循环就不断地将价格加倍直到最高价格超过50元， 然后退出 WHILE 循环。 WHILE Boolean_expression      { sql_statement | statement_block }      [ BREAK ]      [ CONTINUE ]

6.5 流程控制语句 　　4. WHILE语句 WHILE (SELECT AVG(unitprice) FROM products) < 30 BEGIN UPDATE products SET unitprice = unitprice * 2 SELECT MAX(unitprice) FROM products IF (SELECT MAX(unitprice) FROM products) > 50 BREAK ELSE CONTINUE END

6.5 流程控制语句 5. GOTO语句 【案例6.5】 用GOTO循环 代替WHILE循环 。 语法： 首先定义标签： label: 6.5 流程控制语句 DECLARE @empsalary MONEY, @empid INT, @empcount INT SET @empid = 100 SET @empcount = 0 LOOP_COUNT: WHILE @empid > 0 BEGIN SELECT @empsalary = emp_salary FROM employees WHERE emp_id = @empid IF @empsalary >= 10000 SET @empcount = @empcount + 1 SET @empid = @empid - 1 GOTO LOOP_COUNT END ELSE PRINT (CONVERT(char(10),@empcount)) 　　5. GOTO语句 【案例6.5】 　　用GOTO循环 代替WHILE循环 。 语法： 首先定义标签： label: 使用GOTO跳转 执行： GOTO label

使用案例数据库eTradeInfo，在“SQL Server Management Studio”管理窗口中，完成以下题目对象 案例分析 　　使用案例数据库eTradeInfo，在“SQL Server Management Studio”管理窗口中，完成以下题目对象 创建和Transact-SQL代码调试。 　　1. 编写一个函数，返回所有价格在80至630元的U盘的生 产数量。 　　2. 编写一个函数，返回员工“李小鹏”在2007年第2季度销 售的产品名称、数量。 　　3. 编写一个函数，返回“生产部”所有员工销售产品的员 工姓名、职位以及客户姓名、地址等信息。 　　4. 编写一段代码，根据商品库存数量显示相应的提示， 如果库存量小于500，显示“库存已到临界，需进货”；如果库 存书量大于5000，显示“库存量太大，是否降价促销？”。 　　5. 编写一段代码，自2000年以来，销售数量超过50000 的员工和所在部门员工分别提高薪水10%和5%。

1. 正确用T-SQL语句编写三种格式的自定义 函数，并在“SQL编辑器”窗口中调试； 2. 正确调用自定义函数； 案例分析 要求： 　　1. 正确用T-SQL语句编写三种格式的自定义 函数，并在“SQL编辑器”窗口中调试； 　　2. 正确调用自定义函数； 　　3. 熟练掌握自定义函数的代码调试技术。 问题： 　　1. 不带传递参数的自定义函数有何要求格式？ 　　2. 为何调用自定义函数需要用户名？ 讨论： 　　当查询返回多个值时，使用第 2 种格式与第 3 种格式有何异同？

从语言体系结构出发，介绍Transact-SQL 语言元 素的构成、语法和用法，讲解了Transact-SQL 的各种 本章小结 　　从语言体系结构出发，介绍Transact-SQL 语言元 素的构成、语法和用法，讲解了Transact-SQL 的各种 系统函数及其调用方法。 系统函数是实现数据库操作和编程的重要组成部 分，正确理解和使用这些系统函数具有重要意义。 　　SQL Server 2005支持用户自定义的函数，为用户 编写私有的专用函数提供了手段，从而增强了编程的 灵活性。