Hello everyone, I will be registering here in the blog a series of resources that are used in our daily life regarding the ORACLE database.
And the tips this time will refer to SEQUENCES.
A SEQUENCE is an object in Oracle that is used to generate a sequence of numbers. This can be useful when you need to create a unique number to act as a primary key. Remember that a SEQUENCE is autonumber type.
The creation syntax is as follows:
CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;
Let’s create one as an example:
CREATE SEQUENCE employees_seq
MINVALUE 1 (here is the minimum value of the sequence)
MAXVALUE 999999999999999999999999999 (here is the maximum value of the sequence)
START WITH 1
(number that the sequence will be started.Here you can put the number you want, knowing that it will continue from here)
INCREMENT BY 1 (it will create other 1 in 1 numbers. You can change to the number you want too, but good practice is 1 in 1)
CACHE 20;
With a SEQUENCE created, see how it can be used when you INSERT the table:
INSERT INTO suppliers (id, name) VALUES (employees_seq.NEXTVAL, ‘Eduardo’);
To know the current number of a SEQUENCE:
select employees_seq.CURRVAL from dual;
To know the next number of a SEQUENCE:
select employees_seq.NEXTVAL from dual;
To change a SEQUENCE to, for example, change the increment number:
ALTER SEQUENCE employees_seq INCREMENT BY 5;
To drop a SEQUENCE is very simple:
DROP SEQUEMCE employees_seq;
To know which are the registered SEQUENCES in the bank, you can consult with the following code:
select * from all_sequences where sequence_owner = ‘<owner of the schema logged>’
Good people, that’s it. Make good use.
Inquiries, please contact us.
Strong hug.
Eduardo Santana
bufallos@bufallos.com.br