Re: Cursor

Only reason I can think of using the cursor is the rollback segment. If there are large number of rows then you may want to insert in batches. But in your case it may be okay to use insert statement with the subquery. That will be faster.

raghuvir

Mike Chidsey <Chidseym_at_worldnet.att.net> wrote in article <6mrjm3$afi_at_bgtnsc01.worldnet.att.net>...
> I'm a newbie to Oracle and have to edit some sql scripts for use with a
 VB5
> app. Some of the scripts seem to be inefficient. For example, loops are
> used a lot to add records from a cursor to another table. Couldn't you
 just
> append the entire cursor to the table?
>
> CREATE PROCEDURE bday_promo (promo NUMBER) IS
>
> --variable declaration
> l_member_id NUMBER(10)
>
> --define cursors
> CURSOR member IS
> SELECT member_id FROM customer WHERE bdate = SYSDATE;
>
> BEGIN
>
> OPEN member;
> LOOP
> FETCH member INTO l_member_id;
> EXIT WHEN member%NOTFOUND;
> INSERT INTO member_promo VALUES(promo_id, l_member_id, SYSDATE);
>
> END LOOP;
>
> END;
> /
>
> Why can't I just use this: ? (created from an Access append query)
>
> INSERT INTO member_promo(member_id, promo_id, run_date )
> SELECT customer.member_id, promo AS l_promo_id, customer.bdate FROM
 customer
> WHERE customer.bdate=SYSDATE;
>
> it would seem like the above statement could replace the entire original
> statements and run much quicker. Anyone disagree or have any comments?
>
> --
> Mike Chidsey
> Chidseym_at_worldnet.att.net
>
> You can have everything in life you
> want if you will just help enough other
> people get what they want.
> -Zig Ziglar
>
>
>
  Received on Thu Jun 25 1998 - 00:00:00 CDT