SQL Server高级应用.ppt

1、本章提要,本章通过大量实例介绍SQL语句的高级应用，包 括游标、存储过程、触发器、事务处理的功能及基本 操作。它们是网络数据库编程的重要内容，是开发网 络数据库应用程序的关键技术。,第7章 SQL Server高级应用,7.1 游标,7.2 存储过程,7.3 触发器,7.4 事务处理,7.1 游标,7.1.1 游标概述,游标是类似于C语言指针一样的结构,面对包括多 条数据记录的结果集, 游标能完成每次只提取一条记 录的操作。游标由两部分组成：结果集和在结果集中 指向某一记录位置的指针。游标提供了一种在服务器 内部处理结果集的方法，它可以识别一个数据集合内 部指定的工作行，从而可以有选择地按行采

2、取操作。,第七章 SQL Server高级应用,第七章 SQL Server高级应用,7.1.2 声明游标,在使用游标之前首先要声明游标。用T-SQL声明游标的 语法如下： declare 游标名 insensitive scroll cursor for select 语句 for read only |update of 列名1,列名2.,语法注释： 游标名 为声明的游标取的名字，游标名必须符合标识符的命名规则。 insensitive 使用insensitive定义的游标，会将提取出来的数据存放在系统数据库 tempdb创建的一个临时表里。任何通过该游标进行的操作都在临时表里进行， 因

3、此所有对基本表的改动都不会在游标中体现出来。 scroll 使用scroll定义的游标，称为滚动游标，可以提取任意位置的数据行。若无 此关键字，只能按顺序提取。 select 语句 指标准的select语句，用来定义游标所要处理的结果集。 read only 声明只读游标，不允许通过游标进行数据更新。 update of 列名1,列名2. 声明可更新的游标中允许更新的列。若只选update， 则所有的列都可更新。,第七章 SQL Server高级应用,在使用游标之前，必须先打开游标。利用T-SQL打开游 标的语法格式： open 游标名 功能注释： 当执行打开游标的语句时，服务器执行声明游标时

4、使用 的select语句。此时，用游标提取数据的当前位置是数 据集的第一条记录。,7.1.3 打开游标,第七章 SQL Server高级应用,语法注释： （1）使用scroll关键字声明的游标，具有以下所有的取数功能： first 取第一行数据 prior 取前一行数据 next 取下一行数据 last 取最后一行数据 absolute 按绝对位置取数据，从游标中取出第n行记录 relative 按相对位置取数据，读出从游标当前位置向后第行记录。 （2）into 变量名表 使用into 子句对变量赋值时，变量的数量、类型、顺序必须 和声明游标时使用的select语句输出的列项一一对应。,7.1

5、.4 读取游标数据,打开游标之后，就可以用游标读取数据了。利用T-SQL读取游标的语法如下： fetch first | prior | next | last|absolute t n | relative n from 游标名 into 变量名表,第七章 SQL Server高级应用,程序构思： 本例声明一个游标，用来逐条显示“学生表”中性别为 “女”的记录。 操作步骤： （1）单击【开始】【所有程序】【Microsoft SQL Server 2005】【SQL Server Management Studio】，打开【连接到服务器】对话框，单击【连 接】钮，弹出【Microsoft S

6、QL Server Management Studio】管理器窗口。 （2）单击常用工具栏中的 钮，则建立了 一个数据库引擎查询文档，文档名默认为 SQLQuery1.sql。,7.1.5 实例：定义一个滚动游标,第七章 SQL Server高级应用,（3）在SQLQuery1.sql文档界面输入代码： use 学生成绩管理 declare cursor1 scroll cursor for select * from 学生表 where 性别=女 open cursor1 fetch first from cursor1 while fetch_status=0 begin fetch ne

7、xt from cursor1 end （4）单击工具栏上的 按钮,或按F5键,fetch_status是一个全局变量，若其值为0，则表示fetch命令被成功执行。,第七章 SQL Server高级应用,7.1.6 实例：读取游标中的数据存入变量中,程序构思： 本例声明的游标，其结果集为选修“三维动画设计”课程 成绩最高的学生的学号、姓名、课程名称和成绩，并将 从游标中读取的数据存放到局部变量中，再输出变量中 的值。 操作步骤： （1）单击【开始】【所有程序】【Microsoft SQL Server 2005】【SQL Server Management Studio 】，打开【连接到服务器

8、】对话框，单击【连接】钮， 弹出【Microsoft SQL Server Management Studio】管 理器窗口。 （2）单击常用工具栏中的 钮，则建立了一个 数据库引擎查询文档，文档名默认为SQLQuery1.sql。,第七章 SQL Server高级应用,（3）在SQLQuery1.sql文档界面输入代码： use 学生成绩管理 declare cursor2 scroll cursor for select a.学号,姓名,课程名称,成绩 from 学生表 a ,课程表 b,成绩表 c where 课程名称=三维动画设计 and c.学号=a .学号 and b .课程编号=

9、c. 课程编号 order by 成绩 desc,open cursor2 declare num char(20) declare name char(20) declare score_name char(20) declare max int fetch first from cursor2 into num,name,score_ name,max select num as 编号,name as 名字,score_name as 课名,max as 分数 （4）单击工具栏上的 按钮,或按F5键 。,第七章 SQL Server高级应用,语法注释： current of 游标名 表示当

10、前游标的当前数据行，该子句只能在update和delete操作 的语句中使用。,7.1.7 利用游标更新数据和删除数据,要通过游标对数据进行修改，该游标必须被声明为可 更新的游标。在声明游标时，只要不使用for read only 关键字，游标都是可更新的。 利用T-SQL更新游标的语法格式： update 表名 set 列名=表达式， where current of 游标名 利用T-SQL删除游标的语法格式： delete from 表名 set 列名=表达式， where current of 游标名,第七章 SQL Server高级应用,程序构思： 本例声明的游标，其执行结果集为没有被

11、选修的课程 信息，包括课程编号、课程名、学分。统计结果集中 的数据行数，并显示相应的提示信息。,7.1.8 实例：利用游标统计数据行数,操作步骤： （1） 单击【开始】 【所有程序】【Microsoft SQL Server 2005】【SQL Server Management Studio】，打开 【连接到服务器】 对话框，单击【 连接】钮，弹出【Microsoft SQL Server Management Studio】管理器窗口。 （2） 单击常用工具栏中的 钮， 则建立了一个数据库引擎查询文档， 文档 名默认为SQLQuery1.sql。,第七章 SQL Server高级应用,（3

12、）在SQLQuery1.sql文档界面输入代码： use 学生成绩管理 declare cursor3 scroll cursor for select distinct a.课程编号,课程名称,学分 from 课 程表 a,成绩表 b where a.课程编号not in (select distinct 课程编 号 from成绩表),open cursor3 fetch first from cursor3 while fetch_status=0 begin fetch next from cursor3 end select cursor_rows as 未被选修的课程数 打开游标后，

13、可以使用全局变量cursor_rows返回当前游标可以操作的数据 行数。 （4）单击工具栏上的 按钮,或按F5键,第七章 SQL Server高级应用,2释放游标 游标结构本身也会占用一定的计算机资源，所以在使用完游标后，为了回收被占 用的资源，应该将游标释放。 利用T-SQL释放游标的语法格式： deallocate 游标名 当释放完游标以后，如果要重新使用这个游标，则必须重新执行声明游标的语句。,7.1.9 关闭游标和释放游标,1关闭游标 在打开游标之后，SQL Server服务器会专门为游标 开辟一定的内存空间存放游标操作的数据结果集， 同时 游标的使用也会根据具体情况对某些数据进行封锁

14、。 所 以，在不使用游标的时候，一定要关闭游标，以通知服 务器释放游标所占用的资源。 利用T-SQL关闭游标的语法格式： close 游标名 关闭游标之后，可以再次打开游标，在一个批处理中， 可以多次打开和关闭游标。,第七章 SQL Server高级应用,程序构思： 本例声明的游标，其执行结果集为成绩最低的学生 信息，包括学号、姓名、所属院系、成绩，通过游标在 成绩表中将成绩最低的一条记录删除。 操作步骤： （1）单击【开始】【所有程序】【Microsoft SQL Server 2005】 【SQL Server Management Studio】，打开【连接到服务器】对话框，单击 【 连

15、接 】钮， 弹出 【 Microsoft SQL Server Management Studio】管理器窗口。 （2）单击常用工具栏中的 钮，则建立了一 个数据库引擎查询文档，文档名默认为 SQLQuery1.sql。,7.1.10 实例：利用游标删除表中的一行,第七章 SQL Server高级应用,（3）在SQLQuery1.sql文档界面输入代码： use 学生成绩管理 declare cursor4 scroll cursor for select a.学号,姓名,所属院系,成绩 from 学生表 a, 成绩表 b where a.学号=b.学号 and 成绩=(select min(

16、成绩) from 成绩表),open cursor4 fetch first from cursor4 delete from 成绩表 where current of cursor4 close cursor4 deallocate cursor4 （4）单击工具栏上的 按钮,或按F5键 。,第七章 SQL Server高级应用,7.2 存储过程,7.2.1 存储过程概述,存储过程具有以下优点： （1）由一系列T-SQL语句组成，可作为一个整体被程序多次调用； （2）修改容易，可移植性好； （3）存储过程是预编译的，执行速度快； （4）在网络中调用过程，只需要传送调用过程的语句，因此可减少网

17、络流量； （5）可通过对执行存储过程的权限控制来保证系统数据的安全； （6）存储过程可嵌套调用，即在一个存储过程中调用另一个存储过程。,第七章 SQL Server高级应用,7.2.2 存储过程的创建与执行,1创建存储过程 利用T-SQL创建存储过程的语法格式： create proc 过程名 parameter 参数类型 /*输入参数*/ parameter 参数类型 ouput /*输出参数*/ as begin 命令行或命令块 end,2执行存储过程 利用T-SQL执行存储过程的语法格式： execute 过程名 参数1,参数2， output,第七章 SQL Server高级应用,程序

18、构思： 本例存储过程的功能是显示所有选修了“数据库技 术及网络应用”课程的学生的学号、姓名、所属院系和 成绩。 操作步骤： （1）单击【开始】【所有程序】【Microsoft SQL Server 2005 】 【SQL Server Management Studio】，打开【连接到服务器】对话框，单击 【连接】钮，弹出【Microsoft SQL Server Mana -gement Studio】管理器窗口。 （2）单击常用工具栏中的 钮，则建立了一个 数据库引擎查询文档，文档名默认为 SQLQuery1.sql。,7.2.3 实例：不带参数的存储过程,第七章 SQL Server高级

19、应用,（3）在SQLQuery1.sql文档界面输入代码： use 学生成绩管理 go create proc proc1 as begin,select a.学号,姓名,所属院系,成绩 from 学生表 a,课程表 b,成绩表 c where 课程名称=数据库技术及网络应用 and b.课程编号=c.课程编号 and c.学号=a.学号 end go execute proc1 /*执行存储过程*/ （4）单击工具栏上的 按钮,或按F5键 。,第七章 SQL Server高级应用,程序构思： 本例存储过程的功能是查询担任某门课程的教师的 基本信息。执行存储过程时，允许带输入参数，即课程 名称

20、。存储过程的执行结果显示教师的姓名、职称和联 系电话。,7.2.4 实例：带输入参数的存储过程,操作步骤： （1）单击【开始】【所有程序】【Microsoft SQL Server 2005】【SQL Server Management Studio】，打开【连接到服务器】对话框，单击【连 接】钮，弹出【Microsoft SQL Server Management Studio】管理器窗口。 （2）单击常用工具栏中的 钮，则建立了一个数据库引擎查询文档，文档 名默认为SQLQuery1.sql。,第七章 SQL Server高级应用,（3）在SQLQuery1.sql文档界面输入代码： us

21、e 学生成绩管理 go create proc proc2 scrose_name varchar(20) as begin select 姓名,职称,联系电话 from 课程表 a,教师表 b where 课程名称= scrose_name and a.教师编号 =b.教师编号 end go execute proc2 C语言程序设计 /*执行存储过程*/ （4）单击工具栏上的 按钮,或按F5键 。,第七章 SQL Server高级应用,7.2.5 实例：带输入和输出参数的存储过程,程序构思： 本存储过程的功能是统计某院系学生的最大年龄和 平均年龄。执行存储过程时，允许带输入参数和输出参 数

22、。输入参数为所属院系名称，输出参数保存所执行的 结果，即最大年龄和平均年龄。,操作步骤： （1） 单击【开始】 【所有程序】 【Microsoft SQL Server 2005】【SQL Server Management Studio】，打开【连接到服务器】对话框，单击【连接】 钮，弹出【Microsoft SQL Server Management Studio】管理器窗口。 （2） 单击常用工具栏中的 钮，则建立了一个数据库引擎查询文档，文档 名默认为SQLQuery1.sql。,第七章 SQL Server高级应用,（3）在SQLQuery1.sql文档界面输入代码： use 学生成

23、绩管理 go create proc proc3 yuanxi char(20), maxage int output, average real output as begin,select maxage=max(datepart(yy,getdate()-datepart(yy,出生日期) from 学生表 where 所属院系=yuanxi select average=avg(datepart(yy,getdate()-datepart(yy,出生日期) from 学生表 where 所属院系=yuanxi end go declare x1 int,x2 real execute p

24、roc3 教育学院 ,x1 output,x2 output select x1 最大年龄,x2 平均年龄 （4）单击工具栏上的 按钮,或按F5键 。,第七章 SQL Server高级应用,7.2.6 存储过程的修改、重命名和删除,1修改存储过程 利用T-SQL修改存储过程的语法格式： alter proc 过程名 parameter 参数类型 /*输入参数*/ parameter 参数类型 ouput /*输出参数*/ ,as begin 命令行或命令块 end 修改存储过程是指修改存储过程的内容。如果要修改某 一存储过程，在创建该存储过程的代码中，将命令动词 creat改为alter，再修

25、改其内容即可。,第七章 SQL Server高级应用,2. 存储过程的重命名1修改存储过程 利用T-SQL修改存储过程的语法格式： alter proc 过程名 parameter 参数类型 /*输入参数*/ parameter 参数类型 ouput /*输出参数*/ ,as begin 命令行或命令块 end 修改存储过程是指修改存储过程的内容。如果要修改某一存储过程， 在创建该存储过程的代码中，将命令动词creat改为alter，再修改其 内容即可。,第七章 SQL Server高级应用,3删除存储过程 利用T-SQL语句删除存储过程的语法格式： drop proc 存储过程名1,存储过程

26、名2， 删除存储过程是指将存储过程作为数据库对象从数据库中彻底删除。一次可删除多 个存储过程。,利用T-SQL语句给存储过程重命名的语法格式： sp_rename 原过程名, 新过程名 给存储过程重命名只是修改存储过程的名字，和存储过 程内容本身无关。该功能用系统存储过程sp_rename实 现，该系统存储过程存放在master数据库里，在任何数 据库中都可直接调用它。,7.2.7 实例：修改存储过程,第七章 SQL Server高级应用,程序构思： 本例是将存储过程pro3的功能修改为输入院系名称， 输出该院系学生的人数。 操作步骤： （1）单击【开始】【所有程序】【Microsoft SQ

27、L Server 2005】 【SQL Server Management Studio】，打开【连接到服务器】对话框，单击 【连接】钮， 弹出 【 Microsoft SQL Server Management Studio】管理器窗口。 （2）单击常用工具栏中的 钮，则建立了一 个数据库引擎查询文档，文档名默认为 SQLQuery1.sql。,第七章 SQL Server高级应用,（3）在SQLQuery1.sql文档界面输入代码： use 学生成绩管理 go alter proc proc3 yuanxi char(20), count int output as begin selec

28、t count=count(*) from 学生表 where 所属院系=yuanxi end go declare x int execute proc3 物电学院 ,x output select x 人数 （4）单击工具栏上的 按钮,或按F5键 。,此例中，若想输出院系名称和人数，即将输入参数也作为结果输出，应怎样改进？,7.2.8 实例：重命名和删除存储过程,程序构思： 本例是将将存储过程proc1删除,再将存储过程名 pro3改为pro3new。 操作步骤： （1）单击【开始】【所有程序】【Microsoft SQL Server 2005 】 【 SQL Server Manage

29、ment Studio】，打开【连接到服务器】对话框，单击 【 连接 】钮 ， 弹出【 Microsoft SQL Server Management Studio】管理器窗口。 （2）单击常用工具栏中的 钮，则建立了一个 数据库引擎查询文档，文档名默认为 SQLQuery1.sql。,第七章 SQL Server高级应用,（3）在SQLQuery1.sql文档界面输入代码： drop proc proc1 sp_rename proc3, proc3newnew （4）单击工具栏上的 按钮,或按F5键 。,第七章 SQL Server高级应用,7.3.1 触发器概述,1.触发器的概念 触发器

30、是一种特殊的存储过程，它和表密切相连， 可以看作是表格定义的一部分 。 当用户对指定表操作 时， 触发器会自动执行。 触发器在 update、insert、 delete等操作执行结束后才执行。,2.触发器的功能 (1)级联更新数据库中相关表的数据； (2)实现多表之间数据的一致性； (3)执行比检查约束更复杂的约束操作； (4)调用存储过程； (5)在一张表的update、insert、delete操作上可设置多个触发器。,7.3 触发器,第七章 SQL Server高级应用,3.使用触发器应注意的问题 (1)当使用约束、规则、默认值等方法能够实现数据的完 整性，就不用触发器实现； (2)只

31、有表的拥有者才可以在表上创建或删除触发器，这 种权限不准转授； (3)使用update语句可以一次对多行数据进行修改，而触 发器只被触发一次； (4)触发器只能在当前数据库中创建，触发器的命名必须 要遵守标识符的命名规则。,4.触发器的优点 (1)触发器是自动的； (2)触发器可以通过数据库中的相关表进行层叠更改； (3)触发器可以强制限制，这些限制比用 check 所定义的约束更复杂。,第七章 SQL Server高级应用,7.3.2 触发器的工作原理,inserted表和deleted表的特点如下： （1）这两张表是逻辑表，由数据库管理，用户不能对 他们进行修改； （2）这两张表存储在内存

32、中，而不是存储在数据库中； （3）这两张表的结构与被该触发器作用的表结构相同； （4）当触发器完成操作后，这两张表会自动删除； （5）两张表中保存的数据是因用户操作而被影响到的 原数据或新数据； （6）这两张表是只读表。,利用T-SQL创建触发器的语法格式： creat trigger 触发器名称 on 表名 for update,insert ,delete as begin sql语句 end return 语法注释： 触发器名称 是用户给要创建的触发器起的名字 on 表名 触发器作用的表名称 for update,insert ,delete 指明激活触发器的操作 sql语句 指明触发器

33、要执行的操作 return 表示返回调用位置 触发器的执行是指对表进行的update、insert、 delete操作。,第七章 SQL Server高级应用,7.3.3 触发器的创建与执行,第七章 SQL Server高级应用,7.3.4 实例：显示功能触发器,程序构思： 本例创建的触发器具有显示信息功能。为成绩表 创建一个触发器，在删除成绩表中的记录时，显示表 中有多少条记录被删除。 操作步骤： （1）单击【 开始 】【 所有程序 】【Microsoft SQL Server 2005 】 【 SQL Server Man -agement Studio 】，打开【 连接到服务器 】 对话

34、框，单击【 连接 】钮，弹出【 Microsoft SQL Server Management Studio】管理器窗口。 （2）单击常用工具栏中的 钮，则建立了一 个数据库引擎查询文档，文档名默认为 SQLQuery1.sql。,第七章 SQL Server高级应用,（3）在SQLQuery1.sql文档界面输入代码： use 学生成绩管理 go create trigger trigger1 on 成绩表 for delete as begin declare count varchar(30) select count=str(rowcount)+ 个记录被 删除！ print coun

35、t end go delete from 成绩表 where 成绩=0 （4）单击工具栏上的 按钮,或按F5键 。,rowcount是全局变量，该变量存储的是执行一条T-SQL语句后所影响的行数。,第七章 SQL Server高级应用,1修改触发器 利用T-SQL修改触发器的语法格式： alter trigger 触发器名称 on 表名 for update,insert ,delete as begin sql语句 end return 语法注释： 修改触发器只需在创建触发器的语法格式里，将create 改为alter。,7.3.5 触发器的修改、删除和显示,第七章 SQL Server高级

36、应用,2删除触发器 利用T-SQL删除触发器的语法格式： drop trigger 触发器名 ,.n 语法注释： 触发器名 要删除的触发器名称 ,.n 表示可以删除多个触发器,3显示触发器信息 用SQL Server 提供的系统存储过程显示所创建的触发器的有关信息。格式及功能 如下： sp_help 触发器名 显示触发器的一般信息 sp_helptext触发器名 显示触发器的文本信息 sp_depends 表名 显示表中各种依赖关系 sp_depends触发器名 显示触发器所引用的对象。,第七章 SQL Server高级应用,程序构思： 本例为学生表创建一个触发器，实现级联插入。 当向学生表中

37、插入一条新记录时，系统会将该生的学 号自动插入到成绩表中，并在成绩表中填写选修的课 程编号是“EIT27401C”。,7.3.6 实例：插入功能触发器,操作步骤： （1）单击【开始】【所有程序】【Microsoft SQL Server 2005】【SQL Server Management Studio】，打开【连接到服务器】对话框，单击【连 接】钮，弹出【Microsoft SQL Server Management Studio】管理器窗口。 （2）单击常用工具栏中的 钮，则建立了一个数据库引擎查询文档，文 档名默认为SQLQuery1.sql。,第七章 SQL Server高级应用,（

38、3）在SQLQuery1.sql文档界面输入代码： use 学生成绩管理 go create trigger trigger2 on 学生表 for insert,as begin declare number varchar(10) select number=学号from inserted insert into 成绩表(学号,课程编号) values (number,EIT27401C) end go insert into 学生表(学号,姓名,性别)values ( 06506999,齐飞,男) （4）单击工具栏上的 按钮,或按F5键 。,第七章 SQL Server高级应用,程序构思

39、： 本例为成绩表创建一个更新触发器，其作用是只要 执行更新操作，所有记录的成绩都加5分。,7.3.7 实例：更新功能触发器,操作步骤： （1）单击【开始】【所有程序】【Microsoft SQL Server 2005】【SQL Server Management Studio】，打开【连接到服务器】对话框，单击【连 接】钮，弹出【Microsoft SQL Server Management Studio】管理器窗口。 （2）单击常用工具栏中的 钮，则建立了一个数据库引擎查询文档，文 档名默认为SQLQuery1.sql。,第七章 SQL Server高级应用,（3）在SQLQuery1.s

40、ql文档界面输入代码： drop trigger trigger3 go create trigger trigger3 on 成绩表 for update as update 成绩表 set 成绩=成绩+5,select * from inserted select * from deleted return go update 成绩表 set 成绩=100 where 学号=06501011 and 课程编号=FCA10200C （4）单击工具栏上的 按钮,或按F5键 。,第七章 SQL Server高级应用,1.事务的概念 在数据库系统中，把一系列具有“要么什么也不做， 要么都做完”特征

41、的数据库操作定义为一个事务。事务反 映了现实世界中需要以一个完整的单位提交的一项工作。 事务是一个逻辑工作单元，它必须完整地执行，或者全 都不执行。,7.4 事务处理,7.4.1 事务概述,2.数据库一致性状态 数据库的一致性状态是指所有数据都满足数据完整性约束条件的状态。,3.事务处理 事务处理是保证数据库一致性状态的重要方法。,4.实现数据库一致性状态的方法 为了保证数据库的一致性状态，SQL Server必须控制和保证事务的执行能够符合 数据库的完整性约束条件。,第七章 SQL Server高级应用,所有的事务都具有原子性、永久性、串行性和隔离性。,7.4.2 事务属性,1原子性,2永久性,3串行性,4隔离性,第七章 SQL Server高级应用,SQL Server通过事务控制语句，把SQL Server语句 集合分组后，形成独立的逻辑工作单元。事务处理控 制语句有三个： begin transaction 表示事务开始 commit transaction 表示事务提交 rollback tra