Create a PL/SQL block to retrieve name of each department from dept table and print each department name to screen, incorporating a PL/SQL table.
A) Declare PL/SQL table my_dept_table to temporarily store name of departments.
B) Using a loop retrieves names of all departments currently in dept table and store them in PL/SQL table. Each department number is a multiple of 10.
C) Using another loop, retrieves department names from PL/SQL table and print them to screen, using dbms_output.put_line.
Answer
declare
cursor cur1 is select * from ddept_a5518;
cursor cur2 is select * from my_dept_table;
dept_row cur1%rowtype;
total number(4);
dept_row_1 cur2%rowtype;
total number(4);
begin
–execute immediate 'CREATE GLOBAL TEMPORARY TABLE my_dept_table ( dno NUMBER, dname VARCHAR2(20) ) ON COMMIT PRESERVE ROWS';
delete from my_dept_table;
open cur1;
loop
fetch cur1 into dept_row;
exit when cur1%notfound;
insert into my_dept_table values (dept_row.dno*10,dept_row.dname);
dbms_output.put_line('Dept Name from DDEPT_A5518 : ' || dept_row.dname);
end loop;
close cur1;
open cur2;
loop
fetch cur2 into dept_row_1;
exit when cur2%notfound;
dbms_output.put_line('Dept Name from MY_DEPT_TABLE : ' || dept_row_1.dname);
end loop;
close cur2;
end;
/