0. 簡介
【定義】：儲存過程（Stored Procedure） 是在大型資料庫系統中，一組為了完成特定功能的SQL 語句集，它儲存在資料庫中，一次編譯後永久有效，使用者通過指定儲存過程的名字並給出引數（如果該儲存過程帶有引數）來執行它。
【優缺點】：儲存過程優缺點都非常的明顯！幾乎每一篇討論儲存過程的文字，都是會說其他優點是balabala,缺點是balabala，然而最後作者的結論都是：“我不推薦使用儲存過程”。
具體的儲存過程的優缺點這裡就不詳述了！
公司舊專案使用儲存過程實現業務邏輯，沒辦法只能研究了一下 ?！
閒言碎語不要講，書歸正傳，下面就開始儲存過程！
1. 語法細節
-
變數與變數之間使用逗號隔開，語句結尾無標點符號
-
宣告變數:
declare @variate_name variate_type,例如宣告並賦值：declare @name nvarchar(50) ='shanzm'
變數賦值:set @variate_name =value
列印變數：print @variate_name -
begin……end 之間的SQL語句稱之為一個程式碼塊
-
可以使用if……else實現邏輯判斷
-
建立儲存過程：
create procedure pro_name -
執行儲存過程：
execute pro_name -
輸出引數:儲存過程返回的是SQL語句查閱結果，在定義引數後，新增
output,設定為一個輸出引數（和C#中輸出引數類似），相當於多了一個返回值！ -
建立儲存過程的基本形式：
create procedure pro_name_tableName
@param1 param1_type,
@param2 param2_type,
as
begin
--sql語句
end
go
2. 示例1：模擬轉賬
①示例背景：使用儲存過程，模擬在一張存款表中實現使用者與使用者之間的轉賬
②準備工作1：在資料庫中建立表szmBank
CREATE TABLE [dbo].[szmBank](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Balance] [decimal](18, 0) NOT NULL
新增一些測試資料：
Id Balance
--------------- ----------------
1 1000
2 2000
3 3000
③準備工作2：封裝C#程式碼中的SQL輔助類SqlHelper
注意封裝的時候要有一個CommandType引數，決定是執行SQL語句還是儲存過程，
CommandType是一個列舉型別，其中Text值為執行SQL語句，StoreProcedure為執行儲存過程
具體封裝細節這裡就不詳述了。
找到了2年前我封裝的一個SqlHelper.cs,常規使用沒有任何問題，僅供參考：
#region
// ===============================================================================
// Project Name :
// Project Description :
// ===============================================================================
// Class Name : SqlHelper
// Class Version : v1.0.0.0
// Class Description : SQL語句輔助類
// CLR : 4.0.30319.18408
// Author : shanzm
// Create Time : 2018-8-14 18:22:59
// Update Time : 2018-8-14 18:22:59
// ===============================================================================
// Copyright © SHANZM-PC 2018 . All rights reserved.
// ===============================================================================
#endregion
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace _16StoreProcedure
{
public class SqlHelper
{
private static readonly string connStr =
ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
/// <summary>
/// 返回查詢結果的的表
/// </summary>
/// <param name="sql">SQL語句或儲存過程</param>
/// <param name="type">執行型別</param>
/// <param name="param">引數</param>
/// <returns></returns>
public static DataTable GetDataTable(string sql, CommandType type, params SqlParameter[] param)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conn))
{
if (param != null)
{
adapter.SelectCommand.Parameters.AddRange(param);
}
adapter.SelectCommand.CommandType = type;
DataTable da = new DataTable();
adapter.Fill(da);
return da;
}
}
}
/// <summary>
/// 返回影響行數
/// </summary>
/// <param name="sql">SQL語句或儲存過程</param>
/// <param name="type">執行型別</param>
/// <param name="param">引數</param>
/// <returns></returns>
public static int ExecuteNonquery(string sql, CommandType type, params SqlParameter[] param)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
if (param != null)
{
cmd.Parameters.AddRange(param);
}
cmd.CommandType = type;
conn.Open();
return cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 返回查詢結果的第一行第一個單元格的資料
/// </summary>
/// <param name="sql">SQL語句或儲存過程</param>
/// <param name="type">執行型別</param>
/// <param name="param">引數</param>
/// <returns></returns>
public static object ExecuteScalar(string sql, CommandType type, params SqlParameter[] param)
{
using (SqlConnection conn=new SqlConnection (connStr ))
{
using (SqlCommand cmd=new SqlCommand (sql,conn))
{
if (param !=null )
{
cmd.Parameters.AddRange(param);
}
cmd.CommandType = type ;
conn.Open();
return cmd.ExecuteScalar();
}
}
}
}
}
④編寫儲存過程：
在資料庫中：指定資料庫-->可程式設計性-->儲存過程-->右鍵：新建-->儲存過程：
SQL Server中編寫的SQL語句沒有預設的格式化，所有程式碼排版按照我自己習慣進行Tab縮排
建議放到編輯器中檢視下面的儲存過程，會好看一些！
SQL大小寫不敏感，我習慣小寫，方便閱讀！
-- =============================================
-- Author: shanzm
-- Create date: 2020年5月2日 19:56:51
-- Description: 模擬賬戶之間轉賬
-- =============================================
create procedure pro_transfer_szmbank
@from bigint,
@to bigint,
@balance decimal(18,0),
@returnNum int output--(1表示轉賬成功，2表示失敗，3表示餘額不足)
as
begin
--判斷轉出賬戶是否有足夠的金額
declare @money decimal(18,0)
select @money=Balance from dbo.szmBank where Id=@from;
if @money-@balance>=0.1
--開始轉賬
begin
begin transaction
declare @sum int =0
--轉出賬戶扣錢
update szmBank set balance=balance-@balance where id=@from
set @sum=@sum+@@error
--轉入賬戶加錢
update szmBank set balance=balance+@balance where id=@to
set @sum=@sum+@@error
--判斷是否成功
if @sum<>0
begin
set @returnNum=2--轉賬失敗
rollback
end
else
begin
set @returnNum=1--轉賬成功
commit
end
end
else
begin
set @returnNum=3--餘額不足
end
end
go
在資料庫中執行測試（F5）：
--執行測試：
declare @ret int
execute pro_transfer_szmbank
@from='1',
@to='2',
@balance='10',
@returnNum=@ret output--注意輸出引數在執行語句中也是要表明"output"
print @ret --結果是列印:1,即儲存過程實現成功
【注意】：
-
我們需要檢視某個儲存過程，則可以使用資料中自帶的儲存過程檢視：
sp_helptext pro_transfer_szmBank -
修改現有的儲存過程，右鍵儲存過程-->修改：顯示的儲存過程只是把建立儲存過程中的
create變為了alert -
可以在SQL Server的SQL視窗選中某些SQL語句，點選執行，即執行選中的SQL語句
⑤控制檯中測試
新建一個控制檯專案，在配置檔案中新增連線字串
因為封裝的SqlHelper中需要從配置檔案中讀取資料庫連線字串，所以新增引用：System.Configuration
static void Main(string[] args)
{
//轉出賬戶的Id
int from = 1;
//轉入賬戶的Id
int to = 2;
//轉賬金額
decimal balance = 10;
SqlParameter[] param =
{
new SqlParameter ("@from",from),
new SqlParameter("@to",to),
new SqlParameter ("@balance",balance),
//-------------------------------注意：這裡設定為輸出引數
new SqlParameter ("@returnNum",System.Data.SqlDbType.Int{Direction=System.Data.ParameterDirection.Output }
};
//------------------------設定CommonType為StorProcedure型別
SqlHelper.ExecuteNonquery("pro_transfer_szmbank",System.Data.CommandType.StoredProcedure, param);
//------------------------獲取輸出引數
//根據輸出引數判斷轉賬結果
int outPutparam = Convert.ToInt16(param[3].Value);
switch (outPutparam)
{
case 1: Console.WriteLine($"success:從Id:{from}轉賬{balance}元到Id：{to}");break;
case 2: Console.WriteLine("error"); break;
case 3: Console.WriteLine("餘額不足"); break;
}
Console.ReadKey();
}
測試結果：
success：從Id:1轉賬10元到Id:2
3. 示例2：測試返回DataTable
①儲存過程：
create procedure [dbo].[pro_ReturnDataTable]
as
begin
select Id as 使用者ID ,Balance as 餘額 from szmBank;
end
go
②資料庫中測試：
execute pro_ReturnDataTable
測試結果：即顯示szmBank中的所有資料
③控制檯中測試：
static void Main(string[] args)
{
DataTable dt = SqlHelper.GetDataTable("pro_ReturnDataTable", CommandType.StoredProcedure);
foreach (DataRow row in dt.Rows)
{
Console.WriteLine(row["使用者ID"].ToString() + ":" + row["餘額"].ToString());
}
Console.ReadKey();
//TransferAccounts();
ReturnDataTable();
}
測試結果：即列印szmBank中的所有資料
4. 原始碼下載
-
所需要的資料庫表在示例中已說明，可以直接使用建表語句建立！
-
儲存過程的SQL語句在示例中完整的展示了，可以直接複製！