sql - Insert deleted rows to new table in stored procedure -

how can insert rows deleted in delete statement new table within stored procedure in db2 sql?

db2 allows following syntax return deleted rows:

select * old table (     delete my_table foo > 1 ) 

for reason, can't insert based on results returned statement. however, can use common table expressions kludgy workaround:

with deleted (     select * old table (delete my_table foo > 1) ) select * new table (insert archive_table select * deleted) 

this has unnecessary select statement don't want, @ least works. deleted rows inserted table.

however, how can within stored procedure?

a stored procedure doesn't allow bare select statement. thought of putting within set statement:

set my_var = (     deleted (         select * old table (delete my_table foo > 1)     )     select * new table (insert archive_table select * deleted) ); 

however, fails because common table expression not allowed within such statement.

is there way within stored procedure?

(the task can done using other method work-around. want find out if possible way. if not possible, seems quite dumb restriction.)

update: i'm using db2 9.7 luw.

if issue select, have consume result set somehow, whether in procedure or application. can either run dummy loop within procedure, like:

for t in (with deleted (     select * old table (delete my_table foo > 1)     )     select * new table (insert archive_table select * deleted) ) loop   null; end loop; 

or use explicit cursor:

declare c1 cursor deleted (     select * old table (delete my_table foo > 1)     )     select * new table (insert archive_table select * deleted); ... open c1; close c1; 

note neither of these tested.