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 05

￿A successful individual typically sets his next goal somewhat but not too much above his last achievement. In this way he steadily raises his level of aspiration.￿ Kurt Lewin (1890 - 1947)

 

Read first then play the video:

   PLS-VIDEO -Cursor Parameter
 
 

Cursor Parameter

 

Hand-On Introduction

In this Hands-On, you use the ￿test_for_loop2￿ file from the previous Hands-On. Then modify the PL/SQL block that the cursor only calculates the department statistics for a specific department. For example the department number 10. Save the file in the iself directory as ￿test_for_loop3.￿

 

Go to ￿MS-DOS.￿ Change directory to the iself directory. And login to ￿SQLPLUS￿ as ￿iself/schooling.￿ Open the notepad editor. Open the ￿test_for_loop2￿ file from the iself directory.

 

In the declaration section, modify the cursor to use a cursor parameter to pass the department number as an input parameter. Add a WHERE clause condition to query only the input parameter. Declare the department number variable.

 

Defining parameter in a PL/SQL cursor

In the execution section, assign number 10 to the department number variable. Modify the FOR LOOP statement to pass the department number as an input parameter.

 

(Notepad)

DECLARE

 

    -- define department statistics

    cursor c_ds (p_deptno dept.deptno%TYPE) is

        select dname, count (*) ttemp,

                    sum(sal) ttsal, avg(sal) avsal

            from dept d, emp e

            where d.deptno = e.deptno

                and d.deptno = p_deptno

            group by dname;

 

    -- define deptno variable

    v_deptno NUMBER(2);

 

BEGIN

    -- assign deptno 10

    v_deptno := 10;

 

    -- loop to read cursor record.

    for v_ds in c_ds (v_deptno) 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;

/

Save a PL/SQL block

Save the file in the iself directory as ￿test_for_loop3￿.

 

Run a PL/SQL block

Go to ￿sqlplus.￿ Query the department statistics table. Then truncate the dept_stat table in the case there are records in the table.

SQL> truncate table dept_stat;

 

Check the output

Query the dept_stat table again.

SQL> select * from dept_stat;

No data!

 

Run the file.

SQL> @test_for_loop3

 

Query the dept_stat table.

SQL> select * from dept_stat;

Now, you see the result here!

 

￿Eighty percent of success is showing up.￿ Woody Allen

 

Questions:

Q: What is the cursor parameter in the PL/SQL language?
Q: Where do you define a cursor parameter in the PL/SQL language?

Q: Write a PL/SQL block to populate the department table statistics into the ￿dept_stat￿ table for a specific department.

Statistics Information:

The Department Number,

The total number of employees in each department,

The total salary paid in each department, and

The average salary paid in each department.