This programme shows you we may retrieve the results from a query. It works with a cursor that contains another cursor. The example below is a cursor that retrieves data from employee table, along with data from a cursor that is linked to the employee table. Doing so, we may add data to the employee data. Here some data on dependants are added. Likewise, we may add data on projects that employees worked on etc.
As two cursors are used, we must start two loops: one loop that retrieves data on employee and a second loop that retrieves data on the dependants.
We do not know upfront how the data are returned. Therefore a so-called weak cursor is used. This cursor definition does not return the type data that are returned.
The code to undertake is:
create or replace PROCEDURE FamilyWeakCursor AS TYPE DepType IS REF CURSOR; DepCursor DepType; EmpLName employee.LName%TYPE; EmpFName employee.FName%TYPE; EmpRELATIONSHIP dependent.RELATIONSHIP%TYPE; EmpDEPENDENT_NAME dependent.DEPENDENT_NAME%TYPE; CURSOR EmpWork IS SELECT LName, FName, CURSOR(SELECT RELATIONSHIP, DEPENDENT_NAME FROM dependent d WHERE d.essn = e.ssn) AS DepCursor FROM employee e ORDER BY LName; BEGIN OPEN EmpWork; LOOP FETCH EmpWork INTO EmpLName, EmpFName, DepCursor; EXIT WHEN EmpWork%NOTFOUND; dbms_output.put_line ('Processing here for ' || EmpLname||' ,' || EmpFname ); LOOP FETCH DepCursor INTO EmpRELATIONSHIP, EmpDEPENDENT_NAME; EXIT WHEN DepCursor%NOTFOUND; dbms_output.put_line ('Processing here for ' || EmpLname||' ,' || EmpFname || ' and for dependant ' || EmpDEPENDENT_NAME||' (' ||EmpRELATIONSHIP || ')'); END LOOP; END LOOP; CLOSE EmpWork; END; /