"Mikito Harakiri" <mikharakiri_at_iahu.com> wrote in message
news:CsQub.17$OF2.134_at_news.oracle.com...
> "Serge Rielau" <srielau_at_ca.eye-bee-m.com> wrote in message
> news:bpgef8$gp6$1_at_hanover.torolab.ibm.com...
> > Mikito Harakiri wrote:
> >
> > > "Serge Rielau" <srielau_at_ca.eye-bee-m.com> wrote in message
> > > news:bpfunr$dic$1_at_hanover.torolab.ibm.com...
> > >
> > >>The syntax is as follows:
> > >>SELECT .. FROM OLD TABLE(DELETE FROM ...)
> > >>
> > >>OLD TABLE being the set of deleted rows
> > >
> > >
> > > May I ask you how is your single statement it different from the
> following
> > > sequence:
> > >
> > > SELECT * FROM T;
> > > DELETE FROM T;
> > Depending on your isolation level it will give you different results or
> > deadlocks. Not to speak of the fact that you have to read the data twice
> > which is a performance problem.
>
> Correction:
>
> SELECT * FROM T FOR UPDATE;
> DELETE FROM T;
> COMMIT;
>
> > >>Example:
> > >>WITH del AS (SELECT * FROM OLD TABLE(DELETE FROM S))
> > >>SELECT count(*) FROM NEW TABLE(INSERT INTO T SELECT * FROM del);
> > >
> > >
> > > And how is it different from:
> > >
> > > INSERT INTO T as
> > > SELECT * FROM S;
> > > SELECT count(*) FROM S;
> > > DELETE FROM S;
> > So what happens if after my INSERT another connection deletes/inserts
> > some rows in S?
> > I will get wrong COUNT(*).
>
> INSERT INTO T as
> SELECT * FROM S FOR UPDATE;
> SELECT count(*) FROM S;
> DELETE FROM S;
> COMMIT;
>
> > But let me give you another example
> > (it requires the acceptance of surrogate keys as a fact of life):
> > CREATE TABLE T(PK, C1 INT, C2 INT);
> > CREATE TRIGGER BEFORE INSERT ON T REFERENCING NEW ROW AS n FOR EACH ROW
> > SET n.pk = (SELECT COALESCE(MAX(pk), 0) + 1 FROM T);
> >
> > INSERT INTO T(c1, c2) VALUES (5, 6);
> > What is your new primary key?
> > You will never know....
> > When you get a customer reference number where does it come from?
>
> No trigger is necessary:
>
> SELECT COALESCE(MAX(c1), 0) + 1 into :x FROM T;
> insert into T(c1, c2) values (:x, 6);
That does not always work in Oracle in a multi-user environment. But, hey,
the Oracle folks swear that it shouldn't.
Received on Wed Nov 19 2003 - 16:02:01 CST
