| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Development question?
A copy of this was sent to mdneal_at_my-dejanews.com
(if that email address didn't require changing)
On Thu, 20 May 1999 14:58:50 GMT, you wrote:
>With this type of approach, wouldn't I have a "pair" of statements per
>customer supplied table? The # of customer supplied tables here could
>eventually be in the 100s. Trying not to create high-maintanence code
>for the long run. I WANT to try and have a nightly run which will
>merge ALL the customer-supplied tables into the merged table to refresh
>the data within.
>
>Any additional comments/suggestions?
>
>
Oh, you wanted the whole program :)
No problem. In Oracle8i, release 8.1 it would be:
create or replace procedure merge_data( p_tname in varchar2 )
as
begin
execute immediate
'update
( select *
from ' || p_tname || ' a, merged_data
where a.x = merged_data.y
and nvl(a.data,chr(0)) <> nvl(merged_data.mdata,chr(0))
)
set mdata = data';
execute immediate
'insert into merged_data
select *
from ' || p_tname || ' a
where not exists ( select NULL
from merged_data
where merged_data.y = a.x )';
commit;
end;
and then if you had a list of 'new data' tables in some table T, you would just:
begin
for x in ( select tname from T )
loop
merge_data( x.t );
end loop;
end;
/
In 8.0 and before, you would have to code another procedure, execute_immediate, as follows and pass the update/insert to it instead of using the verbs execute immediate as above.
create or replace procedure execute_immediate( sql_stmt in varchar2 ) as
exec_cursor integer default dbms_sql.open_cursor;
rows_processed number default 0;
begin
dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
rows_processed := dbms_sql.execute(exec_cursor);
dbms_sql.close_cursor( exec_cursor );
end;
>
>In article <37460396.6487037_at_newshost.us.oracle.com>,
> tkyte_at_us.oracle.com wrote:
>> A copy of this was sent to mdneal_at_my-dejanews.com
>> (if that email address didn't require changing)
>> On Wed, 19 May 1999 20:10:40 GMT, you wrote:
>>
>> >Looking for some development suggestions. I need to merge multiple
>> >tables containing customer-supplied data into a single table for
>> >display on a web front end.
>> >
>> >I want to merge the tables together into a single table but want to
>do
>> >inserts only if the record doesn't exist, and updates only if there
>is
>> >a change to the data. Don't want to re-populate the table each time
>I
>> >do a merge which would occur nightly. Any development suggestions
>> >would be appreciated. I am not a developer but I am helping out on
>> >a "hot fire" project. I am from the DBA world.
>> >
>>
>> Here is how to do that. You can use a single UPDATE/INSERT pair of
>statements.
>> Assume 'new_data' represents one of your customer supplied data
>tables.
>> merged_data is the single table. It might look like:
>>
>> SQL> create table new_data( x int primary key,
>> 2 data varchar2(255) );
>> Table created.
>>
>> SQL> create table merged_data( y int primary key,
>> 2 mdata varchar2(255) );
>> Table created.
>>
>> SQL> insert into new_data values ( 1, 'First Go Around' );
>> SQL> insert into new_data values ( 2, NULL );
>> SQL> insert into new_data values ( 3, NULL );
>>
>> SQL> -- works as long as chr(0) is not a valid value for data!!
>> SQL> --
>> SQL> -- can use:
>> SQL> -- ( ( x <> y ) OR
>> SQL> -- ( x is not null and y is null ) OR
>> SQL> -- ( x is null and y is not null ))
>> SQL> --
>> SQL> -- instead of nvl...
>> SQL>
>> SQL> update
>> 2 ( select *
>> 3 from new_data, merged_data
>> 4 where new_data.x = merged_data.y
>> 5 and nvl(new_data.data,chr(0)) <> nvl(merged_data.mdata,chr
>(0))
>> 6 )
>> 7 set mdata = data
>> 8 /
>>
>> 0 rows updated.
>>
>> SQL>
>> SQL> insert into merged_data
>> 2 select *
>> 3 from new_data
>> 4 where not exists ( select NULL
>> 5 from merged_data
>> 6 where merged_data.y = new_data.x )
>> 7 /
>>
>> 3 rows created.
>>
>> SQL> select * from merged_data
>> 2 /
>>
>> Y MDATA
>> ---------- ----------------------------------------
>> 1 First Go Around
>> 2
>> 3
>>
>> SQL> insert into new_data values ( 4, 'Second Go Around' );
>> SQL> update new_data set data = NULL where x = 1;
>> SQL> update new_data set data = 'Updated from NULL' where x = 2;
>>
>> SQL> update
>> 2 ( select *
>> 3 from new_data, merged_data
>> 4 where new_data.x = merged_data.y
>> 5 and nvl(new_data.data,chr(0)) <> nvl(merged_data.mdata,chr
>(0))
>> 6 )
>> 7 set mdata = data
>> 8 /
>> 2 rows updated.
>>
>> SQL> insert into merged_data
>> 2 select *
>> 3 from new_data
>> 4 where not exists ( select NULL
>> 5 from merged_data
>> 6 where merged_data.y = new_data.x )
>> 7 /
>>
>> 1 row created.
>>
>> SQL> select * from merged_data
>> 2 /
>>
>> Y MDATA
>> ---------- ----------------------------------------
>> 1
>> 2 Updated from NULL
>> 3
>> 4 Second Go Around
>>
>> >Thanks in advance,
>> >
>> >Mike Neal
>> >
>> >
>> >--== Sent via Deja.com http://www.deja.com/ ==--
>> >---Share what you know. Learn what you don't.---
>>
>> See http://www.oracle.com/ideveloper/ for my column 'Digging-in to
>Oracle8i'...
>>
>> Thomas Kyte
>> tkyte_at_us.oracle.com
>> Oracle Service Industries
>> Reston, VA USA
>>
>> --
>> Opinions are mine and do not necessarily reflect those of Oracle
>Corporation
>>
>
>
>--== Sent via Deja.com http://www.deja.com/ ==--
>---Share what you know. Learn what you don't.---
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Thu May 20 1999 - 12:19:24 CDT
 |
 |