2

I've created a sequence like this :

CREATE SEQUENCE seq_test2
  MINVALUE 0
  MAXVALUE 999999999999999999999999999
  START WITH 0
  INCREMENT BY 1;

Both of these two queries show me the number 0:

 select SEQ_TEST2.nextval from DUAL;   
 select SEQ_TEST2.currval from DUAL;

And I have a table as you can see here :

create table STUDENT
(
  st_id      NUMBER,
  first_name VARCHAR2(150),
  last_name  VARCHAR2(150)
)

After executing the query below ,

select t.st_id , nvl(t.st_id , seq_test2.nextval) as seq
from STUDENT t

When there is no null in st_id column , the result I get is this :

    st_id    seq
   ---------------
      1       1
      2       2
      3       3
      4       4
      5       5

when I insert a row for which the column st_id is null , after executing the query above the result is like below :

      st_id    seq
   ---------------
       1       1
       2       2
       3       3
       4       4
       5       5
       null    10

And if I execute it again I see:

      st_id    seq
   ---------------
       1       1
       2       2
       3       3
       4       4
       5       5
       null    16

Seems like even if column st_id has data , this part of the NVL function (seq_test2.nextval) gets executed !!!! Why is this happening?

Thanks in advance

0

2 Answers 2

5

NVL does not short-circuit, so even when the first input is not null the second argument is evaluated.

COALESCE does use short-circuit evaluation

True, but that's not the reason for the behaviour Pantea has described here. References to NEXTVAL and CURRVAL are not functions but pseudocolumns. No matter if you use nvl or coalesce (or case or decode), sequence.nextval will always be evaluated for every fetched row and the sequence value will grow.

3

NVL does not short-circuit, so even when the first input is not null the second argument is evaluated.

COALESCE does use short-circuit evaluation.

--edit But that doesn't matter, see the answer from Andi Schloegl

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.