Re: select/insert/delete

You need a single statement that handles select delete and insert. How about using a single update statement to do the job? Relationally talking a view is a table. So your tables might lool like something like this.

create table c(n number primary key, deleted timestamp);

create view a as select n from c where deleted is null;

create view b as select n from c where deleted is not null;

insert into a (n) values (1);

insert into a (n) values (2);

update c set deleted=systimestamp where n=1;

If you need to physically separate those a and b rows you could use partitioning. A partition physcally thinking is actually a table.

create table cp(n number primary key, deleted timestamp) partition by list(deleted) (partition a values (null),partition b values (default)) enable row movement;

create or replace view a as select n from cp where deleted is null;

create or replace view b as select n from cp where deleted is not null;

insert into a (n) values (1);

insert into a (n) values (2);

update cp set deleted=systimestamp where n=1;

So the insert and delete parts of your request are handled. You did not mention why you need the select part.
Because it is first in your list one might think that your prosess first takes a look if there is somtehing to do and after that do stuff. It is most often enough just do stuff using the where part of update clause. Or do you need some results back? If so take a look returning_clause of update. http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_10008.htm#i2126358

drop view a;

drop view b;

drop table c;

drop table cp;

--
Timo Raitalaakso
http://rafudb.blogspot.com

--
http://www.freelists.org/webpage/oracle-l