Re: Confusion About Sequence

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Wed, 26 Apr 2006 21:35:14 +0800
Message-ID: <444F7712.2E92@yahoo.com>

Khurram wrote:
>
> hi
>
> I read something about seqence there but still not clear please give
> some light on it
>
> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements6a.htm#4062
>
> Within a single SQL statement containing a reference to NEXTVAL, Oracle
> increments the sequence only once:
>
> For each row returned by the outer query block of a SELECT
> statement. Such a query block
> can appear in the following places:
> -A top-level SELECT statement
> -An INSERT ... SELECT statement (either single-table or
> multi-table). For a multi-table insert, the reference
> to NEXTVAL must appear in the VALUES clause, and the sequence is
> updated once for each row
> returned by the subquery, even though NEXTVAL may be referenced in
> multiple branches of the
> multi-table insert.
>
> Thanx in advance
>
> Khurram

for example, if you had:

insert into T
select s.nextval,

       col2,
       col3, 
       s.nextval,
       col4, 
       col5

from ...

it means that s.nextval won't be run twice

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------

Received on Wed Apr 26 2006 - 08:35:14 CDT