With the introduction of MySQL 5 comes a power that was before now were only available in more advanced SQL systems like PostGres. While I would like to claim mastery of PostGres, it is not a system that I have much experience with - mainly due to the nature of the open-source applications we use, and the availability of PostGres in most hosting environments.
With MySQL 5.x we now have the opportunity to issue a MySQL FUNCTION and MySQL PROCEDURE command that creates a "Code Base" that is lovingly stored in your database for future use (once it has been declared). I am writing this article to you because of how seemingly complicated the process has been to create a PROCEDURE. I have not created a FUNCTION yet, but they appear to be very similar. I would say 90% of the examples and discussions I found online were surrounding the PROCEDURE, and it most certainly can deliver a powerful feature set to your application. This example took nearly 4 hours to craft due to the strict rules and terminology requirements of the MySQL system. While strict rules are a great thing, it is unfortunate that the errors are for the most part, completely useless.
DROP PROCEDURE IF EXISTS loadQueue;
DELIMITER //
CREATE PROCEDURE loadQueue (
IN procSID INT,
IN maxCount INT,
OUT count INT,
OUT idChain TEXT,
OUT dateCheck TEXT,
OUT timeStart TIMESTAMP,
OUT timeStop TIMESTAMP
)
BEGIN
DECLARE queId INT(10) DEFAULT 0;
DECLARE lastId INT(10) DEFAULT 0;
DECLARE queRef CURSOR FOR
SELECT que.id
FROM queue AS que
LEFT JOIN process AS proc ON proc.queue_id = que.id
WHERE proc.id IS NULL
OR proc.created < dateCheck
ORDER BY que.created ASC;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET lastId = 1;
SET timeStart = NOW();
SET dateCheck = TIMESTAMPADD(MINUTE,-10,timeStart);
SET count = 0;
SET idChain = NULL;
OPEN queRef;
REPEAT
FETCH queRef INTO queId;
IF queId > 0 THEN
SET count = count + 1;
IF (idChain IS NULL) THEN
SET idChain = queId;
ELSE
SET idChain = CONCAT(idChain,',',queId);
END IF;
INSERT INTO `process`
(`queue_id`,`sid`,`created`)
VALUES (queId,procSID,NOW());
END IF;
UNTIL lastId OR count >= maxCount END REPEAT;
CLOSE queRef;
SET timeStop = NOW();
END;
//
DELIMITER ;
CALL loadQueue(
NULL,
10,
@count,
@idChain,
@dateCheck,
@timeStart,
@timeStop
);
SELECT
@count,
@idChain,
@dateCheck,
@timeStart,
@timeStop;
I can't tell you how utterly confusing the process has been. While it isn't that hard to review the code and follow the steps, it has simply been a tedious process trying to determine all the quirks of the process for building this type of procedure.
Starting from the top, we have:
DROP PROCEDURE IF EXISTS loadQueue;
This line will remove the procedure named loadQueue if it exists and just move forward without an error if it does not. The procedures you create will be stored in the ROUTINES table of your information_schema database, which you should find visible in most hosting environments.
The next line is very critical for some reason, and while I was able to get some examples to work without it, it seems that using this should be a standard practice.
DELIMITER //
In the place of the // you can (supposedly) use any character combination you wish. I read many examples where the developer used a pipe | or two ampersands && - of course these examples wouldn't run so I'm not sure ;-)
Make sure you close the // delimeter at the end of your procedure - not doing so will throw an error.
The next line is the most fun, where we start our PROCEDURE...
CREATE PROCEDURE loadQueue (
IN procSID INT,
IN maxCount INT,
OUT count INT,
OUT idChain TEXT,
OUT dateCheck TEXT,
OUT timeStart TIMESTAMP,
OUT timeStop TIMESTAMP
)
This part was a total nightmare. It took almost 20 minutes to figure out that the procedure won't compile if there isn't a () after the procedure name. This may seem obvious, but SO many examples simply show the line end with a semicolon - go figure...
CREATE PROCEDURE loadQueue ();
In this example, we are not bringing IN or sending OUT and data, as shown in the first example. Hopefully the variable list is clear, but the be more detailed I will add this. There are three Directional Flags for your variables. IN, OUT, and INOUT. In this example, we show the use of the first two. The final product passes a numeric value to the procedure, which is NOT allowed if you are using an INOUT directional call - which expects that you are passing a variable that can be written back too!!
Next you Start your Procedure with:
BEGIN
And go ahead and write your closing line:
END;
WITH THE SEMICOLON!
In between these you can start building your procedure IN A SPECIFIC ORDER. First you will make your DECLARE calls, which are what you use to create a new Variable for your procedure! Fun... an example:
DECLARE queId INT(10) UNSIGNED DEFAULT 0;
or
DECLARE myDate DATETIME DEFAULT NOW();
or
DECLARE nextWeek DATETIME DEFAULT TIMESTAMPADD(DAY,7,NOW());
or
DECLARE VARCHAR(32) IS NULL DEFAULT NULL;
Ok ok enough of that. Major Point - these MUST BE before anything else or you will get random silly errors that point you at the middle of the line and you wonder what is wrong a word like UNSIGNED!!
Next, we can start assigning values to our variables using the SET call. The SET call, and most other calls can be made in any order from this point forward. The most interesting to me are the LOOP, WHILE, and REPEAT features - since this is probably the only reason you want to use the PROCEDURE in the first place!! An example of looping:
DECLARE nuMartinis INT(10) UNSIGNED DEFAULT 0;
DECLARE fiveFingers INT(11) DEFAULT NULL;
sneakyLoop: LOOP
SET nuMartinis = nuMartinis + 1;
SET fiveFingers = NULL;
SELECT `fredsBalance` INTO fiveFingers FROM `myFruityPebbles` WHERE `fredsBalance` IS NOT NULL;
IF (nuMartinis > 6) THEN
INSERT INTO `fredsCalendar` (`activity`) VALUES (CONCAT('Meeting with Betty at ',TIMESTAMPADD(HOUR,1,NOW()),' after Barny falls asleep'));
LEAVE sneakyLoop;
ELSEIF (fiveFingers IS NOT NULL) THEN
IF fiveFingers > 0 THEN
INSERT INTO `wilmasWallet` (`credit`) VALUES (fiveFingers);
ELSEIF fiveFingers <= 0 THEN
UPDATE `fredsSexLife` SET `frequency` = `frequency` + ((fiveFingers-1) * 2);
END IF;
ELSE
INSERT INTO `fredsCalendar` (`activity`) VALUES (CONCAT('Meet with the Divorce Lawyer on ',TIMESTAMPADD(DAY,4,NOW())));
LEAVE sneakyLoop;
END IF;
END LOOP sneakyLoop;
As you can clearly see, looping is a very powerful tool and can be used to perform operations that would have taken a lot of nasty scripting in the past. Some things to point out about the example above.
1) sneakyLoop is the LOOP identifier, and is referenced when you want to LEAVE the loop, and at the END of the loop. ** A LOOP WILL NEVER STOP ** if you forget to use the LEAVE statement somewhere within the loop. I watched a table grow to 50k records before I finally figured out what was happening... Fun!! PS - if you aren't logged into a shell and run into this problem - rename one of the tables being queried - it will kill the procedure.
2) When comparing for NULL, you MUST use parenthesis around the statement. MySQL will allow you to forego the parenthesis if you are making a numeric comparison, but for some reason IS or IS NOT NULL gets read as too many items and the compiler barfs...
3) The INTO command is used instead of AS when you want to assign the value from the SELECT query to your DECLAREd variable.
4) MAKE SURE to clear your variables during each iteration of your LOOP ( SET fiveFingers = NULL ) since the value from the previous successful SELECT will not be overwritten if the SELECT returns no record(s).
5) There are No such things as ARRAYs in MySQL - the table is the array, which means that if you want to store data during your iterations, you will need to create a temporary table or stack the values in a delimited format in a TEXT or VARCHAR field (as in the first example). Unfortunately, if you want to return values from your procedure, you will need to either return the name of your temporary table (if it is not standard), or return that TEXT value of delimeted values. The @variable that you see in being used as the variable names for the procedure call are NOT arrays - they are just scalars... too bad Mr. Wall...
6) That's it - you probably stoped reading already anyhow...
To close your PROCEDURE, you will:
END;
//
DELIMITER ;
Remember the // which are your closing delimeters for the procedure block, and then the DELIMETER ; call resets the handler so that MySQL isn't looking for another // - this is important!!
At this point, you cacn submit the procedure to your SQL database and should receive a Query Success 0 rows returned. You can look in that schema table mentioned earlier to see that the procedure has been stored correctly.
Now you can call the function as many times as you want like this:
CALL loadQueue(
NULL,
10,
@count,
@idChain,
@dateCheck,
@timeStart,
@timeStop
);
SELECT
@count,
@idChain,
@dateCheck,
@timeStart,
@timeStop;
Where CALL functionName(); is the required minimum, and SELECT {something} is also required. If you did something like this:
CALL procName();
Without the SELECT you will receive an error saying NOTHING RETURNED!! Lame...
You can bypass this problem using:
SELECT NULL;
If your procedure is not returning anything of value.
Have fun with your new friend - the MySQL PROCEDURE!! Prepare to yell at the computer...
Godspeed
These are the DUMPS from the data I'm working with - you can play...
DROP PROCEDURE `loadQueue`//
CREATE DEFINER=`db`@`localhost` PROCEDURE `loadQueue`(
IN procSID INT,
IN maxCount INT,
OUT count INT,
OUT idChain TEXT,
OUT dateCheck TEXT,
OUT timeStart TIMESTAMP,
OUT timeStop TIMESTAMP
)
BEGIN
DECLARE queId INT(10) DEFAULT 0;
DECLARE lastId INT(10) DEFAULT 0;
DECLARE queRef CURSOR FOR
SELECT que.id
FROM queue AS que
LEFT JOIN process AS proc ON proc.queue_id = que.id
WHERE proc.id IS NULL
OR proc.created < dateCheck
ORDER BY que.created ASC;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET lastId = 1;
SET timeStart = NOW();
SET dateCheck = TIMESTAMPADD(MINUTE,-10,timeStart);
SET count = 0;
SET idChain = NULL;
OPEN queRef;
REPEAT
FETCH queRef INTO queId;
IF queId > 0 THEN
SET count = count + 1;
IF (idChain IS NULL) THEN
SET idChain = queId;
ELSE
SET idChain = CONCAT(idChain,',',queId);
END IF;
INSERT INTO `process`
(`queue_id`,`sid`,`created`)
VALUES (queId,procSID,NOW());
END IF;
UNTIL lastId OR count >= maxCount END REPEAT;
CLOSE queRef;
SET timeStop = NOW();
END
// ------------------------------------------
DROP PROCEDURE `queueDomains`//
CREATE DEFINER=`db`@`localhost` PROCEDURE `queueDomains`(
IN filterId INT,
OUT counter INT
)
BEGIN
DECLARE domId BIGINT(20) UNSIGNED DEFAULT 0;
DECLARE maxCount INT(10) DEFAULT 100;
SET counter = 0;
doLoop: LOOP
SELECT dom.id INTO domId
FROM `domain` AS dom
LEFT JOIN `queue` AS que ON que.dom_id = dom.id
WHERE que.id IS NULL
AND dom.active = 1
AND dom.check = 1
AND (
(filterId IS NULL) OR
(dom.id = filterID)
)
ORDER BY dom.checked ASC
LIMIT 1;
IF domId > 0 THEN
INSERT INTO `queue`
( `id`, `dom_id`, `created` ) VALUES
( NULL, domId, NOW() );
SET domId = NULL;
SET counter = counter + 1;
IF counter > maxCount THEN
LEAVE doLoop;
END IF;
ELSE
LEAVE doLoop;
END IF;
END LOOP doLoop;
END
