EXPLICIT CURSORS – modify tables

In the previous article (cursors overview) we have seen simple examples with SQL statements.
You can also use cursor to modify database tables.
If we want to use explicit cursor to update or delete data, we need to:
-declare the cursor with FOR UPDATE clause (to avoid unwanted changes from others).
-use clause WHERE CURRENT OF to modify FETCH returned row.
FOR UPDATE clause determines a specific row level lock on your data, so you are sure that data don’t change before your delete or update statement.
FOR UPDATE syntax is the follow:
DECLARE
CURSOR cur_name IS
SELECT […your cols…]
FROM […your tables…]
WHERE […your conditions…]
FOR UPDATE OF […column to lock, when use several tables…];
 
or, also
 
DECLARE
CURSOR cur_name IS
SELECT […your cols…]
FROM […your tables…]
WHERE […your conditions…]
FOR UPDATE;
 
————————————————————————————————————————
 
EXAMPLES
 
example 1 – explicit cursor, delete
 DECLARE
var_firstname     VARCHAR(20);
var_lastname      VARCHAR(20);
var_salary        people.salary%TYPE;
CURSOR cur_person IS
SELECT first_name, last_name, salary
FROM people
FOR UPDATE;
BEGIN
OPEN cur_person;
LOOP
FETCH cur_person INTO var_firstname, var_lastname, var_salary;
EXIT WHEN cur_person%NOTFOUND;
IF var_salary > 3000 THEN
DELETE people WHERE CURRENT OF cur_person;
END IF;
END LOOP;
CLOSE cur_person;
END;
/
 
example 2 – explicit cursor, update
 
DECLARE
var_firstname     VARCHAR(20);
var_lastname      VARCHAR(20);
var_salary        people.salary%TYPE;
CURSOR cur_person IS
SELECT first_name, last_name, salary
FROM people
FOR UPDATE;
BEGIN
OPEN cur_person;
LOOP
FETCH cur_person INTO var_firstname, var_lastname, var_salary;
EXIT WHEN cur_person%NOTFOUND;
IF var_salary > 3000 THEN
UPDATE people SET salary = 3000
WHERE CURRENT OF cur_person;
END IF;
END LOOP;
CLOSE cur_person;
END;
/