TECH GUIDE BLOG

After a long sleep, I’m back and I want to start with a new topic, database. Okay, the database I’m talking about is Oracle, with using PL/SQL commands. The first time of this topic, I will talk about making sequence in Oracle. What is sequence? We can imagine sequence as a table that keep, of course, sequence numbers, this will be useful when you need to make sequence of numbers automatically, so you don’t have to make a new column that save those numbers or insert it manually.

To make a sequence we can use some options:

1. START WITH n. When we first create the sequence, that sequence will be started with n.
2. INCREMENT BY n. Define the increment value of your sequence, if it isn’t declared, it will set to 1.
3. MINVALUE n. Define the minimum value of the sequence can generate, if it isn’t declared, the sequence is assumed have no minimal value (NOMINVALUE).
4. MAXVALUE n. Define the maximum value of the sequence can generate, if it isn’t declared, the sequence is assumed have no maximum value (NOMAXVALUE).
5. CYCLE. Allow sequence to back with MINVALUE if sequence has reached its MAXVALUE. If it isn’t declared, sequence will not back to MINVALUE (NOCYCLE).
6. CACHE. Allow object sequence to do caching from the values that will be generated to increase performance. If it isn’t declared, sequence will not use cache (NOCACHE).

How to declare it

CREATE SEQUENCE sequence_name
START WITH 2
INCREMENT BY 3
MAXVALUE 98
CYCLE;

To use a sequence, we have to know that sequence is similar to struct variable. It have two virtual variable, CURVAL and NEXTVAL.  When we read the value of NEXTVAL, the CURVAL field will be filled with NEXTVAL, and NEXTVAL value will increase automatically by 1 or your setting with INCREMENT BY n. This is a example how to do it

INSERT INTO table_name values(sequence_name.NEXTVAL);

To change the settings, you can use alter command

ALTER SEQUENCE sequence_name
INCREMENT BY 1
START WITH 1;

To delete a sequence you can use drop command

DROP SEQUENCE sequence_name;

Well, that’s it for now, hope this tutorial can help you. Feel free to comment here if you have any questions.

Popularity: 54% [?]