---恢复内容开始---
2017.11.21
存储过程和触发器
1.存储过程的作用
当SQL server 创建应用程序时,可以用两种方法存储和执行程序:
1).将程序存储在本地,然后创建SQL server 发送命令并处理结果的应用程序
2).将程序以存储过程的形式存储在SQL Server服务器中,然后创建执行存储过程并处理结果的应用程序.SQL server推荐使用这种
2.存储过程(Store Procedure)是一组为了完成特定功能的T-SQL语句集,经过编译后存储在SQL server服务器端数据库中.利用存储过程可以加以加速SQL语句执行
3.优点:
*1.存储过程具有安全性和所有权链接,可以执行所有的权限管理
*2.存储过程已经在服务器上注册,这样可以提高T-sql语句的执行效率
*3.存储过程可以提高应用程序的安全性.可以防止SQL嵌入式攻击
*4.存储过程可以发发减少网络通信流量
4.三种基本类型的存储过程:
1.系统存储过程
2.用户自定义存储过程
3.扩展存储过程
5.存储过程的分类:
1.系统存储过程
系统存储过程是执行数据库基础管理工作的一类特殊存储过程,存储放在SQL server 2008 的源数据库里,通常以"sp_"为前缀名
2.用户自定义存储过程:用户自定义存储过程是指由用户定义的存储过程
T-SQL存储过程是指一组T-sql语句,能够接受和返回用户提供的参数
3.扩展存储过程:
扩展存储过程是在SQL server2008实例的地址空间中动态加载和运行的外部例程
学习了哪些存储过程:
1.sp_helpdb:查看所有数据库名称及大小
2.sp_help:显示表的信息
3.sp_renamedb'old_dbname', 'new_dbname':重命名数据库用的SQL
4.select @@servername:查询本地服务器的名称
5.sp_helpindex表名:查看索引信息
6.sp_rename 'old_viewname','new_viewname':重命名视图
6.2.2 用T-SQL语言创建存储过程
SQL server2008 中可以用create procedure语句来定义存储过程,语法如下:
create {proc|procedure}存储过程名称:[;数值]
[{@参数 数据类型}] [output]
as {<SQL语句>[;][...n]}
1.创建简单的存储过程:
例1.创建一个简单的存储过程Proc_kc,它不包括任何参数,只包含一个简单的select语句,该存储过程用于查询所有课程信息
use students
go
create procedure Proc_kc
as
select * from course
例2.创建一个带有输入参数的存储过程proc_s_g_c.查询指课程的学生成绩信息,默认情况下返回'1'号课程的成绩:
use students
go
create procedure Proc_s_g_c
@cno char(8)='1'
as
select sno,cname,grade from course,sc
where course.cno=sc.cno and course.cno=@cno
例3.创建一个带参数和输出参数的存储过程Proc_avg,用于返回学生所学课程的平均分
use students
go
create proc proc_avg
@sno char(10)='95001',
@aver int output
as
select @aver=avg(grade)from sc
where sno=@sno group by sno
2.执行存储过程:
存储过程创建完成后,可以用exec语句来执行存储过程,语法:
[(exec | execute)]
{[@返回状态= ]
{@存储过程变量}
}
1.执行简单的存储过程
执行上述创建的简单的存储过程Proc_kc,用于显示所有课程的信息
use student
go
exec proc_kc
2.执行一个带参数的的存储过程:执行存储过程Proc_s_g_c,用于显示某个特定的课程信息
use students
go
exec proc proc_s_g_c @sno=1
3.执行一个带输入输出参数的存储过程proc_avg,用于返回学生课程的平均分
declare @sno int,@aver int
set @sno=95006
exec proc_avg @sno,@aver output
print'平均成绩为:'+cast(@aver as char(3))
3.用T-SQL语句修改存储过程
1.修改存储过程
SQL server2008中可以用alter procedure语句以命令方式实现,其基本语法格式如下:
alter{proc|procedure}存储过程名称[;数值]
[{@参数 数据类型}][output]
as{<SQL语句>}[;][...n]
例:修改存储过程proc_avg,用于输出某门课程平均分:
use students
go
alter proc proc_avg
@cno int=1,
@aver int output
as
select @aver=AVG(grade)from sc
where cno=@cno group by sno
4.用T-SQL语句删除存储过程
语法:
drop procedure {procedure}[1...n]
删除上述创建的存储过程Proc_kc:
drop procedure proc_kc
触发器:
触发器实际上就是一种特殊类型的存储过程,它是在执行某些t-sql语句时自动执行的一种存储过程:
触发器的类型:
(1)DML触发器(数据库服务器中发生数据操作语言insert,update,dalete事件时执行)
在sql 2008中为每个DML触发器都定义了两个表,一个是插入insert表,一个是删除delete表.
(2)DDL触发器(响应数据定义语言create,alter,drop语句而触发)
将DML触发器分为insert delete update3类
在SQL server2008里为每个DML触发器都定义了两个特殊的表,一个是插入instead,一个是删除delete表.这两个表是建在数据库服务器的内存中的,是由系统管理的逻辑
表(虚拟表),而不是真正存储在数据库中的物理表.对于这两个表,用户只有修改的权限,没有读取的权限
创建触发器:
1.创建DML触发器,语法:
create trigger 触发器名
on{表名|视图名}
{{for|after|instead of}{[dlete][,][insert][,][uptate]}}
as
SQL语句[..n.]}}
例1:为Student表创建一个update触发器,当更新了某位学生的学号时,就激活了触发器级联更新sc表中
相关成绩记录中的学号信息:
create trigger tri_upd_student
on student for update
as
declare @oldsid char(10),@newsid char(10)
select @oldsid=Deleted.sno,@newsid=inserted.sno
from inserted,deleted where
inserted.sname=deleted.sname
update sc set sno=@newsid where sno=@oldsid
查看结果:
update student set sno=95001 where sno=85001
2.
例2:course表和sc表是主从表之间的关系,当对course表进行删除时操作时,首先应判断sc表中是否有相关记录,
如果有,则禁止删除.
create trigger tri_del_course
on course
instead of delete
as
begin
if exists(select cno from sc where cno in (select cno from deleted))
print 'sc表中有相关记录,不能删除'
else
delete from course where cno in(select cno from deleted)
end
3.管理触发器:
1.查看触发器:
可以使用系统存储过程SP_HELP,SP_HELPTEXT等查看触发器的相关信息,具体语法格式用途如下:
sp_help:用于查询触发器的一般信息,如触发器名称,属性,类型,创建时间
sp_help'触发器名称'
SP_HELPTEXT:用于查看触发器的正文信息"
SP_HELPTEXT '触发器名称'
2.删除触发器:
删除触发器可以用drop trigger语句.可以同时删除一个或者多个触发器,语法:
drop trigger 触发器名称[...n]
on {database|all server}
[;]
删除创建的触发器tri_del_student
drop trigger tri_del_student
3.禁止和启用触发器
语法:
alter table table_name
{disable|enable} trigger
{all|trigger_name[,...n]}
禁止student表上所建的触发器 tri_up_student
alter table student
disable trigger tri_up_student
练习;
--任务1
-- 创建一个简单的存储过程Proc_kc，它不包含任何参数，只包含一条简单Select查询语句。该存储过程用于查询所有课程的信息。
use students
go
create procedure Proc_kc
as
select * from course
--任务2
--创建一个带有输入参数的存储过程Proc_s_g_c，查询指定课程的学生成绩信息，默认情况下返回“2”号课程的成绩。
use students
go
create procedure Proc_s_g_c
@cno char(8)='2'
as
select sno,cname,grade from course,sc
where course.cno=sc.cno and course.cno=@cno
--任务3
-- 创建一个带有输入参数和输出参数的存储过程PROC_AVG，用于返回95001号学生所学课程的平均分。
use students
go
create proc proc_avg
@sno char(10)='95001',
@aver int output
as
select @aver=avg(grade)from sc
where sno=@sno group by sno
--任务4
-- 执行任务1创建的简单的存储过程Proc_kc，用于显示所有课程的信息。
use students
go
exec Proc_kc
--任务5
-- 执行任务3创建的带有输入参数和输出参数的存储过程PROC_AVG，用于返回95006号学生所学课程的平均分。
declare @sno int,@aver int
set @sno=95006
exec proc_avg @sno,@aver output
print'平均成绩为:'+cast(@aver as char(3))
--任务6
-- 修改任务3创建的简单的存储过程Proc_AVG，用于输出某门课程的平均分。
use students
go
alter proc proc_avg
@cno int=1,
@aver int output
as
select @aver=AVG(grade)from sc
where cno=@cno group by sno
--任务7
-- 删除任务1创建的简单的存储过程Proc_kc。
drop procedure Proc_kc
--任务8为student表创建一个UPDATE触发器，当更新了某位学生的学号时，就激活触发器级联更新sc表中相关成绩记录中的学号信息。
create trigger tri_upd_student
on student for update
as
declare @oldsid char(10),@newsid char(10)
select @oldsid=Deleted.sno,@newsid=inserted.sno
from inserted,deleted where
inserted.sname=deleted.sname
update sc set sno=@newsid where sno=@oldsid
--查看结果:
update student set sno=85001 where sno=95001
--任务9创建Instead of 触发器student表和sc表是主从表之间的关系，当对student表进行删除操作时，首先应判断SC表中是否有相关记录，如果有，则禁止删除。
create trigger tri_del_course
on course
instead of delete
as
begin
if exists(select cno from sc where cno in (select cno from deleted))
print 'sc表中有相关记录,不能删除'
else
delete from course where cno in(select cno from deleted)
end
--任务10查看触发器的一般信息
sp_help tri_del_course
--任务11查看触发器的正文信息。
sp_helptext tri_del_course
--任务12禁止student表上所建的触发器。
alter table student
disable trigger tri_upd_student
--任务13删除创建的任务8触发器。
drop trigger tri_upd_student