{"id":535,"date":"2009-12-27T13:26:43","date_gmt":"2009-12-27T13:26:43","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=535"},"modified":"2024-02-06T19:14:11","modified_gmt":"2024-02-07T02:14:11","slug":"sql-cursor-in-stored-procedures","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/sql-cursor-in-stored-procedures\/","title":{"rendered":"MySQL Cursor"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use MySQL cursor<\/strong> in stored procedures to iterate through a result set returned by a SELECT<\/code> statement.<\/p>\n\n\n\n

Introduction to MySQL cursor<\/h2>\n\n\n\n

In MySQL, a cursor is a database object used for iterating the result of a SELECT<\/code> statement.<\/p>\n\n\n\n

Typically, you use cursors within stored procedures, triggers, and functions where you need to process individual rows returned by a query one at a time<\/strong>.<\/p>\n\n\n\n

Here’s the basic syntax of a cursor:<\/p>\n\n\n

-- declare<\/span> a cursor\nDECLARE<\/span> cursor_name CURSOR FOR<\/span> \nSELECT column1, column2 \nFROM your_table \nWHERE your_condition;\n\n-- open the cursor\nOPEN cursor_name;\n\nFETCH cursor_name INTO variable1, variable2;\n-- process the data\n\n\n-- close the cursor\nCLOSE cursor_name;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works:<\/p>\n\n\n\n
First, declare a cursor by using the DECLARE<\/code> statement:<\/p>\n\n\n
DECLARE<\/span> cursor_name CURSOR<\/span> FOR<\/span> \nSELECT<\/span> column1, column2 \nFROM<\/span> your_table \nWHERE<\/span> your_condition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The cursor declaration must come after any variable <\/a>declaration. If you declare a cursor before the variable declarations, MySQL will issue an error. Additionally, a cursor must always associate with a SELECT<\/code> statement.<\/p>\n\n\n\n
Next, open the cursor using the OPEN<\/code> statement:<\/p>\n\n\n
OPEN cursor_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The OPEN<\/code> statement initializes the result set for the cursor; therefore, you must call the OPEN<\/code> statement before fetching rows from the result set.<\/p>\n\n\n\n
Then, retrieve the next row pointed by the cursor and advance the cursor to the subsequent row in the result set using the FETCH<\/code> statement:<\/p>\n\n\n
FETCH cursor_name INTO variable1, variable2;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
After that, check if there is any row available before fetching it.<\/p>\n\n\n\n
Finally, deactivate the cursor and release the memory associated with it using the CLOSE<\/code> statement:<\/p>\n\n\n
CLOSE cursor_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It is a good practice to always close a cursor when it is no longer used.<\/p>\n\n\n\n
When working with MySQL cursor, you must also declare a NOT FOUND<\/code> handler to manage the situation when the cursor cannot find any row.<\/p>\n\n\n\n
Each time you call the FETCH<\/code> statement, the cursor attempts to read the next row in the result set. When the cursor reaches the end of the result set, it will not be able to retrieve the data, and a condition is raised. The handler is used to handle this condition.<\/p>\n\n\n\n
To declare a NOT FOUND<\/code> handler, you use the following syntax:<\/p>\n\n\n
DECLARE<\/span> CONTINUE HANDLER<\/span> FOR<\/span> NOT<\/span> FOUND<\/span> SET<\/span> finished = 1<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The finished<\/code> is a variable to indicate that the cursor has reached the end of the result set. <\/p>\n\n\n\n
Notice that the handler declaration must appear after the variable and cursor declarations within the stored procedures.<\/p>\n\n\n\n
The following diagram illustrates how MySQL cursor works.<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
MySQL Cursor Example<\/h2>\n\n\n\n
We’ll develop a stored procedure that creates an email list of all employees in the employees<\/code> table in the sample database<\/a>:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The following example illustrates how to use a cursor to iterate all rows in the employees<\/code> table and concatenate the email into a string:<\/p>\n\n\n
DELIMITER $$\n\nCREATE PROCEDURE create_email_list (\n\tINOUT email_list TEXT\n)\nBEGIN\n\tDECLARE<\/span> done BOOL DEFAULT<\/span> false<\/span>;\n\tDECLARE<\/span> email_address VARCHAR(100<\/span>) DEFAULT<\/span> \"\"<\/span>;\n    \n\t-- declare<\/span> cursor for<\/span> employee email\n\tDECLARE<\/span> cur CURSOR FOR<\/span> SELECT email FROM employees;\n\n\t-- declare<\/span> NOT FOUND handler\n\tDECLARE<\/span> CONTINUE<\/span> HANDLER \n        FOR<\/span> NOT FOUND SET done = true<\/span>;\n\t\n    -- open the cursor\n\tOPEN cur;\n\t\n    SET email_list = ''<\/span>;\n\t\n    process_email: LOOP\n\t\t\n        FETCH cur INTO email_address;\n        \n\t\tIF<\/span> done = true<\/span> THEN \n\t\t\tLEAVE process_email;\n\t\tEND IF<\/span>;\n\t\t\n        -- concatenate the email into the emailList\n\t\tSET email_list = CONCAT(email_address,\";\"<\/span>,email_list);\n\tEND LOOP;\n    \n    -- close the cursor\n\tCLOSE cur;\n\nEND$$\n\nDELIMITER ;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, declare the variable done<\/code> and initialize its value to false<\/code> and the email_address<\/code> and initialize its value to ''<\/code>.<\/p>\n\n\n
DECLARE<\/span> done BOOL DEFAULT<\/span> false<\/span>;\nDECLARE<\/span> email_address VARCHAR(100<\/span>) DEFAULT<\/span> \"\"<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, declare a cursor that will fetch email<\/code> from the employees<\/code> table:<\/p>\n\n\n
DEClARE<\/span> cur CURSOR<\/span> FOR<\/span> SELECT<\/span> email FROM<\/span> employees;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, open the cursor by using the OPEN<\/code> statement:<\/p>\n\n\n
OPEN cur;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
process_email: LOOP\n\t\t\n        FETCH cur INTO email_address;\n        \n\t\tIF done = true THEN \n\t\t\tLEAVE process_email;\n\t\tEND<\/span> IF<\/span>;\n\t\t\n        -- concatenate the email into the emailList<\/span>\n\t\tSET<\/span> email_list = CONCAT<\/span>(email_address,\";\"<\/span>,email_list);\n\tEND<\/span> LOOP<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, close the cursor using the CLOSE<\/code> statement:<\/p>\n\n\n
CLOSE email_cursor;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following tests the createEmailList<\/code> stored procedure:<\/p>\n\n\n
CALL<\/span> create_email_list(@email_list); \nSELECT<\/span> @email_list\\G<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
*************************** 1.<\/span> row ***************************\n@email_list: mgerard@classicmodelcars.com;ykato@classicmodelcars.com;mnishi@classicmodelcars.com;tking@classicmodelcars.com;pmarsh@classicmodelcars.com;afixter@classicmodelcars.com;bjones@classicmodelcars.com;lbott@classicmodelcars.com;pcastillo@classicmodelcars.com;ghernande@classicmodelcars.com;lbondur@classicmodelcars.com;gvanauf@classicmodelcars.com;ftseng@classicmodelcars.com;spatterson@classicmodelcars.com;jfirrelli@classicmodelcars.com;lthompson@classicmodelcars.com;ljennings@classicmodelcars.com;abow@classicmodelcars.com;gbondur@classicmodelcars.com;wpatterson@classicmodelcars.com;jfirrelli@classicmodelcars.com;mpatterso@classicmodelcars.com;dmurphy@classicmodelcars.com;\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use a MySQL cursor to process a result set row by row individually.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Fourth, concatenate<\/a> each email into the mail_list variable using a loop statement, separating email addresses by semicolons:<\/p>\n\n\n