《网络语言数据库SQLServer高级应用课件》由会员分享，可在线阅读，更多相关《网络语言数据库SQLServer高级应用课件（53页珍藏版）》请在金锄头文库上搜索。
1、n数据共享数据共享n数据独立数据独立n最小冗余最小冗余 n数据安全数据安全 n数据完整数据完整 n灵活性和灵活性和可恢复性可恢复性 数据库技术及网络应用数据库技术及网络应用 章节目录章节目录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实例：实例：定义定义一个滚动游
2、标一个滚动游标 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实例：实例：修改修改
3、存储过程存储过程 7.2.8实例：实例：重命名和删除重命名和删除存储过程存储过程网络语言数据库SQL Server高级应用章节目录章节目录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实例实例:事务事务编程编程
4、 7.4.5事务事务并发处理并发处理与与并发控制并发控制 7.4事务处理事务处理 小结小结 7.3.7实例：实例：更新功能更新功能触发器触发器7.3.6实例：实例：插入功能插入功能触发器触发器网络语言数据库SQL Server高级应用本章提要本章提要本章通过大量实例介绍本章通过大量实例介绍SQL语句的高级应用，包语句的高级应用，包括游标、存储过程、触发器、事务处理的功能及基本括游标、存储过程、触发器、事务处理的功能及基本操作。它们是操作。它们是网络数据库编程的重要内容网络数据库编程的重要内容，是开发网，是开发网络数据库应用程序的络数据库应用程序的关键技术关键技术。第第7章章SQLServer高
5、级应用高级应用7.1游标游标 7.2存储过程存储过程 7.3触发器触发器 7.4事务处理事务处理 小结小结 网络语言数据库SQL Server高级应用7.1游标游标7.1.1游标概述游标概述游标是游标是类似于类似于C语言指针语言指针一样的结构一样的结构,面对包括多面对包括多条数据记录的结果集条数据记录的结果集,游标能完成每次只提取一条记游标能完成每次只提取一条记录的操作。游标由两部分组成：结果集和在结果集中录的操作。游标由两部分组成：结果集和在结果集中指向某一记录位置的指针。游标提供了一种在服务器指向某一记录位置的指针。游标提供了一种在服务器内部处理结果集的方法，内部处理结果集的方法，它可以识
6、别一个数据集合内它可以识别一个数据集合内部指定的工作行，从而可以有选择地按行采取操作。部指定的工作行，从而可以有选择地按行采取操作。第七章第七章SQLServer高级应用高级应用网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用7.1.2声明游标声明游标在使用游标之前首先要声明游标。用在使用游标之前首先要声明游标。用T-SQL声明游标的声明游标的语法如下：语法如下：declare游标名游标名insensitivescrollcursorforselect语句语句forreadonly|updateof列名列名1,列名列名2.语法注释：语法注释：游标名游标名
7、为声明的游标取的名字，游标名必须符合标识符的命名规则。为声明的游标取的名字，游标名必须符合标识符的命名规则。insensitive使用使用insensitive定义的游标，会将提取出来的数据存放在系统数据库定义的游标，会将提取出来的数据存放在系统数据库tempdb创建的一个临时表里。任何通过该游标进行的操作都在临时表里进行，创建的一个临时表里。任何通过该游标进行的操作都在临时表里进行，因因此所有对基本表的改动都不会在游标中体现出来。此所有对基本表的改动都不会在游标中体现出来。scroll使用使用scroll定义的游标，称为滚动游标，可以提取任意位置的数据行。若无定义的游标，称为滚动游标，可以提
8、取任意位置的数据行。若无此关键字，只能按顺序提取。此关键字，只能按顺序提取。select语句语句指标准的指标准的select语句，用来定义游标所要处理的结果集。语句，用来定义游标所要处理的结果集。readonly声明只读游标，不允许通过游标进行数据更新。声明只读游标，不允许通过游标进行数据更新。updateof列名列名1,列名列名2.声明可更新的游标中允许更新的列。若只选声明可更新的游标中允许更新的列。若只选update，则所有的列都可更新。则所有的列都可更新。 网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用在使用游标之前，必须先打开游标。利用在使用游
9、标之前，必须先打开游标。利用T-SQL打开游打开游标的语法格式：标的语法格式：open游标名游标名功能注释：功能注释：当执行打开游标的语句时，服务器执行声明游标时使用当执行打开游标的语句时，服务器执行声明游标时使用的的select语句。此时，用游标提取数据的当前位置是数语句。此时，用游标提取数据的当前位置是数据集的第一条记录。据集的第一条记录。7.1.3打开游标打开游标网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用语法注释：语法注释：（1）使用）使用scroll关键字声明的游标，具有以下所有的取数功能：关键字声明的游标，具有以下所有的取数功能：firs
10、t取第一行数据取第一行数据prior取前一行数据取前一行数据next取下一行数据取下一行数据last取最后一行数据取最后一行数据absolute按绝对位置取数据，从游标中取出第按绝对位置取数据，从游标中取出第n行记录行记录relative按相对位置取数据，读出从游标当前位置向后第行记录。按相对位置取数据，读出从游标当前位置向后第行记录。（2）into变量名表变量名表使用使用into子句对变量赋值时，变量的数量、类型、顺序必须子句对变量赋值时，变量的数量、类型、顺序必须和声明游标时使用的和声明游标时使用的select语句输出的列项一一对应。语句输出的列项一一对应。7.1.4读取游标数据读取游标数
11、据打开游标之后，就可以用游标读取数据了。打开游标之后，就可以用游标读取数据了。利用利用T-SQL读取游标的语法如下：读取游标的语法如下：fetchfirst|prior|next|last|absolutetn|relativenfrom游标名游标名into变量名表变量名表网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用程序构思：程序构思：本例声明一个游标，用来逐条显示本例声明一个游标，用来逐条显示“学生表学生表”中性别为中性别为“女女”的记录。的记录。操作步骤：操作步骤：（1）单击）单击【开始】【开始】【所有程序】【所有程序】【MicrosoftSQL
12、Server2005】【SQLServerManagementStudio】，打开，打开【连接到服务器】【连接到服务器】对话框，单击对话框，单击【连【连接】接】钮，弹出钮，弹出【MicrosoftSQLServerManagementStudio】管理器窗口。管理器窗口。（2）单击常用工具栏中的）单击常用工具栏中的钮，则建立了钮，则建立了一个数据库引擎查询文档，文档名默认为一个数据库引擎查询文档，文档名默认为SQLQuery1.sql。7.1.5实例：定义一个滚动游标实例：定义一个滚动游标网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用（3）在）在SQL
13、Query1.sql文档界面输入代码：文档界面输入代码：use学生成绩管理学生成绩管理declarecursor1scrollcursorforselect*from学生表学生表where性别性别=女女opencursor1fetchfirstfromcursor1whilefetch_status=0beginfetchnextfromcursor1end（4）单击工具栏上的）单击工具栏上的按钮按钮,或按或按F5键键fetch_status是一个全局变量，若其值为是一个全局变量，若其值为0，则表示，则表示fetch命令被成功执行。命令被成功执行。网络语言数据库SQL Server高级应用第七
14、章第七章SQLServer高级应用高级应用7.1.6实例：读取游标中的数据存入变量中实例：读取游标中的数据存入变量中程序构思：程序构思：本例声明的游标，其结果集为选修本例声明的游标，其结果集为选修“三维动画设计三维动画设计”课程课程成绩最高的学生的学号、姓名、课程名称和成绩，并将成绩最高的学生的学号、姓名、课程名称和成绩，并将从游标中读取的数据存放到局部变量中，再输出变量中从游标中读取的数据存放到局部变量中，再输出变量中的值。的值。操作步骤操作步骤：（1）单击）单击【开始】【开始】【所有程序】【所有程序】【MicrosoftSQLServer2005】【SQLServerManagementS
15、tudio】，打开，打开【连接到服务器】【连接到服务器】对话框，单击对话框，单击【连接】【连接】钮，钮，弹出弹出【MicrosoftSQLServerManagementStudio】管管理器窗口。理器窗口。（2）单击常用工具栏中的）单击常用工具栏中的钮，则建立了一个钮，则建立了一个数据库引擎查询文档，文档名默认为数据库引擎查询文档，文档名默认为SQLQuery1.sql。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用（3）在）在SQLQuery1.sql文档界面输入代码：文档界面输入代码：use学生成绩管理学生成绩管理declarecursor2sc
16、rollcursorforselecta.学号学号,姓名姓名,课程名称课程名称,成绩成绩from学生表学生表a,课程表课程表b,成绩表成绩表cwhere课程名称课程名称=三维动画设计三维动画设计andc.学号学号=a.学号学号andb.课程编号课程编号=c.课程编号课程编号orderby成绩成绩descopencursor2declarenumchar(20)declarenamechar(20)declarescore_namechar(20)declaremaxintfetchfirstfromcursor2intonum,name,score_name,maxselectnumas编号编
17、号,nameas名字名字,score_nameas课名课名,maxas分数分数（4）单击工具栏上的）单击工具栏上的按钮按钮,或按或按F5键键 。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用语法注释：语法注释：currentof游标名游标名表示当前游标的当前数据行，该子句只能在表示当前游标的当前数据行，该子句只能在update和和delete操作操作的语句中使用。的语句中使用。7.1.7利用游标更新数据和删除数据利用游标更新数据和删除数据要通过游标对数据进行修改，该游标必须被声明为可要通过游标对数据进行修改，该游标必须被声明为可更新的游标。在声明游标时
18、，只要不使用更新的游标。在声明游标时，只要不使用forreadonly关键字，游标都是可更新的。关键字，游标都是可更新的。利用利用T-SQL更新游标的语法格式：更新游标的语法格式：update表名表名set列名列名=表达式，表达式，wherecurrentof游标名游标名利用利用T-SQL删除游标的语法格式：删除游标的语法格式：deletefrom表名表名set列名列名=表达式，表达式，wherecurrentof游标名游标名网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用程序构思：程序构思：本例声明的游标，其执行结果集为没有被选修的课程本例声明的游标，
19、其执行结果集为没有被选修的课程信息，包括课程编号、课程名、学分。统计结果集中信息，包括课程编号、课程名、学分。统计结果集中的数据行数，并显示相应的提示信息。的数据行数，并显示相应的提示信息。7.1.8实例：利用游标统计数据行数实例：利用游标统计数据行数操作步骤操作步骤：（1）单击单击【开始】【开始】【所有程序】【所有程序】【MicrosoftSQLServer2005】【SQLServerManagementStudio】，打开，打开【连接到服务器】【连接到服务器】对话框，单击对话框，单击【连接】连接】钮，弹出钮，弹出【MicrosoftSQLServerManagementStudio】管理
20、器窗口。管理器窗口。（2）单击常用工具栏中的单击常用工具栏中的钮，钮，则建立了一个数据库引擎查询文档，则建立了一个数据库引擎查询文档，文档文档名默认为名默认为SQLQuery1.sql。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用（3）在）在SQLQuery1.sql文档界面输入代码：文档界面输入代码：use学生成绩管理学生成绩管理declarecursor3scrollcursorforselectdistincta.课程编号课程编号,课程名称课程名称,学分学分from课课程表程表a,成绩表成绩表bwherea.课程编号课程编号notin(sele
21、ctdistinct课程编课程编号号from成绩表成绩表)opencursor3fetchfirstfromcursor3whilefetch_status=0beginfetchnextfromcursor3endselectcursor_rowsas未被选修的课程数未被选修的课程数打开游标后，可以使用全局变量打开游标后，可以使用全局变量cursor_rows返回当前游标可以操作的数据返回当前游标可以操作的数据行数。行数。（4）单击工具栏上的）单击工具栏上的按钮按钮,或按或按F5键键 网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用2释放游标释放游标游
22、标结构本身也会占用一定的计算机资源，所以在使用完游标后，为了回收被占游标结构本身也会占用一定的计算机资源，所以在使用完游标后，为了回收被占用的资源，应该将游标释放。用的资源，应该将游标释放。利用利用T-SQL释放游标的语法格式：释放游标的语法格式：deallocate游标名游标名当释放完游标以后，如果要重新使用这个游标，则必须重新执行声明游标的语句。当释放完游标以后，如果要重新使用这个游标，则必须重新执行声明游标的语句。7.1.9关闭游标和释放游标关闭游标和释放游标 1关闭游标关闭游标在打开游标之后，在打开游标之后，SQLServer服务器会专门为游标服务器会专门为游标开辟一定的内存空间存放游
23、标操作的数据结果集，开辟一定的内存空间存放游标操作的数据结果集，同时同时游标的使用也会根据具体情况对某些数据进行封锁。游标的使用也会根据具体情况对某些数据进行封锁。所所以，在不使用游标的时候，一定要关闭游标，以通知服以，在不使用游标的时候，一定要关闭游标，以通知服务器释放游标所占用的资源。务器释放游标所占用的资源。利用利用T-SQL关闭游标的语法格式：关闭游标的语法格式：close游标名游标名关闭游标之后，可以再次打开游标，在一个批处理中，关闭游标之后，可以再次打开游标，在一个批处理中，可以多次打开和关闭游标。可以多次打开和关闭游标。网络语言数据库SQL Server高级应用第七章第七章SQL
24、Server高级应用高级应用程序构思：程序构思：本例声明的游标，其执行结果集为成绩最低的学生本例声明的游标，其执行结果集为成绩最低的学生信息，包括学号、姓名、所属院系、成绩，通过游标在信息，包括学号、姓名、所属院系、成绩，通过游标在成绩表中将成绩最低的一条记录删除。成绩表中将成绩最低的一条记录删除。操作步骤：操作步骤：（1）单击）单击【开始】【开始】【所有程序】【所有程序】【MicrosoftSQLServer2005】【SQLServerManagementStudio】，打开，打开【连接到服务器】【连接到服务器】对话框，单击对话框，单击【连接连接】钮，钮，弹出弹出【MicrosoftSQL
25、ServerManagementStudio】管理器窗口。管理器窗口。（2）单击常用工具栏中的）单击常用工具栏中的钮，则建立了一钮，则建立了一个数据库引擎查询文档，文档名默认为个数据库引擎查询文档，文档名默认为SQLQuery1.sql。7.1.10实例：利用游标删除表中的一行实例：利用游标删除表中的一行网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用（3）在）在SQLQuery1.sql文档界面输入代码：文档界面输入代码：use学生成绩管理学生成绩管理declarecursor4scrollcursorforselecta.学号学号,姓名姓名,所属院系
26、所属院系,成绩成绩from学生表学生表a,成绩表成绩表bwherea.学号学号=b.学号学号and成绩成绩=(selectmin(成绩成绩)from成绩表成绩表)opencursor4fetchfirstfromcursor4deletefrom成绩表成绩表wherecurrentofcursor4closecursor4deallocatecursor4（4）单击工具栏上的）单击工具栏上的按钮按钮,或按或按F5键键 。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用7.2存储过程存储过程7.2.1存储过程概述存储过程概述存储过程具有以下优点：存储过程具
27、有以下优点：（1）由一系列由一系列T-SQL语句组成，可作为一个整体被程序语句组成，可作为一个整体被程序多次调用多次调用；（2）修改容易，可移植性好；修改容易，可移植性好；（3）存储过程是预编译的，存储过程是预编译的，执行速度快执行速度快；（4）在网络中调用过程，只需要传送在网络中调用过程，只需要传送调用过程的语句调用过程的语句，因此可，因此可减少网络流量减少网络流量；（5）可通过对执行存储过程的权限控制来保证系统数据的可通过对执行存储过程的权限控制来保证系统数据的安全安全；（6）存储过程可存储过程可嵌套调用嵌套调用，即在一个存储过程中调用另一个存储过程。，即在一个存储过程中调用另一个存储过程
28、。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用7.2.2存储过程的创建与执行存储过程的创建与执行1创建存储过程创建存储过程利用利用T-SQL创建存储过程的语法格式：创建存储过程的语法格式：createproc过程名过程名parameter参数类型参数类型/*输入参数输入参数*/parameter参数类型参数类型ouput/*输出参数输出参数*/asbegin命令行或命令块命令行或命令块end2执行存储过程执行存储过程利用利用T-SQL执行存储过程的语法格式：执行存储过程的语法格式：execute过程名过程名参数参数1,参数参数2，output网络语言
29、数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用程序构思：程序构思：本例存储过程的功能是显示所有选修了本例存储过程的功能是显示所有选修了“数据库技数据库技术及网络应用术及网络应用”课程的学生的学号、姓名、所属院系和课程的学生的学号、姓名、所属院系和成绩。成绩。操作步骤：操作步骤：（1）单击）单击【开始】【开始】【所有程序】【所有程序】【MicrosoftSQLServer2005】【SQLServerManagementStudio】，打开，打开【连接到服务器】【连接到服务器】对话框，单击对话框，单击【连接】【连接】钮，弹出钮，弹出【MicrosoftSQLSe
30、rverMana-gementStudio】管理器窗口。管理器窗口。（2）单击常用工具栏中的）单击常用工具栏中的钮，则建立了一个钮，则建立了一个数据库引擎查询文档，文档名默认为数据库引擎查询文档，文档名默认为SQLQuery1.sql。7.2.3实例：不带参数的存储过程实例：不带参数的存储过程网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用（3）在）在SQLQuery1.sql文档界面输入代码：文档界面输入代码：use学生成绩管理学生成绩管理gocreateprocproc1asbeginselecta.学号学号,姓名姓名,所属院系所属院系,成绩成绩fr
31、om学生表学生表a,课程表课程表b,成绩表成绩表cwhere课程名称课程名称=数据库技术及网络应用数据库技术及网络应用andb.课程编号课程编号=c.课程编号课程编号andc.学号学号=a.学号学号endgoexecuteproc1/*执行存储过程执行存储过程*/（4）单击工具栏上的）单击工具栏上的按钮按钮,或按或按F5键键。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用程序构思：程序构思：本例存储过程的功能是查询担任某门课程的教师的本例存储过程的功能是查询担任某门课程的教师的基本信息。执行存储过程时，允许带输入参数，即课程基本信息。执行存储过程时，允
32、许带输入参数，即课程名称。存储过程的执行结果显示教师的姓名、职称和联名称。存储过程的执行结果显示教师的姓名、职称和联系电话。系电话。7.2.4实例：带输入参数的存储过程实例：带输入参数的存储过程操作步骤：操作步骤：（1）单击）单击【开始】【开始】【所有程序】【所有程序】【MicrosoftSQLServer2005】【SQLServerManagementStudio】，打开，打开【连接到服务器】【连接到服务器】对话框，单击对话框，单击【连【连接】接】钮，弹出钮，弹出【MicrosoftSQLServerManagementStudio】管理器窗口。管理器窗口。（2）单击常用工具栏中的）单击常
33、用工具栏中的钮，则建立了一个数据库引擎查询文档，文档钮，则建立了一个数据库引擎查询文档，文档名默认为名默认为SQLQuery1.sql。 网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用（3）在）在SQLQuery1.sql文档界面输入代码：文档界面输入代码：use学生成绩管理学生成绩管理gocreateprocproc2scrose_namevarchar(20)asbeginselect姓名姓名,职称职称,联系电话联系电话from课程表课程表a,教师表教师表bwhere课程名称课程名称=scrose_nameanda.教师编号教师编号=b.教师编号教
34、师编号endgoexecuteproc2C语言程序设计语言程序设计/*执行存储过程执行存储过程*/（4）单击工具栏上的）单击工具栏上的按钮按钮,或按或按F5键键 。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用7.2.5实例：带输入和输出参数的存储过程实例：带输入和输出参数的存储过程程序构思程序构思：本存储过程的功能是统计某院系学生的最大年龄和本存储过程的功能是统计某院系学生的最大年龄和平均年龄。执行存储过程时，允许带输入参数和输出参平均年龄。执行存储过程时，允许带输入参数和输出参数。输入参数为所属院系名称，输出参数保存所执行的数。输入参数为所属院系名
35、称，输出参数保存所执行的结果，即最大年龄和平均年龄。结果，即最大年龄和平均年龄。操作步骤：操作步骤：（1）单击单击【开始】【开始】【所有程序】【所有程序】【MicrosoftSQLServer2005】【SQLServerManagementStudio】，打开，打开【连接到服务器】【连接到服务器】对话框，单击对话框，单击【连接】【连接】钮，弹出钮，弹出【MicrosoftSQLServerManagementStudio】管理器窗口。管理器窗口。（2）单击常用工具栏中的单击常用工具栏中的钮，则建立了一个数据库引擎查询文档，文档钮，则建立了一个数据库引擎查询文档，文档名默认为名默认为SQLQu
36、ery1.sql。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用（3）在）在SQLQuery1.sql文档界面输入代码：文档界面输入代码：use学生成绩管理学生成绩管理gocreateprocproc3yuanxichar(20),maxageintoutput,averagerealoutputasbeginselectmaxage=max(datepart(yy,getdate()-datepart(yy,出生日期出生日期)from学生表学生表where所属院系所属院系=yuanxiselectaverage=avg(datepart(yy,get
37、date()-datepart(yy,出生日期出生日期)from学生表学生表where所属院系所属院系=yuanxiendgodeclarex1int,x2realexecuteproc3教育学院教育学院,x1output,x2outputselectx1最大年龄最大年龄,x2平均年龄平均年龄（4）单击工具栏上的）单击工具栏上的按钮按钮,或按或按F5键键 。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用7.2.6存储过程的修改、重命名和删除存储过程的修改、重命名和删除1修改存储过程修改存储过程利用利用T-SQL修改存储过程的语法格式：修改存储过程的语法
38、格式：alterproc过程名过程名parameter参数类型参数类型/*输入参数输入参数*/parameter参数类型参数类型ouput/*输出参数输出参数*/asbegin命令行或命令块命令行或命令块end修改存储过程是指修改存储过程的内容。如果要修改某修改存储过程是指修改存储过程的内容。如果要修改某一存储过程，在创建该存储过程的代码中，将命令动词一存储过程，在创建该存储过程的代码中，将命令动词creat改为改为alter，再修改其内容即可。，再修改其内容即可。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用2.存储过程的重命名存储过程的重命名1修改
39、存储过程修改存储过程利用利用T-SQL修改存储过程的语法格式：修改存储过程的语法格式：alterproc过程名过程名parameter参数类型参数类型/*输入参数输入参数*/parameter参数类型参数类型ouput/*输出参数输出参数*/asbegin命令行或命令块命令行或命令块end修改存储过程是指修改存储过程的内容。如果要修改某一存储过程，修改存储过程是指修改存储过程的内容。如果要修改某一存储过程，在创建该存储过程的代码中，将命令动词在创建该存储过程的代码中，将命令动词creat改为改为alter，再修改其，再修改其内容即可。内容即可。网络语言数据库SQL Server高级应用第七章第
40、七章SQLServer高级应用高级应用3删除存储过程删除存储过程利用利用T-SQL语句删除存储过程的语法格式：语句删除存储过程的语法格式：dropproc存储过程名存储过程名1,存储过程名存储过程名2，删除存储过程是指将存储过程作为数据库对象从数据库中彻底删除。一次可删除多删除存储过程是指将存储过程作为数据库对象从数据库中彻底删除。一次可删除多个存储过程。个存储过程。利用利用T-SQL语句给存储过程重命名的语法格式：语句给存储过程重命名的语法格式：sp_rename原过程名原过程名,新过程名新过程名给存储过程重命名只是修改存储过程的名字，和存储过给存储过程重命名只是修改存储过程的名字，和存储过
41、程内容本身无关。该功能用系统存储过程程内容本身无关。该功能用系统存储过程sp_rename实实现，该系统存储过程存放在现，该系统存储过程存放在master数据库里，在任何数数据库里，在任何数据库中都可直接调用它。据库中都可直接调用它。网络语言数据库SQL Server高级应用7.2.7实例：修改存储过程实例：修改存储过程第七章第七章SQLServer高级应用高级应用程序构思：程序构思：本例是将存储过程本例是将存储过程pro3的功能修改为输入院系名称，的功能修改为输入院系名称，输出该院系学生的人数。输出该院系学生的人数。操作步骤：操作步骤：（1）单击）单击【开始】【开始】【所有程序】【所有程序】
42、【MicrosoftSQLServer2005】【SQLServerManagementStudio】，打开，打开【连接到服务器】【连接到服务器】对话框，单击对话框，单击【连接】【连接】钮，钮，弹出弹出【MicrosoftSQLServerManagementStudio】管理器窗口。管理器窗口。（2）单击常用工具栏中的）单击常用工具栏中的钮，则建立了一钮，则建立了一个数据库引擎查询文档，文档名默认为个数据库引擎查询文档，文档名默认为SQLQuery1.sql。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用（3）在）在SQLQuery1.sql文档界面
43、输入代码：文档界面输入代码：use学生成绩管理学生成绩管理goalterprocproc3yuanxichar(20),countintoutputasbeginselectcount=count(*)from学生表学生表where所属院系所属院系=yuanxiendgodeclarexintexecuteproc3物电学院物电学院,xoutputselectx人数人数（4）单击工具栏上的）单击工具栏上的按钮按钮,或按或按F5键键。此例中，若想输出院系名称和人数，即将输入参数也作为结果输出，应怎样改进？此例中，若想输出院系名称和人数，即将输入参数也作为结果输出，应怎样改进？网络语言数据库SQL
44、 Server高级应用7.2.8实例：重命名和删除存储过程实例：重命名和删除存储过程程序构思程序构思：本例是将将存储过程本例是将将存储过程proc1删除删除,再将存储过程名再将存储过程名pro3改为改为pro3new。操作步骤：操作步骤：（1）单击）单击【开始】【开始】【所有程序】【所有程序】【MicrosoftSQLServer2005】【SQLServerManagementStudio】，打开，打开【连接到服务器】【连接到服务器】对话框，单击对话框，单击【连接连接】钮钮，弹出弹出【MicrosoftSQLServerManagementStudio】管理器窗口。管理器窗口。（2）单击常用
45、工具栏中的）单击常用工具栏中的钮，则建立了一个钮，则建立了一个数据库引擎查询文档，文档名默认为数据库引擎查询文档，文档名默认为SQLQuery1.sql。第七章第七章SQLServer高级应用高级应用（3）在）在SQLQuery1.sql文档界面输入代码：文档界面输入代码：dropprocproc1sp_renameproc3,proc3newnew（4）单击工具栏上的）单击工具栏上的按钮按钮,或按或按F5键键。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用7.3.1触发器概述触发器概述1.触发器的概念触发器的概念触发器是一种特殊的存储过程，它和表密切
46、相连，触发器是一种特殊的存储过程，它和表密切相连，可以看作是表格定义的一部分可以看作是表格定义的一部分。当用户对指定表操作当用户对指定表操作时，时，触发器会自动执行。触发器会自动执行。触发器在触发器在update、insert、delete等操作执行结束后才执行。等操作执行结束后才执行。2.触发器的功能触发器的功能(1)级联更新数据库中相关表的数据；级联更新数据库中相关表的数据；(2)实现多表之间数据的一致性；实现多表之间数据的一致性；(3)执行比检查约束更复杂的约束操作；执行比检查约束更复杂的约束操作；(4)调用存储过程；调用存储过程；(5)在一张表的在一张表的update、insert、d
47、elete操作上可设置多个触发器。操作上可设置多个触发器。 7.3触发器触发器网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用3.使用触发器应注意的问题使用触发器应注意的问题(1)当使用约束、规则、默认值等方法能够实现数据的完当使用约束、规则、默认值等方法能够实现数据的完整性，就不用触发器实现；整性，就不用触发器实现；(2)只有表的拥有者才可以在表上创建或删除触发器，这只有表的拥有者才可以在表上创建或删除触发器，这种权限不准转授；种权限不准转授；(3)使用使用update语句可以一次对多行数据进行修改，而触语句可以一次对多行数据进行修改，而触发器只被触发
48、一次；发器只被触发一次；(4)触发器只能在当前数据库中创建，触发器的命名必须触发器只能在当前数据库中创建，触发器的命名必须要遵守标识符的命名规则。要遵守标识符的命名规则。4.触发器的优点触发器的优点(1)触发器是自动的；触发器是自动的；(2)触发器可以通过数据库中的相关表进行层叠更改；触发器可以通过数据库中的相关表进行层叠更改；(3)触发器可以强制限制，这些限制比用触发器可以强制限制，这些限制比用check所定义的约束更复杂。所定义的约束更复杂。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用7.3.2触发器的工作原理触发器的工作原理inserted表和
49、表和deleted表的特点如下：表的特点如下：（1）这两张表是逻辑表，由数据库管理，用户不能对）这两张表是逻辑表，由数据库管理，用户不能对他们进行修改；他们进行修改；（2）这两张表存储在内存中，而不是存储在数据库中；）这两张表存储在内存中，而不是存储在数据库中；（3）这两张表的结构与被该触发器作用的表结构相同；）这两张表的结构与被该触发器作用的表结构相同；（4）当触发器完成操作后，这两张表会自动删除；）当触发器完成操作后，这两张表会自动删除；（5）两张表中保存的数据是因用户操作而被影响到的）两张表中保存的数据是因用户操作而被影响到的原数据或新数据；原数据或新数据；（6）这两张表是只读表。）这两
50、张表是只读表。网络语言数据库SQL Server高级应用利用利用T-SQL创建触发器的语法格式：创建触发器的语法格式：creattrigger触发器名称触发器名称on表名表名forupdate,insert,deleteasbeginsql语句语句endreturn语法注释：语法注释：触发器名称触发器名称是用户给要创建的触发器起的名字是用户给要创建的触发器起的名字on表名表名触发器作用的表名称触发器作用的表名称forupdate,insert,delete指明激活触发器的操作指明激活触发器的操作sql语句语句指明触发器要执行的操作指明触发器要执行的操作return表示返回调用位置表示返回调用位
51、置触发器的执行是指对表进行的触发器的执行是指对表进行的update、insert、delete操作。操作。第七章第七章SQLServer高级应用高级应用7.3.3触发器的创建与执行触发器的创建与执行网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用7.3.4实例：显示功能触发器实例：显示功能触发器程序构思：程序构思：本例创建的触发器具有显示信息功能。为成绩表本例创建的触发器具有显示信息功能。为成绩表创建一个触发器，在删除成绩表中的记录时，显示表创建一个触发器，在删除成绩表中的记录时，显示表中有多少条记录被删除。中有多少条记录被删除。操作步骤：操作步骤：（1
52、）单击）单击【开始开始】【所有程序所有程序】【MicrosoftSQLServer2005】【SQLServerMan-agementStudio】，打开，打开【连接到服务器连接到服务器】对话框，单击对话框，单击【连接连接】钮，弹出钮，弹出【MicrosoftSQLServerManagementStudio】管理器窗口。管理器窗口。（2）单击常用工具栏中的）单击常用工具栏中的钮，则建立了一钮，则建立了一个数据库引擎查询文档，文档名默认为个数据库引擎查询文档，文档名默认为SQLQuery1.sql。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用（3）在
53、）在SQLQuery1.sql文档界面输入代码：文档界面输入代码：use学生成绩管理学生成绩管理gocreatetriggertrigger1on成绩表成绩表fordeleteasbegindeclarecountvarchar(30)selectcount=str(rowcount)+个记录被个记录被删除！删除！printcountendgodeletefrom成绩表成绩表where成绩成绩=0（4）单击工具栏上的）单击工具栏上的按钮按钮,或按或按F5键键 。rowcount是全局变量，该变量存储的是执行一条是全局变量，该变量存储的是执行一条T-SQL语句后所影响的行数。语句后所影响的行数。
54、网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用1修改触发器修改触发器利用利用T-SQL修改触发器的语法格式：修改触发器的语法格式：altertrigger触发器名称触发器名称on表名表名forupdate,insert,deleteasbeginsql语句语句endreturn语法注释：语法注释：修改触发器只需在创建触发器的语法格式里，将修改触发器只需在创建触发器的语法格式里，将create改为改为alter。7.3.5触发器的修改、删除和显示触发器的修改、删除和显示网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应
55、用2删除触发器删除触发器利用利用T-SQL删除触发器的语法格式：删除触发器的语法格式：droptrigger触发器名触发器名,.n语法注释：语法注释：触发器名触发器名要删除的触发器名称要删除的触发器名称,.n表示可以删除多个触发器表示可以删除多个触发器 3显示触发器信息显示触发器信息用用SQLServer提供的系统存储过程显示所创建的触发器的有关信息。格式及功能提供的系统存储过程显示所创建的触发器的有关信息。格式及功能如下：如下：sp_help触发器名触发器名显示触发器的一般信息显示触发器的一般信息sp_helptext触发器名触发器名显示触发器的文本信息显示触发器的文本信息sp_depend
56、s表名表名显示表中各种依赖关系显示表中各种依赖关系sp_depends触发器名触发器名显示触发器所引用的对象显示触发器所引用的对象。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用程序构思：程序构思：本例为学生表创建一个触发器，实现级联插入。本例为学生表创建一个触发器，实现级联插入。当向学生表中插入一条新记录时，系统会将该生的学当向学生表中插入一条新记录时，系统会将该生的学号自动插入到成绩表中，并在成绩表中填写选修的课号自动插入到成绩表中，并在成绩表中填写选修的课程编号是程编号是“EIT27401C”。7.3.6实例：插入功能触发器实例：插入功能触发器操
57、作步骤：操作步骤：（1）单击）单击【开始】【开始】【所有程序】【所有程序】【MicrosoftSQLServer2005】【SQLServerManagementStudio】，打开，打开【连接到服务器】【连接到服务器】对话框，单击对话框，单击【连【连接】接】钮，弹出钮，弹出【MicrosoftSQLServerManagementStudio】管理器窗口。管理器窗口。（2）单击常用工具栏中的）单击常用工具栏中的钮，则建立了一个数据库引擎查询文档，文钮，则建立了一个数据库引擎查询文档，文档名默认为档名默认为SQLQuery1.sql。网络语言数据库SQL Server高级应用第七章第七章SQL
58、Server高级应用高级应用（3）在）在SQLQuery1.sql文档界面输入代码：文档界面输入代码：use学生成绩管理学生成绩管理gocreatetriggertrigger2on学生表学生表forinsertasbegindeclarenumbervarchar(10)selectnumber=学号学号frominsertedinsertinto成绩表成绩表(学号学号,课程编号课程编号)values(number,EIT27401C)endgoinsertinto学生表学生表(学号学号,姓名姓名,性别性别)values(06506999,齐飞齐飞,男男)（4）单击工具栏上的）单击工具栏上的
59、按钮按钮,或按或按F5键键 。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用程序构思程序构思：本例为成绩表创建一个更新触发器，其作用是只要本例为成绩表创建一个更新触发器，其作用是只要执行更新操作，所有记录的成绩都加执行更新操作，所有记录的成绩都加5分。分。7.3.7实例：更新功能触发器实例：更新功能触发器操作步骤操作步骤：（1）单击）单击【开始】【开始】【所有程序】【所有程序】【MicrosoftSQLServer2005】【SQLServerManagementStudio】，打开，打开【连接到服务器】【连接到服务器】对话框，单击对话框，单击【连【连
60、接】接】钮，弹出钮，弹出【MicrosoftSQLServerManagementStudio】管理器窗口。管理器窗口。（2）单击常用工具栏中的）单击常用工具栏中的钮，则建立了一个数据库引擎查询文档，文钮，则建立了一个数据库引擎查询文档，文档名默认为档名默认为SQLQuery1.sql。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用（3）在）在SQLQuery1.sql文档界面输入代码：文档界面输入代码：droptriggertrigger3gocreatetriggertrigger3on成绩表成绩表forupdateasupdate成绩表成绩表se
61、t成绩成绩=成绩成绩+5select*frominsertedselect*fromdeletedreturngoupdate成绩表成绩表set成绩成绩=100where学号学号=06501011and课程编号课程编号=FCA10200C（4）单击工具栏上的）单击工具栏上的按钮按钮,或按或按F5键键 。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用1.事务的概念事务的概念在数据库系统中，把一系列具有在数据库系统中，把一系列具有“要么什么也不做，要么什么也不做，要么都做完要么都做完”特征的数据库操作定义为一个事务。事务反特征的数据库操作定义为一个事务。事
62、务反映了现实世界中需要以一个完整的单位提交的一项工作。映了现实世界中需要以一个完整的单位提交的一项工作。事务是一个逻辑工作单元，它必须完整地执行，或者全事务是一个逻辑工作单元，它必须完整地执行，或者全都不执行。都不执行。 7.4事务处理事务处理7.4.1事务概述事务概述2.数据库一致性状态数据库一致性状态数据库的一致性状态是指所有数据都满足数据完整性约束条件的状态。数据库的一致性状态是指所有数据都满足数据完整性约束条件的状态。3.事务处理事务处理事务处理是保证数据库一致性状态的重要方法。事务处理是保证数据库一致性状态的重要方法。4.实现数据库一致性状态的方法实现数据库一致性状态的方法为了保证数
63、据库的一致性状态，为了保证数据库的一致性状态，SQLServer必须控制和保证事务的执行能够符合必须控制和保证事务的执行能够符合数据库的完整性约束条件。数据库的完整性约束条件。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用所有的事务都具有原子性、永久性、串行性和隔离性。所有的事务都具有原子性、永久性、串行性和隔离性。 7.4.2事务属性事务属性1原子性原子性2永久性永久性3串行性串行性4隔离性隔离性网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用SQLServer通过事务控制语句，把通过事务控制语句，把SQLSe
64、rver语句语句集合分组后，形成独立的逻辑工作单元。事务处理控集合分组后，形成独立的逻辑工作单元。事务处理控制语句有三个：制语句有三个：begintransaction表示事务开始表示事务开始committransaction表示事务提交表示事务提交rollbacktransaction表示事务回滚表示事务回滚7.4.3事务控制语句事务控制语句几点说明：几点说明：（1）在）在SQLServer中，通常一个事务是以中，通常一个事务是以begintransaction开始，到一个相匹配的开始，到一个相匹配的committransaction或或rollbacktransaction之间的所有语句序
65、列；之间的所有语句序列；（3）committransaction表示提交事务的一切操作，使得操作对数据的改变生效；表示提交事务的一切操作，使得操作对数据的改变生效；（2）rollbacktransaction表示要撤消该事务已做的一切操作，回滚到事务开始的状表示要撤消该事务已做的一切操作，回滚到事务开始的状态。态。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用程序构思：程序构思：本例向成绩表中插入一条记录，若插入成功，则本例向成绩表中插入一条记录，若插入成功，则提交；否则，回滚。提交；否则，回滚。7.4.4实例实例:事务编程事务编程操作步骤：操作步骤：
66、（1）单击）单击【开始】【开始】【所有程序】【所有程序】【MicrosoftSQLServer2005】【SQLServerManagementStudio】，打开，打开【连接到服务器】【连接到服务器】对话框，单击对话框，单击【连【连接】接】钮，弹出钮，弹出【MicrosoftSQLServerManagementStudio】管理器窗管理器窗口。口。（2）单击常用工具栏中的）单击常用工具栏中的钮，则建立了一个数据库引擎查询文档，文钮，则建立了一个数据库引擎查询文档，文档名默认为档名默认为SQLQuery1.sql。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应
67、用高级应用（3）在）在SQLQuery1.sql文档界面输入代码：文档界面输入代码：begintransactioninsert成绩表成绩表values(06501011,EIT37401,91)iferror=0committransaction/*若无误，则提交此事务若无误，则提交此事务*/elserollbacktransaction/*若有误，则回退此事务若有误，则回退此事务*/（4）单击工具栏上的）单击工具栏上的按钮按钮,或按或按F5键键。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用1.并发处理并发处理指指CPU利用分时方法实现多个事务的交
68、替进行。利用分时方法实现多个事务的交替进行。7.4.5事务并发处理与并发控制事务并发处理与并发控制 2.并发处理可能产生的问题并发处理可能产生的问题(1)丢失更新丢失更新(2)读未提交数据读未提交数据(3)不一致检索不一致检索产生上述问题的原因：违背了事务的隔离性。产生上述问题的原因：违背了事务的隔离性。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用3.封锁机制封锁机制封锁机制是实现并发控制的重要技术，并发控制的封锁机制是实现并发控制的重要技术，并发控制的作用是为了实现事务的隔离性。作用是为了实现事务的隔离性。(1)封锁的类型封锁的类型共享锁共享锁：也
69、称读锁。如果事务：也称读锁。如果事务T给数据对象给数据对象X加上共享锁，则其他事务只能对加上共享锁，则其他事务只能对X再加共再加共享锁，不能加排它锁。享锁，不能加排它锁。T及其他事务都可以且只能读数据。及其他事务都可以且只能读数据。排它锁排它锁：也称写锁。如果事务：也称写锁。如果事务T对数据对象对数据对象X加上排它锁，则只允许事务加上排它锁，则只允许事务T独占数据项独占数据项X，即对数据项，即对数据项X可读、可写。其他任何事务都不能对可读、可写。其他任何事务都不能对X加任何类型的锁，直加任何类型的锁，直到到T释放释放X上的锁。上的锁。网络语言数据库SQL Server高级应用第七章第七章SQL
70、Server高级应用高级应用（2）封锁规则）封锁规则1）数据项）数据项X未加锁，谁都可以给它加锁。未加锁，谁都可以给它加锁。2）数据项）数据项X已加共享锁，其他事务只能加共享锁。已加共享锁，其他事务只能加共享锁。3）数据项）数据项X已加排他锁，其他事务不能加任何锁。已加排他锁，其他事务不能加任何锁。4）谁加的锁，谁释放该锁。）谁加的锁，谁释放该锁。（3）封锁的粒度）封锁的粒度指封锁对象的大小。封锁的对象可以是数据库、表、行、列等逻辑单元。指封锁对象的大小。封锁的对象可以是数据库、表、行、列等逻辑单元。（4）死锁）死锁死锁是若干事务相互等待对方释放封锁，死锁是由于封锁而产生的。死锁是若干事务相互
71、等待对方释放封锁，死锁是由于封锁而产生的。（5）解决死锁的方法）解决死锁的方法死锁的预防死锁的预防：要求每个事务必须一次性封锁所要使用的全部数据；规定封锁的顺序。：要求每个事务必须一次性封锁所要使用的全部数据；规定封锁的顺序。死锁的诊断死锁的诊断：DBMS周期性测试周期性测试DBS系统是否处于死锁状态。系统是否处于死锁状态。死锁的解除死锁的解除：将一个处理代价小的事务撤销，回滚该事务，并释放该事务持有的锁。：将一个处理代价小的事务撤销，回滚该事务，并释放该事务持有的锁。网络语言数据库SQL Server高级应用第七章第七章SQLServer高级应用高级应用本章所讲的内容有一定的难度，必须是在前
72、几章本章所讲的内容有一定的难度，必须是在前几章知识掌握较好的情况下才能轻松进入本章的学习。就知识掌握较好的情况下才能轻松进入本章的学习。就目前网络的发展而言，学习网络编程技术已成为在软目前网络的发展而言，学习网络编程技术已成为在软件道路上发展的必经之路，而数据库技术又是网络编件道路上发展的必经之路，而数据库技术又是网络编程的核心技术，本章的内容又是程的核心技术，本章的内容又是SQLServer中的关中的关键技术。因此，本章在整个教程中的地位很重要，一键技术。因此，本章在整个教程中的地位很重要，一定要认真完成章后的习题，这样能使知识掌握得更加定要认真完成章后的习题，这样能使知识掌握得更加牢固。牢固。小结小结网络语言数据库SQL Server高级应用
