工学网络语言数据库SQL-Server高级应用课件_第1页
工学网络语言数据库SQL-Server高级应用课件_第2页
工学网络语言数据库SQL-Server高级应用课件_第3页
工学网络语言数据库SQL-Server高级应用课件_第4页
工学网络语言数据库SQL-Server高级应用课件_第5页
已阅读5页,还剩101页未读 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

数据共享数据独立最小冗余

数据安全

数据完整

灵活性和可恢复性

数据库技术及网络应用

数据共享数据库技术及网络应用

章节目录数据库技术7.1游标

7.2存储过程

7.3触发器

第7章SQLServer高级应用

7.1.1游标概述7.1.2声明游标7.1.3打开游标

7.2.1存储过程概述

7.2.2存储过程的创建与执行

7.2.3实例:不带参数的存储过程

7.1.4读取游标数据

7.1.5实例:定义一个滚动游标

7.2.4实例:带输入参数的存储过程7.2.5实例:带输入和输出参数的存储过程7.4事务处理

小结

7.1.6实例:读取游标中的数据存入变量中

7.1.7利用游标更新数据和删除数据

7.1.8实例:利用游标统计数据行数

7.1.9关闭游标和释放游标7.1.10实例:利用游标删除表中的一行7.2.6存储过程的修改、重命名和删除

7.2.7实例:修改存储过程

7.2.8实例:重命名和删除存储过程章节目录数据库技术7.1游标7.2存储过程7.32章节目录数据库技术7.1游标

7.2存储过程

7.3触发器

第7章SQLServer高级应用

7.3.1触发器概述

7.3.2触发器的工作原理

7.3.3触发器的创建与执行

7.4.1事务概述

7.4.2事务属性

7.4.3事务控制语句

7.3.4实例:显示功能触发器7.3.5触发器的修改、删除和显示

7.4.4实例:事务编程

7.4.5事务并发处理与并发控制

7.4事务处理

小结

7.3.7实例:更新功能触发器7.3.6实例:插入功能触发器章节目录数据库技术7.1游标7.2存储过程7.33本章提要数据库技术本章通过大量实例介绍SQL语句的高级应用,包括游标、存储过程、触发器、事务处理的功能及基本操作。它们是网络数据库编程的重要内容,是开发网络数据库应用程序的关键技术。第7章SQLServer高级应用7.1游标

7.2存储过程

7.3触发器

7.4事务处理

小结

本章提要数据库技术本章通过大量实例介绍SQL语4数据库技术7.1游标7.1.1游标概述游标是类似于C语言指针一样的结构,面对包括多条数据记录的结果集,游标能完成每次只提取一条记录的操作。游标由两部分组成:结果集和在结果集中指向某一记录位置的指针。游标提供了一种在服务器内部处理结果集的方法,它可以识别一个数据集合内部指定的工作行,从而可以有选择地按行采取操作。第七章SQLServer高级应用数据库技术7.1游标7.1.1游标概述5数据库技术第七章SQLServer高级应用7.1.2声明游标在使用游标之前首先要声明游标。用T-SQL声明游标的语法如下:declare游标名[insensitive][scroll]cursorforselect语句[forreadonly|update[of列名1,列名2.……]]语法注释:游标名为声明的游标取的名字,游标名必须符合标识符的命名规则。[insensitive]使用insensitive定义的游标,会将提取出来的数据存放在系统数据库tempdb创建的一个临时表里。任何通过该游标进行的操作都在临时表里进行,因此所有对基本表的改动都不会在游标中体现出来。[scroll]使用scroll定义的游标,称为滚动游标,可以提取任意位置的数据行。若无此关键字,只能按顺序提取。select语句指标准的select语句,用来定义游标所要处理的结果集。readonly声明只读游标,不允许通过游标进行数据更新。update[of列名1,列名2.……]声明可更新的游标中允许更新的列。若只选update,则所有的列都可更新。

数据库技术第七章SQLServer高级应用7.1.26数据库技术第七章SQLServer高级应用在使用游标之前,必须先打开游标。利用T-SQL打开游标的语法格式:open游标名功能注释:当执行打开游标的语句时,服务器执行声明游标时使用的select语句。此时,用游标提取数据的当前位置是数据集的第一条记录。7.1.3打开游标数据库技术第七章SQLServer高级应用在使用游标之7数据库技术第七章SQLServer高级应用语法注释:(1)使用scroll关键字声明的游标,具有以下所有的取数功能:

first取第一行数据

prior取前一行数据

next取下一行数据

last取最后一行数据

absolute<n>按绝对位置取数据,从游标中取出第n行记录

relative<n>按相对位置取数据,读出从游标当前位置向后第行记录。(2)[into变量名表]使用into子句对变量赋值时,变量的数量、类型、顺序必须和声明游标时使用的select语句输出的列项一一对应。7.1.4读取游标数据打开游标之后,就可以用游标读取数据了。利用T-SQL读取游标的语法如下:fetch[first|prior|next|last]|[absolutetn|relativen]from游标名[into变量名表]数据库技术第七章SQLServer高级应用语法注释:78数据库技术第七章SQLServer高级应用程序构思:本例声明一个游标,用来逐条显示“学生表”中性别为“女”的记录。操作步骤:(1)单击【开始】→【所有程序】→【MicrosoftSQLServer2005】→【SQLServerManagementStudio】,打开【连接到服务器】对话框,单击【连接】钮,弹出【MicrosoftSQLServerManagementStudio】管理器窗口。(2)单击常用工具栏中的钮,则建立了一个数据库引擎查询文档,文档名默认为

SQLQuery1.sql。7.1.5实例:定义一个滚动游标数据库技术第七章SQLServer高级应用程序构思:79数据库技术第七章SQLServer高级应用(3)在SQLQuery1.sql文档界面输入代码:use学生成绩管理declarecursor1scrollcursorforselect*from学生表where性别='女'opencursor1fetchfirstfromcursor1while@@fetch_status=0beginfetchnextfromcursor1end(4)单击工具栏上的按钮,或按F5键@@fetch_status是一个全局变量,若其值为0,则表示fetch命令被成功执行。数据库技术第七章SQLServer高级应用(3)在SQ10数据库技术第七章SQLServer高级应用7.1.6实例:读取游标中的数据存入变量中程序构思:本例声明的游标,其结果集为选修“三维动画设计”课程成绩最高的学生的学号、姓名、课程名称和成绩,并将从游标中读取的数据存放到局部变量中,再输出变量中的值。操作步骤:(1)单击【开始】→【所有程序】→【MicrosoftSQLServer2005】→【SQLServerManagementStudio】,打开【连接到服务器】对话框,单击【连接】钮,弹出【MicrosoftSQLServerManagementStudio】管理器窗口。(2)单击常用工具栏中的钮,则建立了一个数据库引擎查询文档,文档名默认为SQLQuery1.sql。数据库技术第七章SQLServer高级应用7.1.611数据库技术第七章SQLServer高级应用(3)在SQLQuery1.sql文档界面输入代码:use学生成绩管理declarecursor2scrollcursorforselecta.学号,姓名,课程名称,成绩from学生表a,课程表b,成绩表cwhere课程名称='三维动画设计'andc.学号=a.学号andb.课程编号=c.课程编号

orderby成绩desc

opencursor2declare@numchar(20)declare@namechar(20)declare@score_namechar(20)declare@maxintfetchfirstfromcursor2into@num,@name,@score_name,@maxselect@numas编号,@nameas名字,@score_nameas课名,@maxas分数(4)单击工具栏上的按钮,或按F5键。数据库技术第七章SQLServer高级应用(3)在SQ12数据库技术第七章SQLServer高级应用语法注释:currentof游标名表示当前游标的当前数据行,该子句只能在update和delete操作的语句中使用。7.1.7利用游标更新数据和删除数据要通过游标对数据进行修改,该游标必须被声明为可更新的游标。在声明游标时,只要不使用forreadonly关键字,游标都是可更新的。利用T-SQL更新游标的语法格式:update表名set列名=表达式[,……]wherecurrentof游标名利用T-SQL删除游标的语法格式:

deletefrom表名set列名=表达式[,……]wherecurrentof游标名数据库技术第七章SQLServer高级应用语法注释:713数据库技术第七章SQLServer高级应用程序构思:本例声明的游标,其执行结果集为没有被选修的课程信息,包括课程编号、课程名、学分。统计结果集中的数据行数,并显示相应的提示信息。7.1.8实例:利用游标统计数据行数操作步骤:(1)单击【开始】→【所有程序】→【MicrosoftSQLServer2005】→【SQLServerManagementStudio】,打开【连接到服务器】对话框,单击【连接】钮,弹出【MicrosoftSQLServerManagementStudio】管理器窗口。(2)单击常用工具栏中的钮,则建立了一个数据库引擎查询文档,文档名默认为SQLQuery1.sql。数据库技术第七章SQLServer高级应用程序构思:714数据库技术第七章SQLServer高级应用(3)在SQLQuery1.sql文档界面输入代码:

use学生成绩管理

declarecursor3scrollcursorfor

selectdistincta.课程编号,课程名称,学分from课程表a,成绩表b

wherea.课程编号notin(selectdistinct课程编号from成绩表)

opencursor3fetchfirstfromcursor3while@@fetch_status=0beginfetchnextfromcursor3endselect@@cursor_rowsas'未被选修的课程数'打开游标后,可以使用全局变量@@cursor_rows返回当前游标可以操作的数据行数。(4)单击工具栏上的按钮,或按F5键

数据库技术第七章SQLServer高级应用(3)在SQ15数据库技术第七章SQLServer高级应用2.释放游标游标结构本身也会占用一定的计算机资源,所以在使用完游标后,为了回收被占用的资源,应该将游标释放。利用T-SQL释放游标的语法格式:deallocate游标名当释放完游标以后,如果要重新使用这个游标,则必须重新执行声明游标的语句。7.1.9关闭游标和释放游标

1.关闭游标在打开游标之后,SQLServer服务器会专门为游标开辟一定的内存空间存放游标操作的数据结果集,同时游标的使用也会根据具体情况对某些数据进行封锁。所以,在不使用游标的时候,一定要关闭游标,以通知服务器释放游标所占用的资源。利用T-SQL关闭游标的语法格式:close游标名关闭游标之后,可以再次打开游标,在一个批处理中,可以多次打开和关闭游标。数据库技术第七章SQLServer高级应用2.释放游标16数据库技术第七章SQLServer高级应用程序构思:本例声明的游标,其执行结果集为成绩最低的学生信息,包括学号、姓名、所属院系、成绩,通过游标在成绩表中将成绩最低的一条记录删除。操作步骤:(1)单击【开始】→【所有程序】→【MicrosoftSQLServer2005】→【SQLServerManagementStudio】,打开【连接到服务器】对话框,单击

【连接】钮,弹出【MicrosoftSQLServerManagementStudio】管理器窗口。(2)单击常用工具栏中的钮,则建立了一个数据库引擎查询文档,文档名默认为

SQLQuery1.sql。7.1.10实例:利用游标删除表中的一行数据库技术第七章SQLServer高级应用程序构思:717数据库技术第七章SQLServer高级应用(3)在SQLQuery1.sql文档界面输入代码:

use学生成绩管理

declarecursor4scrollcursorfor

selecta.学号,姓名,所属院系,成绩

from学生表a,成绩表b

wherea.学号=b.学号and成绩=(selectmin(成绩)from成绩表)opencursor4fetchfirstfromcursor4deletefrom成绩表wherecurrentofcursor4closecursor4deallocatecursor4(4)单击工具栏上的按钮,或按F5键。数据库技术第七章SQLServer高级应用(3)在SQ18数据库技术第七章SQLServer高级应用7.2存储过程7.2.1存储过程概述存储过程具有以下优点:(1)由一系列T-SQL语句组成,可作为一个整体被程序多次调用;(2)修改容易,可移植性好;(3)存储过程是预编译的,执行速度快;(4)在网络中调用过程,只需要传送调用过程的语句,因此可减少网络流量;(5)可通过对执行存储过程的权限控制来保证系统数据的安全;(6)存储过程可嵌套调用,即在一个存储过程中调用另一个存储过程。数据库技术第七章SQLServer高级应用7.2存19数据库技术第七章SQLServer高级应用7.2.2存储过程的创建与执行1.创建存储过程利用T-SQL创建存储过程的语法格式:createproc过程名@parameter参数类型/*输入参数*/……@parameter参数类型ouput/*输出参数*/……asbegin命令行或命令块end2.执行存储过程利用T-SQL执行存储过程的语法格式:execute过程名[参数1,参数2,……][output]数据库技术第七章SQLServer高级应用7.2.220数据库技术第七章SQLServer高级应用程序构思:本例存储过程的功能是显示所有选修了“数据库技术及网络应用”课程的学生的学号、姓名、所属院系和成绩。操作步骤:(1)单击【开始】→【所有程序】→【MicrosoftSQLServer2005】→【SQLServerManagementStudio】,打开【连接到服务器】对话框,单击

【连接】钮,弹出【MicrosoftSQLServerMana-gementStudio】管理器窗口。(2)单击常用工具栏中的钮,则建立了一个数据库引擎查询文档,文档名默认为

SQLQuery1.sql。7.2.3实例:不带参数的存储过程数据库技术第七章SQLServer高级应用程序构思:721数据库技术第七章SQLServer高级应用(3)在SQLQuery1.sql文档界面输入代码:

use学生成绩管理gocreateprocproc1asbegin

selecta.学号,姓名,所属院系,成绩from学生表a,课程表b,成绩表cwhere课程名称='数据库技术及网络应用'andb.课程编号=c.课程编号andc.学号=a.学号endgoexecuteproc1/*执行存储过程*/(4)单击工具栏上的按钮,或按F5键。数据库技术第七章SQLServer高级应用(3)在SQ22数据库技术第七章SQLServer高级应用程序构思:本例存储过程的功能是查询担任某门课程的教师的基本信息。执行存储过程时,允许带输入参数,即课程名称。存储过程的执行结果显示教师的姓名、职称和联系电话。7.2.4实例:带输入参数的存储过程操作步骤:(1)单击【开始】→【所有程序】→【MicrosoftSQLServer2005】→【SQLServerManagementStudio】,打开【连接到服务器】对话框,单击【连接】钮,弹出【MicrosoftSQLServerManagementStudio】管理器窗口。(2)单击常用工具栏中的钮,则建立了一个数据库引擎查询文档,文档名默认为SQLQuery1.sql。

数据库技术第七章SQLServer高级应用程序构思:723数据库技术第七章SQLServer高级应用(3)在SQLQuery1.sql文档界面输入代码:

use学生成绩管理

go

createprocproc2

@scrose_namevarchar(20)as

begin

select姓名,职称,联系电话from课程表a,教师表b

where课程名称=@scrose_nameanda.教师编号=b.教师编号

endgoexecuteproc2'C语言程序设计'/*执行存储过程*/(4)单击工具栏上的按钮,或按F5键。数据库技术第七章SQLServer高级应用(3)在SQ24数据库技术第七章SQLServer高级应用7.2.5实例:带输入和输出参数的存储过程程序构思:本存储过程的功能是统计某院系学生的最大年龄和平均年龄。执行存储过程时,允许带输入参数和输出参数。输入参数为所属院系名称,输出参数保存所执行的结果,即最大年龄和平均年龄。操作步骤:(1)单击【开始】→【所有程序】→【MicrosoftSQLServer2005】→【SQLServerManagementStudio】,打开【连接到服务器】对话框,单击【连接】钮,弹出【MicrosoftSQLServerManagementStudio】管理器窗口。(2)单击常用工具栏中的钮,则建立了一个数据库引擎查询文档,文档名默认为SQLQuery1.sql。数据库技术第七章SQLServer高级应用7.2.525数据库技术第七章SQLServer高级应用(3)在SQLQuery1.sql文档界面输入代码:

use学生成绩管理

go

createprocproc3

@yuanxichar(20),@maxageintoutput,@averagerealoutputasbegin

select@maxage=max(datepart(yy,getdate())-datepart(yy,出生日期))from学生表where所属院系=@yuanxiselect@average=avg(datepart(yy,getdate())-datepart(yy,出生日期))from学生表where所属院系=@yuanxiendgodeclare@x1int,@x2realexecuteproc3'教育学院',@x1output,@x2outputselect@x1最大年龄,@x2平均年龄(4)单击工具栏上的按钮,或按F5键。数据库技术第七章SQLServer高级应用(3)在SQ26数据库技术第七章SQLServer高级应用7.2.6存储过程的修改、重命名和删除1.修改存储过程利用T-SQL修改存储过程的语法格式:alterproc过程名@parameter参数类型/*输入参数*/……@parameter参数类型ouput/*输出参数*/……asbegin命令行或命令块end修改存储过程是指修改存储过程的内容。如果要修改某一存储过程,在创建该存储过程的代码中,将命令动词creat改为alter,再修改其内容即可。数据库技术第七章SQLServer高级应用7.2.627数据库技术第七章SQLServer高级应用2.存储过程的重命名1.修改存储过程利用T-SQL修改存储过程的语法格式:alterproc过程名@parameter参数类型/*输入参数*/……@parameter参数类型ouput/*输出参数*/……asbegin命令行或命令块end修改存储过程是指修改存储过程的内容。如果要修改某一存储过程,在创建该存储过程的代码中,将命令动词creat改为alter,再修改其内容即可。数据库技术第七章SQLServer高级应用2.存储过28数据库技术第七章SQLServer高级应用3.删除存储过程利用T-SQL语句删除存储过程的语法格式:dropproc存储过程名1[,存储过程名2,……]删除存储过程是指将存储过程作为数据库对象从数据库中彻底删除。一次可删除多个存储过程。利用T-SQL语句给存储过程重命名的语法格式:sp_rename原过程名,新过程名给存储过程重命名只是修改存储过程的名字,和存储过程内容本身无关。该功能用系统存储过程sp_rename实现,该系统存储过程存放在master数据库里,在任何数据库中都可直接调用它。数据库技术第七章SQLServer高级应用3.删除存储29数据库技术7.2.7实例:修改存储过程第七章SQLServer高级应用程序构思:本例是将存储过程pro3的功能修改为输入院系名称,输出该院系学生的人数。操作步骤:(1)单击【开始】→【所有程序】→【MicrosoftSQLServer2005】→【SQLServerManagementStudio】,打开【连接到服务器】对话框,单击

【连接】钮,弹出【MicrosoftSQLServerManagementStudio】管理器窗口。(2)单击常用工具栏中的钮,则建立了一个数据库引擎查询文档,文档名默认为

SQLQuery1.sql。数据库技术7.2.7实例:修改存储过程第七章SQL30数据库技术第七章SQLServer高级应用(3)在SQLQuery1.sql文档界面输入代码:

use学生成绩管理goalterprocproc3@yuanxichar(20),@countintoutputasbeginselect@count=count(*)from学生表where所属院系=@yuanxiendgodeclare@xintexecuteproc3'物电学院',@xoutputselect@x人数(4)单击工具栏上的按钮,或按F5键。此例中,若想输出院系名称和人数,即将输入参数也作为结果输出,应怎样改进?数据库技术第七章SQLServer高级应用(3)在SQ31数据库技术7.2.8实例:重命名和删除存储过程程序构思:本例是将将存储过程proc1删除,再将存储过程名pro3改为pro3new。操作步骤:(1)单击【开始】→【所有程序】→【MicrosoftSQLServer2005】→【SQLServerManagementStudio】,打开【连接到服务器】对话框,单击

【连接】钮,弹出【MicrosoftSQLServerManagementStudio】管理器窗口。(2)单击常用工具栏中的钮,则建立了一个数据库引擎查询文档,文档名默认为

SQLQuery1.sql。第七章SQLServer高级应用(3)在SQLQuery1.sql文档界面输入代码:dropprocproc1sp_renameproc3,proc3newnew(4)单击工具栏上的按钮,或按F5键。数据库技术7.2.8实例:重命名和删除存储过程程序构思:32数据库技术第七章SQLServer高级应用7.3.1触发器概述1.触发器的概念触发器是一种特殊的存储过程,它和表密切相连,可以看作是表格定义的一部分。当用户对指定表操作时,触发器会自动执行。触发器在update、insert、delete等操作执行结束后才执行。2.触发器的功能(1)级联更新数据库中相关表的数据;(2)实现多表之间数据的一致性;(3)执行比检查约束更复杂的约束操作;(4)调用存储过程;(5)在一张表的update、insert、delete操作上可设置多个触发器。

7.3触发器数据库技术第七章SQLServer高级应用7.3.133数据库技术第七章SQLServer高级应用3.使用触发器应注意的问题(1)当使用约束、规则、默认值等方法能够实现数据的完整性,就不用触发器实现;(2)只有表的拥有者才可以在表上创建或删除触发器,这种权限不准转授;(3)使用update语句可以一次对多行数据进行修改,而触发器只被触发一次;(4)触发器只能在当前数据库中创建,触发器的命名必须要遵守标识符的命名规则。4.触发器的优点(1)触发器是自动的;(2)触发器可以通过数据库中的相关表进行层叠更改;(3)触发器可以强制限制,这些限制比用check所定义的约束更复杂。数据库技术第七章SQLServer高级应用3.使用触发34数据库技术第七章SQLServer高级应用7.3.2触发器的工作原理inserted表和deleted表的特点如下:

(1)这两张表是逻辑表,由数据库管理,用户不能对他们进行修改;(2)这两张表存储在内存中,而不是存储在数据库中;(3)这两张表的结构与被该触发器作用的表结构相同;(4)当触发器完成操作后,这两张表会自动删除;(5)两张表中保存的数据是因用户操作而被影响到的原数据或新数据;(6)这两张表是只读表。数据库技术第七章SQLServer高级应用7.3.235数据库技术利用T-SQL创建触发器的语法格式:creattrigger触发器名称on表名forupdate[,insert,delete]asbeginsql语句endreturn语法注释:触发器名称是用户给要创建的触发器起的名字on表名触发器作用的表名称forupdate[,insert,delete]指明激活触发器的操作sql语句指明触发器要执行的操作return表示返回调用位置触发器的执行是指对表进行的update、insert、delete操作。第七章SQLServer高级应用7.3.3触发器的创建与执行数据库技术利用T-SQL创建触发器的语法格式:第七章SQ36数据库技术第七章SQLServer高级应用7.3.4实例:显示功能触发器程序构思:本例创建的触发器具有显示信息功能。为成绩表创建一个触发器,在删除成绩表中的记录时,显示表中有多少条记录被删除。操作步骤:(1)单击【开始】→【所有程序】→【MicrosoftSQLServer2005】→【SQLServerMan-agementStudio】,打开【连接到服务器】对话框,单击【连接】钮,弹出【MicrosoftSQLServerManagementStudio】管理器窗口。(2)单击常用工具栏中的钮,则建立了一个数据库引擎查询文档,文档名默认为

SQLQuery1.sql。数据库技术第七章SQLServer高级应用7.3.437数据库技术第七章SQLServer高级应用(3)在SQLQuery1.sql文档界面输入代码:

use学生成绩管理gocreatetriggertrigger1on成绩表fordeleteasbegindeclare@countvarchar(30)select@count=str(@@rowcount)+‘个记录被删除!'

print@countendgodeletefrom成绩表where成绩=0(4)单击工具栏上的按钮,或按F5键。@@rowcount是全局变量,该变量存储的是执行一条T-SQL语句后所影响的行数。数据库技术第七章SQLServer高级应用(3)在SQ38数据库技术第七章SQLServer高级应用1.修改触发器利用T-SQL修改触发器的语法格式:altertrigger触发器名称on表名forupdate[,insert,delete]asbeginsql语句endreturn语法注释:修改触发器只需在创建触发器的语法格式里,将create改为alter。7.3.5触发器的修改、删除和显示数据库技术第七章SQLServer高级应用1.修改触发39数据库技术第七章SQLServer高级应用2.删除触发器利用T-SQL删除触发器的语法格式:droptrigger触发器名[,...n]语法注释:触发器名要删除的触发器名称[,...n]表示可以删除多个触发器

3.显示触发器信息用SQLServer提供的系统存储过程显示所创建的触发器的有关信息。格式及功能如下:sp_help触发器名显示触发器的一般信息sp_helptext触发器名显示触发器的文本信息sp_depends表名显示表中各种依赖关系sp_depends触发器名显示触发器所引用的对象。数据库技术第七章SQLServer高级应用2.删除触40数据库技术第七章SQLServer高级应用程序构思:本例为学生表创建一个触发器,实现级联插入。当向学生表中插入一条新记录时,系统会将该生的学号自动插入到成绩表中,并在成绩表中填写选修的课程编号是“EIT27401C”。7.3.6实例:插入功能触发器操作步骤:(1)单击【开始】→【所有程序】→【MicrosoftSQLServer2005】→【SQLServerManagementStudio】,打开【连接到服务器】对话框,单击【连接】钮,弹出【MicrosoftSQLServerManagementStudio】管理器窗口。(2)单击常用工具栏中的钮,则建立了一个数据库引擎查询文档,文档名默认为SQLQuery1.sql。数据库技术第七章SQLServer高级应用程序构思:741数据库技术第七章SQLServer高级应用(3)在SQLQuery1.sql文档界面输入代码:

use学生成绩管理gocreatetriggertrigger2on学生表forinsertasbegindeclare@numbervarchar(10)select@number=学号frominsertedinsertinto成绩表(学号,课程编号)values(@number,'EIT27401C')endgoinsertinto学生表(学号,姓名,性别)values('06506999','齐飞','男')(4)单击工具栏上的按钮,或按F5键。数据库技术第七章SQLServer高级应用(3)在SQ42数据库技术第七章SQLServer高级应用程序构思:本例为成绩表创建一个更新触发器,其作用是只要执行更新操作,所有记录的成绩都加5分。7.3.7实例:更新功能触发器操作步骤:(1)单击【开始】→【所有程序】→【MicrosoftSQLServer2005】→【SQLServerManagementStudio】,打开【连接到服务器】对话框,单击【连接】钮,弹出【MicrosoftSQLServerManagementStudio】管理器窗口。(2)单击常用工具栏中的钮,则建立了一个数据库引擎查询文档,文档名默认为SQLQuery1.sql。数据库技术第七章SQLServer高级应用程序构思:743数据库技术第七章SQLServer高级应用(3)在SQLQuery1.sql文档界面输入代码:

droptriggertrigger3gocreatetriggertrigger3on成绩表forupdateasupdate成绩表set成绩=成绩+5

select*frominsertedselect*fromdeletedreturngoupdate成绩表set成绩=100where学号='06501011'and课程编号='FCA10200C'(4)单击工具栏上的按钮,或按F5键。数据库技术第七章SQLServer高级应用(3)在SQ44数据库技术第七章SQLServer高级应用1.事务的概念在数据库系统中,把一系列具有“要么什么也不做,要么都做完”特征的数据库操作定义为一个事务。事务反映了现实世界中需要以一个完整的单位提交的一项工作。事务是一个逻辑工作单元,它必须完整地执行,或者全都不执行。

7.4事务处理7.4.1事务概述2.数据库一致性状态数据库的一致性状态是指所有数据都满足数据完整性约束条件的状态。3.事务处理事务处理是保证数据库一致性状态的重要方法。4.实现数据库一致性状态的方法为了保证数据库的一致性状态,SQLServer必须控制和保证事务的执行能够符合数据库的完整性约束条件。数据库技术第七章SQLServer高级应用1.事务的概45数据库技术第七章SQLServer高级应用所有的事务都具有原子性、永久性、串行性和隔离性。

7.4.2事务属性1.原子性2.永久性3.串行性4.隔离性数据库技术第七章SQLServer高级应用所有的事务都46数据库技术第七章SQLServer高级应用SQLServer通过事务控制语句,把SQLServer语句集合分组后,形成独立的逻辑工作单元。事务处理控制语句有三个:begintransaction表示事务开始committransaction表示事务提交rollbacktransaction表示事务回滚7.4.3事务控制语句几点说明:(1)在SQLServer中,通常一个事务是以begintransaction开始,到一个相匹配的committransaction或rollbacktransaction之间的所有语句序列;(3)committransaction表示提交事务的一切操作,使得操作对数据的改变生效;(2)rollbacktransaction表示要撤消该事务已做的一切操作,回滚到事务开始的状态。数据库技术第七章SQLServer高级应用SQLSe47数据库技术第七章SQLServer高级应用程序构思:本例向成绩表中插入一条记录,若插入成功,则提交;否则,回滚。7.4.4实例:事务编程操作步骤:(1)单击【开始】→【所有程序】→【MicrosoftSQLServer2005】→【SQLServerManagementStudio】,打开【连接到服务器】对话框,单击【连接】钮,弹出【MicrosoftSQLServerManagementStudio】管理器窗口。(2)单击常用工具栏中的钮,则建立了一个数据库引擎查询文档,文档名默认为SQLQuery1.sql。数据库技术第七章SQLServer高级应用程序构思:748数据库技术第七章SQLServer高级应用(3)在SQLQuery1.sql文档界面输入代码:

begintransactioninsert成绩表values('06501011','EIT37401',91)if@@error=0committransaction/*若无误,则提交此事务*/

else

rollbacktransaction/*若有误,则回退此事务*/(4)单击工具栏上的按钮,或按F5键。数据库技术第七章SQLServer高级应用(3)在SQ49数据库技术第七章SQLServer高级应用1.并发处理指CPU利用分时方法实现多个事务的交替进行。7.4.5事务并发处理与并发控制

2.并发处理可能产生的问题(1)丢失更新(2)读未提交数据(3)不一致检索产生上述问题的原因:违背了事务的隔离性。数据库技术第七章SQLServer高级应用1.并发处理50数据库技术第七章SQLServer高级应用3.封锁机制封锁机制是实现并发控制的重要技术,并发控制的作用是为了实现事务的隔离性。(1)封锁的类型共享锁:也称读锁。如果事务T给数据对象X加上共享锁,则其他事务只能对X再加共享锁,不能加排它锁。T及其他事务都可以且只能读数据。排它锁:也称写锁。如果事务T对数据对象X加上排它锁,则只允许事务T独占数据项X,即对数据项X可读、可写。其他任何事务都不能对X加任何类型的锁,直到T释放X上的锁。数据库技术第七章SQLServer高级应用3.封锁机51数据库技术第七章SQLServer高级应用(2)封锁规则1)数据项X未加锁,谁都可以给它加锁。2)数据项X已加共享锁,其他事务只能加共享锁。3)数据项X已加排他锁,其他事务不能加任何锁。4)谁加的锁,谁释放该锁。(3)封锁的粒度指封锁对象的大小。封锁的对象可以是数据库、表、行、列等逻辑单元。(4)死锁死锁是若干事务相互等待对方释放封锁,死锁是由于封锁而产生的。(5)解决死锁的方法死锁的预防:要求每个事务必须一次性封锁所要使用的全部数据;规定封锁的顺序。

死锁的诊断:DBMS周期性测试DBS系统是否处于死锁状态。

死锁的解除:将一个处理代价小的事务撤销,回滚该事务,并释放该事务持有的锁。数据库技术第七章SQLServer高级应用(2)封锁规52数据库技术第七章SQLServer高级应用本章所讲的内容有一定的难度,必须是在前几章知识掌握较好的情况下才能轻松进入本章的学习。就目前网络的发展而言,学习网络编程技术已成为在软件道路上发展的必经之路,而数据库技术又是网络编程的核心技术,本章的内容又是SQLServer中的关键技术。因此,本章在整个教程中的地位很重要,一定要认真完成章后的习题,这样能使知识掌握得更加牢固。小结数据库技术第七章SQLServer高级应用53数据共享数据独立最小冗余

数据安全

数据完整

灵活性和可恢复性

数据库技术及网络应用

数据共享数据库技术及网络应用

章节目录数据库技术7.1游标

7.2存储过程

7.3触发器

第7章SQLServer高级应用

7.1.1游标概述7.1.2声明游标7.1.3打开游标

7.2.1存储过程概述

7.2.2存储过程的创建与执行

7.2.3实例:不带参数的存储过程

7.1.4读取游标数据

7.1.5实例:定义一个滚动游标

7.2.4实例:带输入参数的存储过程7.2.5实例:带输入和输出参数的存储过程7.4事务处理

小结

7.1.6实例:读取游标中的数据存入变量中

7.1.7利用游标更新数据和删除数据

7.1.8实例:利用游标统计数据行数

7.1.9关闭游标和释放游标7.1.10实例:利用游标删除表中的一行7.2.6存储过程的修改、重命名和删除

7.2.7实例:修改存储过程

7.2.8实例:重命名和删除存储过程章节目录数据库技术7.1游标7.2存储过程7.355章节目录数据库技术7.1游标

7.2存储过程

7.3触发器

第7章SQLServer高级应用

7.3.1触发器概述

7.3.2触发器的工作原理

7.3.3触发器的创建与执行

7.4.1事务概述

7.4.2事务属性

7.4.3事务控制语句

7.3.4实例:显示功能触发器7.3.5触发器的修改、删除和显示

7.4.4实例:事务编程

7.4.5事务并发处理与并发控制

7.4事务处理

小结

7.3.7实例:更新功能触发器7.3.6实例:插入功能触发器章节目录数据库技术7.1游标7.2存储过程7.356本章提要数据库技术本章通过大量实例介绍SQL语句的高级应用,包括游标、存储过程、触发器、事务处理的功能及基本操作。它们是网络数据库编程的重要内容,是开发网络数据库应用程序的关键技术。第7章SQLServer高级应用7.1游标

7.2存储过程

7.3触发器

7.4事务处理

小结

本章提要数据库技术本章通过大量实例介绍SQL语57数据库技术7.1游标7.1.1游标概述游标是类似于C语言指针一样的结构,面对包括多条数据记录的结果集,游标能完成每次只提取一条记录的操作。游标由两部分组成:结果集和在结果集中指向某一记录位置的指针。游标提供了一种在服务器内部处理结果集的方法,它可以识别一个数据集合内部指定的工作行,从而可以有选择地按行采取操作。第七章SQLServer高级应用数据库技术7.1游标7.1.1游标概述58数据库技术第七章SQLServer高级应用7.1.2声明游标在使用游标之前首先要声明游标。用T-SQL声明游标的语法如下:declare游标名[insensitive][scroll]cursorforselect语句[forreadonly|update[of列名1,列名2.……]]语法注释:游标名为声明的游标取的名字,游标名必须符合标识符的命名规则。[insensitive]使用insensitive定义的游标,会将提取出来的数据存放在系统数据库tempdb创建的一个临时表里。任何通过该游标进行的操作都在临时表里进行,因此所有对基本表的改动都不会在游标中体现出来。[scroll]使用scroll定义的游标,称为滚动游标,可以提取任意位置的数据行。若无此关键字,只能按顺序提取。select语句指标准的select语句,用来定义游标所要处理的结果集。readonly声明只读游标,不允许通过游标进行数据更新。update[of列名1,列名2.……]声明可更新的游标中允许更新的列。若只选update,则所有的列都可更新。

数据库技术第七章SQLServer高级应用7.1.259数据库技术第七章SQLServer高级应用在使用游标之前,必须先打开游标。利用T-SQL打开游标的语法格式:open游标名功能注释:当执行打开游标的语句时,服务器执行声明游标时使用的select语句。此时,用游标提取数据的当前位置是数据集的第一条记录。7.1.3打开游标数据库技术第七章SQLServer高级应用在使用游标之60数据库技术第七章SQLServer高级应用语法注释:(1)使用scroll关键字声明的游标,具有以下所有的取数功能:

first取第一行数据

prior取前一行数据

next取下一行数据

last取最后一行数据

absolute<n>按绝对位置取数据,从游标中取出第n行记录

relative<n>按相对位置取数据,读出从游标当前位置向后第行记录。(2)[into变量名表]使用into子句对变量赋值时,变量的数量、类型、顺序必须和声明游标时使用的select语句输出的列项一一对应。7.1.4读取游标数据打开游标之后,就可以用游标读取数据了。利用T-SQL读取游标的语法如下:fetch[first|prior|next|last]|[absolutetn|relativen]from游标名[into变量名表]数据库技术第七章SQLServer高级应用语法注释:761数据库技术第七章SQLServer高级应用程序构思:本例声明一个游标,用来逐条显示“学生表”中性别为“女”的记录。操作步骤:(1)单击【开始】→【所有程序】→【MicrosoftSQLServer2005】→【SQLServerManagementStudio】,打开【连接到服务器】对话框,单击【连接】钮,弹出【MicrosoftSQLServerManagementStudio】管理器窗口。(2)单击常用工具栏中的钮,则建立了一个数据库引擎查询文档,文档名默认为

SQLQuery1.sql。7.1.5实例:定义一个滚动游标数据库技术第七章SQLServer高级应用程序构思:762数据库技术第七章SQLServer高级应用(3)在SQLQuery1.sql文档界面输入代码:use学生成绩管理declarecursor1scrollcursorforselect*from学生表where性别='女'opencursor1fetchfirstfromcursor1while@@fetch_status=0beginfetchnextfromcursor1end(4)单击工具栏上的按钮,或按F5键@@fetch_status是一个全局变量,若其值为0,则表示fetch命令被成功执行。数据库技术第七章SQLServer高级应用(3)在SQ63数据库技术第七章SQLServer高级应用7.1.6实例:读取游标中的数据存入变量中程序构思:本例声明的游标,其结果集为选修“三维动画设计”课程成绩最高的学生的学号、姓名、课程名称和成绩,并将从游标中读取的数据存放到局部变量中,再输出变量中的值。操作步骤:(1)单击【开始】→【所有程序】→【MicrosoftSQLServer2005】→【SQLServerManagementStudio】,打开【连接到服务器】对话框,单击【连接】钮,弹出【MicrosoftSQLServerManagementStudio】管理器窗口。(2)单击常用工具栏中的钮,则建立了一个数据库引擎查询文档,文档名默认为SQLQuery1.sql。数据库技术第七章SQLServer高级应用7.1.664数据库技术第七章SQLServer高级应用(3)在SQLQuery1.sql文档界面输入代码:use学生成绩管理declarecursor2scrollcursorforselecta.学号,姓名,课程名称,成绩from学生表a,课程表b,成绩表cwhere课程名称='三维动画设计'andc.学号=a.学号andb.课程编号=c.课程编号

orderby成绩desc

opencursor2declare@numchar(20)declare@namechar(20)declare@score_namechar(20)declare@maxintfetchfirstfromcursor2into@num,@name,@score_name,@maxselect@numas编号,@nameas名字,@score_nameas课名,@maxas分数(4)单击工具栏上的按钮,或按F5键。数据库技术第七章SQLServer高级应用(3)在SQ65数据库技术第七章SQLServer高级应用语法注释:currentof游标名表示当前游标的当前数据行,该子句只能在update和delete操作的语句中使用。7.1.7利用游标更新数据和删除数据要通过游标对数据进行修改,该游标必须被声明为可更新的游标。在声明游标时,只要不使用forreadonly关键字,游标都是可更新的。利用T-SQL更新游标的语法格式:update表名set列名=表达式[,……]wherecurrentof游标名利用T-SQL删除游标的语法格式:

deletefrom表名set列名=表达式[,……]wherecurrentof游标名数据库技术第七章SQLServer高级应用语法注释:766数据库技术第七章SQLServer高级应用程序构思:本例声明的游标,其执行结果集为没有被选修的课程信息,包括课程编号、课程名、学分。统计结果集中的数据行数,并显示相应的提示信息。7.1.8实例:利用游标统计数据行数操作步骤:(1)单击【开始】→【所有程序】→【MicrosoftSQLServer2005】→【SQLServerManagementStudio】,打开【连接到服务器】对话框,单击【连接】钮,弹出【MicrosoftSQLServerManagementStudio】管理器窗口。(2)单击常用工具栏中的钮,则建立了一个数据库引擎查询文档,文档名默认为SQLQuery1.sql。数据库技术第七章SQLServer高级应用程序构思:767数据库技术第七章SQLServer高级应用(3)在SQLQuery1.sql文档界面输入代码:

use学生成绩管理

declarecursor3scrollcursorfor

selectdistincta.课程编号,课程名称,学分from课程表a,成绩表b

wherea.课程编号notin(selectdistinct课程编号from成绩表)

opencursor3fetchfirstfromcursor3while@@fetch_status=0beginfetchnextfromcursor3endselect@@cursor_rowsas'未被选修的课程数'打开游标后,可以使用全局变量@@cursor_rows返回当前游标可以操作的数据行数。(4)单击工具栏上的按钮,或按F5键

数据库技术第七章SQLServer高级应用(3)在SQ68数据库技术第七章SQLServer高级应用2.释放游标游标结构本身也会占用一定的计算机资源,所以在使用完游标后,为了回收被占用的资源,应该将游标释放。利用T-SQL释放游标的语法格式:deallocate游标名当释放完游标以后,如果要重新使用这个游标,则必须重新执行声明游标的语句。7.1.9关闭游标和释放游标

1.关闭游标在打开游标之后,SQLServer服务器会专门为游标开辟一定的内存空间存放游标操作的数据结果集,同时游标的使用也会根据具体情况对某些数据进行封锁。所以,在不使用游标的时候,一定要关闭游标,以通知服务器释放游标所占用的资源。利用T-SQL关闭游标的语法格式:close游标名关闭游标之后,可以再次打开游标,在一个批处理中,可以多次打开和关闭游标。数据库技术第七章SQLServer高级应用2.释放游标69数据库技术第七章SQLServer高级应用程序构思:本例声明的游标,其执行结果集为成绩最低的学生信息,包括学号、姓名、所属院系、成绩,通过游标在成绩表中将成绩最低的一条记录删除。操作步骤:(1)单击【开始】→【所有程序】→【MicrosoftSQLServer2005】→【SQLServerManagementStudio】,打开【连接到服务器】对话框,单击

【连接】钮,弹出【MicrosoftSQLServerManagementStudio】管理器窗口。(2)单击常用工具栏中的钮,则建立了一个数据库引擎查询文档,文档名默认为

SQLQuery1.sql。7.1.10实例:利用游标删除表中的一行数据库技术第七章SQLServer高级应用程序构思:770数据库技术第七章SQLServer高级应

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论