Modifications by Cursor

Modifications by Cursor

When a cursor ranges over the tuples of a base table (i.e., a relation that is stored in the database, rather than a view or a relation constructed by a  query), then one can not only read and process the value of each tuple, but one can update or delete tuples. The syntax of these UPDATE and  DELETE statements are the same as we encountered in "Database Modifications", with the exception of the WHERE clause. That clause may only be WHERE  CURRENT OF followed by the name of the cursor. Of course it is possible for the host-language program reading the tuple to apply whatever condition it likes to the tuple before deciding whether or not to delete or update it.

Example 1: In Figure 1 we see a C function that looks at each tuple of MovieExec and decides either to delete the tuple or to double the net  worth. In lines (3) and (4) we declare variables that correspond to the four attributes of MovieExec, as well as the necessary SQLSTATE. Then, at  line (6), execCursor is declared to range over the stored relation MovieExec itself. Note that, while we could try to modify tuples through a cursor  that ranged over some temporary relation that was the result of some query, we can only have a lasting effect on the database if the cursor ranges over a stored relation such as MovieExec.

Modifying executive net worths

Lines (8) through (14) are the loop, in which the cursor execCursor refers to each tuple of MovieExec, in turn. Line (9) fetches the current tuple into the four variables used for this purpose; note that only worth is actually used. Line (10) tests whether we have exhausted the tuples of  MovieExec. We have again used the macro NO_MORE_TUPLES for the condition that variable SQLSTATE has the "no more tuples" code "02000".

In the test of line (11) we ask if the net worth is under $1000. If so, the tuple is deleted by the DELETE statement of line (12). Note that the WHERE clause refers to the cursor, so the current tuple of MovieExec, the one we just fetched, is deleted from MovieExec. If the net worth is at  least $1000, then at line (14), the net worth in the same tuple is doubled, instead.