Examine this code
CREATE OR REPLACE FUNCTION change_dept
(p_old_id NUMBER, p_deptname VARCHAR2)
RETURN NUMBER
IS
V_new_id NUMBER;
BEGIN
SELECT departments_seq.nextval
INTO v_new_id
FROM dual;
UPDATE departments
SET departmenet_id = v_new_id,
Department_name = p_deptname
WHERE department_id = p_old_id;
Return v_new_id;
END;
/
There are no foreign key integrity constraints on the EMPLOYEES and DEPARTMENTS tables.
Which statement performs a successful update to the EMPLOYEES table?
A.
UPDATE departments
SET department_id = change_dept(10, ‘Finance’)
Where department_id = 10;
B.
UPDATE employees
SET department_id = change_dept(10, ‘Finance’)
Where department_id = 10;
C.
UPDATE departments
change_dept(270, ‘Outsource’)
Where department_name = ‘payroll’;
D.
UPDATE employees
SET department_id = change_dept(10, ‘Finance’)
Where department_id = DEPARTMENTS:CURRVAL;
Explanation:
This statement updates the Department_id of the Employees with department_id 10 to the next
sequence number. The Update Statement invokes the change_dept function in the set statement
and passes the Current department_id & the New Department Name as input parameters. The
Function gets the next Department ID from the Sequence and successfully updates the
Department & Department Name based on the parameters passed to the function.
Incorrect Answers:
C: This Statement attempts to update the wrong table, has incorrect syntax and if corrected would
result in an error due to a mutating table.
D: This is not a valid sequence. You can’t have a sequence with the same name as a table and if
you tried to use the CURRVAL of the departments_seq.nextval sequence in the WHERE you
would get the following error: