iSelfSchooling.com - Copyright © 1999-2007 iSelfSchooling.com  References  Job Openings  |  Secure Login
    Home  | Search more...  |  FREE Online VIDEO Oracle Training  |  Gift Store  |  Bookstore

   Unlimited access!   

    Oracle  Syntax  | Suggestions Your Contribution  |  FREE Legal Forms

 

Email2aFriend Homepage us! |  Bookmark   -  Copyright & User Agreement

Products/Services

 Vision/Mission

 Community Sharing

 Services

  Products

 Biography

 Contact Us

 FAQ

 Current News

 Website Traffic

 Bookstore

 FREE Training

 SQL

 PL/SQL

 Forms 

 Reports

 Other TOOLS

 Fundamentals

 Performance

 OEM

 Application Server

 Grid Control

 Articles

 Prepare for OCP

Oracle SYNTAX

 Oracle Functions

 Oracle Syntax

 Oracle 10g Syntax

  PL/SQL Syntax

UNIX and more...

 UNIX for DBAs

 LINUX for DBAs

 DB using PHP

  A+ Certification

 Basics of JAVA  

 Tips of  SEO

Finance/Jobs

 Financial Aid

 Skilled

 Oracle

 Jobs

  Magazine

More Training

 Q & Answers

 SQL-PL/SQL

 DBA

 Developer

 Important Notes

 Case Studies

 9i New Features

 10g New Features

 10g Qs/As

 Grid Control

 OracleAS # I

 OracleAS # II

  LDAP and OID

  HTTP Server

 Instructor-Led

  Virtual Hosts

 Community Sharing

More to know...

Acknowledgement**

 FREE Legal Forms

 Who is who

 Market Place

 University Directory

 Advisory Articles

 Links...

 

 

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!

 

 
 
Google
 
Web web site