Re: How to loop inside a database

Hi,
thanks , I knew there had to be a better way than using concat of a concat.
steve

Michel Cadot <micadot_at_netcourrier.com> wrote:

> Here's an example of how to concatenate a field values into a string :
>
> SQL>create table t (col varchar2(10));
>
> Table created.
>
> SQL>insert into t values ('aaa');
>
> 1 row created.
>
> SQL>insert into t values ('bbb');
>
> 1 row created.
>
> SQL>insert into t values ('ccc');
>
> 1 row created.
>
> SQL>commit;
>
> Commit complete.
>
> create or replace function f return varchar is
> result varchar2(2000) := '';
> cursor curs is select col from t;
> first boolean := true;
> begin
> for rec in curs loop
> if first then
> result := rec.col;
> first := false;
> else
> result := result || ', ' || rec.col;
> end if;
> end loop;
> return result;
> end;
> /
>
> Function created.
>
> SQL>var res varchar2(2000)
>
> SQL>exec :res := f;
>
> PL/SQL procedure successfully completed.
>
> SQL>print res
>
> RES
> ----------------------------------------------
> aaa, bbb, ccc
>
>
>
> Steve A a écrit dans le message <1dxcu1m.1gkkvpk1n3sh84N@[201.1.1.73]>...
> >Hi,
> >If I want to take a varchar2 field and add it to another varchar2 string
> >based on a condition inside a loop, how can I do this.(code would be in
> >a stored procedure on the server)
> >
> >for example my data may be.
> >
> >Emailsnd shpshed qareport
> >xxx_at_xxx.com 1 0
> >yyy_at_mm.com 0 1
> >pp_at_nbnb.com 1 1
> >
> >basically I would want to prod. a string of the form:
> >
> >xxx&xxx.com,ppp_at_nbnb.com,ETC
> >into a variable in a stored procedure, then write it to a file.
> >I have examples of loops but nothing like what I need.I don't understand
> >how the record pointer gets advanced to the next record.
> >
> >steve
> >
> >
Received on Tue Aug 31 1999 - 18:59:22 CDT