MySQL Cursor
Here we are going to describe about the MySQL cursor and how to declare, open, fetch and close it. Cursor can be created inside the stored procedures, functions and triggers. Cursors are used for rows iteration returned by a query on a row-by-row basis. It is different from typical SQL commands that operate on all the rows in the set returned by a query at one time.
There are following types of properties of MySQL cursor:
|
Cursors must be declared before declaring handlers, and variables and conditions must be declared before declaring either cursors or handlers.
To
create a cursor you must be familiar with the following statements:
|
1. Cursor declaration: To declare a cursor you must use the DECLARE statement.
Syntax:
DECLARE cursor_name cursor for
select_statement; |
If you want to use the multiple
cursor then the cursor name must have an unique name and each cursor have
specified block.
2. Cursor open statement:
To open the cursor you must use the OPEN statement. If you want to fetch rows
from it then you must open the cursor.
Syntax:
OPEN cursor_name; |
3. Cursor fetch statement:
If you want to retrieve next row from the cursor and move the cursor to next row
then you need to fetch the cursor.
Syntax:
FETCH cursor_name INTO var_name; |
If a row exists, then the above statement fetches the next
row and cursor pointer moves ahead to the next row. If no more data left "no data condition"
with SQLSTATE value 02000 occurs.
4. Cursor close statement:
This statement is used to close the opened cursor.
Syntax:
CLOSE cursor_name; |
Cursor Example:
student table:
Declare, open
DELIMITER
// |