存储过程与触发器

一.教学目标与要求:1.熟练掌握存储过程的创建与执行操作;2.熟练掌握触发器的创建与执行操作；3.掌握存储过程与触发器的应用；4.难点：存储过程与触发器的应用。二.教学手段:1.PPT理论讲解;2.演示操作、链接播放教学;3.提问互动、引导自学;第十章存储过程与触发器10/19/20221SQLServer2000(十)

回顾

查询语句SELECT的整体功能select-from-where的操作select的子查询的语法及使用分类汇总的作用联接的概念10/19/20222SQLServer2000(十)第十章存储过程与触发器

教学目标

1.了解存储过程的概念2.熟练掌握存储过程的创建与执行操作3.熟练掌握触发器的创建与执行操作4.掌握存储过程与触发器的应用**************10/19/20223SQLServer2000(十)一.存储过程存储过程的概念

存储过程（stordprocedure）是由SQL语句和可选控制流语句组成的预编译集合。编译后，集合中的SQL语句被编译为可执行的指令。存储过程可包含程序流、逻辑及数据查询，可接收输入参数，输出参数，返回单个结果集或多个结果集给调用它的过程或批处理。执行速度快

存储过程创建时就经过语法检查和性能优化，执行时不必重复检查，第一次调用后驻留内存；模块化的程序设计存储过程创建一次，可重复调用；减少网络通信量存储过程作为一个独立的单元使用，一条语句即可实现存储过程中的大量的SQL语句，减少了反复连接访问网络；保证系统的安全性可以不授予用户访问存储过程中涉及的表的权限，只授予执行存储过程的权限。10/19/20224SQLServer2000(十)2.创建存储过程使用T-SQL创建存储过程语法格式：CREATEPROC[EDURE]存储过程名称[；number/*用于区分同名的过程*/][{@参数名类型[=默认值][OUTPUT表明该参数要返回值]，……参数列表}][WITH重新编译|加密][FORREPLICTION/*不能在订阅服务器上执行为复制创建的存储过程*/]

AS

SQL语句序列注：WITH重新编译，指在使用的临时值等，不希望覆盖缓存在内存中的执行计划VARYING仅适用于游标存储过程是数据库对象！10/19/20225SQLServer2000(十)例：使用基本格式说明USE图书管理系统goCREATEPROCP_图书；1ASselect*from……例：使用基本格式说明USE图书管理系统goCREATEPROCP_图书；2@pppint/*参数为用户要输入数据*//*含OUTPUT参数返回值*/ASselect*from……/*包含=成绩，默认值*/[=成绩OUTPUT]同名过程，但已经有别，使用DROPP_图书？例：将一个查询置入存储过程useNorthwindGocreateprocp_1Asselect*from成绩Goexecutep_110/19/20226SQLServer2000(十)/*创建的存储过程只返回命令是否成功*/10/19/20227SQLServer2000(十)使用企业管理器创建存储过程使用向导创建存储过程（自学）播放操作步骤：【……】→【数据库】→【数据库名】→【存储过程】

选任意的存储过程→→新建存储过程→10/19/20228SQLServer2000(十)3.管理存储过程通过企业管理器可以查看存储过程的定义，相关性以及使用T-SQL查看信息操作步骤：【……】→【数据库】→【数据库名】→【存储过程】右键

选指定的存储过程→属性（所有任务→相关性）sp_help查看存储过程信息sp_helptext查看存储过程语句播放10/19/20229SQLServer2000(十)4.修改存储过程5.执行存储过程修改命令ALTERPROCEDURE格式：与创建时同语法格式：CREATEPROC[EDURE]存储过程名称[；number][{@参数名类型[=默认值][OUTPUT]，……参数列表}][WITH重新编译|加密][FORREPLICTION]ASSQL语句序列ALTER执行存储过程的命令EXECUTE[EXEC][@返回状态码=]（过程名）有设置自动运行的，返回状态码可供应用程序调用10/19/202210SQLServer2000(十)6.删除存储过程企业管理器中，选中→右键→删除T-SQL命令DROPPROCEDURE过程名10/19/202211SQLServer2000(十)二.触发器触发器的概念

触发器（trigger）是一种特殊的存储过程，它不能显示的调用执行，而是通过“事件”触发的，它是基于表的与表紧密关联的程序。当发生表插入、删除等操作事件时，与该表关联的触发器便启动执行触发器内的语句。根据触发器操作的不同，分为：

UPDATE、INSERT、DELETE、INSTEADOF和AFTER5种类型注意：触发器是在事件完成后启动；由于其他原因使得事件中止，触发器不启动；一个语句只能触动一次触发器。触发器取代插入、删除等执行↑↑控制触发器激活10/19/202212SQLServer2000(十)2.创建触发器

使用T-SQL创建触发器使用格式：CREATETRIGGER触发器名ON（表|视图）[WITHENCRYPTION加密]FOR|AFTER|INSTEADOF/*选择其一*/DELETE，INSERT，UPDATE/*执行操作触动触发器*/ASSQL语句……触发器是特殊的存储过程是数据库对象！例：createtriggertton成绩forinsert,deleteasprint‘你在干什么？危险！’操作：deletefrom成绩where成绩<70将触发tt!10/19/202213SQLServer2000(十)10/19/202214SQLServer2000(十)3.管理触发器

查看触发器信息；查看触发器相关性；系统过程查看触发器sp_help……使用企业管理器创建触发器播放【……】【数据库】〖表〗选择指定表右键→→〖所有任务〗→〖管理触发器…〗10/19/202215SQLServer2000(十)4.修改触发器

企业管理器中直接修改；T-SQL命令ALTER，格式同创建；外围重命名sp_rename5.删除触发器

企业管理器右键删除T-SQL命令DROP10/19/202216SQLServer2000(十)三.存储过程与触发器的应用存储过程的应用

例带参数（输入与默认）：书P182例10.9Use信息管理GoCreateproc显示_p@性别char(2)/*键盘输入*/加：=‘女’/*默认值*/Asselect学号，姓名from学生where性别=@性别Return

Exec显示_p‘男’10/19/202217SQLServer2000(十)例****带参数（输入输出）：书P182例10.11Use信息管理Ifexixts(selectnamefromsysobiects/*存储过程存于系统数据库该表*/wherename=‘显示_p’andtype=‘p’)/*过程名在该表中仅为一字段*/dropprocedure显示_p/*系统中只能有唯一的一个，若重复，删除*/goCreateproc显示_p@学号char(6),@成绩numericoutput/*学号为输入，成绩要求output*/Asselect@成绩=成绩fromcjwhere课程名=‘？？’and学号=@学号Return

Declare@cjnumeric/*使用的变量要先定义,亦可在过程中定义，参例10.12*/Exec显示_p‘050013’,@成绩=@cjoutputPrintconvert(varchar(6),@cj)/*将数值类型转换为字符*/10/19/202218SQLServer2000(十)例****带参数（输入输出）：Createprocproc_Booksum@Book_pubchar(50)=‘清华大学出版社’,@sum_BookintoutputAsSelect@sum_Book=count(图书编号)From出版社信息表innerjoin图书明细表On出版社信息表.出版社编号=图书明细表.出版社编号Where出版社信息表.出版社名称=@Book_pubgo

Declare@sum_BookintExecproc_Booksum‘清华大学出版社’,@sum_Bookoutputselect‘现存该出版社图书共：’+str(,@sum_Book)go10/19/202219SQLServer2000(十)例带参数（返回值）：/*读程序分析问题*/UseNorthwindGoCreateProcedureProduct_a@Product_idintAsIf(select库存数量From库存表Where商品编号=@Product_id)<=5BeginPrint‘商品库存不多，请及时进货！！’Return(1)/*可不用（）；Return1*/EndElseif(select库存数量From库存表Where商品编号=@Product_id)>5BeginPrint‘商品库存正常！’Return(2)EndGoDeclare@sum_quantityintPrint‘商品库存信息：’Exec@sum_quantity=Product_a1001/*输入商品编号1001*/Print‘存储过程Product_a的返回值为：’+str(@sum_quantity)goif(select库存数量From库存表Where商品编号=@Product_id)>250BeginPrint‘商品库存充足，放心睡觉吧！’Return(3)End10/19/202220SQLServer2000(十)2.触发器的应用触发器的作用与条件约束相同，一旦执行删除、更新、插入操作即启动触发器创建触发器时，系统会生成两个临时表#deleted、#inserted复制触发器基于的表，待用户对（原始）表的操作触动触发器，二表保存改动的痕迹。

Delete触发器例P18710.17使用触发器串联删除CreateTRIGGER删除班级On班级FordeleteAsPrint‘提示——开始’Delete学生From学生，deleted/*使用表的副本（内存）*/Where学生.班级编号=deleted.班级编号Print‘提示——结束’Select*fromdeleted/*修改后状态*/go10/19/202221SQLServer2000(十)Insert触发器例P18710.18使用触发器在完成插入后提示信息CreateTRIGGER成绩插入触发器On成绩ForinsertAsSelect*frominsretedPrint‘提示——信息’goInsertinto成绩values(…………)结果10/19/202222SQLServer2000(十)Update触发器例P18710.18触发器检查CreateTRIGGER成绩更新触发器On成绩ForupdateAsDeclare@更改前成绩numeric,@更改后成绩numericSelect@更改前成绩=成绩fromdeleted/*临时表*/Print‘更改前成绩为=’Printconvert(varchar(6),@更改前成绩)Select@更改后成绩=成绩fromdeletedPrint‘更改后成绩为=’Printconvert(varchar(6),@更改后成绩)If(@更改后成绩>(@更改前成绩*1.10))/*超过原成绩的10%*/beginprint‘超过原成绩的10%，更改失败！回滚！！’rollbackendelseprint‘成绩更改成功，恭喜你！’go执行更新操作看结果10/19/202223SQLServer2000(十)触发器嵌套触发器嵌套与一般程序语句嵌套有别，在查询中的顺序结构表明嵌套关系例P191例10.20CreateTRIGGER删除班级On班级FordeleteAsPrint‘提示——开始’Delete学生From学生，deletedWhere学生.班级编号=deleted.班级编号Print‘提示——结束’goCreateTRIGGER删除学生On学生FordeleteAs