a row-level operator for copying?
- From: Mark Harrison <mh@xxxxxxxxx>
- Date: Wed, 25 Jul 2007 05:26:33 GMT
I've got two identical tables -- one has current data, and one
has historical data. I've got a nightly job which moves older
data from the current table to the history table. In the
code sample below, these tables are atest and atest_history).
Three questions:
1. The code works, but I don't like having to list out all of
the columns in the insert statement -- is there some way
to insert the data in one go, something like:
insert into atest_history values atest_rec;
2. Are there any other features or extensions that make
this easier? It would be great if there were some
kind of statement like:
move rows from table1 to table2 where ...;
3. I'm a newbie at pl/sql, feel free to suggest where
the code below is lousy!
Many TIA!
Mark
--------------------------------------------------------
-- archive old rows from table "atest" to "atest_history"
create or replace procedure ARCHIVE_ATEST
as
begin
declare
atest_rec atest%rowtype;
cursor getfinished
is
select a, seq, c
from atest
where c = 1;
begin
open getfinished;
loop
fetch getfinished
into atest_rec;
exit when getfinished%notfound;
dbms_output.PUT_LINE('v_atest = ' || atest_rec.a);
insert into atest_history
(a, seq, c)
values (atest_rec.a, atest_rec.seq, atest_rec.c);
delete from atest
where seq = atest_rec.seq;
end loop;
dbms_output.PUT_LINE('done,rowcount=' || getfinished%rowcount);
close getfinished;
end;
end ARCHIVE_ATEST;
.
- Follow-Ups:
- Re: a row-level operator for copying?
- From: Chris L.
- Re: a row-level operator for copying?
- Prev by Date: Re: extracting a sample of each condition
- Next by Date: Re: extracting a sample of each condition
- Previous by thread: extracting a sample of each condition
- Next by thread: Re: a row-level operator for copying?
- Index(es):
Relevant Pages
|
