Sql 存储管理

事务管理：

单个工作单元称为事务，我们将若干条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

?

整理代码如下：