第三章 SqlServer存储过程与触发器.pdf

《第三章 SqlServer存储过程与触发器.pdf》由会员分享，可在线阅读，更多相关《第三章 SqlServer存储过程与触发器.pdf（18页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第三章第三章 SQLServer SQLServer 存储过程与触发器存储过程与触发器一、一、存储过程存储过程1.1.什么是存储过程什么是存储过程 存储过程是存储在数据库中并预编译的子程序。存储过程可以包含 SQL 语句与 T-SQL。存储过程类似于 JS 语言中的函数。用来执行管理任务或应用复杂的业务规则。存储过程可以带参数，也可以返回结果。2.2.存储过程的优点存储过程的优点 基于重用思想 执行速度更快 允许模块化程序设计 提高系统安全性 减少网络流通量3.3.存储过程分类存储过程分类 系统存储过程 由系统定义，存放在 master 数据库中 类似 C 语言中的系统函数 系统存储过程的名称

2、都以“sp_”开头或”xp_”开头 用户自定义存储过程 由用户在自己的数据库中创建的存储过程 类似 C 语言中的用户自定义函数4.4.常用系统存储过程常用系统存储过程系统存储过程系统存储过程说明说明sp_databasessp_databasessp_helpdbsp_helpdbsp_renamedbsp_renamedbsp_tablessp_tablessp_columnssp_columnssp_helpsp_helpsp_helpconstraintsp_helpconstraintsp_helpindexsp_helpindexsp_stored_proceduressp_stor

3、ed_proceduressp_passwordsp_passwordsp_helptextsp_helptext列出服务器上的所有数据库。列出服务器上的所有数据库。报告有关指定数据库或所有数据库的信息报告有关指定数据库或所有数据库的信息更改数据库的名称更改数据库的名称返回当前环境下可查询的对象的列表返回当前环境下可查询的对象的列表返回某个表列的信息返回某个表列的信息查看某个表的所有信息查看某个表的所有信息查看某个表的约束查看某个表的约束查看某个表的索引查看某个表的索引列出当前环境中的所有存储过程。列出当前环境中的所有存储过程。添加或修改登录帐户的密码。添加或修改登录帐户的密码。显示默认值、未

4、加密的存储过程、用户定义的存储过程、显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。触发器或视图的实际文本。-系统存储过程示例EXEC sp_databases-列出当前系统中的数据库-打开数据库USE kpitDBGOEXEC sp_tables-当前数据库中查询的对象的列表数据库开发与设计课程总结主讲：计许凡EXEC sp_columns t_student-返回 t_student 表中列的信息EXEC sp_help t_student-查看表 t_student 的信息EXEC sp_helpconstraint t_student-查看表 t_studen

5、t 的约束EXEC sp_helpindex t_student-查看表 t_student 的索引EXEC sp_helptext sys.databases-查看系统数据库视图的语句文本EXEC sp_stored_procedures -查看当前数据库中的存储过程5.5.扩展存储过程扩展存储过程 xp_cmdshellxp_cmdshell 可以执行 DOS 命令 以文本行方式返回任何输出 调用语法：EXEC xp_cmdshell DOS 命令 NO_OUTPUT/*sp_configure show advanced options,1;GORECONFIGURE;GOsp_conf

6、igure xp_cmdshell,1;GORECONFIGURE;GO*/-执行 dos 命令EXEC xp_cmdshell dirGO6.6.创建并调用自定义的存储过程创建并调用自定义的存储过程 定义存储过程的语法 CREATE PROCEDURE存储过程名 参数 1数据类型=默认值 OUTPUT,参数 n数据类型=默认值 OUTPUT AS SQL 语句 GO第 3 页共 18 页2013-09-01 和 C 语言的函数一样，参数可选 参数分为输入参数、输出参数 输入参数允许有默认值 注：存储过程一般用 p_开头 调用存储过程：EXECUTE过程名 参数 不带参数的存储过程不带参数的存

7、储过程-创建不带参数的存储过程(查询所有的学生信息)-if exists(select*from sysobjects where name=p_student_list)drop proc p_student_listGocreate proc p_student_listasbeginselect*from t_studentendGo-调用不带参数的存储过程exec p_student_listGo 带输入参数的存储过程带输入参数的存储过程-创建带入参数的存储过程(增加某一学生信息)-if exists(select*from sysobjects where name=p_studen

8、t_add)drop proc p_student_addGocreate proc p_student_addstuNo varchar(5),stuName varchar(20),stuSex nvarchar(1)=女,-默认值数据库开发与设计课程总结主讲：计许凡stuAge int,stuAddress varchar(100)asbegininsert into t_student(STUNO,STUNAME,STUSEX,STUAGE,STUADDRESS)values(stuNo,stuName,stuSex,stuAge,stuAddress)endGo-调用带入参数的存储过

9、程exec p_student_add S0007,Tom,男,23,北京Go 带输出参数的存储过程带输出参数的存储过程 如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出参数（OUTPUT）-创建带输出参数的存储过程(汇总学生数)-if exists(select*from sysobjects where name=p_student_count)drop proc p_student_countGocreate proc p_student_countcount int output-输入输出参数asbeginselect count=count(*)from t_studen

10、tendGo-调用带输出参数的存储过程declare count intset count=0exec p_student_count count outputselect count 记录数Go第 5 页共 18 页2013-09-01 带带 returnreturn 返回值的存储过程返回值的存储过程 在存储过程中，Return 关键字的作用 终止存储过程的执行 返回数值（0 值为正确执行）-创建带 return 返回值的存储过程(更新某一学生的姓名)-if exists(select*from sysobjects where name=p_student_update)drop proc

11、 p_student_updateGocreate proc p_student_updatestuNo varchar(5),stuName varchar(20)asdeclare iTemp intbeginselect iTemp=count(*)from t_student where STUNO=stuNoif iTemp=0beginraiserror(查无此人,16,1)-抛出自定义的错误给系统return-2-查无此人endupdate t_student set STUNAME=stuName where STUNO=stuNoif ERROR!=0beginreturn-

12、1-系统错误endendGo-调用带输出参数的存储过程declare return intexec return=p_student_update S0007,Jarryselect return 结果值Go数据库开发与设计课程总结主讲：计许凡7.7.存储过程中的错误处理存储过程中的错误处理 可以使用 PRINT 语句显示错误信息，但这些信息是临时的，只能显示给用户 RAISERROR 显示用户定义的错误信息时 可指定严重级别，设置系统变量ERROR 记录所发生的错误等 msg_str：用户定义的出错信息，最长 255 个字符 severity：定义严重性级别。用户可使用的级别为018级 st

13、ate：表示错误的状态，1 至 127 之间的值，默认为1raiserror(查无此人,16,1)-抛出自定义的错误给系统8.8.存储过程的其他操作存储过程的其他操作 查看存储过程信息EXECUTEsp_helptextProcedure_Name1EXECUTEsp_dependsProcedure_Name1EXECUTEsp_helpProcedure_Name1 删除存储过程DROPPROCEDUREProcedure_Name1DROPPROCEDUREProcedure_Name1,Procedure_Name2 修改存储过程ALTERPROCProcedure_Name1idva

14、rchar(10),sexvarchar(10)ASSELECT*FROMTable_NameWHEREID=idandSEX=sex第 7 页共 18 页2013-09-01二、二、触发器触发器1.1.什么是触发器什么是触发器 触发器是在某些事件发生时自动执行的存储过程 触发器通常用于强制业务规则。触发器是一种高级约束，可以定义比用CHECK 约束更为复杂的约束 不能直接调用 是一个事务（可回滚）注：很多软件开发公司均不建议使用触发器2.2.触发器的类型触发器的类型 DML 触发器 INSERT 触发器 UPDATE 触发器 DELETE 触发器 DDL 触发器 Atfer 触发器与 Ins

15、tead of 替代触发器3.3.触发器临时表触发器临时表 触发器触发时：系统自动在内存中创建 deleted 表或 inserted 表 只读，不允许修改；触发器执行完成后，自动删除数据库开发与设计课程总结主讲：计许凡 inserted 表 临时保存了插入或更新后的记录行 可以从 inserted 表中检查插入的数据是否满足业务需求 如果不满足，则向用户报告错误消息，并回滚插入操作 deleted 表 临时保存了删除或更新前的记录行 可以从 deleted 表中检查被删除的数据是否满足业务需求 如果不满足，则向用户报告错误消息，并回滚插入操作 触发器临时表的功能修改操作修改操作增加(INSE

16、RT)记录删除(DELETE)记录修改(UPDATE)记录insertedinserted 表表存放新增的记录-存放更新后的记录deleteddeleted 表表-存放被删除的记录存放更新前的记录第 9 页共 18 页2013-09-014.4.创建触发器的语法创建触发器的语法 WITH ENCRYPTION 表示加密触发器定义的 SQL 文本 DELETE,INSERT,UPDATE 指定触发器的类型5.5.DMLDML 触发器触发器-DML 触发器示例-增、改、册的触发器if exists(select*from sysobjects where name=tr_student_up)dr

17、op trigger tr_student_upGocreate trigger tr_student_upon t_studentwith encryption-加密码触发器的内容for insert,update,deleteasbegin print 报警了！有人正在更新学生信息表endGo-测试 tr_student_up 触发器update dbo.t_student set STUSEX=女 where STUNO=S0002Go数据库开发与设计课程总结主讲：计许凡INSERTINSERT 触发器触发器-insert 触发器if exists(select*from sysobje

18、cts where name=tr_daily_add)drop trigger tr_daily_addGocreate trigger tr_daily_addon dbo.t_daily_record-with encryption-加密码触发器的内容for insertasbegindeclare stuno varchar(5)declare num int-从 inserted 表中获取插入的数据select stuno=STUNO,num=NUM from inserted/*检查学生纪律分汇总表是否存在当前学生的记录,如果存在则修改，否则插入该学生的汇总记录*/if exist

19、s(select*from t_daily_total where STUNO=stuno)beginupdate t_daily_total set TOTALNUM=TOTALNUM+num where STUNO=stunoendelsebegininsert into t_daily_total(STUNO,TOTALNUM)values(stuno,num)end print insert 触发器.+char(10)endGo-测试 tr_daily_add 触发器insert into dbo.t_daily_record(STUNO,NUM,OPTime)values(S0001

20、,-2,getdate()select*from t_daily_recordselect*from t_daily_totalGo第 11 页共 18 页2013-09-01 DELETEDELETE 触发器触发器-delete 触发器if exists(select*from sysobjects where name=tr_daily_del)drop trigger tr_daily_delGocreate trigger tr_daily_delon dbo.t_daily_record-with encryption-加密码触发器的内容for deleteasbegindeclar

21、e stuno varchar(5)declare num int-从 deleted 表中获取删除的数据select stuno=STUNO,num=NUM from deleted-更新学生纪律分汇总表update t_daily_total set TOTALNUM=TOTALNUM-num where STUNO=stuno print delete 触发器.+char(10)endGo-测试 tr_daily_del 触发器delete from dbo.t_daily_record where DRID=3select*from t_daily_recordselect*from

22、t_daily_totalGo UPDATEUPDATE 触发器触发器-update 触发器if exists(select*from sysobjects where name=tr_daily_update)drop trigger tr_daily_updateGocreate trigger tr_daily_updateon dbo.t_daily_record-with encryption-加密码触发器的内容for update数据库开发与设计课程总结主讲：计许凡asbegindeclare stuno varchar(5)declare beforenum int-更新前的数据

23、declare afternum int -更新后的数据-从 deleted 表中获取更新前的数据select stuno=STUNO,beforenum=NUM from deleted-从 inserted 表中获取更新后的数据select afternum=NUM from inserted-更新学生纪律分汇总表update t_daily_total set TOTALNUM=TOTALNUM+(afternum-beforenum)whereSTUNO=stuno print update 触发器.+char(10)endGo-测试 tr_daily_del 触发器update db

24、o.t_daily_record set NUM=-1 where DRID=1select*from t_daily_recordselect*from t_daily_totalGo列级列级 UPDATEUPDATE触发器触发器-列级 UPDATE 触发器if exists(select*from sysobjects where name=tr_student_up)drop trigger tr_student_upGocreate trigger tr_student_upon t_student-with encryption-加密码触发器的内容for updateasbegini

25、f update(STUNAME)beginprint 注意了！有人正在修改学生姓名第 13 页共 18 页2013-09-01endendGo-测试 tr_student_up 触发器update dbo.t_student set STUNAME=张华 where STUNO=S0001select*from t_studentGo6.6.AfterAfter 触发器触发器执行顺序-After 触发器示例-此示例中，tr_daily_after_delete触发器在 tr_daily_delete 触发器之后执行-if exists(select*from sysobjects where

26、 name=tr_daily_after_delete)drop trigger tr_daily_after_deleteGocreate trigger tr_daily_after_deleteon dbo.t_daily_record-with encryption-加密码触发器的内容数据库开发与设计课程总结主讲：计许凡after deleteasbegin print After 触发器.+char(10)endGo-测试 tr_daily_after_delete触发器delete from dbo.t_daily_record where DRID=6select*from t_

27、daily_recordselect*from t_daily_totalGo7.7.Instead ofInstead of 替代触发器替代触发器执行顺序-Instead of 替代触发器示例-此示例中，tr_daily_after_delete触发器替代了 tr_daily_delete 触发器执行第 15 页共 18 页2013-09-01-if exists(select*from sysobjects where name=tr_daily_insteadof_delete)drop trigger tr_daily_insteadof_deleteGocreate trigger

28、tr_daily_insteadof_deleteon dbo.t_daily_record-with encryption-加密码触发器的内容instead of deleteasbegin print AInstead of替代触发器.+char(10)endGo-测试 tr_daily_after_delete触发器delete from dbo.t_daily_record where DRID=8select*from t_daily_recordselect*from t_daily_totalGo8.8.DDLDDL 触发器触发器 数据定义语言(DDL)语句也可被监控 只可作为

29、After 触发器，不能 Instead Of 触发器。在 DDL 触发器中是没有创建 Inserted&Deleted 过程的。可被控制的触发操作包括:create_index、alter_index、drop_indexcreate_procedure、alter_procedure、drop_procedurecreate_table、alter_table、drop_tablecreate_trigger、alter_trigger、drop_triggercreate_view、alter_view、drop_view数据库开发与设计课程总结主讲：计许凡-DDL 触发器示例-if e

30、xists(select*from sys.triggers where name=tr_table_drop)drop trigger tr_table_drop on databaseGocreate trigger tr_table_dropon database for drop_tableasbegin print Sorry!不允许删除表 rollback-回滚endGo-测试 tr_table_drop 触发器drop table t_student_score19.9.触发器的其他操作触发器的其他操作 查看数据库中已有触发器select*from sysobjects where xtype=TR-查看单个触发器exec sp_helptext 触发器名 删除触发器drop trigger trigger_name 修改触发器alter trigger trigger_nameon table_name view_namefor After Instead of insert,update,delete第 17 页共 18 页2013-09-01Assql_statement