Introduction to stored procedure

What is stored procedure?

A stored procedure is a piece of SQL statements stored inside the database server.

• Typically stored procedures help increase the performance of the applications. Once created, stored procedures are compiled and stored in the database. However MySQL stored procedures are compiled on demand. After compiling a stored procedure, MySQL puts it to a cache and maintains its own stored procedure cache for every single connection. If an application uses a stored procedure multiple times in a single connection, the compiled version is used, otherwise the stored procedure works like a query.
• Stored procedures reduces the traffic between application and database server because instead of sending multiple SQL statements, the application has to send only name and parameters of the stored procedure.
• Can be tested independent of the application.
• Limit direct access to tables via defined roles in the database.

Disadvantages

• Using a lot of stored procedures will consume lots of memory usage.
• It is difficult to debug stored procedures.

What are alternatives to Stored Procedures?

Because Stored Procedures are not always the perfect solution nor do they satisfy all the needs of all developers, other solutions exist that attempt to provide most of what a developer wants to do when accessing a database backend.
These include:

In-line or Parameterized Queries

These are written within the application code itself

Object Relational Mapping (ORM)

Provides an abstraction to the database without having to manually write data access classes. At this point, most all major platforms offer some form of ORM software, as illustrated at this site .

How to create a procedure in MySQL?

IN parameter

delimiter $$
Create PROCEDURE `getUsersByEmail`(
   IN email1 VARCHAR(215)
)
BEGIN
  SELECT name FROM tbl_user WHERE email = email1;
END $$
delimiter ;

call getUsersByEmail('aryan@gmail.com');

OUT parameter

DELIMITER $$
Create PROCEDURE `getUsersById`(
  IN id1 int(10),
  OUT name1 varchar(200))
BEGIN
  SELECT name INTO name1 FROM tbl_user WHERE id= id1;
END $$
DELIMITER ;

call getUsersById(14,@name);

select @name;

Delete/drop a procedure instead of alter

DROP PROCEDURE IF EXISTS `getUsersByEmail`;

delimiter $$
Create PROCEDURE `getUsersByEmail`(
   IN email1 VARCHAR(215)
)
BEGIN
  SELECT name FROM tbl_user WHERE email = email1;
END $$
delimiter ;

How to show all procedures created in a database?

SHOW PROCEDURE STATUS;

SHOW FUNCTION STATUS;

How to create a procedure which return multiple rows?

drop procedure if exists getusers;
delimiter $$
CREATE PROCEDURE `getUsers`()
BEGIN
    SELECT * FROM tbl_user limit 1,10;
END $$
delimiter ;

call getUsers();

Using PHP to call a stored procedure

create table

CREATE TABLE IF NOT EXISTS `tbl_user` (
`id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `contact` varchar(25) DEFAULT NULL,
  `status` tinyint(4) DEFAULT NULL,
  `modified` date DEFAULT NULL,
  `created` date DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `role` int(11) DEFAULT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ;

Insert some rows

INSERT INTO `tbl_user` (`id`, `name`, `email`, `password`, `contact`, `status`, `modified`, `created`, `image`, `role`)
VALUES
(1, 'aryan014', 'aryan@gmail.com', '2a$10$nU4', '999999999', NULL, '2014-10-27', '2014-10-19', NULL, 1),
(2, 'Aryan 022', 'aryan022@gmail.com', '2a$10$8BW5', NULL, NULL, '2014-10-19', '2014-10-19', 'profile.jpg', 6);

Now create procedure

DELIMITER $$
CREATE PROCEDURE getUserRole(
  in p_uId int(11),
  out p_uRole varchar(50)
)
BEGIN
DECLARE role_name varchar(50);
SELECT role INTO role_name FROM tbl_user WHERE id = p_uId;

IF (role_name=1) THEN
  SET p_uRole = 'SUPERADMIN';
ELSEIF (role_name <= 5 AND role_name >= 2) THEN
  SET p_uRole = 'ADMIN';
ELSEIF role_name < 10 THEN 
  SET p_uRole = 'USER';
END IF;
END$$

Now call with php & pdo

<?php 
$userId = 1;
try {
    $conn = new PDO("mysql:host=localhost;dbname=cakephp", "root", "admin");
    $sql = 'CALL getUserRole(:id,@role)';
    $stmt = $conn->prepare($sql);
 
    $stmt->bindParam(':id', $userId , PDO::PARAM_INT);
    $stmt->execute();
    $stmt->closeCursor();
    $r = $conn->query("SELECT @role AS role")->fetch(PDO::FETCH_ASSOC);
    if ($r) {
        echo sprintf('User #%d is %s', $userId, $r['role']);
    }
} catch (PDOException $e) {
    die("Error occurred:" . $e->getMessage());
}