MySQL Stored Procedure

What is a Stored Procedure?
A store procedure is a SQL block which performs one or more specific task. A procedure has a header and a body. The header consists of name of the procedure and the parameters passed to the procedure. The body consists of declaration section and execution section.

The syntax for creating a Stored Procedure is:

CREATE
[DEFINER = { user | CURRENT_USER }]
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’
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }

routine_body:
Valid SQL routine statement

Note : The syntax within [] indicate they are optional.

Please refer following URL for the reference.

http://dev.mysql.com/doc/refman/5.6/en/create-procedure.html

Create the `employee` table in your database as follow.

CREATE TABLE IF NOT EXISTS `employee` (
`employee_id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(55) NOT NULL,
`last_name` varchar(55) NOT NULL,
`email_address` varchar(100) NOT NULL,
`location` varchar(55) NOT NULL,
PRIMARY KEY (`employee_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Now we will write the Stored Procedure `EmployeeActions` to insert and update the records from and to the database.

DELIMITER $$

CREATE PROCEDURE `EmployeeActions`(
IN paramAction TEXT,
IN paramEmployeeId INTEGER,
IN paramFirstName TEXT,
IN paramLastName TEXT,
IN paramEmailAddress TEXT,
IN paramLocation TEXT
)
BEGIN

DECLARE varAction TEXT ;

CASE paramAction
WHEN ‘New’ THEN
INSERT INTO
employee
(
`employee_id`,
`first_name`,
`last_name`,
`email_address`,
`location`
)
VALUES
(
NULL,
paramFirstName,
paramLastName,
paramEmailAddress,
paramLocation
);

WHEN ‘Update’ THEN
UPDATE
employee
SET
first_name = paramFirstName,
last_name = paramLastName,
email_address = paramEmailAddress,
location = paramLocation
WHERE
employee_id = paramEmployeeId;
END CASE;
END;

DELIMITER ;

Note: If you are using phpMyAdmin then please change the delimiter to $$.

Why Stored Procedure?

Stored Procedure are resides in the database and precomplied so any block of code can acess the procedure to insert and update the records. Since it is precomplied it will execute faster than the SQL query.

How to execute a Stored Procedure?

Now we are ready to insert and update the record to and from the database.

Insert :

call EmployeeActions(‘New’,”,’Tushar’,'Mahajan’,'tushar.iuselinux@gmail.com’,'Pune’);

Update :

call EmployeeActions(‘Update’,’1′,’Tushar’,'Mahajan’,'spiderphp@gmail.com’,'Pune’);

Advertisement