Introduction to Stored Procedures in MySQL

Handling MySQL Events with Triggers and Procedures Using SQL – Part 6

Division 5

Forward: In this part of the series, I introduce you to Stored Procedures in MySQL. We are dealing with MySQL 5.1.

By: Chrysanthus Date Published: 7 Aug 2012

Introduction

This is part 6 of my series, Handling MySQL Events with Triggers and Stored Procedures Using SQL. You must have read all the different parts of the series (or equivalent tutorials from elsewhere) up to this point. In this part of the series, I introduce you to Stored Procedures in MySQL. We are dealing with MySQL 5.1.

Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.

A Procedure
A trigger is an immediate response to an event. I trigger is called automatically from an update, insert or delete event in a table. A procedure is like a trigger, but it is not called automatically. It has more flexibility in its coding and can do more analysis than a trigger. A trigger can call a procedure to broaden its activity and power.

A procedure can return a result set (columns and rows) but I will not go into that in this series.

Procedure Privileges
To keep things simple, let the DBA code the procedure. If the procedure would access a table, let the user of the procedure have the privilege to access the table. I will address privileges and security in a separate series.

The Create Procedure Syntax
In simple terms the CREATE PROCEDURE Syntax is:

CREATE
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

type:
    Any valid MySQL data type

characteristic:
    COMMENT 'string'

routine_body:
    Valid SQL routine statement

Remember, anything in square brackets is optional. | means and/or in its position.

sp_name
The sp_name is the name of your choice that you give to the procedure.

proc_parameter
proc_parameter are parameters separated by commas; non is obligatory. The syntax of each parameter is:

    [ IN | OUT | INOUT ] param_name type

param_name is a name of your choice. type is a table column data type.

IN means the param_name represents a value that the procedure would receive from outside and use.
OUT means the param_name represents a value that can be read from outside the procedure. This value is normally given within the procedure after the procedure has done some analysis.
INOUT stands for both IN and OUT.

routine_body
A compound-statement is a BEGIN statement. routine_body is the compound statement.

Dropping a Procedure
A procedure can be dropped. In simple terms the syntax is:

    DROP PROCEDURE sp_name

Calling a Procedure
A trigger can call a procedure.  The syntax of the SQL Calling statement is:

    CALL sp_name([parameter[,...]])

We shall see examples of all these syntaxes in the following parts of the series.

Chrys

Broad Network

Related Articles

Introduction to Stored Procedures in MySQL

Handling MySQL Events with Triggers and Procedures Using SQL – Part 6

Division 5

Introduction

Related Links

Comments