Re: Help with CURSOR question, please?

And what about join ?

DECLARE  out_file1 TEXT_IO.FILE_TYPE;

 CURSOR cur1
  IS
   SELECT a.id, b.e,b.f
   FROM a, b
   WHERE b.id = a.id
   ORDER BY a.id, b.e,b.f ;

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');

  TEXT_IO.PUT_LINE(out_file1,
to_char(cur1_rec.e)||','||to_char(cur1_rec.f));

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

 END LOOP;  TEXT_IO.FCLOSE(out_file1);

END; agibbons_at_erols.com wrote in article <6p7kmv$dmn$1_at_winter.news.erols.com>...
> 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;
>
>
>
Received on Fri Jul 24 1998 - 01:14:33 CDT