目录
0. 简介
1. 语法细节
2. 示例 1: 模拟转账
3. 示例 2: 测试返回 DataTable
4. 源代码下载
shanzm-2020 年 5 月 3 日 23:23:44
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示例背景: 使用存储过程, 模拟在一张存款表中实现用户与用户之间的转账
2准备工作 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
3准备工作 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();
- }
- }
- }
- }
- }
4编写存储过程:
在数据库中: 指定数据库 -->可编程性 -->存储过程 -->右键: 新建 -->存储过程:
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 语句
5控制台中测试
新建一个控制台项目, 在配置文件中添加连接字符串
因为封装的 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
1存储过程:
- create procedure [dbo].[pro_ReturnDataTable]
- as
- begin
select Id as 用户 ID ,Balance as 余额 from szmBank;
end
go
2数据库中测试:
execute pro_ReturnDataTable
测试结果: 即显示 szmBank 中的所有数据
3控制台中测试:
- 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. 源代码下载
C# 中使用存储过程 - 源代码下载
所需要的数据库表在示例中已说明, 可以直接使用建表语句创建!
存储过程的 SQL 语句在示例中完整的展示了, 可以直接复制!
来源: https://www.cnblogs.com/shanzhiming/p/12824650.html