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:
- Asensitive
: Server may or may not make a copy of result table.
- Read
only: Can not be updated.
- Non-scrollable:
Can traverse only in one direction and can not skip rows.
|
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:
- Cursor
declare
- Cursor
open statement
- Cursor
fetch statement
- Cursor
close statement
|
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:
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:
Cursor Example:
student table:
Declare, open,
fetch and close the Cursor:
|
DELIMITER
//
CREATE
FUNCTION student_list() RETURNS VARCHAR(255)
BEGIN
DECLARE record_not_found INTEGER DEFAULT 0;
DECLARE student_name VARCHAR(50) DEFAULT "";
DECLARE stu_list VARCHAR(255) DEFAULT "";
DECLARE my_cursor CURSOR FOR SELECT studentName FROM student;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET record_not_found = 1;
OPEN my_cursor;
allStudents: LOOP
FETCH my_cursor INTO student_name;
IF record_not_found THEN
LEAVE allStudents;
END IF;
SET stu_list = CONCAT(stu_list,", ",student_name);
END LOOP allStudents;
CLOSE my_cursor;
RETURN SUBSTR(stu_list,3);
END
//
DELIMITER
;
SELECT student_list() AS Cities;
DROP FUNCTION student_list;
|
Output:
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
