sequence in sql

Sequence in sql-: A sequence is a database object created by a user, and can be shared by multiple users to generate unique integers. We can use the sequence to automatically generate unique integers. A typical usage for sequences is to create a primary key value, which must be unique for each row. The sequence is generated and incremented (or decremented) by an internal routine. This can be time-saving object because it can reduce the amount of application code needed to write a sequence-generating routine.
Sequence numbers are stored and generated independently of tables. Therefore, the same sequence can be used for multiple tables.

Creating Sequence -:
CREATE SEQUENCE sequence [INCREMENT BY n] [START WITH n]
[{MAXVALUE n|NOMAXVALUE}] [{MINVALUE n |NOMINVALUE}]
[{CYCLE |NOCYCLE }] [{CACHE n|NOCACHE }][{ORDER |NOORDER }];

Automatically generate sequential numbers by using the CREATE SEQUENCE statement.

CREATE SEQUENCE dept_deptno INCREMENT BY 1 START WITH 91 MAXVALUE 100 NOCACHE NOCYCLE;
Sequence Created.
The example creates a sequence named DEPT_DEPTNO to be used for the DEPTNO column for the DEPT table. The sequence starts at 91, does not allow caching, and does not allow the sequence to cycle.
Do not use the CYCLE option of the sequence is used to generate primary key values unless we have a reliable mechanism that purges old rows faster than the sequence cycles.
If the INCREMENT BY value is negative, the sequence will descend.
ORDER guarantees that sequence numbers will be assigned to instances requesting them in the order the requests are received. This is useful in application requiring a history of the sequence in which transaction rook place.

Using a Sequence-:Once we create your sequence, we can use the sequence to generate sequential numbers for use in our tables. Reference the sequence values by using the NEXTVAL and CURRVAL pseudocolumns.

NEXTVAL and CURRVAL Pseudocolumns : The NEXTVAL pseudocolumn is used to extract successive sequence numbers from a specified sequence. We must qualify NEXTVAL with sequence name. When we reference sequence. NEXTVAL, a new sequence number is generated and the current sequence number is placed in CURRVAL.
The CURRVAL pseudocolumn is used to refer to a sequence number that the current user has just generated. NEXTVAL must be used to generate a sequence number in the current user’s session before CURRVAL can be referenced. We must qualify CURRVAL with the sequence name. When sequence CURRVAL is referenced the last value returned to the user’s ;process is displayed.
Rules for Using NEXTVAL and CURRVAL
We can use NEXTVAL and CURRVAL in the following :
The SELECT list of a SELECT statement that is not part of a subquery.
The SELECT list of a subquery in an INSERT statement.
The VALUES clause of an INSERT statement.
The SET clause of an UPDATE statement.
We cannot use NEXTVAL and CURRVAL in the following :
A SELECT list of a view.
A SELECT statement with the DISTINCT keyword.
A SELECT statement with the GROUP BY, HAVING, OR ORDER BY clause.
A subquery in a SELECT, DELETE, or UPDATE statement .
A DEFAULT expression in a CREATE TABLE or ALTER TABLE statement.
INSERT INTO dept(deptno, dname, loc)
VALUE (dept_deptno.NEXTVAL,’MARKETING’,’SALDIEGO’);
1 row created.
The example inserts new department in the DEPT table. It uses the DEPT_DPETNO sequence for generating a new department number.
We can view the current value of the sequenced:
SELECT dept.CURRVAL
FROM dual;

Caching Sequence Values : Cache sequences in the memory to allow faster access to those sequence values. The cache is populated at the first references to the sequence. Each request for the next sequence value is retrieved from the cached sequence. After the last sequence is used, the next request tor the sequence pulls another cache of sequences into memory. Beware of gaps in our sequence. Although sequence generators issue sequential numbers without gaps, this action occurs independent of a commit or rollback. therefore, if we rollback a statement containing a sequence, the number is lost. Another event that can cause gaps in the those values are lost if the system crashes. Because sequences are not tied directly to tables, the same sequence can be used for multiple tables. If this occurs, each table can contain gaps in the sequential numbers.

Modifying of Sequence-: If we reach a MAXVALUE limit for our sequence, no additional values from the sequence will be allocated and we will receive an error indicating that the sequence exceeds the MAXVALUE. To continue to use the sequence, we can modify it by using the ALTER SEQUENCE statement.
Syntax
ALTER SEQUENCE sequence
[INCREMENT BY n]
[{MAXVALUE n|NOMAXVALUE}]
[{MINVALUE n |NOMINVALUE}]
[{CYCLE |NOCYCLE }]
[{CACHE n|NOCACHE }]
[{ORDER |NOORDER }];
Guidelines : We must be the owner of the sequence of have the ALTER privilege for the sequence in order to modify it.
Only future sequence numbers are affected by the ALTER SEQUENCE statement.
The START WITH option cannot be changed using ALTER SEQUENCE. The sequence must be dropped and recreated in order to restart the sequence at a different number.
Some validation is performed. For example, a new MAXVALUE cannot be imposed that is less than the current sequence number.

ALTER SEQUENCE dept_deptno
INCREMENT BY 1
MAXVALUE 90
NOCACHE
NOCYCLE;
ALTER SEQUENCE dept_deptno
*
ERROR at line 1 :
ORA-04009: MAXVALUE cannot be made to be less than the current value.

Removing a Sequence-: To remove a sequence from the data dictionary, use the DROP SEQUENCE statement. We must be the owner of the sequence or have the DROP ANY SEQUENCE privilege to remove it.
DROP SEQUENCE sequence;
When sequence is the name of the sequence generator.
DROP SEQUENCE dept_deptono;
Sequence dropped.

 

Leave a Comment