MySQL Stored Procedure

Stored procedures are SQL statements that you can save into a database and reuse over and over again.

Syntax to create a stored procedure.

 DELIMITER //
 CREATE PROCEDURE procedureName()
   BEGIN
   SELECT *  FROM table_name;
   END //
 DELIMITER ;

To execute a store procedure, call its name.

CALL procedureName();

Advantages of Stored Procedures

Fast as stored procedures can be cached and the big queries can be reduced to one line of call for a stored procedure. The gain is from network traffic.
Portable as stored procedures can be migrated from SQL database to another.

Disadvantages of Stored Procedures

Debugging. Developers might not be familiar with stored procedures or might not know what the store procedures are querying.
Testing. Hard to test from an application developer point of view. The application developer needs to know stored procedures.

Stored Procedure Variables

Variables are local variables that hold values just for the execution of the stored procedure.

DECLARE variable_name datatype[INT,VARCHAR,DATETIME,...](size) DEFAULT default_value;

DECLARE total_count INT DEFAULT 0;
 
SELECT 
   COUNT(*) INTO total_count
FROM 
   table_name;

Variable scope
– variables that start with @ are session variables which go out of scope when the session end.
– variables go out of scope depending on where there are at.

Stored Procedure Parameters

Almost all stored procedures require parameters or input from the applications that call the stored procedures.

A stored procedure parameters have 3 modes – IN, OUT, INOUT

IN – parameters passed from the applications. IN parameters are protected which means that the stored procedure copies the values of the parameters and not modifies the real parameters.

DELIMITER //
CREATE PROCEDURE prodecureName(IN parameterName VARCHAR(255))
 BEGIN
 SELECT * 
 FROM table_name
 WHERE column_name = parameterName;
 END //
DELIMITER ;

CALL prodecureName('parameterName');

OUT – parameters passed as results to the applications.

DELIMITER $$
CREATE PROCEDURE procedureName(
 IN parameter1 VARCHAR(25),
 OUT parameterX INT)
BEGIN
 SELECT count(*)
 INTO parameter1
 FROM table_name
 WHERE parameter_name = parameter1;
END$$
DELIMITER ;

CALL procedureName('parameter1',@total);
SELECT @total;

INOUT – parameters can be changed and passed back to the calling applications.

DELIMITER $$
CREATE PROCEDURE add_name(INOUT name VARCHAR(25),IN newName VARCHAR(25))
BEGIN
 SET name = CONCAT(name," ",newName);
END$$
DELIMITER ;

SET @name = 'Folau'; - Folau
CALL add_name(@name,'Lisa'); -- Folau Lisa
CALL add_name(@name,'Kinga'); -- Folau Lisa Kinga
CALL add_name(@name,'Fusi'); -- Folau Lisa Kinga Fusi
SELECT @name; -- Folau Lisa Kinga Fusi

Return Multiple Values from Stored Procedures

DELIMITER $$
 
CREATE PROCEDURE getCounts(
 IN userType INT,
 OUT parameter1 INT,
 OUT parameter2 INT)
BEGIN
 -- new
 SELECT
            count(*) INTO parameter1
        FROM
            users
        WHERE
            user_type = userType
                AND status = 'new';
 
 -- deleted
 SELECT
            count(*) INTO parameter2
        FROM
            users
        WHERE
            user_type = userType
                AND status = 'deleted';
 
END

CALL getCounts('soldier', @new, @deleted);
SELECT @new, @deleted;

Sometimes you want to run updates using a store precedure and then drop it right after

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sale_update`()
    NO SQL
BEGIN 

DECLARE newPrice INT DEFAULT FALSE;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    SHOW ERRORS;
    ROLLBACK;
    END;
    
START TRANSACTION;

-- transaction start

INSERT INTO `sale`(`member_uuid`, `product_id`, `price`) VALUES ('mem_sadfljlweruoasd',  12, 250);

INSERT INTO `sale`(`member_uuid`, `product_id`, `price`) VALUES ('mem_sadfljlweruoasd',  23, 12);

-- transaction end

COMMIT WORK;

END$$
DELIMITER ;




-- RUN SP: sale_update
CALL sale_update();
-- DROP SP: sale_update
DROP PROCEDURE IF EXISTS sale_update;