T-SQL:存储过程

什么是存储过程

存储过程—就像数据库中运行方法(函数)
和C#里的方法一样，由存储过程名/存储过程参数组成/可以有返回结果。
if else/while/变量/insert/select 等，都可以在存储过程中使用
优点：
执行速度更快 – 在数据库中保存的存储过程语句都是编译过的
允许模块化程序设计 – 类似方法的复用
提高系统安全性 – 防止SQL注入
减少网络流通量 – 只要传输存储过程的名称

语法

定义存储过程的语法

    CREATE  PROC[EDURE]  存储过程名 
    @参数1  数据类型 [= 默认值] [OUTPUT],
    @参数n  数据类型 [= 默认值] [OUTPUT]
    AS
      SQL语句

系统存储过程，由系统定义，存放在master数据库中，名称以“sp_”开头或”xp_”开头
自定义存储过程，由用户在自己的数据库中创建的存储过程，以”usp_”开头

参数说明

参数可选
参数分为输入参数、输出参数
输入参数允许有默认值

调用存储过程

exec  过程名  [参数]

调用无参数的存储过程：

exec usp_upGrade

调用有参数的存储过程：

调用有参数的存储过程有两种方法，一种是按次序调用，一种是按参数名

exec usp_upGrade 60,55 ---按次序
exec usp_upGrade @english=55,@math=60 --参数名
参数有默认值时：
exec usp_upGrade --都用默认值 
exec usp_upGrade 1  --第一个用默认值
exec usp_upGrade 1,5   --不用默认值

存储过程中使用输出参数
输出参数关键字：output

declare @a int
exec usp_pp @canshu= @a output
--如果是按次序调用可写成
exec usp_pp @a output
print @a

用C#操作存储过程

表的创建在上篇博客里T-SQL：事务
定义存储过程

create proc usp_Bank
@from char(4),
@to char(4),
@isSuccess int output,  -- 将要把数据返回出去
@money money = 100
as
begin
    begin transaction
    begin try
        update bank set balance=balance - @money where cid=@from;
        update bank set balance=balance + @money where cid=@to;
        commit transaction;
        set @isSuccess = 1;
    end try
    begin catch
        rollback transaction;
        set @isSuccess = 0;
    end catch
end

C#中的代码

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

namespace 存储过程
{
    class Program
    {
        static void Main(string[] args)
        {
            string connStr = @"server=.;database=MyFirstDB;uid=sa;pwd=157326";
            string sql = "usp_Bank";//1改成存储过程名

            Console.WriteLine("转多少钱");
            decimal countMoney = Convert.ToDecimal(Console.ReadLine());
            int isRes=-1;
            SqlParameter[] ps = {
                                    new SqlParameter("@from", "0001"),
                                    new SqlParameter("@to", "0002"),
                                    new SqlParameter("@money", countMoney),
                                    new SqlParameter("@isSuccess", isRes) {Direction=ParameterDirection.Output }//2.改方向
                                };

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;//3改CommandType的值
                    cmd.Parameters.AddRange(ps);
                    conn.Open();

                    cmd.ExecuteNonQuery();
                    isRes = (int)cmd.Parameters["@isSuccess"].Value;
                }
            }
            if (isRes == 0)
            {
                Console.WriteLine("转账失败");
            }
            else
            {
                Console.WriteLine("转账成功");
            }

            Console.ReadKey();
        }
    }
}

用sql语句和用存储过程只有三点不同
1.用存储过程名代替sql语句
2.设定ComandType为存储过程
3.如果有output参数，需要设置方向