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

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

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

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

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。

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

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

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

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

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

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

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

例：定义变量@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 成绩 --查询变量值

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

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

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

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

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

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

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

（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类型数据。

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

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

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

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

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

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

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

功能：函数可以声明一个或多个形参（最多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

调用： 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。它是函数创建者。

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 平均成绩

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

例创建一个表值函数，返回平均分数大于或等于指定分数的学生学号和平均分数。函数参数为给定的一个“分数”。例创建一个表值函数，返回平均分数大于或等于指定分数的学生学号和平均分数。函数参数为给定的一个“分数”。 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) 必须给出别名。

内嵌表值函数调用格式： 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

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

上例： 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)

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

作业： 实训六本节结束

上机： 实训六

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

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

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

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

例查询选修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个'

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

例引用已建函数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

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

例：对学生选课关系(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

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

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

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

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