Re: a row-level operator for copying?

On Jul 25, 2:26 am, Mark Harrison <m...@xxxxxxxxx> wrote:
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;

If you can do without the first dbms_output.PUT_LINE then you'd have
to run these two commands:

insert into atest_history
select * from atest
where c = 1;

delete from atest
where c = 1;

You can have the total number of rows inserted/deleted by using
implicit cursor variables.

If you gotta have the first PUT_LINE you can use the loop for that,
but you should move the actual insert/delete after the loop. ie. loop
to PUT_LINE, insert all, delete all. Otherwise the loop is
unnecessary.

Regards
C.

.

Relevant Pages

  • a row-level operator for copying?
    ... has historical data. ... these tables are atest and atest_history). ... select a, seq, c ... end loop; ...
    (comp.databases.oracle.misc)
  • Re: a row-level operator for copying?
    ... has historical data. ... code sample below, these tables are atest and atest_history). ... If you gotta have the first PUT_LINE you can use the loop for that, ...
    (comp.databases.oracle.misc)
  • Re: Combine Multiple Records
    ... You would have to do it in a loop or in a cursor. ... Declare @MaxSeg smallint ... The example will stock all comments into Seq# = 1 rows and get rid of the ... rest of Seq#. ...
    (microsoft.public.sqlserver.dts)
  • Re: Can this be written more concisely in a functional style
    ... > def any: ... seq): ... > for elt in itertools.ifilterfalse ... > I don't see why it is used (there is no break in the loop) ...
    (comp.lang.python)
  • Re: Can this be written more concisely in a functional style
    ... My intention was that testreturn True or False. ... for elt in itertools.ifilter(p, seq): ... Is the itertools.imap version as efficient as the for loop version? ...
    (comp.lang.python)