Re: PL/SQL Update/Insert, Need Help

From: Raghavan <raghavan_at_prodigy.net>
Date: Tue, 4 Jan 2000 20:02:57 -0500
Message-ID: <84u568$460s$1@newssvr04-int.news.prodigy.com>

Hi,

Step 1

Create Index Master file and Index file.

CREATE INDEX master_index on master_table (LC,ITEM) CREATE INDEX input_index on input_table (LC,ITEM) (This index optional)

Step 2

Write an PL/SQL

DECLARE
EXCEPTION master_found;

  W_LC varchar2(3);
  W_ITEM varchar2(4);
  M_LC varchar2(3);
  M_ITEM varchar2(4);
  W_ORDER varchar2(1);
  W_CUST  varchar2(30)  -- give the exact length

  CURSOR input_cursor is select LC,ITEM,ORDER,CUST FROM input_file; BEGIN
OPEN input_cursor;
LOOP
  FETCH input_cursor into W_LC,W_ITEM,W_ORDER,W_CUST;   EXIT when input_cursor%notfound;

• now read the maser for the input BEGIN select LC,ITEM into M_LC,M_ITEM from master_file where LC = W_LC and ITEM = W_ITEM ; raise master_found; EXCEPTION when SQL%FOUND then
  • update the non index fields; UPDATE master_table SET ORDER = W_ORDER , CUST = W_CUST WHERE where LC = W_LC and ITEM = W_ITEM ; when others then if SQL%NOTFOUND then INSERT into MASTER_TABLE VALUES W_LC,W_ITEM,W_ORDER,W_CUST); end if;

        END;
END LOOP;
END; Hope this helps,

Regards,

MG

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 Tue Jan 04 2000 - 19:02:57 CST