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
CLOSE Statement The CLOSE statement allows the resources held by a cursor variable or open cursor to be reused. No more rows can be fetched from the cursor variable or closed

Projection and Existential Quantification - SQL Intuitively it might seem that projection in SQL is simply a matter of specifying the required columns in the SELECT clause, a

Providing Results of Queries Expressing queries in SQL is the (big) subject. Here I present just a simple example to give you the flavour of things to come in those chapters.

%ROWTYPE: This attribute gives a record type which represents a row in the database table or a row fetched from a formerly declared cursor. The Fields in the record and corresp

IS NULL Operator The IS NULL operator returns the Boolean value TRUE whenever its operand is null or FALSE if it is not null. The comparisons including the nulls always yield NU

MAX and MIN operator in SQL Example: (SELECT MAX (Mark) FROM EXAM_MARK WHERE StudentId = 'S1') (SELECT MIN (Mark) FROM EXAM_MARK WHERE StudentId = 'S1') Example

LIKE Operator You use the LIKE operator to compare the character value to a pattern. The Case is significant. LIKE returns the Boolean value TRUE when the character patterns mat

PITS Depressions in secondary cell wall is called pit. A pit present on the free cell wall surface without its partner is called Blind pit. It consists of 2 parts -

Character Types The Character types allow you to store alphanumeric data, represent words and text, and manipulate the character strings. CHAR You use the CHAR dataty

INSERT Statement The INSERT statement adds fresh rows of data to the specified database table or view. Syntax: