Re: PL/SQL
From: Manh-Hoa Le <at770_at_FreeNet.Carleton.CA>
Date: 1996/02/03
Message-ID: <DM6EB4.2K9_at_freenet.carleton.ca>#1/1
end;
>
>
> Looks straight forward to me. The only hitch might be that you will have
> one read consistant snapshot for the outer cursor that reads all the rows,
> while you will be opening a second cursor several times after the first
> was opened. This means that if someone changed the values of x or y after
> you started your outer loop it would not be reflected in that query, but
> would be reflected in your inner loop.
>
> David Jordan
> DBA
> Ford Motor Company
Received on Sat Feb 03 1996 - 00:00:00 CET
Date: 1996/02/03
Message-ID: <DM6EB4.2K9_at_freenet.carleton.ca>#1/1
Hi Dave,
Why don't you code like this:
begin
select ....
from this_table a
where a.current_y is not null
and exists (select '1'
from this_table b
where b.x = a.current_y);
exception ....
end;
M-H
Dave Jordan (jordan_at_pt5026.pto.ford.com) writes:
> In article <4es8d5$4f5$1_at_mhafc.production.compuserve.com>, Benita Haslett <75041.1664_at_CompuServe.COM> writes:
>
>> I have a PL/SQL program in which I must read in a large table >> with a cursor and also access the table directly. >> Let's say that this_table had columns x and y; x has not null, >> unique values; y may be null. Each value in >> column y must exist somewhere in the table as an x value. >> For each record in this_table fetched with the cursor, if >> current_y is not null, I would like to : >> begin >> select from this_table where x = current_y; >> exception process_error; >> end; >> Is this ok or is it risky?
>
>
> Looks straight forward to me. The only hitch might be that you will have
> one read consistant snapshot for the outer cursor that reads all the rows,
> while you will be opening a second cursor several times after the first
> was opened. This means that if someone changed the values of x or y after
> you started your outer loop it would not be reflected in that query, but
> would be reflected in your inner loop.
>
> David Jordan
> DBA
> Ford Motor Company
Received on Sat Feb 03 1996 - 00:00:00 CET