Re: Help with CURSOR question, please?

From: Michael <michael_at_wildher.nl>
Date: Thu, 23 Jul 1998 21:17:58 +0200
Message-ID: <35B78C66.7EF245B9_at_wildher.nl>

 

agibbons_at_erols.com schreef:

Can you offer any suggestions please?

I am trying to do the following:

1.  open cursor cur1 to retreive all a.id rows in table a
2.  for each a.id retreived from table a do the following:
     a)  open cursor cur2 and retreive only those rows
           from table b where a.id = b.e
     b)  write some text to a file

The problem I am having is with how to restrict cur2 to retreive
only those rows from table b based on the value of a.id in cur1
each time the LOOP is encountered.

Basically, where does the where statement go???

Any advice would be much appreciated.

Yours,

Elliot G.

My routine is below:

DECLARE

 out_file1 TEXT_IO.FILE_TYPE;

 CURSOR cur1
  IS
   SELECT id
   FROM a
   ORDER BY a.id ASC;

 id1 a.id%type;

 CURSOR cur2
  IS
   SELECT e, f
   FROM b
   ORDER BY e, f ASC;

 e1 b.e%type;
 f1 b.f%type;

BEGIN

 out_file1 := TEXT_IO.FOPEN('c:\myfile', 'w');

  TEXT_IO.PUT_LINE(out_file1, 'mytext');

 FOR cur1_rec IN cur1 LOOP

  TEXT_IO.PUT_LINE(out_file1, 'more text');

  OPEN cur2;
   LOOP
    FETCH cur2 INTO e1, f1;
     IF (cur2%found) THEN
       TEXT_IO.PUT_LINE(out_file1, to_char(e1)||','||to_char(f1));
     ELSE
      EXIT;
     END IF;
   END LOOP;
  CLOSE cur2;

  TEXT_IO.PUT_LINE(out_file1, 'some more text');

 END LOOP;

 TEXT_IO.FCLOSE(out_file1);

END;

 
You should use the value of a.id as a parameter in the second cursor:
( I leave out TEXT_IO lines in this example)

 CURSOR cur1
  IS
   SELECT  a_alias.id   id_column_alias
   FROM a              a_alias
   ORDER BY a.id ASC;

 CURSOR cur2(b_id   b.e%type)
  IS
   SELECT  b_alias.e   e_column_alias
   ,               b_alias.f    f_column_alias
   FROM b             b_alias
   WHERE b_alias.e = b_id
   ORDER BY b_alias.e, b_alias.f ASC;

cur2_rec   cur2%rowtype;

BEGIN

FOR cur1_rec IN cur1 LOOP
  OPEN cur2(cur1_rec.id_column_alias);
   LOOP
    FETCH cur2 INTO cur2_rec;
     EXIT WHEN cur2%notfound;
       TEXT_IO.PUT_LINE(out_file1, to_char(cur2_rec.e_column_alias)||','||to_char(cur2_rec.f_column_alias));
   END LOOP;
  CLOSE cur2;
 END LOOP;
END;

Note that I give the tables and columns aliasses and that I use rowtype in stead of variables for each selected column. Not that your layout was wrong but this improves performance and make it more readable in large programms. Make shure you choose short but adequate names for your aliasses.

Good luck
--
                 \|/
                (_at_ _at_)
  +-------oOO----(_)--------------+
  |          Michael Doel         |
  |       michael1_at_xs4all.nl      |
  |http://www.xs4all.nl/~michael1 |
  +--------------------oOO--------+
               |__|__|
                || ||
               ooO Ooo
  Received on Thu Jul 23 1998 - 21:17:58 CEST