Updating records using cursors
While it seems very easy to create a cursor to update a specific row in a chosen table, your system administrator might prohibit this particularly dangerous activity.In addition, database business rules and relational integrity often depend on synchronizing other tables in the same logical operation.Code to do this is shown below: Of course, in the real world your stored procedure would do something more elaborate than simply list some records, but the principles would remain the same as in our simple example.The next part of this blog series investigates the different types of cursor that you can use in SQL. In some cases, you might be unable to update a result set using a cursor, but be able to update using an action query.In order for a cursor to be used to update base tables, a number of conditions must be met: Many multiuser systems do not attempt to create applications that depend on the direct updatability of base tables.
To update a base table and perform ancillary operations with RDO cursors, you can begin a transaction and manage all aspects of the update in your application, perhaps by executing a number of independent update operations.
However, this fairly complex approach must be duplicated for each application that needs update access to the tables.
When you're using a cursor in SQL you use the FETCH statement to move the cursor to a particular record.
This part of the series examines the FETCH statement in more detail.
There are several other ways to use the FETCH statement, as shown in the table below: Perhaps the most useful thing that you can do with a cursor is use it to execute a stored procedure against each record in a result set.
To replicate the above example using a stored procedure we would first need to create one which can accept a number of parameters.