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
Attributes: Just similar to variable, an attribute is declared with a name and datatype. The name should be exclusive within the object type. The datatype can be any Oracle ty

Role of Abstraction in pl/sql: The abstraction is a high-level description or model of a real-world entity. The Abstractions keep our daily lives convenient. They help us ca

Example of GROUPBY Operator Example: How many students sat each exam, using GROUP BY, NATURAL LEFT JOIN, and COALESCE SELECT CourseId, COALESCE (n, 0) AS n FROM COURS

IF Statement The IF statement executes a series of statement conditionally. Whether the series is executed or not depends on the value of the Boolean expression. Syntax:

UNNEST operator in SQL The inverse operator of GROUP is UNGROUP. SQL has an operator, UNNEST, that can be used for similar purposes, but its method of invocation is somewhat p

Updating Variables For assignment, SQL uses the key word SET, as in SET X = X + 1 (read as "set X equal to X+1") rather than X: = X + 1 as found in many computer languages.

Expression: This is a randomly complex combination of constants, variables, literals, operators, & function calls. The simplest expression consists of a single variable. If th

Keyword and Parameter Description: label_name: This is an undeclared identifier which labels an executable statement or the PL/SQL block. You can use a GOTO statement to

Ending Transactions A good quality programming practice is to commit or roll back every transaction explicitly. Whether you rollback or issue the commit in your PL/SQL program

Anatomy of a Command Figure, showing a simple SQL command, is almost identical to its counterpart in the theory book. The only difference arises from the fact that SQL uses a