第4章SQL Server 存储过程与触发器

第4章SQLServer存储过程与触发器刘翔4.1SQLServer存储过程4.1.1存储过程概述存储过程(StoredProcedure)是一组为了完成特定功能T-SQL语句集合，经编译后存储在SQLServer服务器端数据库中。存储过程可以分为两类：系统存储过程和自定义存储过程。4.1SQLServer存储过程•系统存储过程，图4.1所示。图4.1系统存储过程4.1SQLServer存储过程•系统存储过程在SQLServer安装成功后，就已经存储在系统数据库Master中，这些存储过程都是以sp_为前缀命名的，它们主要是从系统表中获取信息，系统管理员可以通过简单调用系统存储过程而完成复杂的SQLServer管理工作。可以通过系统存储过程完成许多管理性或信息的操作。系统存储过程在Master数据库中，在其他数据库中可以直接调用，调用时不必在存储过程名前加上数据库名。4.1SQLServer存储过程•自定义存储过程是由用户创建并能完成某一特定功能的存储过程。4.1SQLServer存储过程•存储过程的具体优点如下：1．提高应用程序的通用性和可移植性2．可以更有效地管理用户操作数据库的权限3．可以提高T-SQL的速度4.1SQLServer存储过程4．减轻服务器的负担5．块化程序设计。6．减少操作错误。7．能自动处理复杂的或敏感的事务。8．可以实现管理任务自动化。4.1SQLServer存储过程4.1.2存储过程的创建与执行1.直接创建存储过程(1)打开MicrosoftSQLServerManager管理器。(2)单击数据库前面的“+”号，然后单击“Material_Data1”数据库前面的“+”号，再单击“可编程性”前面的“+”号，选择“存储过程”，单击鼠标右键，在弹出的快捷菜单中单击“新建存储过程”命令，图4.2所示。4.1SQLServer存储过程图4.2新建存储过程4.1SQLServer存储过程(3)打开了一个创建存储过程的数据库引擎查询模板，修改相应参数即可。4.1SQLServer存储过程2.代码创建存储过程利用T-SQL创建存储过程的语法格式如下：createproc过程名@parameter参数类型@parameter参数类型Output…asbegin命令行或命令块end4.1SQLServer存储过程3.代码执行存储过程利用T-SQL执行存储过程的语法格式如下：execute过程名[参数值，…[output](1）没有参数的存储过程创建createprochyproclasselect*frommanagerwherewage1800执行该存储过程executehyprocl4.1SQLServer存储过程(2）有参数存储过程创建createprochyproc2@mingzint,@maxgzintasselect*frommanagerwherewagebetween@mingzand@maxgz4.1SQLServer存储过程例4.1假设要显示工资在1000到2000之间的manager信息，具体代码：executehyproc21005，18004.1SQLServer存储过程4.1.3修改存储过程修改存储过程具体格式如下：alterproc过程名@parameter参数类型@parameter参数类型outputasbegin命令行或命令块end其中各参数的意义与创建过程的相同。4.1SQLServer存储过程例4.2修改存贮过程hyproc2,输出manager性别分类人员数与总工资。alterprochyproc2@sex1char(2),@managercountintoutput,@wagetotalrealoutputasbeginselect*frommanagerselect@managercount=count(wage)frommanagerwheresex=@sex1select@wagetotal=sum(wage)frommanagerwheresex=@sex1end4.1SQLServer存储过程例4.3假设要显示manager信息及输出工资的最大值与平均值，具体代码如下：Declare@x1char(2),@x2realexecutehyproc2'男',@x1output,@x2outputselect@x1as男管理人员,@x2as男管理人员工资和4.1SQLServer存储过程4.1.4用C#创建存储过程使用.NET的语言，如C#、VB.NET之类的来开发SQLServer的存储过程、函数和触发器。4.2SQLServer触发器触发器是一种特殊类型的存储过程。4.2.1触发器的作用1．可以调用存储过程2．可以强化数据条件约束3．跟踪数据库内数据变化，并判断数据变化是否符合数据库的要求4．级联和并行运行4.2SQLServer触发器4.2.2触发器的分类SQLServer支持两类触发器，分别是事后触发器(after触发器)和替代触发器(insteadof触发器)。1.事后触发器2.替代触发器4.2SQLServer触发器4.2.3创建与执行触发器利用T-SQL创建触发器的语法格式如下：事后触发器：createtrigger触发器名on表名[withencryption]forinsert[，update，delete]asbegin命令行或程序块end替代触发器：createtrigger触发器名On表名或视图名insteadofinsert[，update，delete]asbegin命令行或程序块end4.2SQLServer触发器例4.4创建一个触发器，向manager中插入一条记录，创建一个数据库表并向表中插入二条记录。(1)打开MicrosoftSQLServerManager管理器。(2)新建一个数据库引擎查询文档。(3)在数据库引擎查询文档中输入如下代码：UseMaterial_Data1(4)按键盘上的F5”键，显示如下提示信息：命令已成功完成。(5)这样就打开要使用的数据库。4.2SQLServer触发器createtriggerhytriggerlonmanagerforupdateasbegincreatetabletriuser(useridintidentity(1,1)primarykey,usermamevarchar(50),userpwdvarchar(50))insertintotriuser(username,userpwd)values('李明','111')insertintotriuser(usermame,userpwd)values('王明','222')insertintotriuser(usermame,userpwd)values('刘芳','333')end4.2SQLServer触发器(6)选择创建触发器的代码，按键盘上的F5”键，显示如下提示信息：命令已成功完成。(7)这样就成功创建了触发器。在这里要注意，只是创建了触发器，并没有执行触发器中的代码，即表triuser还不存在，当然该表中也不会有记录。(8)下面来通过对manager表的更新操作调用触发器hytriggerl，具体代码如下：Updatemanagersetwage=wage+100wheremanagerNo='001'(9)选择SQL语句，按下键盘上的F5”键执行该SQL语句，显示如图4.5提示信息：4.2SQLServer触发器图4.5执行触发器4.2SQLServer触发器(10)提示信息表示影响了四行，即更新了仓库表中的一条记录，创建triuser表，并向该表中插入三条记录，下面通过select*fromtriuser来显示触发器、产生新表中的数据信息，如图4.6所示。4.2SQLServer触发器图4.6显示触发器执行后的结果4.2SQLServer触发器4.2.4查看触发器基本信息通过sp_help能够查看触发器的基本信息，这些基本信息包括触发器名、所有者、创建者和创建时间。其语法格式如下：execsp_help触发器名如查看触发器hytriggerl信息，execsp_helphytriggerl，图4.7所示。4.2SQLServer触发器图4.7查看触发器基本信息4.2SQLServer触发器4.2.5查看触发器代码通过sp_helptext能够查看触发器SQL的代码信息，但要注意如果在创建触发器时使withencrypdon选项，则执行该命令也看不到SQL代码。其语法格式如下：execsp_helptext触发器名。若要查看触发器hytriggerl代码，执行execsp_helptexthytriggerl，如图4.8所示4.2SQLServer触发器图4.8查看触发器hytriggerl代码4.2SQLServer触发器4.2.6修改触发器修改触发器的方法很简单，利用T-SQL修改触发器的语法格式如下：事后触发器：altertrigger触发器名On表名[withencryption]forinsert[，update，delete]asbegin命令行或程序块End4.2SQLServer触发器例4.6如修改例4.4中hytriggerl插入行数据‘刘芳’为‘张清’altertriggerhytriggerlonmanagerforupdateasbegincreatetabletriuser(useridintidentity(1,1)primarykey,usermamevarchar(50),userpwdvarchar(50))insertintotriuser(username,userpwd)values('李明','111')insertintotriuser(usermame,userpwd)values('王明','222')insertintotriuser(usermame,userpwd)values('张清','333')end4.2SQLServer触发器替代触发器：altertrigger触发器名On表名或视图名insteadofinsert[,update,delete]asbegin命令行或程序块end修改触发器与创建触发器几乎相同，只是把create改为alter即可。4.2SQLServer触发器4.2.7删除触发器删除触发器的方法很简单，利用T-SQL删除触发器的语法格式如下：droptrigger触发器名