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.

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.

// 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;

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:

// 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;