Oracle and PL/SQL Recipes: A Problem-Solution Approach

2-9. Updating Rows Returned by a Query

Problem

You've queried the database and retrieved a row into a variable. You want to update some values contained in the row and commit them to the database.

Solution

First, retrieve the database row that you want to update. Second, update the values in the row that need to be changed, and then issue an UPDATE statement to modify the database with the updated values. In the following example, a procedure is created that queries a table of employees for a particular employee. The resulting employee's department ID is then updated with the new one unless the employee is already a member of the given department.

CREATE OR REPLACE PROCEDURE change_emp_dept(emp_id   IN  NUMBER,                                             dept_id  IN  NUMBER) ...