22.2.5.9. Доступ к Хранимым процедурам с Соединителем/Сетью

Версия 5 сервера MySQL и поддерживает хранимые процедуры с синтаксисом хранимой процедуры SQL 2003 года.

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

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

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

Соединитель/Сеть поддерживает вызов хранимых процедур через MySqlCommand объект. В данных можно передать и из хранимой процедуры MySQL посредством использования MySqlCommand.Parameters набор.

Отметить

Когда Вы вызываете хранимую процедуру, объект команды делает дополнительное SELECT вызовите, чтобы определить параметры хранимой процедуры. Следует гарантировать, что пользователь, вызывающий процедуру, имеет SELECT полномочие на mysql.proc таблица, чтобы позволить им проверить параметры. Отказ сделать это приведет к ошибке, вызывая процедуру.

Этот раздел не будет предоставлять всестороннюю информацию о создании Хранимых процедур. Для такой информации, пожалуйста, обратитесь к http://dev.mysql.com/doc/mysql/en/stored-routines.html.

Пример приложения, демонстрирующий, как использовать хранимые процедуры с Соединителем/Сетью, может быть найден в Samples каталог Вашей установки Соединителя/Сети.

22.2.5.9.1. Используя Сохраненные Подпрограммы от Соединителя/Сети

Хранимые процедуры в MySQL могут быть созданы, используя множество инструментов. Во-первых, хранимые процедуры могут быть созданы, используя mysql клиент командной строки. Во-вторых, хранимые процедуры могут быть созданы, используя MySQL Workbench. Наконец, хранимые процедуры могут быть созданы, используя .ExecuteNonQuery метод MySqlCommand объект.

В отличие от командной строки и клиентов GUI, Вы не обязаны определять специальный разделитель, создавая хранимые процедуры в Соединителе/Сети.

Чтобы вызвать хранимую процедуру, используя Соединитель/Сеть, Вы создаете a MySqlCommand возразите и передайте имя хранимой процедуры как .CommandText свойство. Вы тогда устанавливаете .CommandType свойство к CommandType.StoredProcedure.

После того, как хранимую процедуру называют, Вы создаете тот MySqlCommand параметр для каждого параметра в хранимой процедуре. IN параметры определяются с помощью названия параметра и объекта, содержащего значение, OUT параметры определяются с помощью названия параметра и типа данных, который, как ожидают, будет возвращен. Все параметры нуждаются в определенном направлении параметра.

После определения параметров Вы вызываете хранимую процедуру при использовании MySqlCommand.ExecuteNonQuery() метод.

Как только хранимую процедуру вызывают, значения выходных параметров могут быть получены при использовании .Value свойство MySqlConnector.Parameters набор.

Отметить

Когда хранимую процедуру вызывают, используя MySqlCommand.ExecuteReader, и хранимая процедура вывела параметры, выходные параметры только устанавливаются после MySqlDataReader возвращенный ExecuteReader закрывается.

Следующий C# пример кода демонстрирует использование хранимых процедур. Это предполагает, что база данных 'сотрудники' была уже создана:

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using MySql.Data;using MySql.Data.MySqlClient;namespace UsingStoredRoutines{    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.AddWithValue("@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 SystemImports System.Collections.GenericImports System.LinqImports System.TextImports System.DataImports MySql.DataImports MySql.Data.MySqlClientModule 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.AddWithValue("@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 SubEnd Module