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语句在示例中完整的展示了,可以直接复制!