Manuscript

Go to “MS-DOS.”

Change directory to the iself directory.  And login to “sqlplus” as “iself/schooling.”

Create a table named "dept_stat".

It should contain four columns:  department name, total number of employees, total salary, and average salary.  And define the department name as a primary key.

>>          create table dept_stat

                  (dname          varchar2(20) primary key,

                  total_empno          number(3),

        total_sal          number(8,2),

        avg_sal          number(8,2));

 

Table created.

 

Go to “Notepad”

Open the “test_for_loop” file from the iself directory.

 

Modify the PL/SQL block to populate the department stat table.

>>        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;

            begin

                        -- loop to read cursor record.

                        for v_ds in c_ds loop

                             -- insert into dept_stat

                             insert into dept_stat

                             values (v_ds.dname, v_ds.ttemp,

v_ds.ttsal, v_ds.avsal);

                        end loop;

                    -- save the insert transaction.

                    commit;

            end;

            /

 

 

In the “PL/SQL” body, insert each cursor record into the dept_stat table.

Save the insert transaction.

=

Save the file as “test_for_loop2” at the iself directory.

=

Go to “sqlplus.”

 

Run the file.

>> @test_for_loop2

 

Query the dept_stat table.

>> select * from dept_stat;

=

Now, you should practice this over and over, until you become a master at it.

Good Luck!