《第4章SQL Server 存储过程与触发器》由会员分享，可在线阅读，更多相关《第4章SQL Server 存储过程与触发器（35页珍藏版）》请在金锄头文库上搜索。
1、第4章SQL Server 存储过程与触发器,刘翔,4.1 SQL Server 存储过程,4.1.1存储过程概述 存储过程(Stored Procedure)是一组为了完成特定功能T-SQL语句集合，经编译后存储在SQL Server服务器端数据库中。存储过程可以分为两类：系统存储过程和自定义存储过程。,4.1 SQL Server 存储过程,系统存储过程，图4.1所示。,图4.1系统存储过程,4.1 SQL Server 存储过程,系统存储过程在SQL Server安装成功后，就已经存储在系统数据库Master中，这些存储过程都是以sp_为前缀命名的，它们主要是从系统表中获取信息，系统管理
2、员可以通过简单调用系统存储过程而完成复杂的SQL Server管理工作。可以通过系统存储过程完成许多管理性或信息的操作。系统存储过程在Master数据库中，在其他数据库中可以直接调用，调用时不必在存储过程名前加上数据库名。,4.1 SQL Server 存储过程,自定义存储过程是由用户创建并能完成某一特定功能的存储过程。,4.1 SQL Server 存储过程,存储过程的具体优点如下： 1提高应用程序的通用性和可移植性 2可以更有效地管理用户操作数据库的权限 3可以提高T-SQL的速度,4.1 SQL Server 存储过程,4减轻服务器的负担 5块化程序设计。 6减少操作错误。 7能自动处理
3、复杂的或敏感的事务。 8可以实现管理任务自动化。,4.1 SQL Server 存储过程,4.1.2存储过程的创建与执行 1.直接创建存储过程 (1)打开Microsoft SQL Server Manager管理器。 (2)单击数据库前面的“+”号，然后单击“Material_Data1”数据库前面的“+”号，再单击“可编程性”前面的“+”号，选择“存储过程”，单击鼠标右键，在弹出的快捷菜单中单击“新建存储过程”命令，图4.2所示。,4.1 SQL Server 存储过程,图4.2新建存储过程,4.1 SQL Server 存储过程,(3)打开了一个创建存储过程的数据库引擎查询模板，修改相应
4、参数即可。,4.1 SQL Server 存储过程,2.代码创建存储过程 利用T-SQL创建存储过程的语法格式如下： create proc 过程名 parameter参数类型 parameter 参数类型 Output as begin 命令行或命令块 end,4.1 SQL Server 存储过程,3.代码执行存储过程 利用T-SQL执行存储过程的语法格式如下： execute 过程名参数值，output (1）没有参数的存储过程创建 create proc hyprocl as select * from manager where wage 1800 执行该存储过程 execute h
5、yprocl,4.1 SQL Server 存储过程,(2）有参数存储过程创建 create proc hyproc2 mingz int, maxgz int as select*from manager where wage between mingz and maxgz,4.1 SQL Server 存储过程,例4.1假设要显示工资在1000到2000之间的manager信息，具体代码： execute hyproc2 1005，1800,4.1 SQL Server 存储过程,4.1.3 修改存储过程 修改存储过程具体格式如下： alter proc过程名 parameter参数类型
6、parameter参数类型output as begin 命令行或命令块 end 其中各参数的意义与创建过程的相同。,4.1 SQL Server 存储过程,例4.2修改存贮过程hyproc2, 输出manager性别分类人员数与总工资。 alter proc hyproc2 sex1 char(2), managercount int output, wagetotal real output as begin select*from manager select managercount=count(wage)from manager where sex=sex1 select waget
7、otal=sum(wage)from manager where sex=sex1 end,4.1 SQL Server 存储过程,例4.3假设要显示manager信息及输出工资的最大值与平均值，具体代码如下： Declare x1 char(2), x2 real execute hyproc2 男,x1 output,x2 output select x1 as 男管理人员,x2 as 男管理人员工资和,4.1 SQL Server 存储过程,4.1.4用C#创建存储过程 使用.NET的语言，如C#、VB.NET之类的来开发SQL Server的存储过程、函数和触发器。,4.2 SQL S
8、erver 触发器,触发器是一种特殊类型的存储过程。 4.2.1触发器的作用 1可以调用存储过程 2可以强化数据条件约束 3跟踪数据库内数据变化，并判断数据变化是否符合数据库的要求 4级联和并行运行,4.2 SQL Server 触发器,4.2.2 触发器的分类 SQLServer支持两类触发器，分别是事后触发器(after触发器)和替代触发器(instead of触发器)。 1.事后触发器 2.替代触发器,4.2 SQL Server 触发器,4.2.3 创建与执行触发器 利用T-SQL创建触发器的语法格式如下： 事后触发器： create trigger触发器名 on 表名 with en
9、cryption for insert，update，delete as begin 命令行或程序块 end 替代触发器： create trigger触发器名 On 表名或视图名instead of insert，update，delete as begin 命令行或程序块 end,4.2 SQL Server 触发器,例4.4 创建一个触发器，向manager中插入一条记录，创建一个数据库表并向表中插入二条记录。 (1) 打开Microsoft SQL Server Manager管理器。 (2) 新建一个数据库引擎查询文档。 (3)在数据库引擎查询文档中输入如下代码： Use Mater
10、ial_Data1 (4)按键盘上的F5”键，显示如下提示信息： 命令已成功完成。 (5)这样就打开要使用的数据库。,4.2 SQL Server 触发器,create trigger hytriggerl on manager for update as begin create table triuser( userid int identity(1,1)primary key, usermame varchar(50), userpwd varchar(50) ) insert into triuser(username,userpwd)values(李明,111) insert int
11、o triuser(usermame,userpwd)values(王明,222) insert into triuser(usermame,userpwd)values(刘芳,333) end,4.2 SQL Server 触发器,(6)选择创建触发器的代码，按键盘上的F5”键，显示如下提示信息： 命令已成功完成。 (7)这样就成功创建了触发器。在这里要注意，只是创建了触发器，并没有执行触发器 中的代码，即表triuser还不存在， 当然该表中也不会有记录。 (8)下面来通过对manager表的更新操作调用触发器hytriggerl，具体代码如下： Update manager set wa
12、ge=wage+100 where managerNo=001 (9)选择SQL语句，按下键盘上的F5”键执行该SQL语句，显示如图4.5提示信息：,4.2 SQL Server 触发器,图4.5 执行触发器,4.2 SQL Server 触发器,(10)提示信息表示影响了四行，即更新了仓库表中的一条记录，创建triuser表，并向该表中插入三条记录，下面通过select * from triuser来显示触发器、产生新表中的数据信息，如图4.6所示。,4.2 SQL Server 触发器,图4.6 显示触发器执行后的结果,4.2 SQL Server 触发器,4.2.4 查看触发器基本信息
13、通过sp_help能够查看触发器的基本信息，这些基本信息包括触发器名、所有者、创建者和创建时间。其语法格式如下： exec sp_help 触发器名 如查看触发器hytriggerl信息，exec sp_help hytriggerl，图4.7所示。,4.2 SQL Server 触发器,图4.7 查看触发器基本信息,4.2 SQL Server 触发器,4.2.5 查看触发器代码 通过sp_helptext能够查看触发器SQL的代码信息，但要注意如果在创建触发器时使with encrypdon选项，则执行该命令也看不到SQL代码。其语法格式如下： exec sp_helptext 触发器名。
14、若要查看触发器hytriggerl代码，执行exec sp_helptext hytriggerl，如图4.8所示,4.2 SQL Server 触发器,图4.8 查看触发器hytriggerl代码,4.2 SQL Server 触发器,4.2.6 修改触发器 修改触发器的方法很简单，利用T-SQL修改触发器的语法格式如下： 事后触发器： alter trigger触发器名 On 表名with encryption for insert，update，delete as begin 命令行或程序块 End,4.2 SQL Server 触发器,例4.6如修改例4.4中hytriggerl插入行
15、数据刘芳为张清 alter trigger hytriggerl on manager for update as begin create table triuser( userid int identity(1,1)primary key, usermame varchar(50), userpwd varchar(50) ) insert into triuser(username,userpwd)values(李明,111) insert into triuser(usermame,userpwd)values(王明,222) insert into triuser(usermame,userpwd)values(张清,333) end,4.2 SQL Server 触发器,替代触发器： alter trigger触发器名 On 表名或视图名instead of insert,update, delete as begin 命令行或程序块 end 修改触发器与创建触发器几乎相同，只是把create改为alter即可。,4.2 SQL Server 触发器,4.2.7删除触发器 删除触发器的方法很简单，利用T-SQL删除触发器的语法格式如下： drop trigger触发器名,
