SQL Language Guide : 7. SQL Statements : DECLARE CURSOR : DECLARE CURSOR Examples
 
Share this page                  
DECLARE CURSOR Examples
1. Declare a cursor for a retrieval of employees from the shoe department, ordered by name (ascending) and salary (descending). (This can also be specified as a select loop.)
EXEC SQL DECLARE cursor1 CURSOR FOR
    SELECT ename, sal
    FROM employee
    WHERE dept = 'shoes'
    ORDER BY 1 ASC, 2 DESC;
2. Declare two cursors for the department and employee tables, and open them in a master-detail fashion.
EXEC SQL DECLARE master_cursor CURSOR FOR
    SELECT * FROM dept
    ORDER BY dno;

EXEC SQL DECLARE detail_cursor CURSOR FOR
    SELECT * FROM employee
    WHERE edept = :dno
    ORDER BY ename;
    
EXEC SQL OPEN master_cursor;

loop while more department

EXEC SQL FETCH master_cursor
    INTO :dname, :dno, :dfloor, :dsales;

if not found break loop;

/*
    ** For each department retrieve all the
    ** employees and display the department
    ** and employee data.
*/

EXEC SQL OPEN detail_cursor;

loop while more employees

EXEC SQL FETCH detail_cursor
    INTO :name, :age, :idno, :salary, :edept;
    /*
    ** For each department retrieve all the
    ** employees and display the department
    ** and employee data.
    */

process and display data;

END LOOP;
    EXEC SQL CLOSE detail_cursor;
END LOOP;

EXEC SQL CLOSE master_cursor;