Re: Cursor

On Wed, 24 Jun 1998 15:16:38 -0400, "Mike Chidsey" <Chidseym_at_worldnet.att.net> wrote:

>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;

Try this:

insert into member_promo( member_id, promo_id, run_date )

    select member_id, promo, sysdate
    from customer
    where
      to_char(bdate, 'DD-MON-YYYY' ) = to_char(sysdate, 'DD-MON-YYYY')

>
>it would seem like the above statement could replace the entire original
>statements and run much quicker. Anyone disagree or have any comments?
Received on Wed Jun 24 1998 - 00:00:00 CDT