Re: a row-level operator for copying?

On Jul 25, 9:49 am, "Chris L." <diver..._at_uol.com.ar> wrote:
> On Jul 25, 2:26 am, Mark Harrison <m..._at_pixar.com> 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.- Hide quoted text -
>
> - Show quoted text -

I must add that I assumed that you're purging the table in a time window where there's no activity on table atest, otherwise someone could be modifying the table (adding records with c=1 or updating them to c=1) and the delete statement could theoretically wipe records that weren't there when the insert statement ran.

I don't know what the "correct" way would be to circumvent this problem (I'd somehow mark the rows to be moved/deleted and then move/ delete based on that mark but that'd require an extra column)

Regards Received on Wed Jul 25 2007 - 08:45:28 CDT