|
More Resources by
Google: |
|
|
|
|
Topics: Hands-On 05 - Controlling Process Flow
In this Hands-On, you modify the previous PL/SQL block “test_fetch_cursor”
to use the “FOR LOOP” statement vs simple “LOOP.”
Modify the PL/SQL block to list only the department name that their total
number of employees are more than 4. Save the file as “test_for_loop” in
the iself directory.
The Core Subjects are:
1- Process Flow
a. IF ... ELSE ... END IF
b. LOOP statement
c. FOR LOOP statement
Manuscript
Go to “MS-DOS.”
Change directory to the iself directory. And login to “SQLPLUS” as
“iself/schooling.”
Open the notepad editor.
Open the “test_fetch_cursor” file from the iself directory.
Modify the PL/SQL block to do the same thing using the “FOR LOOP” statement.
Remove the “t_ds” record type and the v_ds variable from the declaration
section and delete the open, close, fetch and exit statement from the body
section, since the “FOR LOOP” statement does them implicitly.
Save the file as “test_for_loop” in the iself directory.
Go to “SQLPLUS.”
Compile and run it.
No compilation errors.
Set the serveroutput on.
Run the file again.
The same result!
Go back to Notepad.
Modify the PL/SQL block to list only the department name that their total
number of employees are more than 4.
declare
type t_ds is record (
dname dept.dname%type,
ttemp number(3),
ttsal number(8,2),
avsal number(8,2));
-- define department statistics
cursor c_ds is
select dname, count (*) ttemp,
sum(sal) ttsal, avg(sal) avsal
from dept d, emp e
where d.deptno = e.deptno
group by dname
order by 1;
-- define a variable for cursor
v_ds t_ds;
begin
-- for loop to read cursor record.
for v_ds in c_ds loop
if v_ds.ttemp > 4 then
dbms_output.put_line(v_ds.dname ||
‘ has ’ || v_ds.ttemp || ‘ employees.’);
end if;
end loop;
close c_ds;
end;
/
Save the file.
Go to “SQLPLUS.”
And run it again.
>> start
test_for_loop
Now, you should practice this over and over, until you become a master at it.
Good Luck!
|