Re: Cursor

Sure, you can do an Insert-select.
Reasons you might want to use a PL/SQL loop instead: 1. You need complex logic to process each row before inserting into the table (perhaps skipping some that fail the logic); 2. You're inserting a lot of rows, and you want to perform periodic commits (so you don't blow out the rollback segment). - Dan

-- 
T'was Brillig,
And the slithy toves
Did gyre and gimbol in the wabe;
All mimsey were the borogroves,
And the mome raths outgrabe.
- Lewis Carroll

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

> 

> 

>