问题描述:

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

网友答案:

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.

网友答案:

No, there is no SQL construct to read and delete a row in one go.

You could write a stored procedure which does this, though. Or you could cache the records you have already read in memory (so you do not read them again), then later do a bulk delete (DELETE FROM table WHERE id in (?)). That should be faster than many single DELETEs.

Or you might consider a different approach to the problem. Why do you need to delete so many rows on reading them? Are you using a DB table for passing messages? Is there maybe another technology that is more suited to your problem? If you feel you have to work against the established conventions of a technology, that may be an indication that you are not using the right tool for the job...

网友答案:

Have you tried something like building an audit trigger?

Good description to build an select trigger.

Creation:

begin
  dbms_fga.add_policy
  ( object_schema=>'SCOTT'
  , object_name=>'EMP'
  , policy_name=>'SAL_ACCESS_HANDLED'
  , audit_column => 'SAL'
  , audit_condition => 'SAL>= 1500'
  , handler_schema => 'SCOTT'
  , handler_module => 'AUDIT_HANDLER.HANDLE_EMP_SAL_ACCESS'
  );
end;
/

Function signature:

PROCEDURE HANDLE_EMP_SAL_ACCESS
( object_schema VARCHAR2
, object_name VARCHAR2
, policy_name VARCHAR2
);
相关阅读:
Top