Tuesday, April 18, 2006

Oracle Sequences

Features :

Note : A sequence should be named after its intended use for clarity. However it can be used anywhere irrespective of its name.

Syntax:

CREATE SEQUENCE sequence_name

[INCREMENT BY n]

[START WITH n]

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n | NOCACHE}];


ALTER SEQUENCE sequence_name

[INCREMENT BY n]

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n | NOCACHE}];


DROP SEQUENCE sequence_name;

INCREMENT BY : default sequence increment is by 1.

START WITH : default sequence starts with 1.

NOMAXVALUE : 10^27 for ascending sequence, -1 for descending sequence.

NOMINVALUE : 1 for ascending sequence, 10^26 for descending sequence.

CACHE : how many values Oracle preallocates & keeps in memory. default is 20.

USER_SEQUENCES data dictionary:

SELECT sequence_name, min_value, max_value, increment_by, last_number

from USER_SEQUENCES;

Note : The last_number displays the next available sequence number unless there are cached values.

Pseudocolumns NEXTVAL & CURRVAL:

Rules for using NEXTVAL & CURRVAL

Can be used in:

Can't be used in:

Note : CACHE is populated the 1st time the sequence is referred. NEXTVAL requests pull the values from the cache until the last value is used up, after which the next NEXTVAL request pulls another cache of sequences into the memory.

Gaps in Sequences:

Altering Sequences:

Usage Example:

CREATE SEQUENCE departments_seq2

START WITH 20

INCREMENT BY 20

MAXVALUE 999

NOCACHE

NOCYCLE;

ALTER SEQUENCE departments_seq2

INCREMENT BY 20

MAXVALUE 999999

NOCACHE

NOCYCLE;

INSERT INTO departments (department_id, departments_name, location_id)

VALUES (departments_seq.NEXTVAL, 'Navigation', 9152);

SELECT departments_seq.CURRVAL FROM DUAL;


Comments: Post a Comment

Links to this post:

Create a Link



<< Home
Advertisements

eBay.in - The World's Online Marketplace

ARCHIVES
  • March 2006
  • April 2006
  • May 2006
  • June 2006
  • July 2006
  • August 2006
  • September 2006

This page is powered by Blogger. Isn't yours?

Recent Postings

Google