|
“Never pretend to a
love which you do not actually feel, for love is not ours to
command.” Alan Watts |
Read
first then play the video:
PLS004(VIDEO)-Controlling
Process Flow
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 the iself
directory.
Go to “MS-DOS.” Change
directory to the iself directory. And login to “SQLPLUS” as “iself/schooling.”
FOR LOOP statement
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.
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 the iself 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
|
“Most folks are
about as happy as they make up their minds to be.” Abraham
Lincoln (1809 - 1865) |
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.
|
