iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Training

    Home      .Services     Login       Start Learning     Certification      .                 .Share your BELIEF(s)...

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - PL/SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 |

 

Lesson 03

“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:

   PLS-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 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.