Re: PL/SQ Cursor LOOP problem

The calculations on each group are quite involved. Let's say I really want to use a cursor loop.

FOR Cursor_Rec IN Cursor LOOP ;

  Read records from group G1 doing calculations involving running totals

  using and manipulating many variables for each record until all records for G1 are
exhausted.

  Insert a record of many variables for G1 to RESULT table.

  Repeat for next group untill all groups and records are exhausted

EXIT WHEN Cursor%NOTFOUND ;

END LOOP ; Can this sort of thing be done?

Sybrand Bakker wrote:

> "Larry Tucker" <lrayt_at_swbell.net> wrote in message
> news:3ABF3E35.A7B601E8_at_swbell.net...
> > Hello.
> >
> > I'm new to Oracle programming. Will someone please help me
> >
> > find a simple, elegant way to perform a cursor loop in PL/SQL
> >
> > to do the following?
> >
> >
> >
> > Suppose for discussion purposes each record in cursor c_Main
> >
> > has a Group designation column with values G1, G2, .G?
> >
> > (In fact the group values are character strings. No numeric index
> >
> > of any kind is available from the group designations.)
> >
> > For thousands of records in cursor c_Main, with an unknowable
> >
> > number of groups, G1, G2, ., G?.
> >
> > Read records from cursor c_Main that belong to group G1, making a
> >
> > series of calculations until all records from c_Main in G1 are read,
> >
> > then write a single record of values for G1 to TABLE_RESULT,
> >
> > then read records from c_Main belonging to group G2, making a
> >
> > series of calculations until all records from c_Main in G2 are read,
> >
> > then write a single record of values for G2 to TABLE_RESULT, etc.
> >
> > Repeat for G3, G4, . ?, until all groups are exhausted.
> >
> >
> >
> > The TABLE_RESULT would have a single record for each group in
> >
> > cursor c_Main. So, if there are 247 groups in cursor c_Main, there
> >
> > would be 247 records in TABLE_RESULT.
> >
> >
> >
>
> It is always a very bad idea (tm) to anything in pl/sql which you could have
> done as well (and easily) in SQL.
> The reason for this is quite simple: a fetch (happens in your cursor for
> loop) is very expensive.
> So maybe you should either provide more detail, so you can get a really
> working example, and/or reconsider
> doing it in pl/sql. From your description it looks like
> one single
> insert into
> select
> from
> group by
> should me more than sufficient.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
Received on Mon Mar 26 2001 - 17:15:44 CST