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

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

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

“Don't walk behind me, I may not lead. Don't walk in front of me, I may not follow. Just walk beside me and be my friend.” Albert Camus (1913 - 1960) (attributed)

 

Read first then play the video:

   PLS-VIDEO -Explicit Cursor Handling

  

Explicit Cursor Handling

 

Hands-On Introduction

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. You use the notepad editor.

 

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

 

Declare Variables (Naming convention)

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

The explicit cursor will be defined in the declaration section. Once you define an explicit cursor, you should open it the PL/SQL body, fetch the cursor one at a time, exit from the loop if you have one, and at the end close the cursor.

 

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.

 

OPEN, FETCH, LOOP, and EXIT a Cursor (Simple loop)

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.

 

(Notepad)

DECLARE

 

    -- Declare a variable for a cursor.

    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;

 

            --read a record

            FETCH c_ds INTO v_ds;

 

            -- exit from loop when there is no record

            EXIT WHEN c_ds%notfound;

 

            -- list dept. name

            dbms_output.put_line

                    (v_ds.dname ||

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

 

    -- end the loop

    END LOOP;

 

    -- close cursor

    CLOSE c_ds;

END;

/

 

Save a PL/SQL block

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.

SQL> get c: test_fetch_cursor.sql

SQL> /

You should not have any error messages in the compilation. If you have try to correct your errors before to get to next step.

 

Run a PL/SQL block

Set the serveroutput to on. Then run the file.

SQL> set serveroutput on

SQL> @test_fetch_cursor

 

“The first step to getting the things you want out of life is this: Decide what you want.” Ben Stein

 

Questions:

Q: Describe that why do we need to use a solid naming convention in our PL/SQL program.

Q: What is the explicit cursor in the PL/SQL language?

Q: What are the differences between the explicit and implicit cursors?

Q: Where do you declare an explicit cursor in the PL/SQL language?

Q: Where do you declare an implicit cursor in the PL/SQL language?

Q: What is a simple loop in the PL/SQL language?

Q: How do you open an explicit cursor in the PL/SQL language?

Q: What does the FETCH statement in the Oracle PL/SQL language?

Q: How do you terminate from a simple loop in the PL/SQL language?

Q: How do you OPEN or CLOSE a cursor in the PL/SQL language?

Q: 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 order by the department name.

Write all department name with their total number of employees for each department using the notepad editor.

For example: ACCOUNTING has 3 employees.

(Note: Don’t use the ttemp, ttsal, and avsal item at this time)