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
Explanation:
Answer is A
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);
FOR i IN 1..deptnums.COUNT LOOP
– Count how many rows were inserted for each department; that is,
– how many employees are in each department.
DBMS_OUTPUT.PUT_LINE(‘Dept ‘||deptnums(i)||’: inserted ‘||
SQL%BULK_ROWCOUNT(i)||’ records ‘ || ‘and SQL%rowcount=’||SQL%rowcount);
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘Total records inserted: ‘ || SQL%ROWCOUNT);
END;
/
Output :
Dept 10: inserted 1 records and SQL%rowcount=106
Dept 20: inserted 2 records and SQL%rowcount=106
Dept 30: inserted 6 records and SQL%rowcount=106
Dept 40: inserted 1 records and SQL%rowcount=106
Dept 50: inserted 45 records and SQL%rowcount=106
Dept 60: inserted 5 records and SQL%rowcount=106
Dept 70: inserted 1 records and SQL%rowcount=106
Dept 80: inserted 34 records and SQL%rowcount=106
Dept 90: inserted 3 records and SQL%rowcount=106
Dept 100: inserted 6 records and SQL%rowcount=106
Dept 110: inserted 2 records and SQL%rowcount=106
Dept 120: inserted 0 records and SQL%rowcount=106
Dept 130: inserted 0 records and SQL%rowcount=106
Dept 140: inserted 0 records and SQL%rowcount=106
Dept 150: inserted 0 records and SQL%rowcount=106
Dept 160: inserted 0 records and SQL%rowcount=106
Dept 170: inserted 0 records and SQL%rowcount=106
Dept 180: inserted 0 records and SQL%rowcount=106
Dept 190: inserted 0 records and SQL%rowcount=106
Dept 200: inserted 0 records and SQL%rowcount=106
Dept 210: inserted 0 records and SQL%rowcount=106
Dept 220: inserted 0 records and SQL%rowcount=106
Dept 230: inserted 0 records and SQL%rowcount=106
Dept 240: inserted 0 records and SQL%rowcount=106
Dept 250: inserted 0 records and SQL%rowcount=106
Dept 260: inserted 0 records and SQL%rowcount=106
Dept 270: inserted 0 records and SQL%rowcount=106
Total records inserted: 106
also the link can be used fir ref:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/tuning.htm
search ‘Using the FORALL Statement’ on the page to get to the exact section.
refer example 11-8
A
Answer is A
A
A