[+/-]
With the release of MySQL version 5 the MySQL server now supports stored procedures with the SQL 2003 stored procedure syntax.
A stored procedure is a set of SQL statements that can be stored in the server. Once this has been done, clients don't need to keep reissuing the individual statements but can refer to the stored procedure instead.
Stored procedures can be particularly useful in situations such as the following:
When multiple client applications are written in different languages or work on different platforms, but need to perform the same database operations.
When security is paramount. Banks, for example, use stored procedures for all common operations. This provides a consistent and secure environment, and procedures can ensure that each operation is properly logged. In such a setup, applications and users would not get any access to the database tables directly, but can only execute specific stored procedures.
Connector/NET supports the calling of stored procedures
through the MySqlCommand
object. Data can
be passed in and our of a MySQL stored procedure through use
of the MySqlCommand.Parameters
collection.
Note:
When you call a stored procedure, the command object makes
an additional SELECT
call to determine
the parameters of the stored procedure. You must ensure that
the user calling the procedure has the
SELECT
privilege on the
mysql.proc
table to enable them to verify
the parameters. Failure to do this will result in an error
when calling the procedure.
This section will not provide in-depth information on creating Stored Procedures. For such information, please refer to http://dev.mysql.com/doc/mysql/en/stored-procedures.html.
A sample application demonstrating how to use stored
procedures with Connector/NET can be found in the
Samples
directory of your Connector/NET
installation.
Stored procedures in MySQL can be created using a variety of
tools. First, stored procedures can be created using the
mysql command-line client. Second, stored
procedures can be created using the MySQL Query
Browser
GUI client. Finally, stored procedures can
be created using the .ExecuteNonQuery
method of the MySqlCommand
object:
Visual Basic Example
Dim conn As New MySqlConnection Dim cmd As New MySqlCommand conn.ConnectionString = "server=127.0.0.1;" _ & "uid=root;" _ & "pwd=12345;" _ & "database=test" Try conn.Open() cmd.Connection = conn 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 MessageBox.Show("Error " & ex.Number & " has occurred: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try
C# Example
MySql.Data.MySqlClient.MySqlConnection conn; MySql.Data.MySqlClient.MySqlCommand cmd; conn = new MySql.Data.MySqlClient.MySqlConnection(); cmd = new MySql.Data.MySqlClient.MySqlCommand(); conn.ConnectionString = "server=127.0.0.1;uid=root;" + "pwd=12345;database=test;"; try { conn.Open(); cmd.Connection = conn; 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 (MySql.Data.MySqlClient.MySqlException ex) { MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
It should be noted that, unlike the command-line and GUI clients, you are not required to specify a special delimiter when creating stored procedures in Connector/NET.
To call a stored procedure using Connector/NET, create a
MySqlCommand
object and pass the stored
procedure name as the .CommandText
property. Set the .CommandType
property to
CommandType.StoredProcedure
.
After the stored procedure is named, create one
MySqlCommand
parameter for every parameter
in the stored procedure. IN
parameters are
defined with the parameter name and the object containing the
value, OUT
parameters are defined with the
parameter name and the datatype that is expected to be
returned. All parameters need the parameter direction defined.
After defining parameters, call the stored procedure by using
the MySqlCommand.ExecuteNonQuery()
method:
Visual Basic Example
Dim conn As New MySqlConnection Dim cmd As New MySqlCommand conn.ConnectionString = "server=127.0.0.1;" _ & "uid=root;" _ & "pwd=12345;" _ & "database=test" Try conn.Open() cmd.Connection = conn cmd.CommandText = "add_emp" cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("?lname", 'Jones') cmd.Parameters["?lname"].Direction = ParameterDirection.Input cmd.Parameters.Add("?fname", 'Tom') cmd.Parameters["?fname"].Direction = ParameterDirection.Input cmd.Parameters.Add("?bday", #12/13/1977 2:17:36 PM#) cmd.Parameters["?bday"].Direction = ParameterDirection.Input cmd.Parameters.Add("?empno", MySqlDbType.Int32) cmd.Parameters["?empno"].Direction = ParameterDirection.Output cmd.ExecuteNonQuery() MessageBox.Show(cmd.Parameters["?empno"].Value) Catch ex As MySqlException MessageBox.Show("Error " & ex.Number & " has occurred: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try
C# Example
MySql.Data.MySqlClient.MySqlConnection conn; MySql.Data.MySqlClient.MySqlCommand cmd; conn = new MySql.Data.MySqlClient.MySqlConnection(); cmd = new MySql.Data.MySqlClient.MySqlCommand(); conn.ConnectionString = "server=127.0.0.1;uid=root;" + "pwd=12345;database=test;"; try { conn.Open(); cmd.Connection = conn; cmd.CommandText = "add_emp"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("?lname", "Jones"); cmd.Parameters["?lname"].Direction = ParameterDirection.Input; cmd.Parameters.Add("?fname", "Tom"); cmd.Parameters["?fname"].Direction = ParameterDirection.Input; cmd.Parameters.Add("?bday", DateTime.Parse("12/13/1977 2:17:36 PM")); cmd.Parameters["?bday"].Direction = ParameterDirection.Input; cmd.Parameters.Add("?empno", MySqlDbType.Int32); cmd.Parameters["?empno"].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); MessageBox.Show(cmd.Parameters["?empno"].Value); } catch (MySql.Data.MySqlClient.MySqlException ex) { MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
Once the stored procedure is called, the values of output
parameters can be retrieved by using the
.Value
property of the
MySqlConnector.Parameters
collection.
Ésta es una traducción del manual de referencia de MySQL, que puede encontrarse en dev.mysql.com. El manual de referencia original de MySQL está escrito en inglés, y esta traducción no necesariamente está tan actualizada como la versión original. Para cualquier sugerencia sobre la traducción y para señalar errores de cualquier tipo, no dude en dirigirse a mysql-es@vespito.com.