Oracle sequence use: Create, delete

Before you start to explain how Oracle sequence is used, add a little bit about Oracle client Sqlplus, that is, if you execute multiple lines of statement, you must add "/" to the end and execute! The topic of this article is to increase the primary key of a table by creating an Oracle sequence and a trigger.

1. First create the sequence, the syntax format of the Oracle sequence is:
CREATE SEQUENCE sequence Name
[INCREMENT by N]
[START with N]
[{Maxvalue/minvalue n| Nomaxvalue}]
[{cycle| Nocycle}]
[{CACHE n| NOCACHE}];

1) INCREMENT by is used to define the step of the sequence, and if omitted, the default is 1, and if a negative value is present, the values for the Oracle sequence are decremented by this step.

2) START with defines the initial value of the sequence (that is, the first value produced), which defaults to 1.

3) MAXVALUE defines the maximum value that the sequence generator can produce. Option Nomaxvalue is the default option, which means that there is no maximum value defined, at which point the system can produce a maximum of 10 27 times for incrementing an Oracle sequence, and for a descending sequence, the maximum value is-1.

4) MinValue defines the minimum value that the sequence generator can produce. Option Nomaxvalue is the default option, which means there is no minimum definition, and for a descending sequence, the minimum value that the system can produce is 10 of the 26 square, and for the increment sequence, the minimum value is 1.

5) cycle and nocycle indicate whether the sequence generator will loop if its value reaches the limit value. The cycle represents the loop, and the nocycle represents no loops. If the loop is, it loops to the minimum when the increment sequence reaches its maximum value, and to the maximum value when the descending sequence reaches the minimum value. If you do not loop, the error occurs when the limit value is reached and the new value continues to be generated.

6) the cache (buffer) defines the size of the memory block that holds the sequence, which defaults to 20. NoCache indicates that the sequence is not buffered in memory. Memory buffering of a sequence can improve the performance of the sequence.

2. The syntax for deleting an oracle sequence is the drop SEQUENCE sequence name;

Suppose there is a table test whose primary key is test_id
1) set up the increment sequence seq_test:
Create sequence Seq_test
Increment by 1
Start with 1
MinValue 1 Nomaxvalue
Nocylce

2) Create a trigger that uses an ascending primary key value for which the Oracle sequence goes when there is data inserted into the table test
Create trigger trg_test before insert on TEST
For each row
Begin
Select Seq_test.nextval into:new. TEST_ID from dual;
End

At this point, the creation is complete!

Of course, instead of using triggers, you can call a sequence in an SQL statement at insert time, for example
INSERT into TEST values (Seq_test.nextval, ...)

3. Modify the sequence syntax:

ALTER SEQUENCE sequence Name
[INCREMENT by N]
[{Maxvalue/minvalue n| Nomaxvalue}]
[{cycle| Nocycle}]
[{CACHE n| NOCACHE}];

* Cannot modify the initial value of the sequence

However, it is important to note that:
-The first nextval returns the initial value, and the subsequent nextval automatically increases the increment by value that you define.
The added value is then returned. Currval always returns the value of the current sequence, but for the first time Nextval
Currval can be used after initialization, otherwise an error occurs. A nextval will increase the value of sequence once,
So if you use multiple nextval in the same statement, the value is different. Got it?
-If the cache value is specified, Oracle can pre-place some sequence in the memory so that the fast access
Some. After the cache is finished, Oracle automatically takes another set to the cache. Using the cache may skip the number, for example
The database suddenly fails down (shutdown abort) and the sequence in the cache is lost. So you can
Use NoCache to prevent this in the case of create sequence.

Templates >>

Oracle Creation Sequence:

 Create 99999999with   by 1 NoCache;  

Query sequence:

Select  from dual;  

Generate the corresponding sequence for each table:

--Create a stored procedure:

    Create or Replace procedureP_CREATESEQ (tablenameinch varchar2)        isstrSQLvarchar2( -); beginstrSQL:='Create sequence Seq_'||TableName||'MinValue maxvalue 99999999 start with the increment by 1 NoCache'; Executeimmediate strSQL; EndP_createseq; /  

--oracle Create sequence:

execP_createseq ('T_power'); execP_createseq ('T_roler'); execP_createseq ('T_roler_power'); execP_createseq ('t_department'); execP_createseq ('t_quarters'); execP_createseq ('T_quarters_roler'); execP_createseq ('t_emp'); execP_createseq ('T_require_plan'); execP_createseq ('T_require_minutia'); execP_createseq ('T_require_audit'); execP_createseq ('T_engage'); execP_createseq ('T_home'); execP_createseq ('t_education');... ...