Database Programming with PL/SQL
5-5: Using Cursors FOR UPDATE
Practice Activities
Vocabulary
Declares that each row is locked as it is being fetched so other users cannot modify the rows while the cursor is open - FOR UPDATE
A keyword used to tell the Oracle server not to wait if the requested rows have already been locked by another user - NOWAIT
Try It / Solve It
In this Practice you will INSERT and later UPDATE rows in a new table: PROPOSED_RAISES, which
will store details of salary increases proposed for suitable employees. Create this table by executing
the following SQL statement:
CREATE TABLE proposed_raises
(date_proposed DATE,
date_approved DATE,
employee_id NUMBER(6),
department_id NUMBER(4),
original_salary NUMBER(8,2),
proposed_new_salary NUMBER(8,2));
1. Write a PL/SQL block that inserts a row into PROPOSED_RAISES for each eligible employee.
The eligible employees are those whose salary is below a chosen value. The salary value is
passed as a parameter to the cursor. For each eligible employee, insert a row into
PROPOSED_RAISES with date_proposed = today’s date, date_appoved null, and
proposed_new_salary 5% greater than the current salary. The cursor should LOCK the
employees rows so that no one can modify the employee data while the cursor is open. Test your
code using a chosen salary value of 5000.
DECLARE CURSOR employees_cur(parm_salary employees.salary%TYPE) IS
SELECT * FROM employees
WHERE salary < parm_salary FOR UPDATE
OF salary NOWAIT;
BEGIN
FOR v_employee IN employees_cur(5000) LOOP INSERT INTO
proposed_raises(date_proposed,
date_approved,
employee_id,
department_id,
original_salary,
proposed_new_salary)
VALUES(SYSDATE,
NULL,
v_employee.employee_id,
v_employee.department_id,
v_employee.salary,
v_employee.salary*1.05);
END LOOP;
END;
2. SELECT from the PROPOSED_RAISES table to see the results of your INSERT statements.
There should be 15 rows. If you run your block in question 1 more than once, make sure the
PROPOSED_RAISES table is empty before each test.
SELECT * FROM proposed_raises;
DELETE FROM proposed_raises; -- to clear all rows from the table
Before continuing, ensure there are 15 rows in PROPOSED_RAISES.
3. Imagine these proposed salary increases have been approved by company management.
A. Write and execute a PL/SQL block to read each row from the PROPOSED_RAISES table. For
each row, UPDATE the date_approved column with today’s date. Use the WHERE CURRENT
OF... syntax to UPDATE each row. After running your code, SELECT from the
PROPOSED_RAISES table to view the updated data.
DECLARE CURSOR prop_rais_cur IS
SELECT * FROM proposed_raises
FOR UPDATE NOWAIT;
v_up prop_rais_cur%ROWTYPE;
BEGIN
OPEN prop_rais_cur;
LOOP
FETCH prop_rais_cur INTO v_up;
EXIT WHEN prop_rais_cur%NOTFOUND;
UPDATE proposed_raises
SET date_approved = SYSDATE();
END LOOP;
CLOSE prop_rais_cur;
END;
B. Management has now decided that employees in department 50 cannot have a salary
increase after all. Modify your code from question 3 to DELETE employees in department 50
from PROPOSED_RAISES. This could be done by a simple DML statement (DELETE FROM
proposed_raises WHERE department_id = 50;), but we want to do it using a FOR UPDATE
cursor. Test your code, and view the PROPOSED_RAISES table again to check that the rows
have been deleted.
DECLARE
CURSOR proposed_cur IS
SELECT * FROM proposed_raises
WHERE department_id = 50
FOR UPDATE NOWAIT;
v_prop_rec proposed_cur%ROWTYPE;
BEGIN
OPEN proposed_cur;
LOOP
FETCH proposed_cur INTO v_prop_rec;
EXIT WHEN proposed_cur%NOTFOUND;
DELETE FROM proposed_raises
WHERE CURRENT OF proposed_cur;
END LOOP;
CLOSE proposed_cur;
END;
4. Since Oracle Academy's Application Express automatically commits changes, complete the
following activity as if you were issuing the commands in an installed/local environment with the
ability to use COMMIT and ROLLBACK. The indicated errors and pauses will not actually happen
in the Oracle Academy's online Application Express.
We are going to set up two sessions into the same schema. From one of the sessions we will
manually update an employee row NOT COMMITING. From the other session we will try to
update everyone’s salary, again NOT COMMITING. You should see the difference between
NOWAIT and WAIT when using FOR UPDATE.
In preparation, create a copy of the employees table by executing the following SQL statement. You should use the UPD_EMPS table for the rest of this exercise.
CREATE TABLE upd_emps AS SELECT * FROM employees;
A. Open a second session in a new browser window and connect to your schema.
CREATE TABLE upd_emps AS SELECT * FROM employees;
B. In your first session, update employee_id 200 (Jennifer Whalen) so the stored first name is Jenny.
DO NOT COMMIT. You now have a lock on row 200 that will last indefinitely.
DECLARE
CURSOR upd_cur IS
SELECT * FROM upd_emps
WHERE employee_id = 200
FOR UPDATE;
v_upd upd_cur%ROWTYPE;
BEGIN
OPEN upd_cur;
LOOP
FETCH upd_cur INTO v_upd;
EXIT WHEN upd_cur%NOTFOUND;
END LOOP;
CLOSE upd_cur;
END;
C. In your second session, write a PL/SQL block to give every employee in UPD_EMPS a $1 salary
raise. Your cursor should be declared FOR UPDATE NOWAIT. Execute your code. What happens?
DECLARE
CURSOR upd_cur IS
SELECT * FROM upd_emps
FOR UPDATE NOWAIT;
v_upd upd_cur%ROWTYPE;
BEGIN
OPEN upd_cur;
LOOP
FETCH upd_cur INTO v_upd;
EXIT WHEN upd_cur%NOTFOUND;
UPDATE upd_emps
SET salary = salary+1;
END LOOP;
CLOSE upd_cur;
END;
1 row(s) updated.
D. Still in your second session, modify your block to remove the NOWAIT attribute from the cursor
declaration. Re-execute the block. What happens this time?
DECLARE
CURSOR upd_cur IS
SELECT * FROM upd_emps
FOR UPDATE;
v_upd upd_cur%ROWTYPE;
BEGIN
OPEN upd_cur;
LOOP
FETCH upd_cur INTO v_upd;
EXIT WHEN upd_cur%NOTFOUND;
UPDATE upd_emps
SET salary = salary+1;
END LOOP;
CLOSE upd_cur;
END;
Statement processed.
E. After waiting a minute or so, switch to your first session and COMMIT the update to Jennifer
Whalen’s row. Then switch back to your second session. What happened?
DECLARE
CURSOR upd_cur IS
SELECT * FROM upd_emps
WHERE employee_id = 200
FOR UPDATE;
v_upd upd_cur%ROWTYPE;
BEGIN
OPEN upd_cur;
LOOP
FETCH upd_cur INTO v_upd;
EXIT WHEN upd_cur%NOTFOUND;
UPDATE upd_emps
SET first_name = 'Jenny'
WHERE employee_id = 200;
END LOOP;
CLOSE upd_cur;
END;
0 Comments