Oracle sql delete on read
The question I have is how can I delete a read entry? I am using Oracle ond AIX with Roguewave database layer in a C ++ application.
I have searched Google for this answer, but it seems to be just simple examples. Is there a SQL statement that returns deleted rows?
This would greatly improve performance in my application, because only 0.1% of the cases would be forced to stay in this table, in other words, I will insert 0.1% back into the table.
The only hint I found is the "Into" sentence, I would assume that using delete in would do the job, but I never used it.
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/returninginto_clause.htm
source to share
According to the 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've never used it myself ... but you could try.
source to share
No, there is no SQL construct to read and delete a row in one go.
You can write a stored procedure that does this. Or, you can cache records that you have already read in memory (so you don't read them yet) and then do a bulk delete ( DELETE FROM table WHERE id in (?)
). This should be faster than many single DELETEs.
Or you can consider a different approach to the problem. Why do you need to delete so many lines when reading them? Are you using DB table for message passing? Perhaps there is another technology that is more suitable for your problem? If you feel like you have to work against established technology conventions, it could be an indication that you are not using the right tool for the job ...
source to share
Have you tried something like creating an audit trigger?
A good description for creating a select trigger.
Creature:
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;
/
Functional signature:
PROCEDURE HANDLE_EMP_SAL_ACCESS
( object_schema VARCHAR2
, object_name VARCHAR2
, policy_name VARCHAR2
);
source to share