Re: PL/SQL - Statement -Need help!

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Tue, 03 Nov 1998 18:56:04 GMT
Message-ID: <36424cb2.19799870@dcsun4.us.oracle.com>

On Tue, 03 Nov 1998 17:28:24 GMT, t.klinger_at_mobilkom.at (Thomas Klinger) wrote:

>Hi there!
>
>I have a really hard question (to me it sounds like).
>
>I have 2 tables called TABLE_1 and TABLE_TEMP.
>In TABLE_1 at the initialization is data collected from different
>tables.
>TABLE_1 has 2 important columns called OBJECT_ID and SERIAL_NUMBER.
>OBJECT_ID is unique, can only exist one time in the hole system.
>Now, I generate with an SQL-Script data into TABLE_TEMP were the
>serialnumbers from other tables do not exist in
>TABLE_1.SERIAL_NUMBERS.
>This works already fine (I need this because I have to report every
>week this difference).
>What I want to do after the report is to insert or update the
>collected difference from TABLE_TEMP.
>That means, if there is an OBJECT_ID in TABLE_1 which is also in
>TABLE_TEMP the serialnumber was modified. So this row has to be
>updated in TABLE_1.
>But if there is an OBJECT_ID in TABLE_TEMP which does not exist in
>TABLE_1 this row should be inserted.
>
>The flowing diagram should look like this (looking from TABLE_TEMP):
>
>OBJECT_ID exist in TABLE_1
> ----> update this row with value from TABLE_TEMP with this
>OBJECT_ID
>or
>OBJECT_ID does NOT exist in TABLE_1
> ----> insert this row into TABLE_1
>
>This is necessary to get every week the difference which row has been
>inserted/updated in the other tables.
>

Since object_id has to be unique, I assume there is a unique constraint on that column so:

create or replace
procedure do_it as
begin
  for c1 in ( select * from table_temp ) loop     begin
      insert into table_1 values ...
    exception

      when DUP_VAL_ON_INDEX then
        update table_1
           set ...
         where object_id = c1.object_id;

    end;
  end loop;
end;
/

Hope this helps.

chris.

>Who does the script looks like?
>
>I know that this can be done with PL/SQL but my experience isn't
>higher than to DECLARE/BEGIN/END a PL/SQL statement.
>And I know that it is necessary to compare the OBJECT_ID's in
>TABLE_TEMP with those existing ones in TABLE_1. But I don't know how
>to perform this.
>
>Can anyone help me? I thank you all in forward who's reading this.
>
>
>Kind regards
>
> Thomas Klinger
> Systemspecialist
>=======================================
>t.klinger_at_mobilkom.at
>http://www.mobilkom.at
>=======================================
>
>
Received on Tue Nov 03 1998 - 12:56:04 CST