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.