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!