11-SQLServer的事务、存储过程和触发器

最新推荐文章于 2023-12-29 07:00:00 发布

weixin_33881753

最新推荐文章于 2023-12-29 07:00:00 发布

阅读量405

点赞数

文章标签：数据库 c#

原文链接：http://www.cnblogs.com/yaopengfei/p/7355332.html

版权

一. 事务

　在SQLServer中，每条SQL语句，默认就是一条隐式的事务，但是如果我们需要一组SQL语句，那么就需要采用SQLServer提供的特有的标记来声明事务的开始、提交和回滚了。

事务的开始：begin transaction

　事务的提交：commit transaction

事务的回滚：rollback transaction

下面提供两种方式来封装事务：

　方式一：利用begin try--end try 、begin catch--end catch，来捕捉是否存在异常，从而来判断执行事务提交还是事务回滚。

1  begin transaction
2  begin try
3      SQL语句1
4      SQL语句2
5     commit transaction
6  end try
7  begin catch
8     rollback transaction
9  end catch

　方式二：利用系统变量@@ERROR，代表的意思为：记录最近一次SQL语句执行的状态码，如果大于0表示这条有错误，最终通过判断是否大于0，来决定是事务提交还是事务回滚。

　自定义变量的方式：@+变量名

 1  begin transaction 
 2     declare @myError int;    --自定义变量
 3     set @myError=0;
 4     SQL语句1
 5     set @myError=@myError+@@ERROR;    
 6     SQL语句2
 7     set @myError=@myError+@@ERROR;
 8 if @myError>0
 9     begin 
10         rollback transaction
11     end
12 else
13     begin
14         commit transaction
15     end

　案例：

　　利用LoginRecords表中的LoginIp字段的长度，来测试事务的回滚。

 1 -- 事务
 2 --SQL语句准备
 3 select * from LoginRecords
 4  update LoginRecords set loginIp='192.168.1.2' where id='324534'
 5  update LoginRecords set loginIp='192.168.127.111' where id='3425435lkjsdlkf'
 6  select * from LoginRecords
 7 
 8  -- 写法1
 9  begin transaction
10  begin try
11      update LoginRecords set loginIp='192.168.1.4' where id='324534'
12      update LoginRecords set loginIp='192.168.1.111' where id='3425435lkjsdlkf'
13     commit transaction
14  end try
15  begin catch
16     rollback transaction
17  end catch
18 
19  --写法2
20  -- 利用 @@ERROR 这个系统变量代表的意思为：记录最近一次SQL语句执行的状态码，如果大于0表示这条有错误
21  -- 自定义变量的格式：@+变量名
22  begin transaction 
23     declare @myError int;
24     set @myError=0;
25     update LoginRecords set loginIp='192.168.1.4' where id='324534'
26     set @myError=@myError+@@ERROR;
27     update LoginRecords set loginIp='192.168.1.111' where id='3425435lkjsdlkf'
28     set @myError=@myError+@@ERROR;
29 if @myError>0
30     begin 
31         rollback transaction
32     end
33 else
34     begin
35         commit transaction
36     end

　结论：我们发现两个事务中的第二条SQL语句，长度均超过了12，即使第一条SQL语句能正常进行，但是由于事务一体，事务集中回滚，都不能执行。

二. 存储过程

(一). 基本概念

1. 什么是存储过程？

　　存储过程可以理解为是使用T_SQL编写的一组代码段，将一系列SQL操作(增删改查)封装在一起，组成一个代码块，方便每次调用。同时存储过程是数据库和应用程序间的桥梁，是二者之间的编程接口，比如：ADO.NET、EF等技术都可以在代码中调用存储过程。

　　一句话总结：存储过程是数据库为了实现特定业务需求而将一系列SQL操作封装成代码段存放在数据库服务器上便于应用程序来调用。

2. 存储过程的好处

　 a. 存储过程封装了复杂的SQL操作，简化了操作流程。

　 b. 加快了应用程序系统的运行速度，因为存储过程只在创建时编译，此后的调用无须重新编译。

　 c. 实现了模块化的程序设计，存储过程可以被多次调用，为应用程序提供了统一的数据库访问接口，提高了程序的可维护性，充分体现了模块化的开发思想。

　 d. 提高了代码的安全性，数据库管理员可以为存储过程设定指定的用户可访问的权限。

e. 降低网络流量，有存储过程是存放在服务器上的，所以在应用程序与服务器通信过程中，不会产生大量的T_SQL代码。

3. 存储过程的缺陷

　 a. 与数据库依赖性太强，不同类型数据库间的存储过程不能简单的相互移植。

b. 不支持服务器集群。

c. 没法采用面向对象的思想进行封装。

4. 存储过程的种类

　 a. 系统存储过程

b. 自定义存储过程

c. 扩展存储过程　　

(二). 创建各种存储过程

整体规则：

　　A. 存储过程用 procedure表示，也可以缩写为 proc 。

　　B. 删除存储过程用drop 。

　　C. 创建存储过程前，通常加一段代码，用来判断存储过程是否存在，避免重复创建的问题。

if (exists (select * from sys.objects where name = '存储过程名'))
  drop proc 存储过程名

　　D. 存储过程的参数形式：输入参数和输出参数（可以给输入参数设置默认值）

1. 创建无参的存储过程

　　(查询用户表中的所有信息)

1 if (exists (select * from sys.objects where name = 'GetAllUserInofor'))
2     drop proc GetAllUserInofor
3 go
4 create procedure GetAllUserInofor
5 as
6     select * from [dbo].[Sys_UserInfor];
7 
8 -- 调用存储过程
9 exec GetAllUserInofor;

2. 修改指定存储过程的内容

1 go
2 alter proc GetAllUserInofor
3 as
4   select * from [dbo].[Sys_UserInfor] where userSex = '男'
5 
6 -- 调用存储过程
7 exec GetAllUserInofor;

3. 删除存储过程

1 drop proc GetAllUserInofor;

4. 重命名存储过程(调用系统自带的存储过程来实现)

1 -- 将名为“GetAllUserInofor”的存储过程改为 “proc_GetAllUserInofor”
2 go
3 sp_rename GetAllUserInofor,proc_GetAllUserInofor2;
4 
5 -- 调用存储过程
6 exec GetAllUserInofor2;

5. 创建带参数的存储过程
输入参数：用于向存储过程传入值，类似C#值传递。
输出参数：用于调用存储过程后，返回结果，类似C#引用传递。

-- 5.1 创建带1个输入参数的存储过程
--(根据用户ID来查询用户信息)

 1 go
 2     create proc GetUserById(
 3         @userId varchar(32)
 4     )
 5 as
 6     select * from Sys_UserInfor where id=@userId;
 7 
 8 --执行该存储过程
 9 exec GetUserById '1';
10 exec GetUserById '2';

-- 5.2 创建带两个输入参数的存储过程
--(根据用户id和用户性别来查询用户信息)

 1 go
 2     create proc GetUserByIdSex(
 3         @userId varchar(32),
 4         @userSex varchar(50)
 5     )
 6 as 
 7     select * from Sys_UserInfor where id=@userId and userSex=@userSex;
 8 
 9 -- 执行该存储过程
10 --参数需要按顺序来写
11 exec GetUserByIdSex '1','男';    
12 --参数可以不按照顺序随意写
13 exec GetUserByIdSex @userSex='男',@userId='1'

-- 5.3 创建带有输出参数(返回值)的存储过程
-- (根据性别来查询所有用户的姓名，并输出用户的数量)

 1 go
 2     create proc GetUserNameBySex(
 3        @userSex varchar(50),  --输入参数,无默认值
 4        @count int output  -- 输出参数,无默认值
 5     )
 6 as
 7     select * from Sys_UserInfor where userSex=@userSex;
 8     select @count=count(*) from Sys_UserInfor where userSex=@userSex;

 9 -- 执行该存储过程
10 declare @myCount int   --声明变量来接收存储过程的返回值
11 exec GetUserNameBySex '男',@myCount output
12 select @myCount as Count;

注：纠正一个概念，输出参数是一个符合数据库类型的值，而不能是一个集合。

-- 5.4 创建带通配符的存储过程（参数含默认值）
-- (根据用户名和用户账号模糊查询用户信息)

 1 go
 2     create proc GetInfor(
 3         @userName varchar(50)='李%',  --输入参数,有默认值
 4         @userAccount varchar(50)='admin%'  --输入参数,有默认值
 5     )
 6 as
 7     select * from Sys_UserInfor where userName like @userName and userAccount like @userAccount;
 8 
 9 -- 执行该存储过程
10 exec GetInfor;   --执行调用默认值
11 exec GetInfor '李%','t%';

6. 加密存储过程(with encryption)

-- with encryption 用户隐藏存储过程的文本

 1 go 
 2     create proc GetInforWithPwd
 3     with encryption
 4 as
 5     select * from Sys_UserInfor;
 6 
 7 -- 给该存储过程的文本加密
 8 exec sp_helptext 'GetInforWithPwd';   --系统存储过程获取关于加密的信息
 9 -- 执行该存储过程(加密不影响执行)
10 exec GetInforWithPwd;

7 .不缓存存储过程(with recompile)

1 go
2     create proc GetInforNoCache
3     with recompile
4 as
5     select * from Sys_UserInfor;
6 
7 -- 执行该存储过程
8 exec GetInforNoCache;

8. 分页的存储过程案例（ROW_NUMBER() over()）

　　ROW_NUMBER() over()，表示对查询出来的数据进行标号，over()表示对数据进行分组和排序。

 1 select *, rn1=ROW_NUMBER() over(order by addTime desc) from Sys_UserInfor
 2 
 3 select *, ROW_NUMBER() over(order by addTime desc) as rn2 from Sys_UserInfor
 4 
 5 select * from 
 6 (select *, ROW_NUMBER() over(order by addTime desc) as rn2 from Sys_UserInfor) as t
 7 
 8 
 9 select * from 
10 (select *, ROW_NUMBER() over(order by addTime desc) as rn2 from Sys_UserInfor) as t
11 where t.rn2 between 1 and 2 
12 
13 
14 -- 存储过程分页
15 go
16 create proc FenYe(
17         @pageSize int,   --输入参数：每页的条数
18         @pageIndex int=2,  --输入参数：当前页数,默认值为2
19         @totalCount int output, --输出参数：总条数
20         @pageCount int output   --输出参数：总页数
21     )
22 as
23     select * from 
24    (select *, ROW_NUMBER() over(order by addTime desc) as rn2 from Sys_UserInfor) as t
25     where t.rn2 between ((@pageIndex-1)*@pageSize)+1 and (@pageSize*@pageIndex)
26     select @totalCount=COUNT(*) from Sys_UserInfor
27     set @pageCount=CEILING(@totalCount * 1.0 /@pageSize);
28 
29 --执行该分页的存储过程
30 declare @myTotalCount int,   --声明变量用来接收存储过程中的输出参数
31         @myPageCount int     --声明变量用来接收存储过程中的输出参数
32 exec FenYe 2,1,@myTotalCount output,@myPageCount output;
33 select @myTotalCount,@myPageCount;

三. 触发器

1. 背景：跨数据库表等对象所做的验证，监视数据变动，触发器分为：after触发器和instead of触发器，针对增删改操作进行触发。

2. 语法：

1 go
2 create trigger tr_触发器名字 on 表名
3     after|instead of
4     update|delete|insert
5 as
6 begin 
7     代码
8 end

3. 临时表

　　inserted表和deleted表. (在增加、修改或删除数据的过程中临时创建的表)。

4. 经典案例

　　永远删除不掉的表数据。

 1 go
 2     create trigger tr_NoDelete on LoginRecords
 3     after
 4         delete
 5 as
 6 begin
 7     insert into LoginRecords(id,userId,loginCity,loginIp,loginTime)
 8     select id,userId,loginCity,loginIp,loginTime from deleted
 9 end;
10 
11 -- 测试
12 delete from LoginRecords;

作者 : Yaopengfei(姚鹏飞)
博客地址 : http://www.cnblogs.com/yaopengfei/
声明1 : 本人才疏学浅，用郭德纲的话说“我是一个小学生”，如有错误，欢迎讨论，请勿谩骂^_^。
声明2 : 原创博客请在转载时保留原文链接或在文章开头加上本人博客地址，否则保留追究法律责任的权利。

转载于:https://www.cnblogs.com/yaopengfei/p/7355332.html