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
A
a
A
For FORALL, %ROWCOUNT returns the total number of rows affected by all the FORALL statements executed, not simply the last statement.
The Answer is D,we cannot use Insert select in the FOrall statement
Answer is A.
Check Oracle doc with identical example:
http://docs.oracle.com/database/121/LNPLS/tuning.htm#BABICGJE
C is incorrect.
SQL%BULK_ROWCOUNT attribute gives the number of rows affected by the FORALL statement.
SQL cursor has one more attribute %BULK_ROWCOUNT on top of its regular attributes SQL%ISOPEN, SQL%FOUND, SQL%ROWCOUNT
D is also incorrect.
The BULK COLLECT clause can appear in:
SELECT INTO statement, FETCH statement
RETURNING INTO clause of:
DELETE, INSERT, UPDATE, and EXECUTE IMMEDIATE statement
desc employees;
select * from employees;
create table departments (department_id number);
insert into departments values(50);
/
DROP TABLE emp_by_dept;
/
CREATE TABLE emp_by_dept AS
SELECT employee_id, department_id
FROM employees
WHERE 1 = 0;
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 (employee_id, department_id)
SELECT employee_id, department_id
FROM employees
WHERE department_id = deptnums(i)
ORDER BY department_id, employee_id;
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’ );
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘Total records inserted: ‘ || SQL%ROWCOUNT);
END;
==========================================================
Dept 30: inserted 1 records
Dept 10: inserted 0 records
Dept 50: inserted 0 records
Total records inserted: 1
A
B