T-SQL:存储过程

﹏ヽ暗。殇╰゛Y 2022-09-25 05:12 160阅读 0赞

# 什么是存储过程 #

存储过程—就像数据库中运行方法(函数)  
和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：事务][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参数，需要设置方向

[T-SQL]: http://blog.csdn.net/zsx157326/article/details/52197487