Re: How to loop inside a database

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Mon, 30 Aug 1999 11:01:42 +0200
Message-ID: <7qdhau$k3p$1@oceanite.cybercable.fr>

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 Mon Aug 30 1999 - 04:01:42 CDT