View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.
Examine the PL/SQL block that you execute to find the average salary for employees in the
‘Sales’ department:
DECLARE
TYPE emp_sal IS TABLE OF employees.salary%TYPE INDEX BY VARCHAR2(20);
v_emp_sal emp_sal;
PROCEDURE get_sal(p_dept_name VARCHAR2, p_arr OUT emp_sal) IS
BEGIN
SELECT AVG(salary) INTO p_arr(p_dept_name)
FROM employees WHERE department_id=
(SELECT department_id FROM departments
WHERE department_name=p_dept_name);
END get_sal;
BEGIN
get_sal(‘Sales’,v_emp_sal);
DBMS_OUTPUT.PUT_LINE( v_emp_sal(‘Sales’));
END;
/
What is the outcome?
A.
It executes successfully and gives the correct output.
B.
It generates an error because the associative array definition is not valid.
C.
It generates an error because an associative array cannot be passed to a procedure in OUT
mode.
D.
It generates an error because an associative array cannot be used with the SELECT INTO
statement.
A.
DECLARE
TYPE emp_sal IS TABLE OF employees.salary%TYPE INDEX BY VARCHAR2(20);
v_emp_sal emp_sal;
PROCEDURE get_sal(p_dept_name VARCHAR2, p_arr OUT emp_sal) IS
BEGIN
SELECT AVG(salary) INTO p_arr(p_dept_name)
FROM employees WHERE department_id=
(SELECT department_id FROM departments
WHERE department_name=p_dept_name);
END get_sal;
BEGIN
get_sal(‘Sales’,v_emp_sal);
DBMS_OUTPUT.PUT_LINE( v_emp_sal(‘Sales’));
END;
D
need bulk collect
ok, it’s A
only becuase aggregate functions return scalar result