Oracle sql delete on read

Question

The question i have is, how can i delete a record on read? I'm using Oracle ond AIX with the Roguewave DB Layer in a c++ application.

I have been searching on google for this answer but there seem only to be the simple examples. Is there a SQL Statement which returns the deleted rows?

This would greatly enhance performance on my application because only 0.1% of the cases will have a need to stay in this table, in other words i will insert 0.1% back into the table.

The only hint i have found is the "Into" clause, i would assume that using delete into would do the job but i have never used it.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/returninginto_clause.htm

Answer 1

According to oracle documentation it is indeed possible to delete and read in one go:

DELETE FROM employees
   WHERE job_id = 'SA_REP' 
   AND hire_date + TO_YMINTERVAL('01-00') < SYSDATE 
   RETURNING salary INTO :bnd1;

I never used it myself...but you could give it a try.