第 5 章 Transact-SQL 、 存储过程和触发器

1. 第5章 Transact-SQL、 存储过程和触发器 5.1 Transact-SQL语言 5.2 存储过程与触发器

2. Transact-SQL语言简称T-SQL语言。是微软公司在SQL-Server中使用的流程控制语言。是对标准SQL语言的扩充。Transact-SQL语言简称T-SQL语言。是微软公司在SQL-Server中使用的流程控制语言。是对标准SQL语言的扩充。 主要特点： （1）是一种交互语言，功能强大，简单易学。 （2）既可直接访问数据库，也可嵌入到宿主语言中去执行。 （3）非过程化高，语言的执行由系统自动完成。 （4）所有的T-SQL语言可以在查询分析器中编辑、调试和运行。

3. 5.1 Transact-SQL语言 5.1.1 数据类型（复习回顾） 1. 整型(Bigint、Int、Smallint、tinyint) 整型数参与任何算术运算的结果只保留数值的整数部分。 2. 浮点型(float、real 、Decimal、Numerc) float和real属于近似数据类型。特点是表示范围大,但任何一个数都仅是一个近似值。适合用于科学计算。 Decimal和Numerc是精确数值类型，表示的是一个精确值。适合用于财务金融（如：工资）。 注：最好在WHERE子句中不要引用float类型的字段。

4. 3. 字符型 Char、Varchar、Text、 Nchar 、varchar、Ntext（双字节字符型）。 4. Unicode Unicode（统一码、万国码、单一码）是一种在计算机上使用的字符编码。 5.日期时间型Datetime、Smalldatetime 6.货币数据类型 Money、SmallMoney 相当于带货币符号的Decimal类型。精确到0.0001货币单位。 7.Bit位类型（也称 逻辑类型） 可以取值为 1、0 整数数据类型。用0表示false，1表示true。

5. 8.二进制类型（Binary、Varbinary、image） 存储二进制数据。常用于存储音频视频图像等字节流式数据。 数据表字段中仅存放指针，指向数据实际存放地址。 9.其他类型（Sql_variant、table） Sql_variant: 可存储SQL Server支持的所有数据类型(text、ntext、timestamp和Sql_variant除外)值的数据类型。方便SQL Server的开发工作。 Table: 用于存储对表或视图处理后的结果集。只能用于Transact-SQL用户自定义函数的返回值。

6. 5.1.2 标识符、变量和运算符 1. 标识符 标识符是指用户定义的数据库对象的名称以及变量名称。分为标准标识符和定界标识符。 命名规则： 长度为1~128个字符,不区分大小写字母。可由： 汉字、字母、数字、 _、@、$、#字符构成。 以符号开头的标识符有专门的用途： • 以@开始的标识符，表示局部变量或参数。 • 以#开始的标识符，表示是局部数据库对象。 例如，局部临时表或过程等。

7. 以@@开始的标识符，表示全局变量或参数 • 以两个##开始的标识符，表示全局临时对象。 2. 变量 变量分为局部变量和全局变量。 (1) 局部变量 • 用户定义, 在DECLARE语句中声明 • 在SET或SELECT语句中赋值 局部变量声明: DECLARE @变量名 数据类型 [, @变量名 数据类型] 例：DECLARE @x INT,@y INT

8. 局部变量赋值:(一条语句只能给一个变量赋值) SET @变量名1=表达式| ( SELECT 子查询 ) ] 示例：SET @X=12 或(一条语句为多个变量赋值) SELECT @变量名1=表达式1|( SELECT 子查询) [, @变量名2=表达式| (SELECT 子查询) ] 示例：SELECT @X=12, @Y=23 或 SELECT @变量名1= 字段表达式1[, @变量名2= 字段表达式2] FROM 表名 WHERE 条件--查询赋值，与查询语句结合使用

9. 说明： • 当子查询结果为多个值时，保留最后那个值赋给变量。如果“子查询”没有返回值，则局部变量值为NULL。 例：DECLARE @age INT SELECT @age=SAGE FROM S WHERE SDEPT='计算机' SELECT @age as 年龄 注意：一个SET语句只能为一个变量赋值，而一个SELECT语句可为多个变量赋值。

10. 例：定义一个变量@score，将选课表中的最高成绩赋值给它。例：定义一个变量@score，将选课表中的最高成绩赋值给它。 Use student --选择当前数据库 DECLARE @score int --变量声明语句 SELECT @score=MAX(grade) --赋值语句 FROM sc SELECT @score AS 最高成绩 --结果按“最高成绩”输出 SELECT @score=(SELECT MAX(grade) FROM sc) -- 为之后注释语句

11. 其他Transact-SQL语句还有： (1) /*……*/: 注释语句, 用于多行注释 (2) - -(注释语句):用于单行或嵌套注释 (3) PRINT: 终端输出语句 格式: PRINT 文本字符串| @字符数据类型变量| @@返回字符串结果的函数|字符串表达式 功能: 将用户定义的消息返回客户端。必须是 char 或 varchar，或者能够隐式转换为这些数据类型。 示例：PRINT 'ABCDEFG'

12. 例：定义变量@score、@sno、@cno, 类型分别为 int 、char、char, 并赋值给它们。 DECLARE @score int , @sno char(6) DECLARE @cno char(4) --变量声明 SELECT @sno='s090011', @cno='c002'--变量赋值 SELECT @score=grade --查询赋值 FROM sc WHERE sno= @sno and cno=@cno SELECT @score AS 成绩 --查询变量值

13. (2)全局变量 • 系统提供、系统赋值 • 只报告系统活动信息和连接信息 • 可以在脚本中引用 全局变量由SQL Server系统定义和维护，用户不能给全局变量赋值或直接更改其中的值。 全局变量的名字前有两个@, 即@@。 全局变量对于检查SQL Server环境的某些信息或条件是有用的。 例：查看SQL Server版本号。 SELECT @@version

14. 3. 运算符(复习回顾） 运算符的类型： • 算术运算符 • 比较运算符 • 字符串连接运算符 • 逻辑运算符 (1) 算术运算符：+、-、*、/、%(求余) (2) 比较运算符: =、<>(不等于)、>、>=、<=、 <、!=(不等于, 非SQL92标准)、!<(不小于)、!>

15. (3) 字符串连接运算符：＋。 例： print 'asd' + 'dfg'+ '1234' 结果为： 'asddfg1234 ' 空串(‘ ')作为单个空格处理。 (4)逻辑运算符 例： 'abc '+ ' '+ 'efg '，结果为'abc egf '.

16. (5) 运算符优先级 括号→算术运算符(字符串连接符) → 比较运算符→逻辑运算符→赋值（由高到低）

17. 5.1.3 函数 SQL Server提供了非常丰富的函数供用户使用，同时也允许用户定义自己的函数。 • Transact-SQL提供的系统函数: 字符函数 （1） SUBSTRING 函数 格式：SUBSTRING (<字符表达式>,<m>[,<n>]) 功能：从字符表达式中的第m个字符开始截取n个字符，形成一个新字符串，m,n都是数值表达式。 （2） LTRIM函数 格式：LTRIM (<字符表达式>) 功能：删除字符串起始空格函数，返回varchar类型数据

18. （3）RTRIM函数 格式：RTRIM (<字符表达式>) 功能：删除字符串尾随空格函数，返回varchar类型数据。 （4）RIGHT函数 格式：RIGHT (<字符表达式>,<数据表达式>) 功能：返回字符串中从右边开始指定个数的字符，返回varchar类型数据。 （5）LEFT函数 格式：LEFT (<字符表达式>,<数据表达式>) 功能：返回字符串中从左边开始指定个数的字符，返回varchar类型数据。

19. （6）UPPER函数 格式：UPPER(<字符表达式>) 功能：将小写字符数据转换为大写的字符表达式，返回varchar类型数据。 （7）LOWER函数 格式：LOWER (<字符表达式>) 功能：将大写字符数据转换为小写的字符表达式，返回varchar类型数据。 （8）REVERSE函数 格式：REVERSE (<字符表达式>) 功能：返回字符表达式的反转。返回varchar类型数据。

20. （9）SPACE函数 格式：SPACE(<整数表达式>) 功能：返回由重复的空格组成的字符串。整数表达式的值表示空格个数。返回char类型数据。 （10）STUFF函数 格式：STUFF(字符表达式1, m ,n , 字符表达式2 ) 功能：删除指定长度的字符并在指定的起始点插入另一组字符。m,n是整数，m指定删除和插入的开始位置,n指定要删除的字符数,最多删除到最后一个字符。如果m或n 是负数，则返回空字符串。如果m比字符表达式1 长，则返回空字符串。返回char类型数据。 （11）CHARINDEX函数 格式： CHARINDEX (表达式1 , 表达式2 [ , m ] ) 功能： 在表达式2的第m个字符开始查找表达式1起始字符位置。m是整数表达式，如果m是负数或缺省，则将从表达式2 的起始位置开始搜索。返回int类型数据。

21. （12）LEN函数 格式： LEN (字符表达式) 功能： 返回给定字符串表达式的字符个数，不包含尾随空格。 （13）ASCII函数 格式： ASCII(字符表达式) 功能： 返回给定字符串表达式的最左端字符的ASCII码值。返回整型值。 （14）CHAR函数 格式： CHAR(整数表达式) 功能： 用于将ASCII码转换为字符，整数表达式的取值范围为0到255之间的整数，返回字符型数据值。

22. 数学函数 （1）ABS函数 格式：ABS(数字表达式) 功能： 返回给定数字表达式的绝对值。 （2）EXP函数 格式：EXP (数字表达式) 功能： 返回给定数字表达式的指数值。参数数字表达式是 float 类型的表达式。返回类型为float。 （3）SQRT函数 格式：SQRT(数字表达式) 功能： 返回给定数字表达式的平方根。参数数字表达式是 float 类型的表达式。返回类型为float。

23. （4）ROUND函数 格式：ROUND (数字表达式，m) 功能：返回返回数字表达式并四舍五入为指定的长度或精度。 （5）RAND函数 格式：RAND ([seed]) 功能：返回 0 到1 之间的随机float 值。参数seed为整型表达式。

24. 日期和时间函数 （1）DATEADD函数 格式： DATEADD (日期参数, 数字, 日期) 功能： 在向指定日期加上一段时间的基础上，返回新的 datetime 值。日期参数规定了新值的类型。参数有：Year、Month、Day、Week、Hour （2）GETDATE函数 格式： GETDATE ()

25. （3）DAY函数 格式： DAY (日期) 功能：返回代表指定日期的“日”部分的整数。返回类型为int。 （4）YEAR函数 格式：YEAR(日期) 功能：返回表示指定日期中的年份的整数。返回类型为int。 （5）MONTH函数 格式：MONTH (日期) 功能：返回表示指定日期中的月份的整数。返回类型为int。

26. 数据转换函数 （1）CAST函数 格式：CAST (表达式 AS 数据类型 ) 功能： 将指定的表达式转换成对应的数据类型。 （2）CONVERT函数 格式：CONVERT (数据类型[(长度)], 表达式[,样式]) 功能：样式是指日期格式样式。 系统函数 函数DB_NAME()的功能是返回数据库的名称。 函数HOST_ NAME()的功能是返回服务器端计算机的名称。 函数HOST_ID()的功能是返回服务器端计算机的ID号。 函数USER_NAME()的功能是返回用户的数据库用户名。

27. 2. 用户自定义函数 用户可以根据应用需要定义自己的函数。 自定义函数分三种：标量函数、内嵌表值函数、多语句表值函数。 （1）标量函数 标量函数指函数返回单个值(字符串\数值等)。 语法格式： CREATE FUNCTION [拥有者.]函数名 ([{ @形参名1[AS]数据类型1[=默认值]}[,…n]]) RETURNS 函数返回值数据类型 [AS] BEGIN 函数体 RETURN 返回值表达式 END

28. 功能：函数可以声明一个或多个形参（最多1024个），执行函数时，需要提供形参的值，除非该形参定义了默认值，调用时 函数名(DEFAULT)，获得默认值。函数体由一组SQL语句组成。 例创建一个标量函数，返回某学生的平均分数。 学号作为函数参数。 CREATE FUNCTION get_avg ( @sno char(6) ) RETURNS int AS BEGIN DECLARE @temp int SELECT @temp =AVG(grade) FROM sc WHERE sno= @sno RETURN @temp END

29. 调用： 1）在SELECT语句中调用 格式：SELECT 拥有者.函数名（实参1,…,实参n） 说明：实参可为已赋值的局部变量或表达式。实参与形参要顺序一致。 2）使用EXEC语句调用 格式1：EXEC 拥有者.函数名 实参1,…,实参n 格式2：EXEC 拥有者.函数名 形参1=实参1,…,形参n=实参n 说明：格式1要求实参与形参顺序一致，格式2的参数顺序可与定义时的参数顺序不一致。 可以用下列语句调用get_avg函数： SELECT dbo.get_avg('s09001') AS 's1平均成绩' 注意：调用时必须给出用户名dbo。它是函数创建者。

30. declare @a int EXEC @a=dbo.get_avg 's09001' select @a as 平均成绩 declare @a int EXEC @a=dbo.get_avg @sno='s09001' select @a as 平均成绩

31. （2）内嵌表值函数 内嵌表值函数指 函数返回值是一个表. 语法格式： CREATE FUNCTION 函数名 ([{ @参数名1[AS]数据类型1[=默认值]}[,…n]]) RETURNS TABLE AS RETURN [( ] SELECT查询语句[ ) ] 内嵌表值函数，返回值是一个表。内嵌函数体没有相关联的返回变量。通过SELECT语句返回内嵌表。RETURN 定义了单个 SELECT 语句，它是返回值。

32. 例创建一个表值函数，返回平均分数大于或等于指定分数的学生学号和平均分数。函数参数为给定的一个“分数”。例创建一个表值函数，返回平均分数大于或等于指定分数的学生学号和平均分数。函数参数为给定的一个“分数”。 CREATE FUNCTION get_all_avg ( @score int ) RETURNS TABLE AS RETURN SELECT sno, AVG(grade) AS 平均成绩 FROM sc GROUP BY sno HAVING AVG(grade)>= @score 注意：表达式AVG(grade) 必须给出别名。

33. 内嵌表值函数调用格式： SELECT …… FROM [拥有者.][内嵌表值函数](实参1,…实参n) 说明：内嵌表值函数只能使用SELECT语句调用。 例：查询平均成绩大于60分的学生信息。 SELECT * FROM dbo.get_all_avg(60) 例：查询平均成绩大于80分的学生信息 SELECT s.sno, sname , 平均成绩 FROM get_all_avg(80) , s WHERE get_all_avg.sno = s.sno

34. (（3）多语句表值函数 格式： CREATE FUNCTION [ 拥有者.] 函数名 ([{ @参数名1[AS]数据类型1[=默认值]}[,…n]]) RETURNS @表变量 TABLE < 表的属性定义> [ WITH <{ ENCRYPTION | SCHEMABINDING }> [,…n]] [ AS ] BEGIN 函数体 RETURN END 功能 函数体由一组在表变量中插入记录行的语句组成。

35. 上例： CREATE FUNCTION get_all_avg1 ( @score int ) RETURNS @a TABLE(学号char(6),平均成绩int) AS begin insert @a SELECT sno, AVG(grade) AS 平均成绩 FROM sc GROUP BY sno HAVING AVG(grade)>= @score return End 例：查询平均成绩大于60分的学生信息。 SELECT * FROM dbo.get_all_avg1(60)

36. (4) 删除用户自定义函数 DROP FUNCTION 函数名 (5) 修改用户自定义函数 ALTER FUNCTION 函数名 参数定义与代码

37. 作 业 ： 实训六 本节结束

38. 上机 ： 实训六

39. 5.1.4 流程控制语句 流程控制语句用于控制SQL语句、语句块、存储过程或触发器的执行流程。 主要的流程控制语句有：

40. 1. BEGIN … END语句 该语句将多条SQL语句封装在一起，构成一个语句块。主要语句块就可以在IF/ELSE、WHILE等语句中作为一个整体来执行。 语法格式： BEGIN 若干SQL语句 END 功能：BEGIN...END 语句将多个SQL 语句组合成一组语句块，并将些语句块视为一个单元。BEGIN...END 语句块允许嵌套。

41. 2. IF…ELSE语句 语法格式： IF 条件 语句 | 语句块1 [ ELSE 语句 | 语句块2 ] 功能：IF...ELSE语句是双分支条件判断语句，根据某个条件的成立与否，来决定执行哪组语句。如果省略ELSE，则为单分支语句。

42. 例：至少有一门课程的成绩大于90的学生的人数。例：至少有一门课程的成绩大于90的学生的人数。 DECLARE @num int SELECT @num= (SELECT COUNT(DISTINCT SNO) FROM SC WHERE GRADE>90) IF @num<>0 SELECT @num AS '成绩大于的人数'

43. 例查询选修c001课的学生成绩，如有大于90分以上的，则将其姓名显示出来；若无人大于90分，则显示“成绩优秀者为0个”。例查询选修c001课的学生成绩，如有大于90分以上的，则将其姓名显示出来；若无人大于90分，则显示“成绩优秀者为0个”。 IF EXISTS( SELECT * FROM sc WHERE cno='c001' AND grade>=90 ) SELECT sname FROM s ,sc WHERE s.sno=sc.sno AND cno='c001' AND grade>=90 ELSE PRINT '成绩优秀者为0个'

44. WHILE 条件 SQL语句| 语句块 [ BREAK ] SQL语句| 语句块 [ CONTINUE ] 循环体 3. WHILE语句 语法格式： 1）当条件为真时，重复执行 SQL 语句或语句块，直到条件为假。可以使用 BREAK 和 CONTINUE 语句改变WHILE 循环的执行。 2）END 关键字为循环结束标记。BREAK语句可以完全退出本层WHILE循环，执行END后面的语句。 3）CONTINUE语句回到循环的第一行命令，重新开始循环

45. 例引用已建函数get_all_avg，分别求出平均成绩大于60、70、80、90的学生成绩信息。例引用已建函数get_all_avg，分别求出平均成绩大于60、70、80、90的学生成绩信息。 DECLARE @j int SET @j=60 WHILE @j<100 BEGIN SELECT *, @j as '平均成绩大于' FROM get_all_avg(@j) SET @j=@j+10 END

46. 4. WAITFOR语句 WAITFOR语句可以指定在某一时间点或时间间隔后执行SQL语句、语句块、存储过程或事务。 语法格式： WAITFOR DELAY ‘time’| TIME ‘time’ time 格式为：hh:mm:ss 。

47. 例：对学生选课关系(1) 设置在9:00执行一次查询操作；(2)再设置在1分钟以后再执行一次查询操作，查看学生选课情况。 BEGIN WAITFOR TIME '9:00' -- 等到9点 SELECT sno,cno FROM sc END BEGIN WAITFOR DELAY ‘00:01’ -- 延迟1分钟 SELECT sno, cno FROM sc END

48. 5. RETURN语句 • RETURN无条件退出语句。可在任何时候用于从过程、批处理或语句块中退出。不执行位于 RETURN 之后的语句。 格式：RETURN [ 整数表达式 ] RETURN语句可以返回整数值。一般用于表示存储过程或应用程序的执行状态。如，所有系统存储过程返回 0 值表示成功，返回非零值则表示失败。 注意：当用于存储过程时，RETURN 不能返回空值。

49. 6. CASE 表达式 • CASE 表达式用于多分支结构，有两种语法格式。 (1) 简单CASE表达式 CASE 表达式 WHEN 表达式1 THEN 结果表达式1 […] WHEN 表达式n THEN 结果表达式n [ELSE 结果表达式n+1] END

50. 说明： • <表达式>可以是常量、属性名、函数、子查询和算术运算符、字符串运算符等组合的有意义的式子。 • 执行过程：计算表达式的值。按书写顺序计算表达式1的值，如果 表达式=表达式1， 返回结果表达式1，否则，继续计算表达式2的值，如果表达式=表达式2，返回结果表达式2，以此类推。如果所有的 “表达式= 表达式<n>”为FLASE，则返回ELSE 后的表达式n+1；如果没有指定 ELSE 子句，则返回 NULL 值。