Sql 复习3之存储管理
一、事务管理
单个工作单元称为事务，我们将若干条sql命令组合在一起，形成存储过程、触发器等，利用存储过程和触发器可以进行事务管理。
二、编程基础介绍
主要有：函数、程序设计语句等。
程序设计语句：
1、begin ......end 中间插入若干条sql语句，我们将其视为一个单元。
2、跳转语句（goto语句）：
goto语句可以使程序跳到带有标识符的指定位置继续执行。
3、条件分支语句（if --- else）：
--利用条件分支语句和跳转语句求出1到5之和并输出
declare @sum int,@count int
select @sum=0,@count=1
label_1:
if @count<=5
begin
select @sum = @sum + @count
select @count = @count + 1
goto label_1
end
else
select @count,@sum
4、循环语句（while ..... continue .....break）
-- 求出1到5之和并输出
declare @sum int, @count int
select @sum=0, @count=1
while (@count<=5)
begin
select @sum = @sum + @count
select @count += 1
end
select @count,@sum
5、打印输出语句（print 语句）
print 字符串|@局部变量|@@全局变量
declare @sum int, @count int
select @sum = 0, @count = 1
while(@count <= 5)
begin
select @sum += @count
select @count += 1
end
print '1到5的总和是：'
print @sum
6、终止语句（return 语句）
return 无条件终止
7、注释语句
-- 单条语句的注释
/* */ 多条语句的注释
三、事务
事务（transation）是一个工作单元。通过事务能将逻辑相关的操作绑定在一起，保持数据的完整性。而且，这些逻辑相关的操作是同生共死的关系，必须一起执行。
如果将若干条sql 命令作为一个事务，可通过下面的语句进行定义。
begin (transation|tran)
sql 语句组
commit (transation|tran)
一个是事务的开始，一个是事务的结束。
rollback (transation|tran) [保持点名称] 在commit tran之前使用回滚机制，可以取消事务并撤销对数据所做的任何改变。
save tran[sation] 保存点名称
begin transation
insert into department values(2,'销售部')
save tran A
insert into department values(3,'市场部')
rollback tran A
insert into department values(5,'人力资源')
commit
上面用save tran 定义保存点，rollback tran A 作用将第二条insert 操作从数据库撤销
四、存储过程：
指的是将一些固定的操作集中起来，有sql服务器来完成，以实现某个任务，这种方法就是存储过程。存储过程
是一套已经编译好的sql语句，允许用户声明变量、输出参数、返回单个或者多个结果集以及返回值。存储过程存在于数据库中，可由应用程序调用执行。
一些系统存储过程：比如 sp_helptext sp_rename 等。当然也有一些用户自定义的存储过程。
create proc[edure] 过程名 [@参数名 参数类型[=默认值][output]....] as sql 语句组
执行存储过程的语法：
exec(ute) 过程名 [[@参数名=][参数][默认值][output].....]
exec 具有编辑权限的人
--带参数的存储过程，我们执行的时候可以有两种赋值方式
--其中 + 作为字符串的连接符出现。
create proc 权限查询
(@permit varchar(20))
as
select *
from mag_dept
where permitstr like '%' + @permit + '%'
exec 权限查询 '核稿'
exec 权限查询 '定稿'
事例：建立一个名为人员职务的存储过程，查询某雇员的职务情况，并把查询的结果以输出参数的形式返回。
其中存储过程的输入参数是雇员姓名，以@emp_name表示。声明输出参数@role保存职务信息，参数类型必须以存储过程定义时的输出参数类型一致。
create proc 人员职务
(@emp_name varchar(30)='', @role varchar(20) output)
as
select @role=emprole
from mag_dept
where empname=@emp_name
return
declare @emprole varchar(20)
exec 人员职务 @emp_name='赵华'
@role = @emprole output
select @emprole as 职务
修改存储过程：
将create换成alter就是修改存储过程。
事例：修改存储过程 权限查询 ，增加统计出满足条件的雇员的人数
alter proc 权限查询
(@permit varchar(20))
as
select *
from mag_dept
where permitstr like '%' + @permit + '%'
select count(*) as 人数
from mag_emp
where permitstr like '%' + @permit + '%'
exec 权限查询 '签发'
删除存储过程
drop proc 权限查询
存储过程与事务管理
为了避免事务分散，我们一般讲事务写入到存储过程中，存储过程执行中，服务器会捕捉错误信息，这样有助于在存储过程中管理事务嵌套。
--编辑存储过程期刊编辑，保证在期刊采集系统数据库中对 mag_info表增加一个新纪录时完成日期要早于出版日期。
--其中，begin -- end 相当于 {} 大括号。
create proc 期刊编辑
(@magid char(9), @magname varchar(50), @pubdate datetime, @depid int,
@designername varchar(30), @finishdate datetime )
as
begin tran
insert into mag_info values
(@magid,@magname,@pubdate,@depid,@designername,@finishdate)
if @pubdate > @finishdate
begin
commit tran
return 0
end
else
begin
rollback tran
return 9999
end
-- 执行
declare @status int
exec @status='期刊编辑' 'baxddd','北京信息周报','2007-4-2','2','张斌','2004-3-30 15:00'
select @status
触发器
触发器是定义在表上面的一个对象，是一种特殊的存储过程。触发器不需要专门语句去调用，它主要是通过事件进行触发而被执行的，即当执行 insert delete update 时，语句自动触发，而存储过程可以通过存储过程名称而被直接调用。其可以用于约束，默认值和规则的完整性检查。
创建和执行触发器：
create trigger 触发器名
on 表名
for{[detete],[insert],[update]}
as sql语句组
注意：触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。也就是说，触发器可以引用临时表对象。
--建立约束
--创建 更新部门 触发器来保证期刊采集系统数据库中 mag_dept表中部门负责人与mag_emp表中的部门信息的参照完整性
--语句流程：
--从inserted表中获取部门编号与负责人姓名，分别保存在变量@depid 与@manager中。
--查询出该负责人在mag_emp中所在的depid，并判断是否与变量@depid一致，如果不一致，将负责人@manager在mag_emp
--中的depid修改为@depid。
create trigger 更新部门
on mag_dept for update
as
declare @depid int
declare @manager varchar(50)
select @depid=Depid,@manager = Depmanager
from inserted
if(@depid <> (select Depid from mag_emp where Empname=@manager))
update mag_dept set Depid = @depid
where empname = @manager
--使用
update mag_dept
set Depmanager = '王玲玲' ， DepTel='1111111'
where Depid = 3
--查看
select Empname, Depid
from mag_emp
where Empname='王玲玲'
修改触发器
alter trigger 更新部门
on mag_dept for update
as
declare @depid int
declare @manager varchar(50)
select @depid=Depid,@manager = Depmanager
from inserted
if(@depid <> (select Depid from mag_emp where Empname=@manager))
update mag_dept set Depid = @depid
where empname = @manager
print '更新成功'
删除触发器
drop trigger 更新部门
触发器与事务管理
触发器最常用的应用就是执行复杂的行验证，保证数据的完整性。如果触发器能够确定激发触发器的命令是无效的，它就可以回滚此事务。
create trigger 增加期刊
on mag_info for insert
as
declare @magid char(9)
declare @num tinyint
select @magid=Magid from inserted
select @num=count(*) from mag_info
where Magid=@magid
if @num >0
begin -- sql语句有多条，所以用begin end 语句。
rollback tran
print '期刊号不唯一'
end
整理代码如下：
