Re: a row-level operator for copying?

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. Received on Wed Jul 25 2007 - 07:49:38 CDT