iSelfSchooling.com - Since 1999  References  |  Job Openings
    Home  | Search more  | Oracle Syntax  | Computer Institute   | (Login or Register to access to VIDEOS)
 

Copyright & User Agreement

   Suggestions Email2aFriendHomepage us! |  Bookmark

Services

  Vision/Mission

  Services

  Biography

  Contact Us

 FREE Training

  Start...

  SQL

  PL/SQL

  Forms 

  Reports

  DBA Fundamentals

  Performance

  Prepare for OCP

  ShareUrNotes

...

  Acknowledgement

  Who is who

  University Directory

  Links...

 

 

 

More Resources by Google:

Topics:  Hands-On 04 - Explicit Cursor Handling

 

In this Hands-On, you will declare a cursor to list the department name (dname), total number of employees (ttemp), total salary (ttsal), and average salary (avsal) for each department from the department table and employee table.

 

Then, you print all department name with their total number of employees (For example: ACCOUNTING has 3 employees) for each department using the notepad editor.  (Note: We don’t use the ttemp, ttsal, and avsal item in this Hands-On)

 

The Core Subjects are:

    1- Implicit vs. explicit cursors

    2- Declaring explicit cursors

    3- Using explicit cursor.

        a- Open Cursor

        b- Fetch Cursor

        c- Close Cursor

        d- Declare variable

    3- Using simple loop

        a- Exit statement.


 

Manuscript

Go to “MS-DOS.”  Change directory to the iself directory.  And login to “sqlplus” as "iself/schooling."

=

Open the notepad editor,

write a PL/SQL block to print all the department names with their total number of employees.

 

Declare a record type to have four items:  Department name, total number of employees, total salary, and average salary.

 

Follow the naming convention to start a type name with "t_", a variable name with "v_", a cursor name with "c_", and a parameter name with "p_".

 

Declare a cursor to list the department name, total number of employees, total salary, and average salary from the department and employee table order by the department name.

 

Declare a variable for a cursor.

 

 

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

-- open the cursor

     open c_ds;

-- start loop

Loop

--read a record

          fetch c_ds into v_ds;

                             -- exit from loop

exit when c_ds%notfound;

                             -- list dept. name

                             dbms_output.put_line(v_ds.dname ||

‘ has ’ || v_ds.ttemp || ‘ employees.’);

                    end loop;

                    close c_ds;

          end;

          /

 

=

In the body or execution section, open the cursor.

Make a simple loop.

In the loop, read a record one at a time using fetch statement.

Make sure to exit from the loop.

Use the "dbms_output" package to print the department name and their total number of employees.

End the loop and then close the cursor.

=

Make it easy to read.

=

Save the file in the "iself" directory as "test_fetch_cursor.sql."

=

Go to “SQLPLUS.”

Get the file.

Compile and run the PL/SQL block.

No error messages in the compilation.

 

Set the serveroutput to on.

Then run the file.

>> set serveroutput on

>> @test_fetch_cursor

 

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

Good Luck!

 

 
 
Google
 
Web web site