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 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 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)

Last update: 2015.

Table of Contents

 About This Book

 Introduction of SQL

 MySQL Introduction and Installation

 Introduction of MySQL Programs

 Perl Programs and MySQL Servers

 PHP Programs and MySQL Servers

 Java Programs and MySQL Servers

 Datatypes and Data Literals

 Operations and Expressions

 Character Strings and Bit Strings

 Commonly Used Functions

 Table Column Types for Different Types of Values

 Using DDL to Create Tables and Indexes

 Using DML to Insert, Update and Delete Records

 Using SELECT to Query Database

 Transaction Management and Isolation Levels

 Locks Used in MySQL

Defining and Calling Stored Procedures

 What Is a Stored Procedures?

CREATE PROCEDURE - Statement to Create Stored Procedures

 Stored Procedure Parameters

 Variables, Loops and Cursors Used in Stored Procedures

 Outdated Tutorials

 References

 PDF Printing Version