Application programs often use cursors to allow the user to browse through a table of data row by row. For example, the user may ask to see all of the orders placed by a particular customer. The program declares a cursor for a query of the ORDERS table and displays each order on the screen, possibly in a computer-generated form, waiting for a signal from the user to advance to the next row. Browsing continues in this fashion until the user reaches the end of the query results. The cursor serves as a pointer to the current row of query results. If the query draws its data from a single table and it is not a summary query, as in this example, the cursor implicitly points to a row of a database table, because each row of query results is drawn from a single row of the table.
While browsing the data, the user may spot data that should be changed. For example, the order quantity in one of the orders may be incorrect, or the customer may want to delete one of the orders. In this situation, the user wants to update or delete this order. The row is not identified by the usual SQL search condition; rather, the program uses the cursor as a pointer to indicate which particular row is to be updated or deleted.
Embedded SQL supports this capability through special versions of the DELETE and UPDATE statements, called the positioned DELETE and positioned UPDATE statements, respectively.
The positioned DELETE statement, shown in Figure 17-30, deletes a single row from a table. The deleted row is the current row of a cursor that references the table. To process the statement, the DBMS locates the row of the base table that corresponds to the current row of the cursor and deletes that row from the base table. After the row is deleted, the cursor has no current row. Instead, the cursor is effectively positioned in the empty space left by the deleted row, waiting to be advanced to the next row by a subsequent FETCH statement.
The positioned UPDATE statement, shown in Figure 17-31, updates a single row of a table. The updated row is the current row of a cursor that references the table. To process the statement, the DBMS locates the row of the base table that corresponds to the current row of the cursor and updates that row as specified in the SET clause. After the row is updated, it remains the current row of the cursor. Figure 17-32 shows an order-browsing program that uses the positioned UPDATE and DELETE statements:
- The program first prompts the user for a customer number and then queries the ORDERS table to locate all of the orders placed by that customer.
- As it retrieves each row of query results, it displays the order information on the screen and asks the user what to do next.
- If the user types an N, the program does not modify the current order, but moves directly to the next order.
- If the user types a D, the program deletes the current order using a positioned DELETE statement.
- If the user types a U, the program prompts the user for a new quantity and amount, and then updates these two columns of the current order using a positioned UPDATE statement.
- If the user types an X, the program halts the query and terminates.
Although it is primitive compared to a real application program, the example in Figure 17-32 shows all of the logic and embedded SQL statements required to implement a browsing application with cursor-based database updates.
The SQL1 standard specifies that the positioned DELETE and UPDATE statements can be used only with cursors that meet these very strict criteria:
- The query associated with the cursor must draw its data from a single source table; that is, there must be only one table named in the FROM clause of the query specified in the DECLARE CURSOR statement.
- The query cannot specify an ORDER BY clause; the cursor must not identify a sorted set of query results.
- The query cannot specify the DISTINCT keyword.
- The query must not include a GROUP BY or a HAVING clause.
- The user must have the UPDATE or DELETE privilege (as appropriate) on the base table.
The IBM databases (DB2, SQL/DS) extended the SQL1 restrictions a step further. They require that the cursor be explicitly declared as an updateable cursor in the DECLARE CURSOR statement. The extended IBM form of the DECLARE CURSOR statement is shown in Figure 17-33. In addition to declaring an updateable cursor, the FOR UPDATE clause can optionally specify particular columns that may be updated through the cursor. If the column list is specified in the cursor declarations, positioned UPDATE statements for the cursor may update only those columns.
In practice, all commercial SQL implementations that support positioned DELETE and UPDATE statements follow the IBM SQL approach. It is a great advantage for the DBMS to know, in advance, whether a cursor will be used for updates or whether its data will be read-only, because read-only processing is simpler. The FOR UPDATE clause provides this advance notice and can be considered a de facto standard of the embedded SQL language.
Because of its widespread use, the SQL2 standard includes the IBM-style FOR UPDATE clause as an option in its DECLARE CURSOR statement. However, unlike the IBM products, the SQL2 standard automatically assumes that a cursor is opened for update unless it is a scroll cursor or it is explicitly declared FOR READ ONLY. The FOR READ ONLY specification in the SQL2 DECLARE CURSOR statement appears in exactly the same position as the FOR UPDATE clause and explicitly tells the DBMS that the program will not attempt a positioned DELETE or UPDATE operation using the cursor. Because they can significantly affect database overhead and performance, it can be very important to understand the specific assumptions that your particular DBMS brand makes about the updateability of cursors and the clauses or statements that can be used to override them. In addition, programs that explicitly declare whether their intention is to allow updates via an opened cursor are more maintainable.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.