The WHERE CURRENT OF statement allows you to update or delete the record that was last fetched by the cursor.Using for update nowait will cause the rows to be busy and acquires a lock until a commit or rollback is executed. Any other session that tries to acquire a lock will get an Oracle error message like ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired instead of waiting the lock to release.Oracle provides the FOR UPDATE NOWAIT clause in SQL syntax to allow the developer to lock a set of Oracle rows for the duration of a transaction.
select employee_id,salary from employees;
DECLARE
CURSOR sal_cursor IS SELECT salary FROM employees FOR UPDATE NOWAIT 15;
BEGIN
FOR emp_record IN sal_cursor
LOOP
IF emp_record.salary < 5000 THEN
UPDATE employees
SET salary = emp_record.salary +1000;
--WHERE CURRENT OF sal_cursor;
END IF;
END LOOP;
END;
/
select employee_id,salary from employees;
