SqlServer存储过程,学习

存储过程：存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。功能强大，限制少

如何调用存储过程

1 --无参数的存储过程
2 exec sp_databases;
3 
4 --有参数的
5 exec sp_helpdb TestDataBase;
6 
7 exec sp_renamedb TestDataBase, TDB;
8 -- 当参数较多的时候，建议使用显示赋值
9 exec sp_renamedb @newname='Testdatabase', @dbname='TDB';

创建,修改无参存储过程:

创建:

-- 一个无参的查询存储过程
/*
create proc usp_存储过程名
as
    查询步骤
*/
go
create proc usp_spFenYe
as
    select * from dbo.fn_fenye(1,10);--一个分页的fn,获取第一页的数据,10条/页
go

修改已有的存储过程usp_Test,以实现转账为例子(开启事务),

 1 alter proc usp_Test
 2 as
 3 begin
 4     begin transaction
 5     declare @myError int;
 6     set @myError = 0;
 7         update bank set balance=balance + 900 where cid='0001';
 8         set @myError += @@ERROR;
 9         update bank set balance=balance - 900 where cid='0002';
10         set @myError += @@ERROR;
11     if(@myError > 0)
12     begin
13         rollback transaction;
14     end
15     else
16     begin
17         commit transaction;
18     end
19 end;
20 go
21 
22 select * from bank;
23 
24 exec usp_Test;

建个有参数的存储过程,继续转账:

 1 create proc usp_ZZ2
 2 @from char(4),  --从那里转
 3 @to char(4),        --转到哪里
 4 @money money  --金额
 5 as
 6 begin
 7     begin transaction
 8     declare @myError int;
 9     set @myError = 0;
10         update bank set balance=balance - @money where cid=@from;
11         set @myError += @@ERROR;
12         update bank set balance=balance + @money where cid=@to;
13         set @myError += @@ERROR;
14     -- 什么时候提交，什么时候回滚？
15     if(@myError > 0)
16     begin
17         rollback transaction;
18     end
19     else
20     begin
21         commit transaction;
22     end
23 end
24 go
25 
26 select * from bank;
27 
28 exec usp_ZZ2 '0001', '0002', 90;
29 
30 exec usp_ZZ2 @money=500, @from = '0001', @to = '0002';
31 
32 exec usp_ZZ2 '0001', '0002', 410;
33 
34 go

依然在转账

 1 create proc usp_ZZ3
 2 @from char(4),
 3 @to char(4),
 4 @money money
 5 as
 6 begin
 7     begin transaction
 8     declare @last money;
 9     set @last = (select balance from bank where cId=@from);
10     if(@last - 10 >= @money)
11     -- bank表里有一个检查约束，余额要大于等于10
12     begin
13         update bank set balance=balance - @money where cid=@from;
14         update bank set balance=balance + @money where cid=@to;
15         commit;    
16         select '转账成功';
17     end
18     else
19     begin
20         rollback;
21         select '转账失败';
22     end
23 end
24 go
25 
26 exec usp_ZZ3 '0001', '0002', 900;
27 
28 select * from bank;

 给存储过程的参数设置默认值,示例:

1 create proc usp_testDefault
2 @str nvarchar(50) = '默认值'
3 as
4     select @str;
5     
6 exec usp_testDefault '我是传进来的参数啊';

output参数怎么用,还在转(转账),修改存储过程zz4;

 1 -- 带有output参数的存储过程
 2 
 3 go
 4     
 5 create proc usp_ZZ4
 6 @from char(4),
 7 @to    char(4),
 8 @money money,
 9 @state int output 
10 -- 这个state表示需要在存储过程中赋值，外面使用的参数
11 as
12 begin
13     begin transaction
14     declare @last money;
15     set @last = (select balance from bank where cId=@from);
16     if(@last - 10 >= @money)
17     
18     begin
19         update bank set balance=balance - @money where cid=@from;
20         update bank set balance=balance + @money where cid=@to;
21         commit;    
22         set @state = 1;--1表示转账成功
23     end
24     else
25     begin
26         rollback;
27         set @state = 0;--转账失败
28     end
29 end
30 go
31 ----------------
32 -- 使用
33 declare @State int;
34 exec usp_ZZ4 '0001', '0002', 1000, @State output;
35 exec usp_ZZ4 @from = '0001', @to = '0002', @money = -501, @state = @State output;
36 
37 select @State;