Positioned Delete

The previous example demonstrated a read-only cursor. Cursors can also be used to delete data at the current cursor position as long as it is deletable. Here are some of the characteristics of a deletable cursor:

• Each FROM clause of the outer fullselect references only one table.

• The outer fullselect does not include a VALUES, GROUP BY, or HAVING clause and does not include column functions.

• The select list of the outer fullselect does not include DISTINCT.

• The select-statement does not include an ORDER BY or FOR READ ONLY clause.

• The cursor is statically defined, or the FOR UPDATE clause is specified.

The definition for what constitutes a deletable cursor is quite similar for each of the platforms, but there are some minor differences. Refer to the individual SQL references for the complete requirements.

Using a cursor to delete the row on which it is currently positioned is known as a positioned delete. The syntax diagram for positioned delete is shown in Figure 5.5.

Figure 5.5. A simplified positioned DELETE syntax.

 >>-DELETE FROM-|----table-name---------|---------------------->                +-----view-name---------+ >----WHERE CURRENT OF--cursor-name--------------------------->< 

The example in Figure 5.6 demonstrates how to use a cursor for positioned delete.

Figure 5.6. An example of a positioned delete.

 CREATE PROCEDURE cleanup_act ( IN  p_date    DATE                              , OUT p_deleted INT )     LANGUAGE SQL     SPECIFIC cleanup_act                          -- applies to LUW and iSeries  -- WLM ENVIRONMENT <env>                         -- applies to zSeries ca: BEGIN     -- Declare variable     DECLARE v_date DATE;     -- Declare returncode     DECLARE SQLSTATE CHAR(5);     -- Procedure logic     DECLARE c_emp CURSOR FOR                       -- (1)         SELECT emendate         FROM emp_act     FOR UPDATE;     OPEN c_emp;     FETCH FROM c_emp INTO v_date;     SET p_deleted = 0;     WHILE ( SQLSTATE = '00000' ) DO         IF ( v_date < p_date ) THEN             DELETE FROM emp_act             WHERE CURRENT OF c_emp;                -- (2)             SET p_deleted = p_deleted + 1;         END IF;         FETCH FROM c_emp INTO v_date;     END WHILE;     CLOSE c_emp; END ca 

This procedure removes old records from the emp_act table. It takes a cut-off date as the input parameter. All records prior to this date are deleted from the emp_act, and the total number of deleted records is returned as an output parameter once the entire result set has been processed. This is a simple example to show how to use positioned delete. In the real world, a searched delete (that is, a direct DELETE statement without using a cursor) would perform much better for a simple operation like this. However, with positioned delete, you are able to implement much more complicated logic on the data retrieved before you decide if you want to delete the current row.

The DECLARE CURSOR statement in Line (1) is similar to the read-only cursor except that it has the FOR UPDATE clause. This clause is not required if you intend to perform positioned deletes, but it is good practice to use it so that DB2 can place more granular locks on the affected rows.

The WHERE CURRENT OF clause in the DELETE statement on Line (2) indicates that the row to be deleted is the row where the cursor is currently positioned. When the positioned delete is executed, the cursor must be positioned on a row. That is, if you want to delete the first row in a result set, you must execute FETCH to properly position the cursor.