Download
slide1 n.
Skip this Video
Loading SlideShow in 5 Seconds..
第 5 章 Transact-SQL 、 存储过程和触发器 PowerPoint Presentation
Download Presentation
第 5 章 Transact-SQL 、 存储过程和触发器

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

238 Views Download Presentation
Download Presentation

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

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  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 值。