Although behind the scenes Oracle will perform a “select .nextval from dual” and therefore really doesn’t improve performance or anything I’m looking forward to write 11g PL/SQL code where you can use the sequence pseudo-columns CURRVAL and NEXTVAL in expressions. First off, I always prefer to use the .NEXTVAL in the SQL statement itself and use the RETURNING INTO clause to assign the new value to a PL/SQL variable. BUT, if the case requires for some reason to get the sequence value in PL/SQL without going through a SQL statement first just imagine how beautiful and elegant your code will look like without having “select .netxval into from dual;” spread all over the place…
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL> create sequence seq;
SQL> declare
nextval number;
begin
select seq.nextval
into nextval
from dual;
end;
/
SQL> declare
nextval number;
begin
nextval := seq.nextval;
end;
/
|
You can basically use the sequence pseudo-columns everywhere where a number value is valid in the expression, e.g. “…if seq.currval > 10 then…”