Oracle Procedural Language Development
(90.459-021)

Project Assignment #3
 
Instructor:  Leon Xia

A)  Overview

(Project due date:  Next Lecture Date.)
(For questions, please contact instructor at:   lxia88@yahoo.com)

Understand PL/SQL CURSOR could accept parameters
Learn how to use a cursor which accept parameters
Learn how to use a cursor_for_loop within another cursor_for_loop

-----------------------------------------------------------------------------------
 
To understand the syntax for this assignment, read and run the two examples in the lecture page.  (prc_print_dept_emp.sql)

B)  The Project Assignments:  (total 1)

        /****************************************
        * Description:
        *     This program will print out location 
        *     of each dept, then print the name, job 
        *     of each employee at that location;
        *     then, print the manager’s name for that person
        * 
        *     1) loop through a cursor cur_dept to get 
        *        the location, deptno, AND MANAGERS NAME of each dept
        *     2) pass the deptno to the second cursor cur_emp to 
        *        get the name and job for each employee at that location 
        *     3) in this program, you learn:
        *        use nested loops
        *        use cursor within cursor
        *        use the value you get from a parent cursor 
        *        and pass it to a child cursor
        *
        ****************************************
        */

-- The out put of the assignment will look similar to:
SQL> 
SQL> exec prc_print_dept_emp

Dept Locaion: NEW YORK

#1 CLARK MANAGER, reports to: KING
#2 KING PRESIDENT, reports to:
#3 MILLER CLERK, reports to: CLARK

Dept Locaion: DALLAS

#1 SMITH CLERK, reports to: FORD
#2 JONES MANAGER, reports to: KING
#3 SCOTT ANALYST, reports to: JONES
#4 ADAMS CLERK, reports to: SCOTT
#5 FORD ANALYST, reports to: JONES
#6 LeonXia Manager, reports to:

Dept Locaion: CHICAGO

#1 ALLEN SALESMAN, reports to: BLAKE
#2 WARD SALESMAN, reports to: BLAKE
#3 MARTIN SALESMAN, reports to: BLAKE
#4 BLAKE MANAGER, reports to: KING
#5 TURNER SALESMAN, reports to: BLAKE
#6 JAMES CLERK, reports to: BLAKE

Dept Locaion: BOSTON

PL/SQL procedure successfully completed.

Hint: 1)  (FYI) Some student will think of using ‘select into’ to solve the problem.

Inside the cur_emp, since you already have the empno for his manager (the MGR) field.  Do a ‘select ename into v_mgr from emp where empno = rec_emp.mgr’.  Then you have the manager’s name.  Of course, you have to declare the v_mgr variable and make sure that cur_emp have MGR be part of the selection.

However , this syntax will run into exception if not exactly ONE manager is selected.  After we learn exception handling, we can use this syntax.

          2) Alternatively, which I suggest that you should use in today’s assignment.
You declare a third cursor and get the work done.  That will be a better answer, because a cursor_for_loop is an explicit cursor, it runs faster ‘select into’, which is an implicit cursor.
              Here is the syntax:

i)	declare the v_mgr variable as varchar2(50) or emp.ename%type:
ii)	declare the cursor cur_mgr

cursor cur_mgr (cp_empno number)
 is
		select ename from emp
 	where empno = cp_empno;

 		iii)       inside the second cursor, add the code:

                        -- re-initialized v_mgr, so you will not use the old value
                        v_mgr := ' ';
                        
                        For rec_mgr in cur_mgr (rec_emp.mgr) loop
                           V_mgr := rec_mgr.ename;
                           -- exit the loop after only one fetch.
                           Exit;
                        End loop;

iii)	modify the dbms_output.put_line code, 
	|| ‘ , reports to: ‘||v_mgr);
                        	
This is a perfect example where you would like to avoid ‘select into’ and use explicit cursor for loops.

As you can see that our project is getting very exciting and interesting.