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

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

《第三章 SqlServer存储过程与触发器.docx》由会员分享，可在线阅读，更多相关《第三章 SqlServer存储过程与触发器.docx（20页珍藏版）》请在冰点文库上搜索。

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

第三章SQLServer存储过程与触发器

一、存储过程

1.什么是存储过程

●存储过程是存储在数据库中并预编译的子程序。

●存储过程可以包含SQL语句与T-SQL。

●存储过程类似于JS语言中的函数。

●用来执行管理任务或应用复杂的业务规则。

●存储过程可以带参数，也可以返回结果。

2.存储过程的优点

●基于重用思想

●执行速度更快

●允许模块化程序设计

●提高系统安全性

●减少网络流通量

3.存储过程分类

●系统存储过程

⏹由系统定义，存放在master数据库中

⏹类似C语言中的系统函数

⏹系统存储过程的名称都以“sp_”开头或”xp_”开头

●用户自定义存储过程

⏹由用户在自己的数据库中创建的存储过程

⏹类似C语言中的用户自定义函数

4.常用系统存储过程

系统存储过程

说明

sp_databases

列出服务器上的所有数据库。

sp_helpdb

报告有关指定数据库或所有数据库的信息

sp_renamedb

更改数据库的名称

sp_tables

返回当前环境下可查询的对象的列表

sp_columns

返回某个表列的信息

sp_help

查看某个表的所有信息

sp_helpconstraint

查看某个表的约束

sp_helpindex

查看某个表的索引

sp_stored_procedures

列出当前环境中的所有存储过程。

sp_password

添加或修改登录帐户的密码。

sp_helptext

显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。

--系统存储过程示例

EXECsp_databases--列出当前系统中的数据库

--打开数据库

USEkpitDB

GO

EXECsp_tables--当前数据库中查询的对象的列表

EXECsp_columnst_student--返回t_student表中列的信息

EXECsp_helpt_student--查看表t_student的信息

EXECsp_helpconstraintt_student--查看表t_student的约束

EXECsp_helpindext_student--查看表t_student的索引

EXECsp_helptext'sys.databases'--查看系统数据库视图的语句文本

EXECsp_stored_procedures--查看当前数据库中的存储过程

5.扩展存储过程xp_cmdshell

●可以执行DOS命令

●以文本行方式返回任何输出

●调用语法：

⏹EXECxp_cmdshellDOS命令[NO_OUTPUT]

/*

sp_configure'showadvancedoptions',1;

GO

RECONFIGURE;

GO

sp_configure'xp_cmdshell',1;

GO

RECONFIGURE;

GO

*/

--执行dos命令

EXECxp_cmdshelldir

GO

6.创建并调用自定义的存储过程

●定义存储过程的语法

CREATEPROC[EDURE]存储过程名

@参数1数据类型=默认值OUTPUT,

……,

@参数n数据类型=默认值OUTPUT

AS

SQL语句

GO

⏹和C语言的函数一样，参数可选

⏹参数分为输入参数、输出参数

⏹输入参数允许有默认值

⏹注：

存储过程一般用p_开头

●调用存储过程：

EXEC[UTE]过程名[参数]

●不带参数的存储过程

-------------------------------------------------

--创建不带参数的存储过程（查询所有的学生信息）

-------------------------------------------------

ifexists（select*fromsysobjectswherename='p_student_list'）

dropprocp_student_list

Go

createprocp_student_list

as

begin

select*fromt_student

end

Go

--调用不带参数的存储过程

execp_student_list

Go

●带输入参数的存储过程

-------------------------------------------------

--创建带入参数的存储过程（增加某一学生信息）

-------------------------------------------------

ifexists（select*fromsysobjectswherename='p_student_add'）

dropprocp_student_add

Go

createprocp_student_add

@stuNovarchar（5）,

@stuNamevarchar（20）,

@stuSexnvarchar

（1）='女',--默认值

@stuAgeint,

@stuAddressvarchar（100）

as

begin

insertintot_student（STUNO,STUNAME,STUSEX,STUAGE,STUADDRESS）

values（@stuNo,@stuName,@stuSex,@stuAge,@stuAddress）

end

Go

--调用带入参数的存储过程

execp_student_add'S0007','Tom','男',23,'北京'

Go

●带输出参数的存储过程

⏹如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出参数（OUTPUT）

--------------------------------------------

--创建带输出参数的存储过程（汇总学生数）

--------------------------------------------

ifexists（select*fromsysobjectswherename='p_student_count'）

dropprocp_student_count

Go

createprocp_student_count

@countintoutput--输入输出参数

as

begin

select@count=count（*）fromt_student

end

Go

--调用带输出参数的存储过程

declare@countint

set@count=0

execp_student_count@countoutput

select@count记录数

Go

●带return返回值的存储过程

⏹在存储过程中，Return关键字的作用

◆终止存储过程的执行

◆返回数值（0值为正确执行）

---------------------------------------------------

--创建带return返回值的存储过程（更新某一学生的姓名）

---------------------------------------------------

ifexists（select*fromsysobjectswherename='p_student_update'）

dropprocp_student_update

Go

createprocp_student_update

@stuNovarchar（5）,

@stuNamevarchar（20）

as

declare@iTempint

begin

select@iTemp=count（*）fromt_studentwhereSTUNO=@stuNo

if@iTemp=0

begin

raiserror（'查无此人',16,1）--抛出自定义的错误给系统

return-2--查无此人

end

updatet_studentsetSTUNAME=@stuNamewhereSTUNO=@stuNo

if@@ERROR!

=0

begin

return-1--系统错误

end

end

Go

--调用带输出参数的存储过程

declare@returnint

exec@return=p_student_update'S0007','Jarry'

select@return结果值

Go

7.存储过程中的错误处理

●可以使用PRINT语句显示错误信息，但这些信息是临时的，只能显示给用户

●RAISERROR显示用户定义的错误信息时

⏹可指定严重级别，

⏹设置系统变量@@ERROR

⏹记录所发生的错误等

⏹msg_str：

用户定义的出错信息，最长255个字符

⏹severity：

定义严重性级别。

用户可使用的级别为0–18级

⏹state：

表示错误的状态，1至127之间的值，默认为1

raiserror（'查无此人',16,1）--抛出自定义的错误给系统

8.存储过程的其他操作

●查看存储过程信息

EXECUTE sp_helptext Procedure_Name1  

EXECUTE sp_depends Procedure_Name1 

EXECUTE sp_help Procedure_Name1 

●删除存储过程

DROP PROCEDURE Procedure_Name1

DROP PROCEDURE Procedure_Name1,Procedure_Name2

●修改存储过程

ALTER PROC Procedure_Name1  

@id varchar（10）,  @sex varchar（10）  

AS  SELECT * FROM Table_Name  WHERE ID=@id and SEX=@sex

二、触发器

1.什么是触发器

●触发器是在某些事件发生时自动执行的存储过程

●触发器通常用于强制业务规则。

●触发器是一种高级约束，可以定义比用CHECK约束更为复杂的约束

●不能直接调用

●是一个事务（可回滚）

●注：

很多软件开发公司均不建议使用触发器

2.触发器的类型

●DML触发器

⏹INSERT触发器

⏹UPDATE触发器

⏹DELETE触发器

●DDL触发器

●Atfer触发器与Insteadof替代触发器

3.触发器临时表

●触发器触发时：

⏹系统自动在内存中创建deleted表或inserted表

⏹只读，不允许修改；触发器执行完成后，自动删除

●inserted表

⏹临时保存了插入或更新后的记录行

⏹可以从inserted表中检查插入的数据是否满足业务需求

⏹如果不满足，则向用户报告错误消息，并回滚插入操作

●deleted表

⏹临时保存了删除或更新前的记录行

⏹可以从deleted表中检查被删除的数据是否满足业务需求

⏹如果不满足，则向用户报告错误消息，并回滚插入操作

●触发器临时表的功能

修改操作

inserted表

deleted表

增加（INSERT）记录

存放新增的记录

------

删除（DELETE）记录

-----

存放被删除的记录

修改（UPDATE）记录

存放更新后的记录

存放更新前的记录

4.创建触发器的语法

●WITHENCRYPTION表示加密触发器定义的SQL文本

●DELETE,INSERT,UPDATE指定触发器的类型

5.DML触发器

-------------------------------------------

--DML触发器示例

-------------------------------------------

--增、改、册的触发器

ifexists（select*fromsysobjectswherename='tr_student_up'）

droptriggertr_student_up

Go

createtriggertr_student_up

ont_student

withencryption--加密码触发器的内容

forinsert,update,delete

as

begin

print'报警了！

！

！

有人正在更新学生信息表'

end

Go

--测试tr_student_up触发器

updatedbo.t_studentsetSTUSEX='女'whereSTUNO='S0002'

Go

●INSERT触发器

--insert触发器

ifexists（select*fromsysobjectswherename='tr_daily_add'）

droptriggertr_daily_add

Go

createtriggertr_daily_add

ondbo.t_daily_record

--withencryption--加密码触发器的内容

forinsert

as

begin

declare@stunovarchar（5）

declare@numint

--从inserted表中获取插入的数据

select@stuno=STUNO,@num=NUMfrominserted

/*

检查学生纪律分汇总表是否存在当前学生的记录,

如果存在则修改，否则插入该学生的汇总记录

*/

ifexists（select*fromt_daily_totalwhereSTUNO=@stuno）

begin

updatet_daily_totalsetTOTALNUM=TOTALNUM+@numwhereSTUNO=@stuno

end

else

begin

insertintot_daily_total（STUNO,TOTALNUM）values（@stuno,@num）

end

print'insert触发器...'+char（10）

end

Go

--测试tr_daily_add触发器

insertintodbo.t_daily_record（STUNO,NUM,OPTime）values（'S0001',-2,getdate（））

select*fromt_daily_record

select*fromt_daily_total

Go

●DELETE触发器

--delete触发器

ifexists（select*fromsysobjectswherename='tr_daily_del'）

droptriggertr_daily_del

Go

createtriggertr_daily_del

ondbo.t_daily_record

--withencryption--加密码触发器的内容

fordelete

as

begin

declare@stunovarchar（5）

declare@numint

--从deleted表中获取删除的数据

select@stuno=STUNO,@num=NUMfromdeleted

--更新学生纪律分汇总表

updatet_daily_totalsetTOTALNUM=TOTALNUM-@numwhereSTUNO=@stuno

print'delete触发器...'+char（10）

end

Go

--测试tr_daily_del触发器

deletefromdbo.t_daily_recordwhereDRID=3

select*fromt_daily_record

select*fromt_daily_total

Go

●UPDATE触发器

--update触发器

ifexists（select*fromsysobjectswherename='tr_daily_update'）

droptriggertr_daily_update

Go

createtriggertr_daily_update

ondbo.t_daily_record

--withencryption--加密码触发器的内容

forupdate

as

begin

declare@stunovarchar（5）

declare@beforenumint--更新前的数据

declare@afternumint--更新后的数据

--从deleted表中获取更新前的数据

select@stuno=STUNO,@beforenum=NUMfromdeleted

--从inserted表中获取更新后的数据

select@afternum=NUMfrominserted

--更新学生纪律分汇总表

updatet_daily_totalsetTOTALNUM=TOTALNUM+（@afternum-@beforenum）whereSTUNO=@stuno

print'update触发器...'+char（10）

end

Go

--测试tr_daily_del触发器

updatedbo.t_daily_recordsetNUM=-1whereDRID=1

select*fromt_daily_record

select*fromt_daily_total

Go

●列级UPDATE触发器

--列级UPDATE触发器

ifexists（select*fromsysobjectswherename='tr_student_up'）

droptriggertr_student_up

Go

createtriggertr_student_up

ont_student

--withencryption--加密码触发器的内容

forupdate

as

begin

ifupdate（STUNAME）

begin

print'注意了！

！

！

有人正在修改学生姓名'

end

end

Go

--测试tr_student_up触发器

updatedbo.t_studentsetSTUNAME='张华'whereSTUNO='S0001'

select*fromt_student

Go

6.After触发器

●执行顺序

--------------------------------

--After触发器示例

--此示例中，tr_daily_after_delete触发器在tr_daily_delete触发器之后执行

--------------------------------

ifexists（select*fromsysobjectswherename='tr_daily_after_delete'）

droptriggertr_daily_after_delete

Go

createtriggertr_daily_after_delete

ondbo.t_daily_record

--withencryption--加密码触发器的内容

afterdelete

as

begin

print'After触发器...'+char（10）

end

Go

--测试tr_daily_after_delete触发器

deletefromdbo.t_daily_recordwhereDRID=6

select*fromt_daily_record

select*fromt_daily_total

Go

7.Insteadof替代触发器

●执行顺序

--------------------------------

--Insteadof替代触发器示例

--此示例中，tr_daily_after_delete触发器替代了tr_daily_delete触发器执行

--------------------------------

ifexists（select*fromsysobjectswherename='tr_daily_insteadof_delete'）

droptriggertr_daily_insteadof_delete

Go

createtriggertr_daily_insteadof_delete

ondbo.t_daily_record

--withencryption--加密码触发器的内容

insteadofdelete

as

begin

print'AInsteadof替代触发器...'+char（10）

end

Go

--测试tr_daily_after_delete触发器

deletefromdbo.t_daily_recordwhereDRID=8

select*fromt_daily_record

select*fromt_daily_total

Go

8.DDL触发器

●数据定义语言（DDL）语句也可被监控

●只可作为After触发器，不能InsteadOf触发器。

●在DDL触发器中是没有创建Inserted&Deleted过程的。

●可被控制的触发操作包括:

create_index、alter_index、drop_index

create_procedure、alter_procedure、drop_procedure

create_table、alter_table、drop_table

create_trigger、alter_trigger、drop_trigger

create_view、alter_view、drop_view

--------------------------------

--DDL触发器示例

--------------------------------

ifexists（select*fromsys.triggerswherename='tr_table_drop'）

droptriggertr_table_dropondatabase

Go

createtriggertr_table_drop

ondatabas