MySQL :: MySQL Connector/NET 開發人員指南 :: 5.5 建立和呼叫預存程序

MySQL Connector/NET 開發人員指南 / Connector/NET 程式設計 / 建立和呼叫預存程序

5.5 建立和呼叫預存程序

預存程序是儲存在伺服器中的一組 SQL 語句。用戶端會對預存程序進行單一呼叫，傳遞可影響程序邏輯和查詢條件的參數，而不是發出個別的硬式編碼 SQL 語句。

在下列情況下，預存程序特別有用

預存程序可以作為 API 或抽象層，允許多個用戶端應用程式執行相同的資料庫操作。這些應用程式可以使用不同的語言編寫，並在不同的平台上執行。這些應用程式不需要硬式編碼表格和欄位名稱、複雜的查詢等等。當您擴充和最佳化預存程序中的查詢時，所有呼叫該程序的應用程式都會自動獲得好處。
當安全性至關重要時，預存程序可防止應用程式直接操作表格，甚至不知道表格和欄位名稱等詳細資訊。例如，銀行會將預存程序用於所有常見操作。這提供了一致且安全的環境，程序可以確保每個操作都已正確記錄。在這種設定下，應用程式和使用者不會直接存取資料庫表格，只能執行特定的預存程序。

本節不提供有關建立預存程序的深入資訊。如需相關資訊，請參閱使用預存常式。

建立預存程序

可以使用各種工具在 MySQL 中建立預存程序，例如

mysql 命令列用戶端
MySQL Workbench
MySqlCommand 物件

與命令列和 GUI 用戶端不同，在使用 MySqlCommand 類別在 Connector/NET 中建立預存程序時，您不需要指定特殊的分隔符號。例如，若要建立名為 add_emp 的預存程序，請使用具有預設命令類型 (SQL 文字命令) 的 CommandText 屬性，以在您的命令內容中執行每個 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 預存程序。

呼叫預存程序後，可以使用 MySqlCommand.Parameters 集合的 .Value 屬性來擷取輸出參數的值。

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

注意

當使用 MySqlCommand.ExecuteReader 呼叫預存程序，且預存程序具有輸出參數時，只有在關閉 ExecuteReader 傳回的 MySqlDataReader 後，才會設定輸出參數。

預存程序程式碼範例

下列 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