8. 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 a cursor for updating the salaries and departments of employees currently in the shoe department.
EXEC SQL DECLARE cursor2 CURSOR FOR
    SELECT ename, sal
    FROM employee
    WHERE dept = 'shoes'
    FOR UPDATE OF sal, dept;
3. Declare a cursor for updating the salaries of employees whose last names are alphabetically like a given pattern.
searchpattern = 'a%';
EXEC SQL DECLARE cursor3 CURSOR FOR
    SELECT ename, sal
    FROM employee
    WHERE ename LIKE :searchpattern
    FOR UPDATE OF sal;
...
EXEC SQL OPEN cursor3;
In the above example, the variable, searchpattern, must be a valid declaration in the host language at the time the statement OPEN CURSOR3 is executed. It also must be a valid embedded SQL declaration at the point where the cursor is declared.
4. Declare a cursor to print the results of a retrieval for runtime viewing and salary changes.
EXEC SQL DECLARE cursor4 CURSOR FOR
    SELECT ename, age, eno, sal
    FROM employee
    FOR DIRECT UPDATE OF sal;

EXEC SQL WHENEVER sqlerror stop;
EXEC SQL WHENEVER NOT FOUND GOTO close_cursor;
EXEC SQL OPEN cursor4;

loop /* loop is broken when NOT FOUND becomes true. */
EXEC SQL FETCH cursor4
        INTO :name, :age, :idno, :salary;
    PRINT name, age, idno, salary;
    PRINT 'New salary';
    READ newsal;
    IF (newsal > 0 AND newsal <> salary) THEN
        EXEC SQL UPDATE employee
            SET sal = :newsal
            WHERE CURRENT OF cursor4;
    END IF;
END LOOP;
close_cursor:
  EXEC SQL CLOSE cursor4;
5. Declare a cursor for retrieval of specific data. The FOR UPDATE clause refers to column name, sal, and not, res.
EXEC SQL DECLARE cursor5 CURSOR FOR
    SELECT ename, sal AS res
    FROM employee
    WHERE eno BETWEEN :eno_low AND :eno_high
    FOR UPDATE OF sal;
. . .

loop while more input
    READ eno_low, eno_high;

EXEC SQL OPEN cursor5;

print and process rows;
END LOOP;
6. 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;
7. Declare a cursor that is a union of three tables with identical typed columns (the columns have different names). As each row returns, record the information and add it to a new table. Ignore all errors.
EXEC SQL DECLARE shapes CURSOR FOR
    SELECT boxname, box# FROM boxes
    WHERE boxid > 100
    UNION
    SELECT toolname, tool# FROM tools
    UNION
    SELECT nailname, nail# FROM nails
    WHERE nailweight > 4;

EXEC SQL OPEN shapes;
EXEC SQL WHENEVER NOT FOUND GOTO done;

loop while more shapes

EXEC SQL FETCH shapes INTO :name, :number;
    record name and number;
    EXEC SQL INSERT INTO hardware
    (:name, :number);

END LOOP;

done:

EXEC SQL CLOSE shapes;