Stored Procedures and Functions are a biggy on the MySQL Developer exam. They account for 20% of the marks of the second paper. And no wonder, there's a lot of new functionality (and syntax) to keep in mind. So with that in mind, let's put a procedure together.
Suppose you want to see the all of Genesis Cap 1 together. We can use group_concat() to squidge all the verses in Genesis 1 together on one line.
You might find that your Genesis was cut short. This is because the system variable group_concat_max_len is probably set too small, the default is a flimsy 1K. Can't fit a whole lot of bible in a kilobyte.
Let's set it to something bigger, say 10K. Should be enough to handle most chapters.
Now when you run the select above, you should see all of the chapter.
*************************** 1. row *************************** genesis: In the beginning God created the heaven and the earth. And the earth was without form, and void; and darkness was upon the face of the deep. And the Spirit of God moved upon the face of the waters. And God said, Let there be light: and there was light. And God saw the light, that it was good: and God divided the light from the darkness. And God called the light Day... [CUT SHORT TO FIT ON PAGE]
So what if you want to see a RANDOM chapter from the bible. We could just ask for a random line, remember the book and cap, then plug them into our query above.
SELECT GROUP_CONCAT(line ORDER BY verse SEPARATOR ' ') as genesis
FROM bible
WHERE book = 'Lev'
AND cap = 13 \G
But what a lot of hard work that is. How about we marry the two queries together in one glorious whole. The idea is that we join our outer query to the random selection query. So the join is not to a real table, but to a table produced by a subquery. We'll need to give both tables aliases for this to work. Quite sneaky, I know.
Now, that's way too much to type every time you want to see a random chapter. So now let's wrap it up in a procedure. It will give us a named book/chapter if you specify, otherwise a random one.
Note how we have to tell the mysql client to use a delimiter other than the standard semicolon, otherwise we would never be able to enter our procedure.
delimiter bye_nya
CREATE PROCEDURE bible_cap(book_var VARCHAR(8), cap_var INT UNSIGNED)
SQL SECURITY definer
COMMENT 'Prints book/cap unless book_var is null in which case random'
BEGIN
IF book_var IS NOT NULL THEN
SELECT bk.name, cap,
GROUP_CONCAT(line ORDER BY verse SEPARATOR ' ') as TEXT
FROM bible as bb
INNER JOIN books bk ON bb.book = bk.abbr
WHERE book = book_var
AND cap = cap_var
GROUP BY book,cap ;
ELSE
SELECT bk.name, b1.cap,
GROUP_CONCAT(b1.line ORDER BY verse SEPARATOR ' ') as TEXT
FROM bible as b1
INNER JOIN
(SELECT book, cap FROM bible ORDER BY RAND() LIMIT 1) as b2
ON b1.book=b2.book AND b1.cap=b2.cap
INNER JOIN books as bk ON b1.book = bk.abbr
GROUP BY b1.book, b1.cap ;
END IF;
END;
bye_nya
delimiter ;
(By the way, I notice on Linux and Windows I can get away with long comments, but when I tested it on my Mac the procedure failed if the comment was > 64 chars. Maybe there's a system variable for this, I don't know).
So that defines are procedure. All we need to do now is to test it. First we ask it to print Genesis, cap 1.
mysql> CALL bible_cap('Ge',1) \G
And now with null arguments we should get a random chapter.
mysql> CALL bible_cap(NULL, NULL) \G
Now let's go for one more enhancement. If you call the procedure with a book, but leave the chapter as NULL, you get a random chapter from that book. Similarly if you call it with a NULL book, but give a chapter number, you get a random book with that chapter. The only clever stuff is making sure if a user passes in chapter 40, say, we randomly choose a book with at least 40 chapters. An error message shows if a way-too high chapter is asked for. No books in the bible have more than 150 chapters. Thank God.
CREATE PROCEDURE bible_cap(book_var VARCHAR(8), cap_var INT UNSIGNED)
SQL SECURITY definer
COMMENT 'Prints named book/cap. Null params mean random selection'
BEGIN
IF book_var IS NULL AND cap_var IS NULL THEN /* Do the whole thing randomly */
SELECT bk.name, b1.cap,
GROUP_CONCAT(b1.line ORDER BY verse SEPARATOR ' ') as TEXT
FROM bible as b1
INNER JOIN
(SELECT book, cap FROM bible ORDER BY RAND() LIMIT 1) as b2
ON b1.book=b2.book AND b1.cap=b2.cap
INNER JOIN books as bk ON b1.book = bk.abbr
GROUP BY b1.book, b1.cap ;
ELSE /* one param is set, so choose other randomly */
IF book_var IS NOT NULL AND cap_var IS NULL THEN
/* choose a random cap, note how we're selecting into a variable */
SELECT DISTINCT cap INTO cap_var FROM bible WHERE book = book_var ORDER BY RAND() LIMIT 1;
-- SELECT cap_var AS 'rand cap';
ELSE
/* choose a random book, note where clause makes sure we choose a book with high enough cap */
SELECT DISTINCT book INTO book_var FROM bible WHERE cap >= cap_var ORDER BY RAND() LIMIT 1;
-- SELECT book_var AS 'rand_book';
IF book_var IS NULL THEN
SELECT CONCAT_WS( ' ', 'Chapter', cap_var, 'too high. Max cap no. is',
( SELECT MAX(cap) FROM bible)
) as messg;
END IF;
END IF;
SELECT bk.name, cap,
GROUP_CONCAT(line ORDER BY verse SEPARATOR ' ') as TEXT
FROM bible AS bb
INNER JOIN books AS bk ON bb.book = bk.abbr
WHERE book = book_var
AND cap = cap_var
GROUP BY book,cap ;
END IF;
END;
//
delimiter ;
P.S. One of the things I find quite hard to get right about Stored Procedure syntax is the IF syntax. Firstly don't forget the THEN, just like BASIC. And it's ELSEIF not ELSE IF or ELSIF. Note the lack of space. However it's END IF not ENDIF, the space is essential. At least the terminal ELSE is just plain ELSE. And when you get it wrong, mysql doesn't give you a very helpful error message either.