Re: Cursor

Both ways to insert data are functionaly equivalent.

May I suggest a few amendments :

Here's your INSERT STATEMENT:

INSERT INTO member promo(member id, promo id, run date ) SELECT customer.member id, promo , customer.bdate FROM customer WHERE customer.bdate=trunc(SYSDATE);

• I have removed AS PROMO ID because it is useless (I'm not even sure it is syntaxically correct in ORACLE)
• You probally want to select the customers whose bdate is today's date. SYSDATE includes all date and time components, including hours, minutes and seconds. TRUNC(SYSDATE) eliminates hours, minutes and seconds. Issuing the query without TRUNC would most probably not return data at all : If the present time, i.e. , were 1998 June 20th 8.25 AM 56 seconds, just the customers whose bdate is exactly that date and time would be selected.

Moreover , if bdate is not stored in a truncated form , the condition becomes :
WHERE trunc(customer.bdate)=trunc(SYSDATE)

This also applies for the queries in your procedure :

CURSOR member IS

    SELECT member id FROM customer WHERE bdate = trunc(SYSDATE);

LOOP
    FETCH member INTO l member id;
    EXIT WHEN member%NOTFOUND;
    INSERT INTO member promo VALUES(promo id, l member id, TRUNC(SYSDATE)); /* to store date in a truncated form */

END LOOP; (The TRUNC in the insert statement eliminates time components (h,m & s) in the stored data)

If the amount of data to be inserted into the table member promo is reasonably small, one single INSERT statement is preferable. If it's bigger, doing so may cause you to run out of space in your rollback segment.
In this case you would preferably use the procedure, issuing a COMMIT statement just after the insert statement (before END LOOP)

--