View the Exhibits and examine the structure of the EMPLOYEES, DEPARTMENTS AND EMP_BY_DEPT tables.
EMPLOYEES
DEPAERTMENT
EMP_BY_DEPT
Examine the following code:
What is the outcome on execution of the above code?
A. It executes successfully but the output statements show different values.
B. It executes successfully and both output statements show the same values.
C. It gives an error because the SQL%ROWCOUNT attribute cannot be used with BULK COLLECT.
D. It gives an error because the INSERT SELECT construct cannot be used with the FORALL
A is correct. The output statements show different values.
create table EMP_BY_DEPT (employee_id number(6) not null, department_id number(4) not null);
DECLARE
TYPE dept_tab is table of departments.department_id%type ;
deptnums dept_tab ;
BEGIN
select department_id BULK COLLECT INTO deptnums FROM departments;
forall i in 1..deptnums.count
insert into EMP_BY_DEPT
select employee_id, department_id from EMPLOYEES
where department_id = deptnums(i);
DBMS_OUTPUT.PUT_LINE(sql%bulk_rowcount(deptnums.count));
DBMS_OUTPUT.PUT_LINE(sql%rowcount);
END;
/