Delete with a cursor

You can also write a DELETE statement with a cursor to delete the row that was last fetched. Deleting rows in this manner lets you program deletions based on conditions that cannot be tested in a WHERE clause, as the following example shows. The following example applies only to databases that are not ANSI compliant because of the way that the beginning and ending of the transaction are set up.

Warning: The design of the function in this example is unsafe. It depends on the current isolation level for correct operation. Isolation levels are discussed later in the chapter. For more information on isolation levels, see Programming for a multiuser environment. Even when the function works as intended, its effects depend on the physical order of rows in the table, which is not generally a good idea.

int delDupOrder()
{
   int ord_num;
   int dup_cnt, ret_code;

   EXEC SQL declare scan_ord cursor for
      select order_num, order_date
         into :ord_num, :ord_date
         from orders for update;
   EXEC SQL open scan_ord;
   if (sqlca.sqlcode != 0) 
      return (sqlca.sqlcode);
   EXEC SQL begin work;
   for(;;)
   {
      EXEC SQL fetch next scan_ord;
      if (sqlca.sqlcode != 0) break;
      dup_cnt = 0; /* default in case of error */
      EXEC SQL select count(*) into dup_cnt from orders
         where order_num = :ord_num;
      if (dup_cnt > 1)
      {
         EXEC SQL delete from orders
            where current of scan_ord;
         if (sqlca.sqlcode != 0) 
            break;
      }
   } 
   ret_code = sqlca.sqlcode;
   if (ret_code == 100)             /* merely end of data */
      EXEC SQL commit work;
   else      /* error on fetch or on delete */
      EXEC SQL rollback work;
   return (ret_code);
}

The purpose of the function is to delete rows that contain duplicate order numbers. In fact, in the demonstration database, the orders.order_num column has a unique index, so duplicate rows cannot occur in it. However, a similar function can be written for another database; this one uses familiar column names.

The function declares scan_ord, a cursor to scan all rows in the orders table. It is declared with the FOR UPDATE clause, which states that the cursor can modify data. If the cursor opens properly, the function begins a transaction and then loops over rows of the table. For each row, it uses an embedded SELECT statement to determine how many rows of the table have the order number of the current row. (This step fails without the correct isolation level, as Programming for a multiuser environment describes.)

In the demonstration database, with its unique index on this table, the count returned to dup_cnt is always one. However, if it is greater, the function deletes the current row of the table, reducing the count of duplicates by one.

Cleanup functions of this sort are sometimes needed, but they generally need more sophisticated design. This function deletes all duplicate rows except the last one that the database server returns. That order has nothing to do with the content of the rows or their meanings. You can improve the function in the previous example by adding, perhaps, an ORDER BY clause to the cursor declaration. However, you cannot use ORDER BY and FOR UPDATE together. An insert example presents a better approach.