存储过程

最新推荐文章于 2024-07-06 03:38:50 发布

冯佳兴

最新推荐文章于 2024-07-06 03:38:50 发布

阅读量663

点赞数 2

本文链接：https://blog.csdn.net/fjxcsdn/article/details/86598199

版权

1.什么是存储过程？

和C#中方法一样，由存储过程名/存储过程参数组成，可以有返回结果。

2.为什么要用存储过程

优点：

减少代码量——相同的代码无需重复写，模块化设计

执行速度快——在数据库中的存储过程语句，都是编译过的。

提高系统的安全性——防止SQL注入

减少网络流通量——只是传输存储过程的名称，整个过程不需要将所有的SQL语句进行传输

缺点：

存储过程过多，数据库访问量大，数据库压力大，不利于优化，合理使用。

系统存储过程：通常以SP_开头

效果展示：

 exec sp_databases  --列出服务器上所有的数据库
 exec sp_helpdb     --列出有关指定数据库或所有数据库的信息
 exec sp_tables     --返回当前环境下可查询的对象列表
 exec sp_columns 'UserInfo'--返回某个表列的信息
 exec sp_help 'UserInfo'   --查询某个表的所有信息
 exec sp_helpindex 'UserInfo' --查询某个表的索引
 exec sp_helptext 'sp_databases'  --查询某一个存储过程实际文本

3.存储过程的创建删除修改与使用

创建一个无参数，无返回值的存储过程

create proc usp_say_hello
as 
begin 
   print 'hello world'
end

创建一个带有两个参数的存储过程

--创建一个带有两个参数的存储过程
create proc usp_add_number
@n1 int,
@n2 int
as 
begin 
   select @n1+@n2
end
exec  usp_add_number @n1=1,@n2=2

创建一个带有输出参数的存储过程

--带输出参数的存储过程
--当存储过程中需要返回多个值的时候，就可以使用输出参数来返回这些值
create proc usp_showInfo
@ID Int,
@recordcount Int output  --输出参数
as 
begin
  select * from UserInfo where ID>@ID
  --把查询语句查询到的记录条数赋值给变量@recordcount
  set @recordcount=(select count (*) from userInfo where ID>@ID) 
end
--调用存储过程
--输出参数调用的时候，需要定义变量，将变量传递给输出参数，存储过程中使用输出参数，其实是你传递进来的变量
declare @rc int
exec usp_showInfo @ID=1,@recordcount=@rc output
print @rc

创建一个分页查询的存储过程

--使用一个存储过程编写一个分页查询
create proc usp_UserInfopage
@pagesize int=2, --每页记录条数
@pageindex int=1,--当前要查看的第几页记录
@recordcount int output,--总的记录条数
@pagecount int output --总结页数
as 
begin 
   --1.编写查询语句，查询用户需要的数据
   select *
   from (select *,rn=row_number()over (order by ID asc )from userInfo)as t
   where t.rn between (@pageindex-1)*@pagesize+1 and @pagesize*@pageIndex
   
   --2.计算总的记录条数
   set @recordcount=(select count(*)from UserInfo)
   --3.计算总页数,celling,向上取整
   set @pagecount=ceiling(@pagecount*1.0/@pagesize)
end 

declare @rc int,@pc int
exec usp_UserInfopage  @recordcount=@rc output,@pagecount=@pc output
print @rc
print @pc

修改一个带有两个参数的存储过程


--修改一个带有两个参数的存储过程
alter  proc usp_select_ID_RegTime
@ID int,
@RegTime dateTime
as 
begin 
   select * from UserInfo where ID>@ID and Regtime>@RegTime
end
exec  usp_select_ID_RegTime @ID =1,@RegTime=2018

删除一个存储过程

--删除存储过程
drop proc 存储过程名字

4.项目实践--实现两个项目之间的转账

select * from T_bank 
go
--创建一个存储过程实现账户与账户之间的转账
create proc usp_transfer
@ID char(4),--转账用户
@TOID char(4),--进账用户
@balance money,--账户余额
@resultNumber int output --转账是否成功（1--表示成功  2——表示失败  3--表示余额不足）
as
begin
  --1.判断金额是否足够转账
  declare @money money
  select @money=balance from T_bank where ID=@ID
  if @money-@balance>=10
  begin 
    --开始转账
	begin transaction
	  declare @sum int=0
			--2账户1扣钱
			update T_bank set balance=balance - @balance where ID=@ID
			set @sum=@sum+@@error
			--3账户2加钱
			update T_bank set balance=balance + @balance where ID=@TOID
			set @sum=@sum+@@error
			--判断是否转账成功，进行提交或者回滚
			if @sum<>0
			begin 
			      set @resultNumber=2--转账失败
			      rollback
		    end
			else
			begin
			      set @resultNumber=1--转账成功
			      commit
		    end 
	 end
	 else
	 begin
	       set @resultNumber=3--余额不足
     end
end

declare @r int
exec usp_transfer @ID='0001',@TOID='0002',@balance=100,@resultNumber=@r output
print @r

效果展示：

效果2展示：

需要注意两点

1.如何给带有参数的输出参数赋值

new SqlParameter("@resultNumber",SqlDbType.Int){Direction=ParameterDirection.Output}//如果是带有输出参数的存储过程，一定要表明{Direction=ParameterDirection.Output}

2.如何接收输出参数返回来的值

int result = Convert.ToInt32(ps[3].Value);//获取输出参数的值，输出参数相当属于ps数组的第四个位置，从0开始

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 winFormUI
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string id = textBox1.Text.Trim();//转出用户
            string toid = textBox2.Text.Trim();//转入用户
            double money = double.Parse(textBox3.Text.Trim());//转账金额
            SqlParameter[] ps = new SqlParameter[]
            {
                new SqlParameter("@ID",SqlDbType.Char,4){Value=id},
                new SqlParameter("@ToID",SqlDbType.Char,4){Value=toid},
                new SqlParameter("@balance",SqlDbType.Money){Value=money},
                new SqlParameter("@resultNumber",SqlDbType.Int){Direction=ParameterDirection.Output}//如果是带有输出参数的存储过程，一定要表明{Direction=ParameterDirection.Output}
            };
            SqlHelper.ExcuteNonQuery("usp_transfer", CommandType.StoredProcedure, ps);
            int result = Convert.ToInt32(ps[3].Value);//获取输出参数的值，相当属于ps数组的第四个位置，从0开始
            if (result == 1)
            {
                MessageBox.Show("成功");
            }
            else if (result == 2)
            {
                MessageBox.Show("失败");
            }
            else
            {
                MessageBox.Show("余额不足");
            }
        }
    }
}

效果展示：

如果调用存储过程，此时一定要注意SqlHelper的编写——参数CommandType type放在可变参数之前

cmd.CommandType = type;


using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace winFormUI
{
    public  class SqlHelper
    {
        private static readonly string connStr = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
        public static DataTable GetDataTable(string sql, CommandType type, params SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(connStr))//创建连接对象
            {
                //创建适配器对象
                using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conn))
                {
                    if (paras != null)
                    {
                        adapter.SelectCommand.Parameters.AddRange(paras);//添加参数
                    }
                    adapter.SelectCommand.CommandType = type;
                    DataTable dt = new DataTable();//构造数据表用于接收查询结果                   
                    adapter.Fill(dt);//执行结果，fill方法内部自动打开链接，不需要conn.open();   
                    adapter.SelectCommand.Parameters.Clear();//清空集合
                    return dt; //返回结果集       
                }
            }
        }

        public static int ExcuteNonQuery(string sql, CommandType type, params SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(connStr))//创建连接对象
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn)) //创建Command连接对象
                {
                    if (paras != null)
                    {
                        cmd.Parameters.AddRange(paras);//添加参数
                    }
                    cmd.CommandType = type;
                    conn.Open();//打开链接 
                    int n = cmd.ExecuteNonQuery(); //执行命令并返回受影响的行数  
                    cmd.Parameters.Clear();
                    return n;
                }

            }
        }



        public static object ExecuteScalar(string sql, CommandType type, params SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(connStr))//创建连接对象
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn)) //创建Command连接对象
                {
                    if (paras != null)
                    {
                        cmd.Parameters.AddRange(paras);//添加参数
                    }
                    cmd.CommandType = type;
                    conn.Open();//打开链接                    
                    return cmd.ExecuteScalar(); ;
                }

            }
        }
    }
}

关于存储过程的分享就先到这里，希望本篇博客对您有所帮助，本篇博客将会持续更新！