Cursor

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 Wed Jun 24 1998 - 00:00:00 CDT