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) AS
emp_row employees%ROWTYPE;
dept departments.department_name%TYPE;
rec_count number := 0;
BEGIN
SELECT count(*)
INTO rec_count
FROM employees
WHERE employee_id = emp_id;
IF rec_count = 1 THEN
SELECT *
INTO emp_row
FROM employees
WHERE employee_id = emp_id;
IF emp_row.department_id != dept_id THEN
emp_row.department_id := dept_id;
UPDATE employees SET ROW = emp_row
WHERE employee_id = emp_id;
SELECT department_name
INTO dept
from departments
WHERE department_id = dept_id;
DBMS_OUTPUT.PUT_LINE('The employee ' || emp_row.first_name || ' ' ||
emp_row.last_name || ' is now in department: ' || dept);
ELSE
DBMS_OUTPUT.PUT_LINE('The employee is already in that department...no change'),
END IF;
ELSIF rec_count > 1 THEN
DBMS_OUTPUT.PUT_LINE('The employee ID you entered is not unique'),
ELSE
DBMS_OUTPUT.PUT_LINE('No employee records match the given employee ID'),
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Invalid employee or department ID, try again'),
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unsuccessful change, please check ID numbers and try again'),
END;
As you can see, the example queries the database into a record declared using the %ROWTYPE attribute. The value that needs to be updated is then modified using the data contained in the record. Lastly, using the SET ROW clause updates the table with the modified record.
How It Works
As you've seen in the solution to the recipe, it is possible to update the values of a row returned by a query using the UPDATE...SET ROW syntax. In many cases, using a single UPDATE statement can solve this type of transaction. However, in some scenarios where you need to evaluate the current value of a particular column, then this solution is the correct choice.
Using the UPDATE ROW statement, you can update entire database rows with a single variable of either the %ROWTYPE or RECORD type. The UPDATE statement also allows you to return values after the update by adding the RETURNING clause to the end of the statement followed by the column names to return and the variables that will receive their values. Take a look at this next example:
DECLARE
first employees.first_name%TYPE;
last employees.last_name%TYPE;
new_salary employees.salary%TYPE;
BEGIN
UPDATE employees
SET salary = salary + (salary * .03)
WHERE employee_id = 100
RETURNING first_name, last_name,salary INTO first, last, new_salary;
DBMS_OUTPUT.PUT_LINE('The employee ' || first || ' ' || last || ' now has a salary of:
' || new_salary);
END;
As you can see, the example outputs the new values that are the result of the update statement. Using the RETURNING clause saves a step in that you are not required to requery the table after the update in order to display the updated results.