存储过程介绍以及使用（转账，分页）

作者: 达?矢抾哆拉?
来源: 51数据库
2021-08-02

课堂上老师让人报名讲课，在“触发器”、“存储过程”、“连接”中选择一个内容，那时还没学过“触发器”和“存储过程”，所以我报名要讲“存储过程”。

然后花了两个星期，有空就查查资料，测试代码，应该也算是理解了大概了吧。

成果有：1.简单的ppt介绍存储过程；2.数据库创建、运行“存储过程”；3.应用程式连接数据库调用存储过程

下面按照我在课堂上讲的ppt流程写下文字教程，希望大家对存储过程有个整体的认识。
我的微薄：http://www.51sjk.com/Upload/Articles/1/0/253/253663_20210627003157693.jpg 欢迎程序员互粉。

一：简介

存储过程确实像是高级语言中的“方法”、“函数”，里面可以封装很多的sql语句等代码，这些代码联合起来会实现某个功能，即某个需求。若一存储过程完成对某功能的封装后，就像一个盒子，外部想使用它的时候，无需关心内部的代码，只需把调用该盒子，将一些参数传进去（可以没有参数）。存储过程在内部运行，操作数据库数据，然后可以有返回值送回给它的调用者。

二：优缺点

存储过程确实有不少优点！

1.运行速度快。

但当时只知道并不是因为它的执行速度快，对“体现在预编译”也没什么理解。还好讲课后有同学问到，然后老师调拨了下：当客户端第一次调用该存储过程时，数据库会进行编译等操作，然后把“过程”存到内存中，这第一次会比较慢。当客户端（可以是不同的客户端）之后再次调用该存储过程时，便是直接从内存中执行，从而速度比较快。

2.可以降低网络通信量

为什么可以可以降低网络通信量？一开始我也是摸不着头脑，和“客户端直接写sql语句”相比，同样都差不多是发送一个请求去数据库，同样数据库可能返回一个结果集，到底在哪个方面上可以降低了网络通信量？

后来找到了一个例子，焕然大悟。这个例子就是“有分页功能”的存储过程。可以想象一下，如果有一张表有100万行数据，如果在客户端写一条sql语句让这100行数据抓回客户端，然后再写个for循环让它们分页显示，这得消耗多大的资源啊！

现在有了存储过程，就可以让客户端把“每页要显示的行数”和“第几页”的参数传到数据库的存储过程，存储过程再去获取对应的页面的数据，然后在把该页数据返回给客户端。这样降低的网络通信量将是大大的！

除此之外，还有一些挺不错的优点

3.提高安全性

因为客户端只是发送一个调用某存储过程的命令到服务器，这时就算该信息被截取了，对方也不知道该存储过程是做了什么事。

4.便于集中控制。

像一些可能要经常改变运算规则的运算放到存储过程中，需要修改时对存储过程进行修改就行。打个比方，一个数据库，多个客户端，如果不使用存储过程，修改操作数据库的sql语句时，就要更新修改所有客户端。

缺点不多，但肯定有，不然全部用存储过程来处理数据好了呵呵。但暂时不能很好的体会，就不乱写了……

三：实例需求

这里有个叫atm的数据库，里面有一张t_users的表，里面存在id、姓名、余额的信息

简单的转账功能：即a向b转账金额c，a的余额减去c，b的余额加上c

分页：即显示某页面的数据

四：实例流程

这张图表示了数据库向应用程序返回数据的两个方式。后来老师提到，我应该多画几个“应用程序”，体现下存储过程的便利。如果不使用存储过程，那应用程序有时要完成某个个功能，将不知一次地向数据库发送请求。比如，有可能要去获取一个数据到应用程序进行判断，符合条件了，再发送另一个执行命令到数据库。而采用存储过程，则直接调用存储过程即可，它在里面判断是否符合条件后进行不同操作返回不同的值给应用程序。

五：演示（这里使用的sql server 2008 + vs2010 的c# & wpf & ado.net）

1.数据库创建一个存储过程：

在数据库atm中，新建查询，执行以下代码

create procedure transferaccounts (@fromid int, @money decimal, @toid int)
as
--如果（余额>转账）的代码省略
--委托的代码省略
begin --begin~end相当于高级语言的 { }
update t_users set balance = balance - @money where id = @fromid
update t_users set balance = balance + @money where id = @toid
return 1 --返回值
end
go

create procedure transferaccounts (@fromid int, @money decimal, @toid int)
 as
 --如果（余额>转账）的代码省略
 --委托的代码省略
 begin  --begin~end相当于高级语言的  {  }
  update t_users set balance = balance - @money where id = @fromid
  update t_users set balance = balance + @money where id = @toid
  return 1 --返回值
 end
 go

2.数据库可视化编辑一个存储过程：

目录位置：数据库--atm--可性--存储过程，会看到transferaccounts 。右键点击它，可以修改、删除等操作

3.数据库可视化执行一个存储过程：

右键点击transferaccounts，编写存储过程脚本为--execute 到新查询编辑器窗口

declare @rc int
declare @fromid int
declare @money decimal(18,0)
declare @toid int
-- todo: 在此处设置参数值。
set @fromid = 1
set @toid = 2
set @money = 100
execute @rc = [atm].[dbo].[transferaccounts]
@fromid
,@money
,@toid
go

declare @rc int
declare @fromid int
declare @money decimal(18,0)
declare @toid int

-- todo: 在此处设置参数值。
set @fromid = 1
set @toid = 2
set @money = 100

execute @rc = [atm].[dbo].[transferaccounts] 
   @fromid
  ,@money
  ,@toid
go

（浅蓝色部分为手动添加的参数值）

执行之后，结果正确

4.程序连接数据库执行一个存储过程：

这里就只能简单说下了。

首先在sqlhelper中编写一个执行的存储过程的方法

public static bool executeprocedure(string procedurename, params sqlparameter[] parameters)
{
using (sqlconnection conn = new sqlconnection(connstr))
{
conn.open();
using (sqlcommand cmd = conn.createcommand())
{
cmd.commandtext = procedurename;
cmd.commandtype = commandtype.storedprocedure; //修改命令为执行存储过程！！！！！！
parameters[3].direction = parameterdirection.returnvalue; //设置第四个参数为返回参数（为了测试方便就直接设置了）
cmd.parameters.addrange(parameters); //添加参数
cmd.executenonquery(); //执行存储过程！！！！！！！！
int thereturn = (int)parameters[3].value; //接收返回值
if (thereturn == 1) //bool类型在数据库为bit，返回值为 0 或 1
{
return true;
}
else
{
return false;
}
}
}
}

public static bool executeprocedure(string procedurename, params sqlparameter[] parameters)
        {
            using (sqlconnection conn = new sqlconnection(connstr))
            {
                conn.open();
                using (sqlcommand cmd = conn.createcommand())
                {
                    cmd.commandtext = procedurename;
                    cmd.commandtype = commandtype.storedprocedure;  //修改命令为执行存储过程！！！！！！
                    parameters[3].direction = parameterdirection.returnvalue; //设置第四个参数为返回参数（为了测试方便就直接设置了）
                    cmd.parameters.addrange(parameters); //添加参数
                    cmd.executenonquery(); //执行存储过程！！！！！！！！
                    int thereturn = (int)parameters[3].value;  //接收返回值
                    if (thereturn == 1)  //bool类型在数据库为bit，返回值为 0 或 1
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
            }
        }

界面层中调用一个存储过程

private void btconfirm_click(object sender, routedeventargs e)

{
int fromid = int32.parse(txtfromid.text);
int money = int32.parse(txtmoney.text);
int toid = int32.parse(txttoid.text);
bool issuccessed=false;
issuccessed = sqlhelper.executeprocedure("transferaccounts",
new sqlparameter("@fromid", fromid),
new sqlparameter("@money", money),
new sqlparameter("@toid", toid),
new sqlparameter("@issuccessed",0));
messagebox.show("" + issuccessed);
}

private void btconfirm_click(object sender, routedeventargs e)
        {
            int fromid = int32.parse(txtfromid.text);
            int money = int32.parse(txtmoney.text);
            int toid = int32.parse(txttoid.text);
            bool issuccessed=false;

            issuccessed = sqlhelper.executeprocedure("transferaccounts",
                new sqlparameter("@fromid", fromid),
                new sqlparameter("@money", money),
                new sqlparameter("@toid", toid),
                new sqlparameter("@issuccessed",0));

            messagebox.show("" + issuccessed);

        }

5.最后说下数据库可视化执行分页的存储过程

同样的，先创建

create procedure paging (@pagesize int, @pageindex int) --参数：每页显示的行数，页数的索引
as
begin with temptable as(select row_number() over(order by id) as row, * from t_users) --临时表，按id排序，首列（row），记录所在行数
select * from temptable where row between (@pageindex-1)*@pagesize + 1 and @pageindex*@pagesize
end

create procedure paging (@pagesize int, @pageindex int)  --参数：每页显示的行数，页数的索引
 as
 begin with temptable as(select row_number() over(order by id) as row, * from t_users)  --临时表，按id排序，首列（row），记录所在行数
  select * from temptable where row between (@pageindex-1)*@pagesize + 1 and @pageindex*@pagesize
 end

本教程到此为止