Create a PL/SQL block to retrieve last name, first name, and salary from the EMPLOYEE table into a cursor. Populate three tables with the values retrieved into the cursor: one to store last names, one to store first names, and one to store salaries. Use a loop to retrieve this information from the three tables and print it to the screen, using the package DBMS_OUTPUT.PUT_LINE.

Question

What will be an ideal response?

Answer 1

```
SQL> DECLARE
2 TYPE last_table_type IS TABLE OF employee.Lname%TYPE
3 INDEX BY BINARY_INTEGER;
4 TYPE first_table_type IS TABLE OF employee.Fname%TYPE
5 INDEX BY BINARY_INTEGER;
6 TYPE salary_table_type IS TABLE OF employee.Salary%TYPE
7 INDEX BY BINARY_INTEGER;
8 last_tab last_table_type;
9 first_tab first_table_type;
10 salary_tab salary_table_type;
11 CURSOR emp_cur IS
12 SELECT Lname, Fname, Salary
13 FROM employee;
14 c NUMBER(2) := 1;
15 BEGIN
16 OPEN emp_cur;
17 FETCH emp_cur INTO
18 last_tab(c), first_tab(c), salary_tab(c);
19 WHILE emp_cur%FOUND LOOP
20 c := c + 1;
21 FETCH emp_cur INTO
22 last_tab(c), first_tab(c), salary_tab(c);
23 END LOOP;
24 FOR i IN 1..emp_cur%ROWCOUNT LOOP
25 DBMS_OUTPUT.PUT
26 (last_tab(i)||', '||first_tab(i));
27 DBMS_OUTPUT.PUT_LINE
28 (' Salary: '||salary_tab(i));
29 END LOOP;
30 END;
31 /
Smith, John Salary: 265000
Houston, Larry Salary: 150000
Roberts, Sandi Salary: 75000
McCall, Alex Salary: 66500
Dev, Derek Salary: 80000
Shaw, Jinku Salary: 24500
Garner, Stanley Salary: 45000
Chen, Sunny Salary: 35000

PL/SQL procedure successfully completed.
```