Image for post
Image for post

Advanced Stored Procedures In MySQL

Peter Lafferty
May 4, 2017 · 5 min read

This time around I’m going to cover reading information from tables and manipulating it. Previously I’ve covered basic usage and using loops. For this article I’ll go over EXISTS, SELECT INTO and CURSORS.

The EXISTS subqueries simply check if a query returns any rows and evaluates to true if it does. Use it to check if data is present in other tables.

IF NOT EXISTS(SELECT * FROM People) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'OMG PANIC';
END IF;

The SELECT INTO allows for columns to be selected from a table in to variables. The select will need to return one row and the variables being selected “in to” need to be already declared.

SELECT id, name FROM Profile WHERE id = 10 INTO v_id, v_name;

You might want to declare a continue handler for SELECT INTO when nothing is found. Although I prefer to use the EXISTS to check for the presence of a row and the SELECT INTO when I know something already exists and fail if it doesn’t exist.

In order to iterate over a result a CURSOR can be used. Cursors are a little bit fiddly and take a bit of care and patience. They follow a similar format to SELECT INTO but need to be declared in advance, opened, fetched and closed.

They also need some handlers to deal with the end of the cursor. They are read only and are traversable in one direction. A basic CURSOR would look something like this:

BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE p_id INT;
DECLARE p_name CHAR(24);
DECLARE cursorForProfile CURSOR FOR SELECT id, name FROM Profile;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cursorForProfile;

There’s a lot going on in this code. First is the order of how everything is declared. VARIABLES, CURSORS then HANDLERS. That order is set. The CONTINUE HANDLER is fired when the cursor comes to the end of the result set. It sets the done variable to true and then the done variable is used to break out of the loop.

The cursor is made active with OPEN, finished with CLOSE and read from using FETCH INTO x, y.

For a working example I’m going to write a contrived procedure that updates days of availability for a hotel. Hotels have rooms and rooms have capacities. On specific days there is a certain amount of rooms available to sell. This procedure will add availability for all the rooms in a hotel for one day.

The schema (available on gist) looks a little something like this:

Image for post
Image for post

The sproc is going to check if there are rooms present, if there are rooms present it’s going to read them from a cursor and insert in to the availability table for the given date.

The basic sproc looks something like this:

CREATE PROCEDURE `addAvailability`(
IN p_idHotel INT,
IN p_date DATE
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
ROLLBACK;
RESIGNAL;
END;

IF NOT EXISTS(SELECT * FROM Room WHERE idHotel = p_idHotel) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No Rooms';
END IF;
END

Then adding in the cursor and the loop looks like this:

CREATE PROCEDURE `addAvailability`(
IN p_idHotel INT,
IN p_date DATE
)
BEGIN
#variables
DECLARE v_done INT DEFAULT FALSE;
DECLARE v_idRoom INT;
DECLARE v_maxAvailable INT;
DECLARE v_price DECIMAL(10, 2);

#cursors
DECLARE cursorForRoom CURSOR FOR
SELECT
idRoom,
maxAvailable,
price
FROM
Room
WHERE
idHotel = p_idHotel;

#handlers
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
ROLLBACK;
RESIGNAL;
END;

IF NOT EXISTS(SELECT * FROM Room WHERE idHotel = p_idHotel) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No Rooms';
END IF;

Finally adding the insert into the sproc looks like this:

CREATE PROCEDURE `addAvailability`(
IN p_idHotel INT,
IN p_date DATE
)
BEGIN
#variables
DECLARE v_done INT DEFAULT FALSE;
DECLARE v_idRoom INT;
DECLARE v_maxAvailable INT;
DECLARE v_price DECIMAL(10, 2);

#cursors
DECLARE cursorForRoom CURSOR FOR
SELECT
idRoom,
maxAvailable,
price
FROM
Room
WHERE
idHotel = p_idHotel;

#handlers
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
ROLLBACK;
RESIGNAL;
END;

IF NOT EXISTS(SELECT * FROM Room WHERE idHotel = p_idHotel) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No Rooms';
END IF;

OPEN cursorForRoom;
START TRANSACTION;

read_loop: LOOP
FETCH cursorForRoom INTO v_idRoom, v_maxAvailable, v_price;
IF v_done THEN
LEAVE read_loop;
END IF;

INSERT INTO
Availability
SET
idAvailability = NULL,
idRoom = v_idRoom,
numberAvailable = v_maxAvailable,
price = v_price,
date = p_date;
END LOOP;

COMMIT;
CLOSE cursorForRoom;
END

Putting that in to practice looks like this:

mysql> CALL addAvailability(1, '2017-12-10');
ERROR 1644 (45000): No Rooms

That covers off advanced sprocs in MySQL.


Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store