Use a PL/SQL table of records to retrieve all information from the DEPT table and print information to the screen. You will declare a table to store names and locations of the departments. Remember that the department number is a multiple of 10. Retrieve all department information from the DEPT table to the PL/SQL table using a loop, and then use another loop to print the information.

Question

What will be an ideal response?

Answer 1

```
SQL> DECLARE
2 CURSOR dept_cur IS
3 SELECT * FROM dept;
4 TYPE dept_table_type IS TABLE OF dept%ROWTYPE
5 INDEX BY BINARY_INTEGER;
6 dept_table dept_table_type;
7 TYPE name_table_type IS TABLE OF dept.DeptName%TYPE
8 INDEX BY BINARY_INTEGER;
9 name_table name_table_type;
10 TYPE location_table_type IS TABLE OF dept.location%TYPE
11 INDEX BY BINARY_INTEGER;
12 location_table location_table_type;
13 c NUMBER(1) := 1;
14 BEGIN
15 OPEN dept_cur;
16 FETCH dept_cur INTO dept_table(c);
17 WHILE dept_cur%FOUND LOOP
18 c := c + 1;
19 FETCH dept_cur INTO dept_table(c);
20 END LOOP;
21 FOR i IN 1..dept_cur%ROWCOUNT LOOP
22 name_table(i) := dept_table(i).DeptName;
23 location_table(i) := dept_table(i).Location;
24 DBMS_OUTPUT.PUT
25 ('Dept Name: ' || name_table(i));
26 DBMS_OUTPUT.PUT_LINE
27 (' Location : ' || location_table(i));
28 END LOOP;
29 END;
30 /
Dept Name: Finance Location : Charlotte
Dept Name: InfoSys Location : New York
Dept Name: Sales Location : Woodbridge
Dept Name: Marketing Location : Los Angeles
Dept Name: Projects Location : Monroe

PL/SQL procedure successfully completed.
```