5.5 Creating and Calling Stored Procedures — MySQL Connectors 1.0

5.5 Создание и вызов хранимых процедур

Хранимая процедура — это набор SQL-команд, хранящихся на сервере. Клиенты выполняют один вызов хранимой процедуры, передавая параметры, которые могут повлиять на логику процедуры и условия запроса, вместо того, чтобы выдавать отдельные жёстко закодированные SQL-команды.

Хранимые процедуры могут быть особенно полезны в следующих ситуациях:

Хранимые процедуры могут выполнять роль API или абстракционного слоя, позволяя нескольким клиентским приложениям выполнять одни и те же операции с базой данных. Приложения могут быть написаны на разных языках и работать на разных платформах. Приложениям не нужно жёстко кодировать имена таблиц и столбцов, сложные запросы и т. д. При расширении и оптимизации запросов в хранимой процедуре все приложения, которые обращаются к процедуре, автоматически получают выгоду.
Когда безопасность имеет первостепенное значение, хранимые процедуры препятствуют приложениям непосредственному манипулированию таблицами или даже получению информации, такой как имена таблиц и столбцов. Например, банки используют хранимые процедуры для всех распространённых операций. Это обеспечивает согласованную и безопасную среду, а процедуры могут гарантировать, что каждая операция должным образом регистрируется. В такой настройке приложения и пользователи не получают прямого доступа к таблицам базы данных, а могут только выполнять определённые хранимые процедуры.

В данном разделе не приводится подробная информация о создании хранимых процедур. Для получения такой информации см. .

Создание хранимой процедуры

Хранимые процедуры в MySQL могут быть созданы с помощью различных инструментов, таких как:

Командная строка mysql
MySQL Workbench
Объект MySqlCommand

В отличие от командной строки и графических клиентов, при создании хранимых процедур в Connector/NET с использованием класса MySqlCommand не требуется указывать специальный разделитель. Например, для создания хранимой процедуры с именем 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. Данные могут передаваться в и из хранимой процедуры MySQL с помощью коллекции MySqlCommand.Parameters.

После вызова хранимой процедуры значения выходных параметров можно получить, используя свойство .Value коллекции MySqlCommand.Parameters.

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

Примечание

При вызове хранимой процедуры с помощью MySqlCommand.ExecuteReader, если хранимая процедура имеет выходные параметры, выходные параметры устанавливаются только после того, как объект MySqlDataReader, возвращённый методом 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