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;