Monitor data in MySQL with Cursors and Stored Procedures.

MySQL cursors traverse rows in a table. Okay…but why use them? In this blog post, we will look at a CURSOR within a stored procedure that returns specific records of interest.

Photo by Brendan Church on Unsplash

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

I will be using Xubuntu Linux 16.04.3 LTS (Xenial Xerus) and MySQL 5.7.21 for these exercises.

Up To Speed

Recently, I published a two-part series in regards to MySQL triggers for a specific data integrity check use case. Part 1 detailed the end goal behind the trigger while Part 2 focused on the TRIGGER itself and its workings. Visit those posts to get up to speed on the data set and tables referred to in this blog post.

Note: I want to mention these two great MySQL Youtube videos on Cursors. The channel as a whole has some of the best videos on MySQL. After watching both of these videos and applying what I learned there to my own goals/studies, hence this blog post was born.
Visit them both through the provided links and enjoy. They are well structured and super informative.

• Cursors in Mysql part-1
• Cursors in Mysql part-2

Suppose you are tasked with checking any records in the flagged_asset table.
Here is the table description:

This table holds records that need some sort of verification prior to final storage in ‘production’ tables.
Potentially, monitoring this table is a task that could be carried out daily.

Of course, we can easily run SELECT queries against the table such as:

Yet, there is a better way. First off we have MySQL Stored Routines. And a member of that family, in particular, Stored Procedures. Utilizing stored procedures, we can wrap up bits of code for reuse later. By encapsulating these commonly used queries, we save ourselves time, going forward.

And here is an ideal use for such a thing.

An Example Stored Procedure

Let’s look at an example stored procedure we can apply for this use case.
The code, of stored procedure asbuilt.proc_flagged_asset is shown below:

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage!
Be sure and visit the “Best Of” page for a collection of my best blog posts while you are there!

The CURSOR will move through the table row by row, returning all records when the procedure is called.
Let’s put it in action.

The CALL statement, invokes the procedure.

Notice on line 2, CREATE PROCEDURE asbuilt.proc_flagged_notify(). Specifying the asbuilt database prefix to the name, we are assuring this procedure is available only in that named database. Since the results set is applicable to table flagged_asset, I feel this is a better practice than making it available (the procedure) in the default database.

CREATE PROCEDURE proc_flagged_notify()
works with no errors.

And You Mentioned Cursors?

Here are three important aspects concerning CURSOR‘s you should be aware of, provided directly from the MySQL documentation in section 13.6.6.

• Asensitive: The server may or may not make a copy of its result table.
• Read only: Not updatable
• Nonscrollable: Can be traversed only in one direction and cannot skip rows

Drawing conclusions from the above points, we know that a CURSOR, merely return a result set to us (if any) and are not meant to be updated.

CURSOR‘s travel the entire result set and return everything found there, in order. If you need something more customizable, with the ability to skip rows, then other alternatives may be a better choice.

Understanding This Procedure

Let’s study the asbuilt.proc_flagged_asset procedure, line by line, and determine how it works.

• Lines 1 and 24 set the DELIMITER for use within the procedure body. Since we have multiple statements and want to use the default semicolon (;), we must temporarily change it.
• Line 2: The CREATE PROCEDURE command is responsible for creating and naming the proc_flagged_notify procedure.
• Lines 4 -9: Here we have multiple DECLARE statements defining variables to use with the CURSOR located on line 11. Basically, the column values returned from the SELECT query are stored in them.
• Line 10 contains a declaration for a variable v_finished which is used in a test case for the CONTINUE HANDLER declared on line 13. Similar functionality is exceptionally demonstrated in the above-mentioned YouTube videos along with the official MySQL documentation (link attached in Closing section).
• Line 11: SELECT all columns from the flagged_asset table into the variable declared on lines 4 – 9 as mentioned above. All returned columns are then placed in CURSOR v_cur.
• Line 13, I find most interesting. The CONTINUE HANDLER specifies that when a NOT FOUND condition is raised we will SET the value of v_finished to 1. That condition arises when no rows remain to traverse. Why do this? Continue reading.
• We have to open the CURSOR to use it. Taken care of on line 14.
• Line 15: Here we are naming and starting a LOOP, get_flagged.
• Line 16: Now, the declared variables from lines 4–9, are loaded with the values from our v_cur CURSOR.
• Line 17: This boolean expression is key for the variables being loaded with all rows from the table. Until the v_finished variable equals 1, the LOOP continues.
• Upon evaluating to true, get_flagged LOOP exits on line 18.
• Line 20: We SELECT all loaded variables from the cursor.
• The LOOP is completed and ended on line 21.
• Line 22: We must close the v_cur CURSOR to free up any resources. A CURSOR is automatically closed at the finish of an BEGIN/END block. However, it is generally best practice to close them explicitly.
• Line 23 ends and creates the procedure with the $$ DELIMITER.
• Line 24 re-sets the DELIMITER back to the default semicolon (;).

Information Bits

I found the below points informative from my studies about cursors in the documentation and want to share them with you.

• Declare cursors after variables and before handlers.
• A CURSOR is closed at the end of a BEGIN/END block.
• Declare a CURSOR to retrieve rows from an associated SELECT statement that does not have an INTO clause.
• Use FETCH to retrieve rows processed by the CURSOR. Note: The number of columns retrieved by the associated SELECT statement for the CURSOR must match the number of columns extracted by FETCH.

Cursors Are Cool

Writing this blog post, enabled me to learn and share useful concepts on cursors. What are some of your favorite uses for cursors in stored procedures? I would love to know of them as well, and further advance my MySQL skills. Until next time, thanks for reading.

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage!
Be sure and visit the “Best Of” page for a collection of my best blog posts while you are there!

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

Originally published at joshuaotwell.com on March 12, 2018.

✉️ Subscribe to CodeBurst’s once-weekly Email Blast, 🐦 Follow CodeBurst on Twitter, view 🗺️ The 2018 Web Developer Roadmap, and 🕸️ Learn Full Stack Web Development.