Sequence in Database Management System(DBMS).

-
Sequence are used to generate unique numbers for the fields in the table which require values to remain unique.
If table has been specified with primary key constraints or an uniqueness constraints , sequence can be used to generate unique numbers.
If generation of unique numbers is not automatically, which will lead to be done manually, which will lead to inconsistent values of primary key and unique key.
Sequence are stored as database object and for it object in database, whenever we use create, we can use  drop also if we want to remove them.  

Syntax of sequence:

CREATE SEQUENCE sequence_name
START WITH  integer_value
INCREMENT/DECREMENT BY integer_value
MINVALUE  integer_value
MAXVALUE  integer_value
CYCLE|NO-CYCLE
CACHE integer_value
ORDER|NO-ORDER

In the above sequence CREATE and SEQUENCE  are keyword , sequence name is the name of sequence. Start with is a keyword and it is used to specify value of the sequence to be specified as an integer and it is a compulsory clause along with create sequence.

INCREMENT/DECREMENT BY are the keywords which can be used to specify ascending or descending order sequence starting with an integer value , it is also a compulsory clause.

MINVALUE  is used to specify the minimum value of the sequence and it is also a keyword.It is an optional clause and if not specify then minimum value will be the default value of the system. If we need to wrapped the sequence then minimum value is compulsory.

MAXVALUE is the keyword used to specify the maximum value ti the sequence , It is also an optional clause and if not specify then  maximum value will be the default maximum value of the integer . If sequence is to be wrapped around then max value is compulsory.

CYCLE|NO-CYCLE is keyword used to specify wrapping of the sequence , If cycle clause is specified then min value and max value both should be specified . If  nothing is specified then non cycle is by default applicable.

CACHE is the keyword used to specify the number of sequence values to be kept in cache memory for faster access, if no cache is specified then default is two.

ORDER|NO-ORDER are the keyword used to specify the sequence values to be created on demand. This clause placed significant role if  sequence are used to create time-stamp value otherwise for generating unique number order does not have any role.

After sequence is created , then unique values can be accessed with sequence name.nextval and it can be used in a table as follow:

INSERT INTO TABLE_NAME VALUE (sequencename.nextval,.....,.......);


Example:
Create a sequence to generate unique numbers starting with 4 digit and ending with 5 digit number. After sequence reaches to max value it should be wrapped by taking a default  cache.

Syntax:

CREATE SEQUENCE unique_no
START WITH 1000
MAXVALUE 99999
CYCLE.


Comments

Post a Comment

Popular posts from this blog

Introduction to Python.

-
What is python? Python is an object-oriented programming language created by Guido Rossum in 1998. If you just beginning your programming career, python suits you best. With python you can do everything from GUI developement, web application, Machine learning, IOT, Game Developement etc. so it is general purpose high level programming language. It is very easy language,it is good for beginners. It is case sensitive language. Python is Dynamically Typed, Write less code do more. Introduction Easy to Learn and Use Python is easy to use and learn, it is user friendly and high level programming language. Expressive language Pyton language is more expressive means that is more understand and readable. Unique style Python is an interpreted language i.e interpreter execute the code line by line at a time . This makes debugging easy and thus suitable for beginners. Cross Platform Language Python can run equally on different platform such as windows, Linu...
Read more

Decision Making Statement.

-
Image
Conditional Statement Decision making of an anticipation of condition occuring by execution of program and dpecific action is taken according to condtion. Following is a general form of typical decision making structure found in most programming language. Python programming language uses any non-zero or non-null value as true and if it is either zero or null then it is assume false value. if Condition: In python the block of statement is executed if the condition is true. Syntax if (condition): Block(s) Example var1=100 if var1: print('i got an true value') print(var1) output i got an true value 100 Question: Write a program that prompt user to enter two integer values, print the message "equal" if both entered value are equal a=int(input("Enter first number")) b=int(input("Enter second number")) if a==b: print("equal") output Enter fir...
Read more