Currval and nextval - sql pseudocolumns, PL-SQL Programming

CURRVAL and NEXTVAL

The series is a schema object which generates the sequential numbers. Whenever you form a sequence, you can specify its primary value and an increment. The CURRVAL returns the present value in a specified sequence.

Before you can reference a CURRVAL in a session, you should use NEXTVAL to generate a number. The reference to NEXTVAL stores the present sequence number in the CURRVAL.

The NEXTVAL increments the series and returns the next value. To obtain the present or next value in a series, you should use dot notation, as shown:

sequence_name.CURRVAL

sequence_name.NEXTVAL

After creating a sequence, you can use it to generate exclusive series numbers for the transaction processing. Though, you can use the CURRVAL and NEXTVAL only in a SELECT list, the VALUES clause, & the SET clause. In the illustration below, you use a series to insert the similar employee number into two tables:

INSERT INTO emp VALUES (empno_seq.NEXTVAL, my_ename, ...);

INSERT INTO sals VALUES (empno_seq.CURRVAL, my_sal, ...);

When a transaction generates a sequence number, the series is incremented directly whether you commit or roll back the transaction.

Posted Date: 10/4/2012 3:41:40 AM | Location : United States







Related Discussions:- Currval and nextval - sql pseudocolumns, Assignment Help, Ask Question on Currval and nextval - sql pseudocolumns, Get Answer, Expert's Help, Currval and nextval - sql pseudocolumns Discussions

Write discussion on Currval and nextval - sql pseudocolumns
Your posts are moderated
Related Questions
Implicit Cursor is declared and used by the oracle environment internally. while the explicit cursor is declared and used by the external user. more over implicitly cursors are no

What Is a Package? The package is a schema object that group logically related PL/SQL items, types, and subprograms. The Packages usually have 2 parts, a specification & a bo

TTITLE and BTITLE are commands in Pl-SQL to control report headings and footers. This Ttitle & Btitle are mainly used on creating SQL*PLUS report. Ttitle is used for toptitle headi

Effects of NULL for union - SQL The treatment of NULL in invocations of EXCEPT is as for UNION. This is different from its treatment in those of NOT IN and quantified compari

Assignment Statement: The assignment statement sets the present value of the variable, parameter, field, or element. The statement consists of an assignment target followed by

Cursors   To execute the multi-row query, the Oracle opens an unnamed work region which stores the processing information. The cursor names the work region, access the informa

Example of NOT EXISTS Operator - SQL Example is a translation into SQL of the corresponding example, which is included there merely to show that for any scalar comparison the

Positional and Named Notation You can write the actual parameters when calling a subprogram, using either positional or named notation. That is, you can point to the relationsh

%ROWCOUNT When its cursor or cursor variable is opened, the %ROWCOUNT is zeroed. Before the first fetch, the %ROWCOUNT yields 0. Afterward, it yields the number of rows fetche

How Exceptions Are Raised By the run-time system, the internal exceptions are raised implicitly as are user-defined exceptions that you have related with an Oracle error number