MySql - Stored Procedures

[Admin]

What Is A Stored Procedures?

A stored procedure is an executable program that is stored in the database server waiting to be called for execution. A stored procedure should be written in a programming language that is supported by the database server. Some database servers offer special languages that are extended from the standard SQL. For example:

• PL/SQL - Stored procedure language for Oracle database server.
• Transact SQL - Stored procedure language for MS SQL server.

Store procedure languages usually offer you statements to:

• Receive and return data from the calling environment.
• Declare and use local variables.
• Execute regular SQL statements on the server.
• Loop through a set of statements multiple times.
• Conditionally control the execution flow.

Creating Stored Procedures in MySQL
MySQL also offers an extention of SQL to support stored procedures. The first statement for stored procedure is the CREATE PROCEDURE statement.
To create a stored procedure of a single statement, you should use the CREATE PROCEDURE statement with the procedure body statement enclosed at the end:
CREATE PROCEDURE proc_name() body_statement;To create a stored procedure of a group of statements, you should use the CREATE PROCEDURE statement with the procedure body statements enclosed in the "BEGIN ... END" block statement. In order to protect the statement delimites in the "BEGIN ... END", DELIMITER statements can be used to temporarily change the delimiter character:
DELIMITER '/';CREATE PROCEDURE proc_name() BEGIN body_statement; body_statement; ...END/DELIMITER ';'/To define parameters for the procedure, just list them in the parentheses next to the procedure name:
CREATE PROCEDURE proc_name(parameter, parameter, ...) body_statement;To execute a stored procedure, you should use the CALL statement:
CALL proc_name(expression, expression, ...);To remove a stored procedure from the server, you should the DROP statement:
DROP PROCEDURE proc_name;Here is a simple test on stored procedure the MySQL client tool:
mysql> USE test; mysql> DELIMITER '/';mysql> CREATE PROCEDURE Msg() -> BEGIN -> SELECT CURRENT_TIME() AS Time; -> SELECT 'Hello world!' As Message; -> END/Query OK, 0 rows affected (0.75 sec)mysql> DELIMITER ';'/mysql> CALL Msg();+----------+| Time |+----------+| 17:35:51 |+----------+1 row in set (0.07 sec)+--------------+| Message |+--------------+| Hello world! |+--------------+1 row in set (0.08 sec)Query OK, 0 rows affected (0.09 sec)Stored Procedure Parameters
A stored procedure parameter is play the same role as a function paramter in many other languages. It can be used to pass data in to and/or out of the stored procedure. Here is how to specify a parameter in the create procedure statement:
parameter_type parameter_name data_typewhere parameter_type can be one the following:

• IN - To indicate that this parameter is for input data only.
• OUT - To indicate that this parameter is for output data only.
• INOUT - To indicate that this parameter is for input and output data.
• If not specified, default to IN.

When calling a procedure that requires parameters, expressions must be provided in the calling statement:

• The number of expressions must be equal to the number of parameters.
• For an input type parameter, the resulting value of the expression will be passed into the procedure.
• For an output type parameter, the expression should be a variable where the procedure can store the output data.

Here is a sample SQL code creating and calling a stored procedure with parameters, ProcedureTest.sql:
-- ProcedureTest.sql-- Copyright (c) 2004 by Dr. Herong Yang--DROP DATABASE IF EXISTS Test;CREATE DATABASE Test;USE Test;DROP TABLE IF EXISTS Map;CREATE TABLE Map (C REAL, F REAL);--DROP PROCEDURE IF EXISTS C2F;DELIMITER '/';CREATE PROCEDURE C2F(IN C REAL, OUT F REAL)BEGIN INSERT INTO Map VALUES (C, 1.8*C+32.0); SELECT 1.8*C+32.0 INTO F;END/DELIMITER ';'/--CALL C2F(0.0, @F0);CALL C2F(1.0, @F1);CALL C2F(22.0, @F2);CALL C2F(40.0, 100.0);--SELECT 'Output values:' AS '---';SELECT @F0, @F1, @F2;SELECT 'C2F map table:' AS '---';SELECT * FROM Map;Output:
---Output values:@F0 @F1 @F232 33.8 71.6---C2F map table:C F0 321 33.822 71.640 104Observe that if you provide a data literal to an output type parameter, the output data will be stored no where.