[go: up one dir, main page]
Scribd
Upload
3 views12 pages

Unit 4 Lecture 6

The document covers the topic of sequences in relational database management systems, focusing on their creation, usage, and destruction. It explains how sequences help generate unique identifiers for records, ensuring distinct values and avoiding duplicates. Additionally, it provides syntax examples for creating and destroying sequences, as well as using pseudo columns NEXTVAL and CURRVAL to access generated values.

Uploaded by

bhattpruthvi15
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views12 pages

Unit 4 Lecture 6

The document covers the topic of sequences in relational database management systems, focusing on their creation, usage, and destruction. It explains how sequences help generate unique identifiers for records, ensuring distinct values and avoiding duplicates. Additionally, it provides syntax examples for creating and destroying sequences, as well as using pseudo columns NEXTVAL and CURRVAL to access generated values.

Uploaded by

bhattpruthvi15
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 12

RELATIONAL DATABASE MANAGEMENT SYSTEM

DIPLOMA IN COMPUTER ENGINEERING (GTU)

SEMESTER: 3

SUBJECT CODE: DI03000031

Mr. NIKUL JAYSWAL


Unit: 4 Lecture:6 Lecturer in Computer Engineering
Shri K. J. Polytechnic, Bharuch
RECAP
•Advantages of View.
•Destroying Views.
•Synonyms
•Creating a Synonym.
•Destroying a Synonym.
TODAY’S LECTURE OUTCOMES
• Sequences.
• Creating a Sequence.
•Destroying a Sequence.
SEQUENCE
▪To distinguish different records of a table from each other, it is required
that each record must have distinct values.
▪The primary key constraint ensures this by not allowing duplicate or NULL
values in columns defined as a primary key.
▪Such columns generally contain sequential numbers such as 1, 2, 3.... or
combination of sequential values with some strings, such as, 'A01', 'A02',
'A03’..
▪While entering data manually in insert or update operations, it is difficult
to track such type of sequences.
▪Data entry made by humans may involve gaps in sequence, or may involve
duplicates violating constraints.
SEQUENCE
▪An Oracle object, a Sequence, help to ease the process of creating unique
identifiers for a record in a database.
▪“A Sequence is simply an automatic counter, which generates sequential
numbers whenever required.“
▪The value generated by a Sequence can be used in insert and update
operations.
▪The values generated by the Sequence are numerical values.
▪These values can be combined with strings to get required values.
▪For example, an account number A01 requires to combine ‘A0' with 1 to
get 'A01'.
SEQUENCE
▪A value generated can have a maximum of 38 digits.
▪A sequence can be defined to
- Generate numbers in ascending or descending order.
- Provide intervals between numbers.
- Caching of sequence numbers in memory to speed up their
availability.
CREATING A SEQUENCE
▪Syntax:
CREATE SEQUENCE sequence_name
[ START WITH n
INCREMENT BY n
MINVALUE n
MAXVALUE n
CYCLE / NOCYCLE ] ;
CREATING A SEQUENCE
▪Example:
CREATE SEQUENCE mys
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 99
NOCYCLE ;
NEXTVAL & CURRVAL
▪Oracle provides two pseudo columns: NEXTVAL and CURRVAL
▪Used to access the values generated by the Sequence.
▪Syntax:
sequenceName.CURRVAL Returns the current value of the sequence.
sequenceName.NEXTVAL: Increases the value of the sequence and
returns the next value.
GENERATE SEQUENTIAL NUMBER LIKE ‘A01, A02’
▪Example:
select ('A'||LTRIM(to_char(mys.nextval,'00'))) from dual;

▪The same kind of sentence can be used with INSERT or UPDATE operation
also to insert or update values in tables.
insert into abc values(‘A’||LTRIM(to_char(mys.nextval, ‘00’)), ‘nikul’);
insert into abc values(‘A’||LTRIM(to_char(mys.nextval, ‘00’)), ‘Rahul’ );
DESTROYING A SEQUENCE
▪ Syntax:
DROP SEQUENCE sequence_name ;
▪ Examle:
DROP SEQUENCE mys;
THANK YOU…..

You might also like