步步为营-51-存储过程
说明 :存储过程类比C#中的方法
1.1 语法:create procedure 存储过程名称
@变量 类型[=默认值] [output],
@变量 类型[=默认值] [output] ,
...
as
begin
内容
end
1.2 创建一个数据表,并添加数据
use DemoDB create table bank ( cId char(4) primary key not null, balance money ) insert into bank (cId,balance) Values('0001',1000); insert into bank (cId,balance) Values('0002',10); select * from bank;
1.3 创建存储过程
1.3.1 存储过程Version:1.0
--02 创建存储过程 ------ go create procedure usp_转账_01 as begin --02-01 捕获异常 begin try --02-02 事务通过事务比较好 begin transaction Update bank set balance = balance - 200 where cId = '0001' Update bank set balance = balance + 200 where cId = '0002' commit transaction end try BEGIN catch rollback transaction END catch end --03 执行存储过程--- exec usp_转账_01;
1.3.2 存储过程1.0没有办法指定某个账号及金额,而且也不知道是否转账成功.所以对其进行升级
1.3.3 添加约束 alter table bank add constraint CK_bank_balance check(balance>0 );
--02 创建存储过程 ------ go create procedure usp_转账_02 @from Char(4), @to char(4), @money money, @IsSuccess nvarchar(32) output as begin --02-01 捕获异常 begin try --02-02 事务通过事务比较好 begin transaction Update bank set balance = balance - @money where cId = @from Update bank set balance = balance + @money where cId = @to commit transaction set @IsSuccess = '转账成功!' end try BEGIN catch rollback transaction set @IsSuccess = '转账失败!' END catch end
--03-01 按顺序输入参数 declare @IsSuccess nvarchar(32) exec usp_转账_02 '0001','0002',100, @IsSuccess output; select @IsSuccess --03-02 按指定内容输入参数 declare @IsSuccess nvarchar(32) exec usp_转账_02 @from='0001',@to='0002',@money=100, @IsSuccess= @IsSuccess output; select @IsSuccess
运行结果
执行成功后
再次执行,由于约束限制,执行失败
2.1 存储过程在C#中的调用
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; namespace ProcedureDemo { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void btnConfirm_Click(object sender, EventArgs e) { string connStr = "server=.;uid=sa;pwd =sa;database=DemoDB"; string from = txtFrom.Text; string to = txtTo.Text; string money = txtMoney.Text; using (SqlConnection conn = new SqlConnection( connStr)) { conn.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = conn; //01 将SQLcommand的text文本设置成存储过程的名称 cmd.CommandText = "usp_转账_02"; //02 设置参数 SqlParameter[] sps = { new SqlParameter("@from",from), new SqlParameter("@to",to), new SqlParameter("@money",money), //传出参数 new SqlParameter("@IsSuccess",SqlDbType.NVarChar,32), }; //指明输出参数 sps[3].Direction = ParameterDirection.Output; //03执行SQL语句,所选择的方法根据存储过程的返回值而定, cmd.Parameters.AddRange(sps); cmd.ExecuteNonQuery(); MessageBox.Show(sps[3].Value.ToString()); } } } } }
运行效果
3.1 job任务用到的存储过程,
场景:一张表中存储一个ProcessID和StrSQL语句,每隔一段时间遍历该表进行更新另一张(权限表)
3.1.1 第一步,先实现传入对应的ProcessID和StrSQL实现更新
ALTER procedure [dbo].[sp_UpdateProcRightsStartUserForJob2] @ProcID Int, @StrSQL varchar(max) as begin BEGIN delete from ProcRightsStartUser where ProcID = @ProcID IF OBJECT_ID('tempdb.dbo.#tbl_UserID_Temp','U') IS NOT NULL DROP TABLE dbo.#tbl_UserID_Temp; CREATE TABLE #tbl_UserID_Temp (ProcID int,UserID varchar(50) ) --创建一个临时表 INSERT INTO #tbl_UserID_Temp(UserID) EXEC (@StrSQL) --StrSQL语句可能获得多行单列数据 Update #tbl_UserID_Temp set ProcID=@ProcID; INSERT INTO ProcRightsStartUser(ProcID,UserID) select ProcID,UserID from #tbl_UserID_Temp END end
3.1.2 进一步完善
ALTER procedure [dbo].[sp_UpdateProcRightsStartUserForJob] as begin --01 捕获异常 begin try -- 创建临时表 IF OBJECT_ID('tempdb.dbo.#devicetemp','U') IS NOT NULL DROP TABLE dbo.#devicetemp; SELECT ProcessID,StrSQL INTO dbo.#devicetemp from ProcRightsStartPermission ORDER BY ProcessID; -- 声明变量 DECLARE @ProcessID AS INT, @StrSQL varchar(max) WHILE EXISTS(SELECT ProcessID FROM dbo.#devicetemp) BEGIN -- 也可以使用top 1 --SET ROWCOUNT 1 SELECT @ProcessID=ProcessID, @StrSQL=StrSQL FROM dbo.#devicetemp; --print @StrSQL begin transaction delete from ProcRightsStartUser where ProcessID = @ProcessID IF OBJECT_ID('tempdb.dbo.#tbl_UserID_Temp','U') IS NOT NULL DROP TABLE dbo.#tbl_UserID_Temp; CREATE TABLE #tbl_UserID_Temp (ProcessID int,UserID varchar(50) ) INSERT INTO #tbl_UserID_Temp(UserID)EXEC (@StrSQL) Update #tbl_UserID_Temp set ProcessID=@ProcessID; INSERT INTO ProcRightsStartUser(ProcessID,UserID) select ProcessID,UserID from #tbl_UserID_Temp commit transaction SET ROWCOUNT 0 DELETE FROM dbo.#devicetemp WHERE ProcessID=@ProcessID; END end try BEGIN catch rollback transaction -- set @IsSuccess = '失败!' END catch end
3.2 分割字符串进行循环
ALTER procedure [dbo].[sp_UpdateProcRightsStartUser] @ProcID Int, @UsersID nvarchar(max) --权宜之计 as begin --02-01 捕获异常 begin try --02-02 事务通过事务比较好,如果 1删除数据,2插入数据, 如果插入失败那么也不能删除 begin transaction delete from ProcRightsStartUser where ProcID = @ProcID --02-03 按照";"分割字符串 Begin Declare @i Int Set @UsersID = RTrim(LTrim(@UsersID)) --去掉字符串变量左右空白 Set @i = CharIndex(';',@UsersID) --获取第一个分隔字符所在字符中的索引位置@i While @i >= 1 --while循环，一直到@i=0结束，因为索引为0的话说明字符串中已不存在分隔符 Begin Insert into ProcRightsStartUser (ProcID,UserID) Values(@ProcID,Left(@UsersID,@i-1)) Set @UsersID = SubString(@UsersID,@i+1,Len(@UsersID)-@i) Set @i = CharIndex(';',@UsersID) End If @UsersID <> '' Insert into ProcRightsStartUser (ProcID,UserID) Values(@ProcID,@UsersID) --Return End commit transaction -- set @IsSuccess = '成功!' end try BEGIN catch rollback transaction -- set @IsSuccess = '失败!' END catch end
