Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql

How to use cursors in SQL

Hassan Azhar

Cursors

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.

Usage

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 CITIES table.

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 NOT FOUND.

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.

RELATED TAGS

sql

CONTRIBUTOR

Hassan Azhar
Copyright ©2022 Educative, Inc. All rights reserved
RELATED COURSES

View all Courses

Keep Exploring