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;