数据库原理——存储过程、触发器

存储过程

存储过程 是一组命名了的SQL语句集合

• 该集合编译后存储在数据库中，可根据实际情况重新编译
• 存储过程在服务器端运行，也可在客户端远程调用

使用存储过程的优点：

• 将业务操作封装
• 便于事务管理
• 实现一定程度的安全性保护
• 适合统计和查询操作
• 减少网络通信

创建存储过程

• 语法：

create procedure <procedureName>
	[(<@parameterName dataType [= defaultValue] [OUTPUT],...)]
as
	<sql - Statements>

如果存储过程 的输出参数 取集合值，则该输出参数不在存储过程的参数中定义，而是在存储过程中定义一个临时表来存储该集合值

• 临时表的表名前加一个#符号：如:#myTemp
• 在存储过程的尾部，使用下面语句，使集合结果返回给调用者：
  SELECT * FROM #myTemp
• 存储过程结束后，临时表会自动删除

注意：

• 用户定义的存储过程只能在当前数据库中创建
• 一个存储过程不能超过128MB

【例1】输入某个学生学号，统计该同学的平均分。

create procedure proOne(@sNo char(7)) -- 默认参数为输入参数
as
	select a.studentNo, studentName, avg(score)
	from Student a, Score b
	where a.studentNo = b.studentNo
		and a.studentNo = @sNo
	group by a.studentNo, studentName
-- 调用存储过程时，不需要 给参数加括号
execute proOne '1017001'

【例2】输入某学院名称，统计该学院每个班级 同学的选课信息， 返回 班级编号，班级名称，课程名称，课程选课人数，课程平均分。

• 思路：

1. 输入参数：学院名称 @institute
2. 输出参数：一个集合值，包含了所有班级的班级编号，班级名称，课程名称，课程选课人数，课程平均分 (对于集合值的输出参数，在存储过程中定义一个临时表来存储该集合）
3. 一个学院肯定不止一个班级，一个班级里面不止一个学生，所以需要两层游标来统计

• 存储过程：

create procedure proInstitute(@institute varchar(30))
as
begin
	declare @className varchar(30), @courseName varchar(30),@classNo char(6), @count int, @avg numeric(5,1)
	/*创建一个临时表，存放班级名称，班级编号，
	课程名称，课程选课人数，课程平均分*/
	create table #mytemp(
		classNo		char(6),
		className	varchar(30),
		courseName	varchar(30),
		classCount	int,
		classAvg	numeric(5,1)
	)
	
	-- 定义游标，根据输入的@institute 查找出班级编号和班级名称
	declare curClass cursor for
		select classNo, className
		from class
		where institue = @institue
	open curClass
	fetch curClass into @classNo, @className
	while( @@TFETCH_STATUS = 0)
	begin
		-- 定义新游标，查找@classNo 对应的课程名称，选课人数，平均分
		declare curCourse cursor for
			select courseName, count(*), avg(score)
			from Student a, Score b, Course c
			where a.studentNo = b.studentNo
				and b.courseNo = c.courseNo
				and classNo = @classNo
			group by courseName
		open curCourse
		fetch curCourse into @courseName,@count,@avg
		while(@@FETCH_STATUS = 0)
			begin
				insert into #mytemp values(@classNo,@className,@courseName,@count,@avg)
				fetch curCourse into @curseName, @count, @avg
			end
		close curCourse
		deallocate curCourse
		-- 获取班级游标集中的下一个班级信息
		fetch curClass into @classNo, @className
	end
	close curClass
	deallocate curClass
	-- 显示临时表中的值
	select * from #mytemp
end

执行存储过程

• EXECUTE的参数要完全对应 PROCEDURE 的参数列表，例如

--调用带输出参数的存储过程，并显示
declare @avg numeric(5,1)
execute proAvg '1017001',@avg OUTPUT
select @avg

修改和删除存储过程

• 修改存储过程（与创建时类似）

alter procedure <procedureName>
	[(<@parameterName dataType [= defaultValue] [OUTPUT],...)]
as
	<sql - Statements>

• 删除存储过程
  drop procedure <procedureName>

触发器（trigger）

• 触发器是用户定义在关系表上的一类 事件驱动 的存储过程，由服务器 自动激活
• 触发器 是一种特殊的存储过程，不管什么原因造成的数据变化都能自动响应，对于每条SQL语句，触发器仅执行一次。
• 有两个特殊的表用在触发器语句中，不同的数据库名称不同：
  1. SQL server：deleted表 和 inserted表
  2. Oracle：old表 和 new表
• 触发器类型：插入，删除，修改
  插入、删除、修改可组合为一种触发器（查询不会产生触发动作）

SQLServer触发器

• deleted表 和 inserted表 和 触发器 作用的基本表 结构完全一致
• 当针对触发器作用的基本表的 SQL语句 开始执行时，自动产生 deleted表和inserted表
• 当 SQL 语句执行完，deleted表 和 inserted表也将被删除
• deleted表：存储当delete 和 update语句执行所影响的行的拷贝，即，影响之前将该行存入deleted表中
• inserted表：同理，该表存储当insert 和 update语句执行所影响的行的拷贝，即，影响之后，改行存入inserted表中

创建触发器

create trigger <triggerName>
on <tableName>
for {insert | update| delete}
as
	<sql-staement>

• 如果该触发器执行失败，则激活触发器的事件不会生效，即产生回滚操作
• 注意：
  1. 原则上不限制一张基本表上创建触发器的数量
  2. 由于触发器是自动执行的，所有创建过多触发器必然加大系统开销
  3. 触发器常常用来维护复杂的完整性约束，不用于业务逻辑
  4. 凡可以使用一般约束限制的，就不要使用触发器，如：性别只能取男女，可以用检查约束check实现

修改和删除触发器

• 修改：（类似创建）

alter trigger <triggerName>
on <tableName>
for {insert | update| delete}
as
	<sql-staement>

• 删除：
  drop trigger <triggerName>

【例3】创建触发器，如果对学生表进行更新操作，则自动修改班级表中的人数，假设一次仅允许更新一个学生记录，否则当作违反约束规则。

/*创建插入类型的触发器*/
create trigger classInsert
on Student
for insert
as
	begin
		-- 定义@classNo 接收被插入的学生的班级号
		declare @classNo char(6)
		-- 判断inserted表中记录的条数 >1 则回滚
		if (select count(*) from inserted)>1
			rollback
		else
			begin
				-- 从inserted表中获取该学生的班级号
				select @classNo = classNo
				from inserted
				-- 修改班级号对应的班级的人数
				update Class
				set classNum = classNum +1  -- 将人数加 1
				where classNo = @classNo
			end
	end

• 在触发器中要合理使用inserted表 和 deleted表
• 删除类型触发器和修改过程触发器略

最后

以上就是迷人橘子为你收集整理的数据库原理——存储过程、触发器的全部内容，希望文章能够帮你解决数据库原理——存储过程、触发器所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。