第三章 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