MySQL - Stored Procedures

Part:   1   2 

This chapter describes:

• What is a stored procedure?
• How to create a stored procedure.
• How to pass data through parameters.

All notes in chapter are based MySQL 5.0 server.

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;

(Continued on next part...)

Part:   1   2