Oracle
Update
After a FETCH statement completes successfully, you are positioned
on a current row within the cursor. At this point, you can execute an
UPDATE or DELETE statement using the WHERE CURRENT OF cursor_name syntax
to update or delete the row. PowerBuilder enforces Oracle cursor update
restrictions, and any violation results in an execution error.
Example 1
This cursor example illustrates how you can loop through a result
set. Assume the default transaction object (SQLCA) has been assigned valid
values and a successful CONNECT has been executed.
The statements retrieve rows from the employee table and then
display a message box with the employee name in each row that is
found.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
// Declare the emp_curs cursor. DECLARE emp_curs CURSOR FOR SELECT emp_name FROM EMPLOYEE WHERE emp_state = :sle_1.text; // For UPDATE WHERE CURRENT OF cursor_name and // DELETE WHERE CURRENT OF cursor_name to work // correctly in Oracle 7, include the FOR UPDATE // clause in the SELECT statement. // Declare a destination variable for employee // names. string emp_name_var // Execute the SELECT statement with the // current value of sle_1.text. OPEN emp_curs; // Fetch the first row from the result set. FETCH emp_curs INTO :emp_name_var; // Loop through result set until exhausted. DO WHILE SQLCA.sqlcode = 0 // Display a message box with the employee name. MessageBox("Found an employee!",emp_name_var) // Fetch the next row from the result set. FETCH emp_curs INTO :emp_name_var; LOOP // All done, so close the cursor. CLOSE emp_curs; |
Error checking
Although you should test the SQLCode after every SQL statement,
these examples show statements to test the SQLCode only to illustrate a
specific point.
Example 2
This cursor example illustrates how to use a cursor to update or
delete rows. The statements use emp_curs to retrieve rows from the
employee table and then ask whether the user wants to delete the
employee:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
// Declare the emp_curs cursor. DECLARE emp_curs CURSOR FOR SELECT emp_name FROM employee WHERE emp_state = :sle_1.text; // Declare a destination variable for employee // names. string emp_name_var // Declare a return variable for the MessageBox. int return_var // Execute the SELECT statement with the current // value of sle_1.text. OPEN emp_curs; // Fetch the first row from the result set. FETCH emp_curs INTO :emp_name_var; // Loop through result set until it is // exhausted. DO WHILE SQLCA.sqlcode = 0 // Ask the user to confirm the deletion. return_var = MessageBox( "Want to delete?",& emp_var_name, Question!, YesNo!, 2 ) // Delete? If ( return_var = 1 ) then // Yes - delete the employee. DELETE FROM employee WHERE CURRENT OF emp_curs; End If // Fetch the next row from the result set. FETCH emp_curs INTO :emp_name_var; LOOP // All done, so close the cursor. CLOSE emp_curs; |