Re: Nested queries in function

From: aymeric barantal <mric_at_grolier.fr>
Date: Tue, 23 Nov 1999 13:35:20 +0100
Message-ID: <81e1lc$6ha$1@front4.grolier.fr>

[Cc by mail]
<buurd_at_my-deja.com> wrote in message news:81dmt4$ho9$1_at_nnrp1.deja.com...
> Hi!
> I have a function that should look like this in some pseduo languange.
> How to do this function in pl/sql

Use 2 cursors with the second one using an input parameter : declare

    cursor c1 is select toto from table1;     hv_toto table1.toto%TYPE;
    cursor c2 (in_toto table1.toto%TYPE) is

        select tata from table2 where toto_id = in_toto;     hv_tata table2.tata%TYPE;
begin

    open c1;
    loop

        fetch c1 into hv_toto;
        exit when c1%NOTFOUND;
        open c2(hv_toto);
        loop
            fetch c2 into hv_tata;
            exit when c2%NOTFOUND;
            -- do what U want with c2 :)
        end loop;
        close c2;

    end loop;
    close c1;
end;

best regards,

    aymeric barantal (mric)
    dba @ grolier interactive europe Received on Tue Nov 23 1999 - 06:35:20 CST