Re: Passing values to Nested Cursors (possible?)

This answers your query using "flexible cursors" - i.e. cursors that will
accept parameters...

DECLARE    CURSOR A2 (id_in emp_preference.id%TYPE) IS

      SELECT profile
      FROM   emp_preference
      WHERE  id = id_in;

   varX emp_preference.profile%TYPE;

BEGIN    /* Use a CURSOR FOR LOOP with an IMPLICIT cursor to loop through the first

      table */
   FOR rec_emp IN (SELECT id

, name

                   FROM   emp
                   WHERE  sal > 10000) LOOP

      /* Now open the static cursor and pass in the current id from
the returning
         records from the first loop */
      OPEN A2(rec_emp.id);
      FETCH A2 INTO varX;

      IF A2%FOUND THEN
         DBMS_OUTPUT.PUT_LINE(TO_CHAR(rec_emp.id)||','||rec_emp.name||','||varX);
      END IF;
      CLOSE A2;

   END LOOP;
     

END;
/

But what I'd actually do is to join the two tables on id in the CURSOR FOR LOOP.
Opening and closing cursors can be "expensive"...

BEGIN

   /* Do it all in one go... */
   FOR rec_emp IN (SELECT a.id


                   ,      a.name


                   ,      b.profile


                   FROM   emp a


                   ,      emp_preference b


                   WHERE  a.id = b.id
                   AND    a.sal > 10000) LOOP

         DBMS_OUTPUT.PUT_LINE(TO_CHAR(rec_emp.id)||','||rec_emp.name||','||rec_emp.profile);

HTH Adrian Received on Fri Dec 21 2001 - 09:43:37 CET