Cursors allow the receipt of small portions of results instead of the whole result of a query in a database.
It is often impossible or undesirable for applications to handle large amounts of data in the form of database queries. In this case, a cursor can be used to traverse the result by handling one record, or one small group of records, at a time.
To initialize a cursor, it should be bound to a query:
BEGIN DECLARE city_cursor CURSOR FOR SELECT city_name FROM CITIES; END;
The previous code declares the cursor
city_cursor on a query on the
A more complete example of cursors is:
BEGIN DECLARE done INT DEFAULT FALSE; DECLARE city_cursor CURSOR FOR SELECT city_name FROM CITIES; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; DECLARE temp VARCHAR(40); OPEN city_cursor; read_loop: LOOP FETCH city_cursor INTO temp; IF done THEN LEAVE read_loop; END IF; /* Carry out application specific operation here */ END LOOP; CLOSE city_cursor; END;
This code offers an example of how a cursor could be used in a larger SQL program. The
done variable controls the end of the loop. On line 4, a
CONTROL HANDLER is set up to run until the cursor reaches the end of the result. The end condition is specified on line 4 as
Inside the loop, the
FETCH operator gets individual results from the query and stores them into the
temp variable. If the end of the data is reached, the
done variable will become
TRUE and the
IF statement will exit the loop. Otherwise, any processing of the received data should be carried out in the area marked by commas or found to be appropriate for the program.
View all Courses