Re: One Cursor? or Two Cursors?

It is hard to tell from your pseudocode what you are doing. But it is a no-brainer to say that when you open a cursor there is an overhead while the cursor performs the select statement and builds a result set. If you can do that once, rather than twice, it is a good thing.

Daniel A. Morgan

Steve wrote:

> Can someone offer some ideas?
>
> I have one procedure to do two things: 1) utlsmtp job, 2) update table.
> I would like to know what is the best way in terms of performance by using
> one cursor or two cursors?
>
> My original codes are like the followings (before adding update table code):
>
> ------------------------
> procedures is
> ....
> cursor C1 is
> select .......;
>
> begin
> for Process in C1 loop
> ....
> ...
> utlsmtp stuffs
> end loop;
> end;
> ------------------------
>
> So I have "insert into tbl values(.....)" clause to add. If I add it
> before "end loop;", I am doing so with every row after first Process related
> utlsmtp jobs. If I have 400 rows of records to be processed by utlsmtp,
> then following I have 400 rows of data inserted into other tbl one of the
> time.
>
> Would it be better to have insert table job to be done after all utlsmtp job
> done? If so, where to insert clause, or to need 2nd cursor? When loop
> is ended, would C1 be finished?
>
> Thanks for any comment.
>
> Steve
  Received on Thu Apr 12 2001 - 01:20:48 CDT