PL/SQL sequences

Define PL/SQL sequences and write syntax for a sequence

A sequence is a database object that is used to generate sequential number.
CREATE SEQUENCE seqname [increment] [minimum value][maximum value][start][cache][cycle]
Nextval and currval lets us get the next value and current value from the sequence.

What does cache and no cache options mean while creating a sequence?

The CACHE option means how many sequences will be stored in memory for access by the application objects. The performance is faster. However in case of the database is down the data is memory is lost for the sequence.

The NO CACHE option means values are not stored in memory. So there might be some performance issue.

How do we set the LASTVALUE value in an Oracle Sequence?

ALTER SEQUENCE emp_id
   INCREMENT by 7000

How does one get the value of a sequence into a PL/SQL variable?

CREATE SEQUENCE emp_seq ;

SELECT emp_seq.NEXTVAL into lv_variable from dual;
Oracle/PLSQL indexes
Oracle/PLSQL indexes - What is an Index? How to create an Index, What is Function-Based Index? Create a Function-Based Index, How do we drop an Index?...
Advantages of PL/SQL
PL/SQL is a transaction processing language that offers the following advantages: support for SQL - SQL is flexible, powerful and easy to learn...
Explain block structure of PL/SQL
The order of the parts is quite logical: First comes the declarative part, in which items can be declared. Items can be manipulated in the executable part...
Post your comment

You may like