Mysql 存储过程以及在.net中的应用示例

一、存储过程概述

MySQL 5.0 版本开始支持存储过程。存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

优点

存储过程可封装，并隐藏复杂的商业逻辑。

存储过程可以回传值，并可以接受参数。

运行速度：对于复杂的业务逻辑，使用存储过程比较快，因为在存储过程创建的时候，数据库已经对其进行了编译，已经通过语法检查和性能优化，以后每次执行存储过程都不需要再重新编译。对于很简单的sql，存储过程没有什么优势。

可降低网络的通信量：不需要通过网络来传送很多的sql语句到数据库服务器。

缺点

移植性差：存储过程往往定制化于特定的数据库上，因为支持的编程语言不同，当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
SQL是一种结构化查询语言，本质上是过程化的语言。面对复杂的业务逻辑，过程化的处理会很吃力。同时SQL擅长的是数据查询而非业务逻辑的处理。
维护复杂：如果需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新调用等等，这时候估计会比较繁琐了。
开发调试复杂：由于IDE的问题，存储过程的开发调试要比一般程序困难。

总结

适当的使用存储过程，能够提高我们SQL查询的性能，但不应该大规模使用、滥用。
随着众多ORM 的出现，存储过程很多优势已经不明显。

二、存储过程的创建

存储过程就是具有名字的一段代码，用来完成一个特定的功能。
创建的存储过程保存在数据库的数据字典中。

存储过程的创建语法

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
 
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
 
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
 
routine_body:
　　Valid SQL routine statement
 
[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

先来创建一个数据库School和数据表user

mysql> create database school;
Query OK, 1 row affected

mysql> use school;
Database changed
mysql> create table user(
    -> Id int not null auto_increment,
    -> Name varchar(100) not null,
    -> Email varchar(100) not null,
    -> primary key (Id)
    -> )engine=innodb default charset=utf8;
Query OK, 0 rows affected
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| user             |
+------------------+
1 row in set

创建一个存储过程，往user表中插入数据

mysql> use school;
Database changed
mysql> delimiter $$ #将语句的结束符号从分号;临时改为$$（可自定义）
mysql> create procedure insert_user(in name varchar(100),in email varchar(100))
    -> begin
    -> insert into user(Name,Email) values(name,email);
    -> end$$
Query OK, 0 rows affected
mysql> delimiter ; #将语句的结束符号恢复为分号

调用存储过程

mysql> call insert_user('qxh','qxh@qq.com')
    -> ;
Query OK, 1 row affected
mysql>

查看效果：

存储过程概念解疑

DELIMITER的作用：在mysql执行过程中，遇到分号就执行了。存储过程是一个代码段，里面可能会加入分号，但是该分号不是结束符号。使用DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为两个$$，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释，即不会被执行。当我们创建完存储过程后，在通过命令 “delimiter ;”将语句的结束符号恢复为分号。

BEGIN .... END：是存储过程开始和结束符号，可嵌套使用，如下：

BEGIN
　　BEGIN
　　　　BEGIN
　　　　　　statements; 
　　　　END
　　END
END

为语句块贴标签：增强代码的可读性

label1: BEGIN
　　label2: BEGIN
　　　　label3: BEGIN
　　　　　　statements; 
　　　　END label3 ;
　　END label2;
END label1

使用call sp_name(参数)调用存储过程

mysql> call insert_user('qxh','qxh@qq.com') ;
Query OK, 1 row affected
mysql>

三、存储过程的参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：

CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量），尽量少用。

1、in 输入参数

mysql> use school;#指定数据库
Database changed
mysql> delimiter $$ #将语句的结束符号从分号;临时改为$$（可自定义）
mysql> create procedure in_param(in p_in int) #创建存储过程，可输入参数
    -> begin #存储过程开始
    ->     select p_in;
    ->     set p_in=2; #设置局部参数
    ->     select P_in;
    -> end$$ #存储过程结束
Query OK, 0 rows affected

mysql> delimiter ; #将语句的结束符号恢复为分号
mysql> set @p_in=1; #设置全局变量
Query OK, 0 rows affected

mysql> call in_param(@p_in);#调用存储过程
+------+
| p_in |
+------+
|    1 |
+------+
1 row in set
#接收传入的参数，所以输出1
+------+
| P_in |
+------+
|    2 |
+------+
1 row in set
#设置局部变量为2，所以输出2
Query OK, 0 rows affected

mysql> select @p_in;
+-------+ 
| @p_in | 
+-------+ 
|   1   | 
+-------+
#p_in 在存储过程中被修改，但并不影响 @p_in 的值，因为前者为局部变量、后者为全局变量。

2、out输出参数

mysql> use school;#指定数据库
Database changed
mysql> delimiter //   #将语句的结束符号从分号;临时改为//（可自定义）
mysql> create procedure out_param(out p_out int) #创建存储过程，可输出参数
    -> begin #存储过程开始
    ->     select p_out;
    ->     set p_out=2; #局部变量
    ->     select p_out;
    -> end// #存储过程结束
Query OK, 0 rows affected

mysql> delimiter ; #将语句的结束符号恢复为分号
mysql> set @p_out=1; #设置全局变量
Query OK, 0 rows affected

mysql> call out_param(@p_out); #调用存储过程
+-------+
| p_out |
+-------+
| NULL  |
+-------+
1 row in set
#因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null
+-------+
| p_out |
+-------+
|     2 |
+-------+
1 row in set
#调用了out_param存储过程，输出参数，改变了p_out变量的值
Query OK, 0 rows affected

mysql>

3、inout输入参数

mysql> use school;#指定数据库
Database changed

mysql> delimiter $$

mysql> create procedure inout_param(inout p_inout int)
    ->   begin
    ->     select p_inout;
    ->     set p_inout=2;
    ->     select p_inout;
    ->   end
    -> $$
mysql> delimiter ;
 
mysql> set @p_inout=1;
 
mysql> call inout_param(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+
 
+---------+
| p_inout |
+---------+
|       2 |
+---------+
 
mysql> select @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+
#调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量

4、注意：

如果过程没有参数，也必须在过程名后面写上小括号
确保参数的名字不等于列的名字，否则在过程体中，参数名被当做列名来处理

四、变量

变量定义

局部变量声明一定要放在存储过程体的开始：

DECLAREvariable_name [,variable_name...] datatype [DEFAULT value];

其中，datatype 为 MySQL 的数据类型，如: int, float, date,varchar(length)，例如：

DECLARE l_int int unsigned default 4000000;  
DECLARE l_numeric number(8,2) DEFAULT 9.95;  
DECLARE l_date date DEFAULT '1999-12-31';  
DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';  
DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';

变量赋值

SET 变量名 = 表达式值 [,variable_name = expression ...]

用户变量

在MySQL客户端使用用户变量：

mysql > SELECT 'Hello World' into @x;  
mysql > SELECT @x;  
+-------------+  
|   @x        |  
+-------------+  
| Hello World |  
+-------------+  
mysql > SET @y='Goodbye Cruel World';  
mysql > SELECT @y;  
+---------------------+  
|     @y              |  
+---------------------+  
| Goodbye Cruel World |  
+---------------------+  
 
mysql > SET @z=1+2+3;  
mysql > SELECT @z;  
+------+  
| @z   |  
+------+  
|  6   |  
+------+

用户变量名一般以@开头
滥用用户变量会导致程序难以理解及管理

五、注释

MySQL 存储过程可使用两种风格的注释

两个横杆--：该风格一般用于单行注释。
c 风格：/* 注释内容 */ 一般用于多行注释。

六、存储过程中常用的命令

MySQL存储过程的调用

用call和你过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输入输出参数。

MySQL存储过程的查询

show databases;--展示所有的数据库

mysql> use school;
Database changed
mysql> show tables;--展示某库中所有数据表

mysql> SHOW PROCEDURE STATUS WHERE db='school';--school数据库中的所有存储过程

MySQL存储过程的修改

ALTER PROCEDURE 存储过程名 [ 特征 ... ]

MySQL存储过程的删除

删除一个存储过程比较简单，和删除表一样：

DROP PROCEDURE  IF EXISTS insert_user;

从 MySQL 的表格中删除一个或多个存储过程。

MySQL存储过程的控制语句

变量作用域

内部的变量在其作用域范围内享有更高的优先权，当执行到 end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储过程外再也不能找到这个申明的变量，但是你可以通过 out 参数或者将其值指派给会话变量来保存其值。

条件语句

if-then-else 语句
case语句

循环语句

while ···· end while
repeat···· end repeat
loop ·····endloop

LABLES 标号

标号可以用在 begin repeat while 或者 loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。

ITERATE迭代

ITERATE 通过引用复合语句的标号,来从新开始复合语句

七、NET中使用存储过程

先定义一个数据表base_user，如下：

创建一个存储过程procBaseUserInsert，用于往base_user中插入数据。创建存储过程的SQL如下：

CREATE PROCEDURE procBaseUserInsert(
    _guid varchar(200),
    _userName varchar(200),
    _email varchar(200),
    _password varchar(1020),
    _createTime datetime,
    _createUserName varchar(75),
    _updateTime datetime,
    _updateUserName varchar(75)
)
begin
    INSERT INTO base_user (
            Guid,
            UserName,
            Email,
            Password,
            CreateTime,
            CreateUserName,
            UpdateTime,
            UpdateUserName
    ) VALUES (
        _guid,
        _userName,
        _email,
        _password,
        _createTime,
        _createUserName,
        _updateTime,
        _updateUserName
);
END

接下来，在ADO.NET中使用：

数据表对应的实体如下：

//===================================================================  
// FileName: base_user.cs 
// Author  : qiuxianhu 
// Description: 实体(Entity) 
// CreateTime: 2020/11/18 16:57:36
//===================================================================
using System;
namespace Qxh.DB.MySql.Entity
{
    /// <summary>
    /// 描述:base_user实体信息   
    /// </summary>
    [Serializable] 
    public partial class Base_user
    {    
        #region 常量
        public const string _BASE_USER_="base_user";
        public const string _GUID_="Guid";
        public const string _USERNAME_="UserName";
        public const string _EMAIL_="Email";
        public const string _PASSWORD_="Password";
        public const string _CREATETIME_="CreateTime";
        public const string _CREATEUSERNAME_="CreateUserName";
        public const string _UPDATETIME_="UpdateTime";
        public const string _UPDATEUSERNAME_="UpdateUserName";
        #endregion
        
        #region 构造函数
        /// <summary>
        /// base_user
        /// </summary>
        public Base_user()
        {
        }        
        #endregion
        
        #region 公共属性
        
        ///<summary>
        ///Guid 分布式
        ///</summary>
        public string Guid{get;set;}
        ///<summary>
        ///用户名
        ///</summary>
        public string UserName{get;set;}
        ///<summary>
        ///邮件
        ///</summary>
        public string Email{get;set;}
        ///<summary>
        ///密码
        ///</summary>
        public string Password{get;set;}
        ///<summary>
        ///创建时间
        ///</summary>
        public DateTime? CreateTime{get;set;}
        ///<summary>
        ///创建人
        ///</summary>
        public string CreateUserName{get;set;}
        ///<summary>
        ///更新时间
        ///</summary>
        public DateTime? UpdateTime{get;set;}
        ///<summary>
        ///更新人
        ///</summary>
        public string UpdateUserName{get;set;}
        #endregion
    }
}

View Code

逻辑层代码如下：

var result = Base_userBLL.Insert(new Base_user
{
　　Guid = "1232342431234",
　　UserName = "qxh",
　　Password = "12323",
　　Email = "q@q",
　　CreateTime = DateTime.Now,
　　CreateUserName = "qxh",
　　UpdateTime = DateTime.Now,
　　UpdateUserName = "qxh"
});

数据层代码如下：

/// <summary>
/// 向数据库中插入一条新记录
/// </summary>
/// <parameterseterseters name="baseUser">Base_user实体对象</parameterseterseters>
/// <returns>-1表示没有执行数据库操作；>0表示执行数据库操作成功；=0表示执行数据库操作失败</returns>
public int Insert(Base_user baseUser)
{
　　int errorCode = -1;
　　const string SQL_COMMAND = "procBaseUserInsert";
　　MySqlParameter[] parameters = {
　　　　new MySqlParameter("_guid", MySqlDbType.VarChar,200),
　　　　new MySqlParameter("_userName", MySqlDbType.VarChar,200),
　　　　new MySqlParameter("_email", MySqlDbType.VarChar,200),
　　　　new MySqlParameter("_password", MySqlDbType.VarChar,1020),
　　　　new MySqlParameter("_createTime", MySqlDbType.DateTime,0),
　　　　new MySqlParameter("_createUserName", MySqlDbType.VarChar,75),
　　　　new MySqlParameter("_updateTime", MySqlDbType.DateTime,0),
　　　　new MySqlParameter("_updateUserName", MySqlDbType.VarChar,75)
　　};
　　parameters[0].Value = baseUser.Guid;
　　parameters[1].Value = baseUser.UserName;
　　parameters[2].Value = baseUser.Email;
　　parameters[3].Value = baseUser.Password;
　　parameters[4].Value = baseUser.CreateTime;
　　parameters[5].Value = baseUser.CreateUserName;
　　parameters[6].Value = baseUser.UpdateTime;
　　parameters[7].Value = baseUser.UpdateUserName;
　　errorCode = MySqlHelper.ExecuteNonQuery(Conn.SqlConn, CommandType.StoredProcedure, SQL_COMMAND, parameters);
}

如果你有现成的MySqlHelper文件，就可以不用看下面的代码了。MySqlHelper中ExecuteNonQuery方法体：

public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params DbParameter[] commandParameters)
{
　　//create & open a MySqlConnection, and dispose of it after we are done.
　　using (MySqlConnection cn = new MySqlConnection(connectionString))
　　{
　　　　cn.Open();

　　　　　MySqlCommand cmd = new MySqlCommand();
　　　　 PrepareCommand(cmd, connection, (DbTransaction)null, commandType, commandText, commandParameters);　　　　

　　　　 int retval = cmd.ExecuteNonQuery();　　　

　　　　 cmd.Parameters.Clear();
　　　　 return retval;

}
}

然后再看一下PrepareCommand方法体：

private static void PrepareCommand(MySqlCommand command, DbConnection connection, DbTransaction transaction, CommandType commandType, string commandText, DbParameter[] commandParameters)
{
　　//if the provided connection is not open, we will open it
   if (connection.State != ConnectionState.Open)
   {
   　　connection.Open();
   }

   //associate the connection with the command
   command.Connection = connection as MySqlConnection;

   //set the command text (stored procedure name or SQL statement)
   command.CommandText = commandText;

   //command.CommandTimeout = connection.ConnectionTimeout;

   //if we were provided a transaction, assign it.
   if (transaction is MySqlTransaction)
   {
   　　command.Transaction = transaction as MySqlTransaction;
   }

   //set the command type
   command.CommandType = commandType;

   //attach the command parameters if they are provided
   if (commandParameters != null)
   {
      AttachParameters(command, commandParameters);
   }

      return;
}