CREATE PROCEDURE - Statement to Create Stored Procedures

MySQL Tutorials - Herong's Tutorial Examples

∟CREATE PROCEDURE - Statement to Create Stored Procedures

This section provides tutorial examples on how to use CREATE PROCEDURE statements to create stored procedures.

MySQL also offers an extension 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 delimiters 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)