Doing some coding today. Encountered following problem with sequences. This code in PL/SQL:
does not work as expected i.e. sequence is not incremented by 10 as I wanted - only by 1. This code:
works fine. Sequence is incremented by 10 as expected.
Why is this explain?
You can see what's happening by adding some debugs:
create sequence s42;
set serveroutput on
declare
cursor c is
select s42.nextval from dual;
n pls_integer;
begin
open c;
for i in 1..5 loop
fetch c into n;
dbms_output.put_line('i: ' || i
|| '; n: ' || n
|| '; found: ' || case when c%found then 'true' else 'false' end);
end loop;
close c;
end;
/
i: 1; n: 1; found: true
i: 2; n: 1; found: false
i: 3; n: 1; found: false
i: 4; n: 1; found: false
i: 5; n: 1; found: false
PL/SQL procedure successfully completed.
You are only opening the cursor once, and the cursor only returns one row. So, the first fetch finds a value (c%found is true). The second and subsequent fetches do not find a row because the single-row result set has already been exhausted.
In short, the nextval is only executed once, so the sequence is only incremented once.
In your second version the entire query is re-executed inside the loop, so nextval is called multiple times, and the sequence is thus incremeneted multiple times too.
You'd see the same if you re-opened the cursor inside the loop:
declare
cursor c is
select s42.nextval from dual;
n pls_integer;
begin
for i in 1..5 loop
open c;
fetch c into n;
dbms_output.put_line('i: ' || i
|| '; n: ' || n
|| '; found: ' || case when c%found then 'true' else 'false' end);
close c;
end loop;
end;
/
i: 1; n: 2; found: true
i: 2; n: 3; found: true
i: 3; n: 4; found: true
i: 4; n: 5; found: true
i: 5; n: 6; found: true
but that's really just a longer, more complicated way of doing a select ... into, since it can only return one row at a time.
Only in the second code is executing nextval 10 times because NEXTVAL is called inside a loop
The NEXTVAL is causing the increment
NEXTVAL: Increments the sequence and returns the next value
The first code call nextval once and store value in cursor, Then same value is updating 10 times in loop
