|
Controlling Process Flow
Hands-on
Introduction
In this
Hands-On, you modify the previous PL/SQL
block �test_fetch_cursor� to use the �FOR
LOOP� statement vs simple �LOOP.� The simple
LOOP, FOR LOOP, IF-ENDIF, etc are examples
of the PL/SQL statements that can be use to
apply in program logic to implement
controlling process flow.
Modify the
PL/SQL block to list only the department
name that their total number of employees is
more than 4. Save the file as
�test_for_loop� in your directory.
Go to
�MS-DOS.� Change directory to your
directory. And login to �SQLPLUS� as
�oracle/learning�
FOR LOOP
statement
Open the
notepad editor. Open the �test_fetch_cursor�
file from your directory.
Modify the
PL/SQL block to do the same thing using the
�FOR LOOP� statement.
FOR LOOP vs.
Simple LOOP
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.
(Notepad)
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
DBMS_OUTPUT.PUT_LINE
(v_ds.dname ||
� has � ||
v_ds.ttemp || �
employees.�);
END IF;
END LOOP;
CLOSE
c_ds;
END;
/
Save a PL/SQL
block
Save the file
as �test_for_loop� in your directory.
Go to
�SQLPLUS.� Compile and run it.
SQL> get c:
test_for_loop.sql
SQL> /
SHOW ERRORS
You should not
have any error. If you have any error, use
�SHOW ERRORS� command and correct your
errors.
SERVEROUTPUT
Notice that
you should have the serveroutput on to see
the results, if you have exit from your
session. Run the file again and you should
have the same output!
IF-THEN-END
statement
Go back to
Notepad. Modify the PL/SQL block to list
only the department name that their total
number of employees is more than 4.
(Notepad)
DECLARE
-- 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;
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;
END;
/
Save a PL/SQL
block
Save the file.
Go to �SQLPLUS.� And run it again.
SQL> start test_for_loop
Questions:
Q: What does
the FOR LOOP statement in the PL/SQL
language?
Q: What are
the differences between a SIMPLE LOOP and
FOR LOOP?
Q: What are
the advantages of using the FOR LOOP
statement?
Q: What does
the SHOW ERRORS statement in the PL/SQL
language?
Q: What is the
IF-THEN-ELSE statement?
Q: Modify the
previous PL/SQL block and use the �FOR LOOP�
statement vs the simple �LOOP� statement.
Also, list only the department name that
their total number of employees is more than
4. |
