5.5 创建和调用存储过程_MySQL 连接器/NET 开发人员指南

MySQL 连接器/NET 开发人员指南 / 第 5 章连接器/NET 编程 / 5.5 创建和调用存储过程

5.5 创建和调用存储过程

存储过程是一组存储在服务器中的 SQL 语句。客户端对存储过程进行一次调用，传递可以影响过程逻辑和查询条件的参数，而不是发出单独的硬编码 SQL 语句。

存储过程在以下情况下特别有用：

存储过程可以充当 API 或抽象层，允许多个客户端应用程序执行相同的数据库操作。应用程序可以用不同的语言编写并在不同的平台上运行。应用程序不需要硬编码表名和列名、复杂的查询等。当您扩展和优化存储过程中的查询时，所有调用该过程的应用程序都会自动受益。
当安全性至关重要时，存储过程会阻止应用程序直接操作表，甚至无法了解表名和列名等详细信息。例如，银行对所有常见操作都使用存储过程。这提供了一致且安全的环境，并且程序可以确保正确记录每个操作。在这样的设置中，应用程序和用户无法直接访问数据库表，只能执行特定的存储过程。

本节不提供有关创建存储过程的深入信息。有关此类信息，请参阅使用存储例程。

创建存储过程

可以使用多种工具创建 MySQL 中的存储过程，例如：

mysql命令行客户端
MySQL 工作台
MySqlCommand对象 _

MySqlCommand与命令行和 GUI 客户端不同，您在使用该类在 Connector/NET 中创建存储过程时不需要指定特殊的分隔符。例如，要创建名为的存储过程 add_emp，请使用 CommandText具有默认命令类型（SQL 文本命令）的属性在与服务器建立连接的命令的上下文中执行每个单独的 SQL 语句。

cmd.CommandText = "DROP PROCEDURE IF EXISTS add_emp";
cmd.ExecuteNonQuery();
cmd.CommandText = "DROP TABLE IF EXISTS emp";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE emp ( +
                  "empno INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(20)," + 
                  "last_name VARCHAR(20), birthdate DATE)";
cmd.ExecuteNonQuery();

cmd.CommandText = "CREATE PROCEDURE add_emp(" +
                  "IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME, OUT empno INT)" +
                  "BEGIN INSERT INTO emp(first_name, last_name, birthdate) " +
                  "VALUES(fname, lname, DATE(bday)); SET empno = LAST_INSERT_ID(); END";
cmd.ExecuteNonQuery();

访问存储过程

命名存储过程后，为存储过程中 MySqlCommand的每个参数定义一个参数。IN参数是用参数名称和包含值的对象OUT定义的，参数是用参数名称和预期返回的数据类型定义的。所有参数都需要定义参数方向。

要使用 Connector/NET 调用存储过程，您可以创建一个 MySqlCommand对象并将存储过程名称作为CommandText属性传递。然后将CommandType属性设置为 CommandType.StoredProcedure。定义参数后，您可以使用 MySqlCommand.ExecuteNonQuery()方法调用存储过程。

cmd.CommandText = "add_emp";
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@lname", "Jones");
cmd.Parameters["@lname"].Direction = ParameterDirection.Input;

cmd.Parameters.AddWithValue("@fname", "Tom");
cmd.Parameters["@fname"].Direction = ParameterDirection.Input;

cmd.Parameters.AddWithValue("@bday", "1940-06-07");
cmd.Parameters["@bday"].Direction = ParameterDirection.Input;

cmd.Parameters.Add("@empno", MySqlDbType.Int32);
cmd.Parameters["@empno"].Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();

Connector/NET 支持通过 MySqlCommand对象调用存储过程。可以通过使用 MySqlCommand.Parameters集合将数据传入和传出 MySQL 存储过程。

笔记

调用存储过程时（MySQL 8.0 发布系列之前的版本），命令对象会额外 SELECT调用一次以确定存储过程的参数。您必须确保调用该过程的用户对该表具有 SELECT权限， mysql.proc以使他们能够验证参数。不这样做会导致在调用该过程时出错。

调用存储过程后，可以使用集合的.Value属性检索输出参数的值。MySqlCommand.Parameters

Console.WriteLine("Employee number: "+cmd.Parameters["@empno"].Value);
Console.WriteLine("Birthday: " + cmd.Parameters["@bday"].Value);

笔记

当使用调用存储过程 MySqlCommand.ExecuteReader，并且存储过程有输出参数时，只有在MySqlDataReader返回的 by ExecuteReader关闭后才设置输出参数。

存储过程代码示例

以下 C# 代码示例演示了存储过程的使用。此示例假设预先创建了“employees”数据库：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace UsingStoredProcedures
{
    class Program
    {
        static void Main(string[] args)
        {
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "server=localhost;user=root;database=employees;port=3306;password=******";
            MySqlCommand cmd = new MySqlCommand();

            try
            {
                Console.WriteLine("Connecting to MySQL...");
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = "DROP PROCEDURE IF EXISTS add_emp";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "DROP TABLE IF EXISTS emp";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "CREATE TABLE emp (" +
                                  "empno INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY," + 
                                  "first_name VARCHAR(20), last_name VARCHAR(20), birthdate DATE)";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "CREATE PROCEDURE add_emp(" +
                                  "IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME, OUT empno INT)" +
                                  "BEGIN INSERT INTO emp(first_name, last_name, birthdate) " +
                                  "VALUES(fname, lname, DATE(bday)); SET empno = LAST_INSERT_ID(); END";

                cmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                Console.WriteLine ("Error " + ex.Number + " has occurred: " + ex.Message);
            }
            conn.Close();
            Console.WriteLine("Connection closed.");
            try
            {
                Console.WriteLine("Connecting to MySQL...");
                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "add_emp";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@lname", "Jones");
                cmd.Parameters["@lname"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@fname", "Tom");
                cmd.Parameters["@fname"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@bday", "1940-06-07");
                cmd.Parameters["@bday"].Direction = ParameterDirection.Input;

                cmd.Parameters.Add("@empno", MySqlDbType.Int32);
                cmd.Parameters["@empno"].Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();

                Console.WriteLine("Employee number: "+cmd.Parameters["@empno"].Value);
                Console.WriteLine("Birthday: " + cmd.Parameters["@bday"].Value);
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
            }
            conn.Close();
            Console.WriteLine("Done.");
        }
    }
}

下面的代码在 Visual Basic 中显示了相同的应用程序：

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text

Imports System.Data
Imports MySql.Data
Imports MySql.Data.MySqlClient

Module Module1

    Sub Main()
        Dim conn As New MySqlConnection()
        conn.ConnectionString = "server=localhost;user=root;database=world;port=3306;password=******"
        Dim cmd As New MySqlCommand()

        Try
            Console.WriteLine("Connecting to MySQL...")
            conn.Open()
            cmd.Connection = conn
            cmd.CommandText = "DROP PROCEDURE IF EXISTS add_emp"
            cmd.ExecuteNonQuery()
            cmd.CommandText = "DROP TABLE IF EXISTS emp"
            cmd.ExecuteNonQuery()
            cmd.CommandText = "CREATE TABLE emp (" & 
                              "empno INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                              "first_name VARCHAR(20), last_name VARCHAR(20), birthdate DATE)"
            cmd.ExecuteNonQuery()

            cmd.CommandText = "CREATE PROCEDURE add_emp(" & 
                              "IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME, OUT empno INT)" & 
                              "BEGIN INSERT INTO emp(first_name, last_name, birthdate) " & 
                              "VALUES(fname, lname, DATE(bday)); SET empno = LAST_INSERT_ID(); END"

            cmd.ExecuteNonQuery()
        Catch ex As MySqlException
            Console.WriteLine(("Error " & ex.Number & " has occurred: ") + ex.Message)
        End Try
        conn.Close()
        Console.WriteLine("Connection closed.")
        Try
            Console.WriteLine("Connecting to MySQL...")
            conn.Open()
            cmd.Connection = conn

            cmd.CommandText = "add_emp"
            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.AddWithValue("@lname", "Jones")
            cmd.Parameters("@lname").Direction = ParameterDirection.Input

            cmd.Parameters.AddWithValue("@fname", "Tom")
            cmd.Parameters("@fname").Direction = ParameterDirection.Input

            cmd.Parameters.AddWithValue("@bday", "1940-06-07")
            cmd.Parameters("@bday").Direction = ParameterDirection.Input

            cmd.Parameters.Add("@empno", MySqlDbType.Int32)
            cmd.Parameters("@empno").Direction = ParameterDirection.Output

            cmd.ExecuteNonQuery()

            Console.WriteLine("Employee number: " & cmd.Parameters("@empno").Value)
            Console.WriteLine("Birthday: " & cmd.Parameters("@bday").Value)
        Catch ex As MySql.Data.MySqlClient.MySqlException
            Console.WriteLine(("Error " & ex.Number & " has occurred: ") + ex.Message)
        End Try
        conn.Close()
        Console.WriteLine("Done.")

    End Sub

End Module