第 存储过程和触发器PPT课件

本章难点本章难点有参存储过程的创建、执行触发器的触发时机DELETED与INSERTED表第1页/共81页第第10章章 存储过程和触发器存储过程和触发器存储过程的概念1建立和执行存储过程2存储过程的管理与维护3触发器的概念4创建和应用DML触发器5触发器的管理与维护6DDL触发器7事务8第2页/共81页10.1 存储过程的概念存储过程的概念 在数据库的实际应用中，常需要重复执行一些程序段。为了方便用户，也为了提高执行效率，SQL Server 2005中的用户自定义函数、存储过程、触发器可以用来满足这些应用需求。第3页/共81页10.1 存储过程的概念存储过程的概念举例：USE XSSELECT 学号,姓名,系名,总学分FROM XSDAWHERE 系名=信息ORDER BY 总学分 DESC1. 将将T-SQL语句发送到服务器语句发送到服务器2. 服务器编译服务器编译T-SQL语句语句3. 优化产生查询执行计划优化产生查询执行计划4. 数据库引擎执行查询计划数据库引擎执行查询计划5. 执行结果返回客户端执行结果返回客户端第4页/共81页10.1 存储过程的概念存储过程的概念第5页/共81页10.1 存储过程的概念存储过程的概念EXEC xxEXEC xxEXEC xxEXEC xxEXEC xxEXEC xxEXEC xxEXEC xxEXEC xxEXEC xxEXEC xxEXEC xxEXEC xx第6页/共81页10.1 存储过程的概念存储过程的概念第7页/共81页10.1 存储过程的概念存储过程的概念 存储过程的优点u执行效率高u统一的操作流程u重复使用u安全性第8页/共81页10.1 存储过程的概念存储过程的概念 存储过程的分类u系统存储过程 存储在master数据库中以sp_为前缀u用户自定义存储过程 用户存储过程是用户根据需要，在自己的普通数据库中创建，是用来完成某项特定任务的存储过程。第9页/共81页10.1 存储过程的概念存储过程的概念存储过程与视图比较1.可以在单一的存储过程中执行一系列T-SQL语句，而视图只能是SELECT。2.存储过程可以接受参数，并能返回单个或多个结果集以及返回值，视图不能接受参数，只能返回结果集。一般将经常用到的多个表的链接查询定义为视图，而存储过程完成复杂的一系列的处理，在存储过程中也会经常使用到视图。第10页/共81页第第10章章 存储过程和触发器存储过程和触发器存储过程的概念1建立和执行存储过程2存储过程的管理与维护3触发器的概念4创建和应用DML触发器5触发器的管理与维护6DDL触发器7事务8第11页/共81页10.2 建立和执行存储过程建立和执行存储过程1、创建存储过程 创建存储过程时，需要注意下列事项： （1）只能在当前数据库中创建存储过程。 （2）数据库的所有者可以创建存储过程，也可以授权其他用户创建存储过程。 （3）存储过程是数据库对象，其名称必须遵守标识符命名规则。 （4）不能将 CREATE PROCEDURE 语句与其它 SQL 语句组合到单个批处理中。 （5）创建存储过程时，应指定所有输入参数和调用过程或批处理返回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处理以表明成功或失败的状态值。第12页/共81页10.2 建立和执行存储过程建立和执行存储过程（1）语法格式： CREATE PROC EDURE 存储过程名存储过程名 输入参数输入参数 数据类型数据类型 =defaultOUTPUT,n AS sql_statement .n 第13页/共81页10.2 建立和执行存储过程建立和执行存储过程第14页/共81页10.2 建立和执行存储过程建立和执行存储过程例10.1从xsgl数据库的三个表中查询，返回学生学号、姓名、课程名、成绩。该存储过程实际上只返回一个查询信息。USE xsglGOCREATE PROCEDURE stu_cjASSELECT xs.学号学号,姓名姓名,课程名课程名,成绩成绩 FROM xs INNER JOIN cj ON xs.学号学号=cj.学号学号 INNER JOIN kcON cj.课程号课程号=kc.课程号课程号GO第15页/共81页10.2 建立和执行存储过程建立和执行存储过程2、执行存储过程EXEC 存储过程名 参数值,n第16页/共81页10.2 建立和执行存储过程建立和执行存储过程第17页/共81页第18页/共81页第19页/共81页第20页/共81页第21页/共81页10.2 建立和执行存储过程建立和执行存储过程例10.3从xsgl数据库的三个表中查询某人指定课程的成绩。CREATE PROCEDURE stu_cj1 name char(10),cname char(16)ASSELECT xs.学号学号,姓名姓名,课程名课程名,成绩成绩 FROM xs INNER JOIN cj ON xs.学号学号=cj.学号学号INNER JOIN kcON cj.课程号课程号=kc.课程号课程号 WHERE xs.姓名姓名=name AND kc.课程课程=cnameGO第22页/共81页第23页/共81页第24页/共81页第25页/共81页第26页/共81页第27页/共81页第28页/共81页第29页/共81页10.2 建立和执行存储过程建立和执行存储过程练习： 在xs数据库中，创建一个存储过程jsjzf_p，用于查询信息系学生的总学分的排名情况（从高分到低分）。 USE XS GO CREATE PROC jsjzf_p AS SELECT 学号学号,姓名姓名,系名系名,总学分总学分 FROM xsda WHERE 系名系名=信息信息 ORDER BY 总学分总学分 DESC第30页/共81页10.2 建立和执行存储过程建立和执行存储过程练习： 使用输入参数，创建查询存储过程xzf_p，用于查询指定某系学生总学分的排名情况（由高到低）。 USE XS GO CREATE PROC xzf_p xiname char(10) AS SELECT 学号学号,姓名姓名,系名系名,总学分总学分 FROM xsda WHERE 系名系名=xiname ORDER BY 总学分总学分 DESC第31页/共81页10.2 建立和执行存储过程建立和执行存储过程练习： 使用输入参数，创建存储过程grkccj_p，查询某人指定课程的成绩和学分。 USE XS GO CREATE PROC grkccj_p name char(8),kcname char(20) AS SELECT xsda.学号学号,姓名姓名,课程名称课程名称,成绩成绩,总学总学分分 FROM xsda,xscj,kcxx WHERE xsda.学号学号=xscj.学号学号 AND xscj.课程编号课程编号=kcxx.课程编号课程编号 AND 姓名姓名=name AND 课程名称课程名称=kcname第32页/共81页10.2 建立和执行存储过程建立和执行存储过程例:参数传递技巧 在执行存储过程时，若未指明参数名称，则必须依照存储过程所需的参数依次传过去，而且除非该参数指定有默认值，否则不可省略。CREATE PROCEDURE testa int,b int=NULL,c int =3ASSELECT a,b,cGOEXEC testEXEC test 1EXEC test 1,DEFAULTEXEC test 1,DEFAULT,5EXEC test 1,2,3第33页/共81页10.2 建立和执行存储过程建立和执行存储过程例：创建一个存储过程xscj_p，查询指定学生的学号,姓名,所选课程名称及该课程的成绩，默认为查询刘姓学生的学习情况。CREATE PROCEDURE xscj_pname varchar(30) = 刘刘%AS SELECT xsda.学号学号,姓名姓名,课程名称课程名称,成绩成绩FROM xsda,xscj,kcxxWHERE xsda.学号学号=xscj.学号学号AND xscj.课程编号课程编号=kcxx.课程编号课程编号AND 姓名姓名 LIKE nameGO 第34页/共81页10.2 建立和执行存储过程建立和执行存储过程例：执行存储过程xscj_pEXEC xscj_pEXEC xscj_p 李李%EXEC xscj_p 王红王红EXEC xscj_p %第35页/共81页10.2 建立和执行存储过程建立和执行存储过程例10.5：创建一个存储过程用于计算指定学生各科成绩的总分，存储过程中使用了一个输入参数和一个输出参数。CREATE PROCEDURE stu_sum name char(10),total int OUTPUTASSELECT total=SUM(成绩成绩)FROM xs,cjWHERE 姓名姓名=name AND xs.学号学号=cj.学号学号 GROUP BY xs.学号学号GO第36页/共81页10.2 建立和执行存储过程建立和执行存储过程例10.5中存储过程的执行方法为：USE xsglDECLARE total intEXECUTE stu_sum 程明程明, total OUTPUTSELECT 程明程明,totalGO第37页/共81页10.2 建立和执行存储过程建立和执行存储过程练习： 创建一个存储过程kcavg_p，用于查询所有学生所学指定课程的平均成绩，并将该平均成绩返回。CREATE PROC kcavg_p kcname char(20),kcavg decimal(3,1) output AS SELECT kcavg=avg(成绩成绩) FROM xscj,kcxx WHERE xscj.课程编号课程编号=kcxx.课程编号课程编号 and 课程名称课程名称=kcname第38页/共81页10.2 建立和执行存储过程建立和执行存储过程练习： 执行存储过程kcavg_p，查询计算机文化基础课程的平均分。declare avg decimal(3,1)exec kcavg_p 计算机文化基础计算机文化基础,avg outputSELECT avg as 计算机文化基础的平均成绩计算机文化基础的平均成绩第39页/共81页10.2 建立和执行存储过程建立和执行存储过程课堂练习：1.在Sales数据库中，创建存储过程proc_Employees，查询所有的员工信息。2.创建一个带有输入参数的存储过程proc_goods，查询指定员工所进商品信息。3.创建一个带有输入和输出参数的存储过程proc_GNO，查询指定厂商指定名称的商品所对应的商品编号。第40页/共81页10.2 建立和执行存储过程建立和执行存储过程1.在Sales数据库中，创建存储过程proc_Employees，查询所有的员工信息。USE SalesGOCREATE PROC proc_EmployeesASSELECT * FROM Employees-执行存储过程执行存储过程EXEC proc_Employees第41页/共81页10.2 建立和执行存储过程建立和执行存储过程2.创建一个带有输入参数的存储过程proc_goods，查询指定员工所进商品信息。USE SalesGOCREATE PROC proc_goods 员工编号员工编号 char(6)=1001AS SELECT 商品编号商品编号,商品名称商品名称,生产厂商生产厂商,进货价进货价,零售价零售价,数量数量,进进货时间货时间 FROM Goods WHERE 进货员工编号进货员工编号=员工编号员工编号-执行存储过程，查询执行存储过程，查询1001号员工所进的商品的信息号员工所进的商品的信息EXEC proc_goods 员工编号员工编号=default-或或EXEC proc_goods 员工编号员工编号=1001第42页/共81页10.2 建立和执行存储过程建立和执行存储过程3.创建一个带有输入和输出参数的存储过程proc_GNO，查询指定厂商指定名称的商品所对应的商品编号。USE SalesGOCREATE PROC proc_GNO 商品名称商品名称 varchar(20),生产厂商生产厂商 varchar(30), 商品编号商品编号 int OUTPUTAS SELECT 商品编号商品编号=商品编号商品编号 FROM Goods WHERE 商品名称商品名称=商品名称商品名称 AND 生产厂商生产厂商=生产厂商生产厂商-执行存储过程，查询惠普公司打印机商品编号执行存储过程，查询惠普公司打印机商品编号DECLARE 商品编号商品编号 int EXEC proc_GNO 打印机打印机, 惠普公司惠普公司,商品编号商品编号 OUTPUTPRINT 该商品编号为：该商品编号为：+STR(商品编号商品编号)第43页/共81页第第10章章 存储过程和触发器存储过程和触发器存储过程的概念1建立和执行存储过程2存储过程的管理与维护3触发器的概念4创建和应用DML触发器5触发器的管理与维护6DDL触发器7事务8第44页/共81页10.3 存储过程的管理与维护存储过程的管理与维护1、查看存储过程的定义信息v在SQL Server Management Studio的“对象资源管理器”中，可以在要查看信息的存储过程上单击鼠标右键，在快捷菜单中选择“属性”，弹出“存储过程属性”窗口.v也可以通过系统存储过程sp_helptext查看存储过程的定义；通过sp_help查看存储过程的参数；通过sp_depends查看存储过程的相关性。第45页/共81页第第10章章 存储过程和触发器存储过程和触发器存储过程的概念1建立和执行存储过程2存储过程的管理与维护3触发器的概念4创建和应用DML触发器5触发器的管理与维护6DDL触发器7事务8第46页/共81页10.4 触发器的概念触发器的概念 触发器是一种与数据表（库）紧密结合的存储过程，当该数据表（库）有新建、更改或删除事件发生时，所设置的触发器即会自动被执行，以进行维护数据完整性，或其他一些特殊的数据处理工作。触发器就是当数据表内容被更改时，会自动执行的存储过程第47页/共81页10.4 触发器的概念触发器的概念第48页/共81页10.4 触发器的概念触发器的概念 与存储过程的区别: 触发器可以说是当触发器表（库）内容被更改时自动执行的存储过程。不能直接被调用，也不能传递或接受参数。第49页/共81页10.4 触发器的概念触发器的概念 触发器的用途l 约束可以直接设置于数据表内，通常不需另外撰写程序。但些方法只能进行比较简单的操作，包括自动填入默认值，确保字段数据不得重复，限制输入值，维护表间的参考完整性等。l 触发器是针对单一数据表所撰写的特殊存储过程，当该数据表发生INSERT、UPDATE、DELETE时会自动被触发（执行），以进行各项必要的处理工作，由于是撰写程序，因此无论是简单或复杂的工作都可一手包办。第50页/共81页10.4 触发器的概念触发器的概念 触发器的功能l 检查所做的更改是否允许触发器可以做多样、复杂的检查，例如同时检查多个数据表，或使用IFELSE等来作更有弹性的检查。 注：如果数据表同时设有约束及触发器，那么二者都可被执行，至于谁先谁后，则根据触发器的种类而定。当任何一者发现有错误时，也都可以立即回滚该次的数据修改。第51页/共81页第第10章章 存储过程和触发器存储过程和触发器存储过程的概念1建立和执行存储过程2存储过程的管理与维护3触发器的概念4创建和应用DML触发器5触发器的管理与维护6DDL触发器7事务8第52页/共81页10.4 触发器的概念触发器的概念l 进行其他相关数据的更改动作例如，当某笔订单被取消时，我们可以利用触发器去自动删除相关的送货单数据，并将业务员的奖金扣除；或是在更改员工的薪资时，将更改的日期及原薪资存入另一个薪资修改数据表中。l 发出更改或警告通知 例如，当有新员工时，触发器可以自动发Mail通知该部门的所有人员；或者当库存量小于安全存量时，即发Mail通知仓库管理员要赶快进货。第53页/共81页10.4 触发器的概念触发器的概念l 自定义错误信息当操作不符合约束时，返回给前端应用程序的错误信息都是固定的内容。利用触发器，则可以返回我们自定义的错误信息。l 更改原来所要进行的数据操作 利用INSTEAD OF触发器，我们可以撰写程序来取代原来应该进行的数据操作。例如，当新建一条记录时，可以将该记录的数据另外处理，而不存入数据表中。第54页/共81页第55页/共81页10.4 触发器的概念触发器的概念 触发器就像是仓库的管理员一样，当有货物要进出时，管理员即会出面作查核或协调，以维护整个仓库的正常运作。第56页/共81页第57页/共81页第58页/共81页第59页/共81页第第10章章 存储过程和触发器存储过程和触发器存储过程的概念1建立和执行存储过程2存储过程的管理与维护3触发器的概念4创建和应用DML触发器5触发器的管理与维护6DDL触发器7事务8第60页/共81页10.6 触发器的管理与维护触发器的管理与维护二、修改触发器ALTER TRIGGER 触发器名称 ON 表 |视图 FOR | AFTER | INSTEAD OF INSERT , DELETE, UPDATE AS IF UPDATE ( 列名) AND | OR UPDATE ( 列名 ) .n SQL语句 第61页/共81页10.6 触发器的管理与维护触发器的管理与维护二、删除触发器DROP TRIGGER 触发器名称 DROP TRIGGER tr_xscj 第62页/共81页第63页/共81页第64页/共81页第65页/共81页第第10章章 存储过程和触发器存储过程和触发器存储过程的概念1建立和执行存储过程2存储过程的管理与维护3触发器的概念4创建和应用DML触发器5触发器的管理与维护6DDL触发器7事务8第66页/共81页10.7 DDL触发器触发器 DDL触发器是SQL Server2005新增的一个触发器类型，像常规触发器一样，DDL触发器将激发存储过程以响应事件。DDL触发器为响应多种数据定义语言语句而激活，这些语句主要是以CREATE、ALTER和DROP开头的语句。第67页/共81页10.7 DDL触发器触发器 一般来说，在以下几种情况下可以使用DDL触发器。（1）防止数据库架构进行某些修改。（2）防止数据库或数据表被误操作而删除。（3）希望数据库发生某种情况以响应数据库架构中的更改。（4）要记录数据库架构的更改或事件。 仅在运行DDL触发器的DDL语句后，DDL触发器才会激发。DDL触发器无法作为INSTEAD OF触发器而使用。第68页/共81页10.7 DDL触发器触发器创建DDL触发器的语法格式如下：CREATE TRIGGER 触发器名触发器名ON 服务器服务器|数据库数据库WITH ENCRYPTIONFOR|AFTER DDL语句名称语句名称ASSQL语句语句第69页/共81页10.7 DDL触发器触发器例10.16 建立用于保护数据库xsgl中的数据表不被删除的触发器。CREATE TRIGGER dis_drop_table ON xsgl FOR DROP_TABLEASBEGIN RAISERROR(对不起，对不起，xsgl数据库中的表不能删除数据库中的表不能删除,16,10)ENDGO第70页/共81页10.7 DDL触发器触发器查看和修改DDL触发器（1）作用在当前SQL Server服务器上的DDL触发器所在的位置第71页/共81页10.7 DDL触发器触发器（2）作用在当前数据库中的DDL触发器所在位置第72页/共81页第第10章章 存储过程和触发器存储过程和触发器存储过程的概念1建立和执行存储过程2存储过程的管理与维护3触发器的概念4创建和应用DML触发器5触发器的管理与维护6DDL触发器7事务8第73页/共81页10.8 事务事务 事务是作为单个逻辑工作单元执行的一系列操作。这一系列操作或者都被执行，或者都不被执行。第74页/共81页10.8 事务事务 事务作为一个逻辑工作单元有4个属性，称为ACID（原子性、一致性、隔离性和持久性）属性。 （1）原子性。事务必须是原子工作单元，对于其数据修改，要么全都执行，要么全都不执行。 （2）一致性。事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构都必须是正确的。 （3）隔离性。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。保证事务查看数据时数据所处的状态，只能是一并发事务修改它之前的状态或者是另一事务修改它之后的状态，而不能查看中间状态的数据。 （4）持久性。事务完成之后对系统的影响是永久的。第75页/共81页10.8 事务事务 SQL Server有以下3种事务模式。 （1）自动提交事务。这是SQL Server的默认模式。每个单独的SQL语句都是一个事务，并在其完成后提交。不必指定任何语句控制事务。 （2）显式事务。每个事务均以BEGIN TRANSACTION语句显式开始，以COMMIT或ROLLBACK语句显式结束。 （3）隐性事务。通过API函数或Transact-SQL的SET IMPLICIT_TRANSACTION ON语句，将隐性事务模式设置为打开。这样在前一个事务结束时新事务隐式启动，但每个事务仍以COMMIT或ROLLBACK语句显式结束。第76页/共81页10.8 事务事务 事务组织结构的一般形式如下。 （1）定义一个事务的开始：BEGIN TRANSACTION。 （2）提交一个事务：COMMIT TRANSACTION。 （3）回滚事务：ROLLBACK TRANSACTION。 BEGIN TRANSACTION代表一个事务的开始点，事务执行到COMMIT TRANSACTION提交语句后结束事务，完成对数据库所做的永久改动。如果遇上错误用ROLLBACK TRANSACTION语句可以撤消所有改动。第77页/共81页本章小结本章小结 1.存储过程是一种数据库对象，是存储在服务器上的一组预定义的SQL语句集合。创建存储过程并将编译好的版本存储在高速缓存中，可以加快程序的执行效率。存储过程可以有输入、输出参数，可以返回结果集以及返回值。通过本章的学习，应掌握各种存储过程的创建、执行、修改和删除方法。第78页/共81页本章小结本章小结 2.触发器是一种特殊的存储过程，当有INSERT、UPDATE和DELETE操作影响到触发器所保护的数据时，触发器就会自动触发执行。触发器主要用于加强业务规则和数据完整性，能实现比CHECK约束更复杂的检查，一般在使用触发器之前应优先考虑使用约束，只在必要的时候才使用触发器。 第79页/共81页习题习题P2161、2、3、5、6、7第80页/共81页感谢您的观看！第81页/共81页