Nested Cursors: Reading from Multiple Tables

The following is an example of how to loop through a cursor within a cursor to generate a report as shown. This uses the HR schema supplied by Oracle.

DECLARE 
    ------------------------------------------------------- 
    -- This cursor pulls 'Clerks' from the Jobs table.  
    ------------------------------------------------------- 
    CURSOR cur_job IS 
      SELECT job_id 
             , job_title 
        FROM jobs 
       WHERE job_title LIKE '%Clerk%'; 
    rec_job cur_job%ROWTYPE; -- record layout matches cursor 
    ------------------------------------------------------- 
    -- This cursor pulls employees with the parameter for 
    -- the Job_Id being supplied on the open of the employee 
    -- cursor.  
    ------------------------------------------------------- 
    CURSOR cur_emp ( 
      inp_job VARCHAR2 ) IS 
      SELECT RPAD(last_name, 10) AS Last_Name 
             , salary 
        FROM employees 
       WHERE job_id = inp_job 
         AND salary <= 2500 
       ORDER BY salary; 
    rec_emp cur_emp%ROWTYPE; -- record layout matches cursor 
BEGIN 
    OPEN cur_job; 

    ------------------------------------------------------- 
    -- In the outer loop, fetch one record from the Jobs 
    -- cursor, and print the info. 
    ------------------------------------------------------- 
    LOOP 
        FETCH cur_job INTO rec_job; 

        Exit WHEN cur_job%NOTFOUND; 

        dbms_output.PUT_LINE(CHR(10) -- line break 
                             ||'Job: ' 
                             ||rec_job.job_title 
                             ||CHR(10) 
                             ||'---------------------'); 

        ----------------------------------------------------- 
        -- in the inner loop, open the employee cursor using 
        -- the Job_Id from the Jobs cursor. Print the employee 
        -- name, and salary, before closing to get another 
        -- jobs record. 
        ----------------------------------------------------- 
        OPEN cur_emp (rec_job.job_id); 

        LOOP 
            FETCH cur_emp INTO rec_emp; 

            Exit WHEN cur_emp%NOTFOUND; 

            dbms_output.PUT_LINE(rec_emp.last_name 
                                 ||' $' 
                                 ||rec_emp.salary); 
        END LOOP; 

        CLOSE cur_emp; 
    ----------------------------------------------------- 
    END LOOP; 

    CLOSE cur_job; 
END; 

 

Tiny URL: http://tinyurl.com/nest-cur