Re: PL/SQL Update/Insert, Need Help

From: Tim Lindsey <timlind_at_hotmail.com>
Date: Fri, 7 Jan 2000 17:37:59 GMT
Message-ID: <Fnz7nB.Dqp@news.boeing.com>

Thanks to M. Raghavan for his/her help. I couldn't have done with out it. The procedure is below with the problem stated below that. Thanks again.

The solution is below. I modified it to work with a master file containing 900K+ records and an input file containing 200K+ records. The load took 16 minutes on a dual processor server (PLANET10).

CREATE OR REPLACE PROCEDURE PR_TEST_LOAD

-- ========================================================
-- || Description: This procedure does an insert/update to
-- ||    the TA_A table based on a key match in

-- || the TA_B table.

-- ========================================================

IS
  CURSOR b_cursor is

        select  LC_CODE,
                ITEMNUM,
                ORDERNUM,
                CUST
        FROM ta_b;

  master_found EXCEPTION;
  W_LC_CODE     ta_a.lc_code%type;
  W_ITEMNUM     ta_a.itemnum%type;
  M_LC_CODE     ta_a.lc_code%type;
  M_ITEMNUM     ta_a.itemnum%type;
  W_ORDERNUM    ta_a.ordernum%type;
  W_CUST        ta_a.cust%type;

  prodr_count_updates NUMBER := 0 ;
  i number := 0;

BEGIN    BEGIN

      OPEN  b_cursor;
      LOOP
        FETCH  b_cursor into
              W_LC_CODE,
              W_ITEMNUM,
              W_ORDERNUM,
              W_CUST;
        EXIT  when b_cursor%notfound;

      /*
      || now read the master for the input and check for match in cursor
      */
        BEGIN
           select  LC_CODE,ITEMNUM
           into    M_LC_CODE,M_ITEMNUM
           from    ta_a
           where   LC_CODE = W_LC_CODE
             and   ITEMNUM = W_ITEMNUM ;
         raise master_found;

         EXCEPTION
            when master_found then
            /*


            || update the non index fields


            */
               UPDATE  ta_a SET
                       ORDERNUM = W_ORDERNUM ,
                       CUST = W_CUST
               WHERE   LC_CODE = W_LC_CODE
                 and   ITEMNUM = W_ITEMNUM;
                 prodr_count_updates :=   prodr_count_updates + 1;
                 i := i + 1;
            /*


            || insert the record from the input file


            || if no match is found.


            */
            when no_data_found then
               INSERT into ta_a VALUES (
                       W_LC_CODE,
                       W_ITEMNUM,
                       W_ORDERNUM,
                       W_CUST);
               i := i + 1;
            /*


            || if there are any other errors when matching files


            || then do this


            */
            when others then
               --INSERT INTO TA_SYSTEM_LOG (REC132) VALUES
               --('Exception in insert/update block of
PR_PRODR_INSERT_LOAD');
         END;
         /*
         || do a commit every 500 update/inserts
         */
         if i > 500 then
           commit;
           i := 0;         -- reset counter to zero
         end if;
      END LOOP;
      commit;

   END;
END; Tim Lindsey <timlind_at_hotmail.com> wrote in message news:Fnu2u9.7rr_at_news.boeing.com...

>     I have a large master file I want to update with new data. The key
> fields are LC and ITEM.
>
>     If there is a matching record in the new input file I want to update

the

> ORDER and CUST fields.
>
>     If there is no match I want to insert the new record.
>
>     I assume I need to have both files indexed by the key fields. Does
> anybody know how I can do this? I am an experienced programmer but I'm new
> at writing PL/SQL.
>
> Master File
> SQLWKS> select * from ta_a;
> LC  ITEM  ORDER  CUST
> --- --------------------------------
> 605 5555    A            LIGHT STRUCTURES
> 602 2222    A            MACHINING
> 603 3333    A            ASSEMBLY
>
> New Input
> SQLWKS> select * from ta_b;
> LC  ITEM  ORDER  CUST
> --- --------------------------------
> 602 2222    B             FABRICATION
> 604 4444    A             SHIPPING
>
> Updated Master File
> SQLWKS> select * from ta_a;
> LC  ITEM  ORDER  CUST
> --- --------------------------------
> 605 5555    A             LIGHT STRUCTURES
> 602 2222    B             FABRICATION       (record modified)
> 603 3333    A            ASSEMBLY
> 604 4444    A            SHIPPING                (record added)
>
>
>
>

Received on Fri Jan 07 2000 - 11:37:59 CST