Automatic incrementing of the Oracle Database creation table ID field

Reprint Address: http://blog.itpub.net/22880668/viewspace-1117343/

Set the field ID of the table T_uaer to self-increment: (Implemented using the method of sequence sequence)

----Create a table
Create Table T_user (
Id Number (6), UserID varchar2 (LOGINPASSWORDVARCHAR2), isdisable number (6)
);

----Create a sequence
Create sequence User_seq

Increment by 1
Start with 1
Nomaxvalue
Nominvalue
NoCache

----CREATE TRIGGER
create or Replace trigger Tr_user
before insert on T_USER
for each row< BR style= "padding:0px; margin:0px; " >BEGIN
end;

----Test
Insert into T_user (Userid,loginpassword, isdisable)
VALUES (' ffll ', ' Liudddyujj ', 0);
Insert into T_user (Userid,loginpassword, isdisable)
VALUES (' dddd ', ' Zhang ', 0)
SELECT * from T_user;
You can see the results.

Description of sequence:

IncrementBy: is used to specify the sequence increment (default: 1), and if a positive integer is specified, the sequence number is automatically incremented, and if a negative value is specified, it is automatically decremented.

Startwith: is used to specify the first sequence number generated by the series generator, and the default value is the maximum value of the sequence number if the default value is the minimum worth sequence number when the sequence number is incremented.

Maxvalue: Used to specify the maximum sequence number that the series generator can generate (must be greater than or equal to Startwith, and must be greater than minvalue), and the default is Nomaxvalue.

Minvalue: Used to specify the minimum sequence number that the series generator can generate (must be less than or equal to Starrwith, and must be less than MaxValue), and the default value is Nominvalue.

Cycle: Specifies whether to continue generating serial numbers after the maximum or minimum value of the sequence is reached, by default, Nocycle.

Cache: Specifies the number of serial numbers that can be pre-allocated in memory (default: 20).

It should be noted in sequence:

1, the first time Nextval return is the initial value, the subsequent nextval will automatically increase your defined IncrementBy value, and then return the added value. Currval always returns the value of the current sequence, but the currval is not used until the first nextval is initialized, otherwise an error occurs. A nextval will increment the value of sequence once, so if you use multiple nextval in the same statement, the value is different.

2, if the cache value is specified, Oracle can pre-put some sequence in memory, so that access faster. After the cache is finished, Oracle automatically takes another set to the cache. Using the cache may jump, such as the database suddenly abnormal down (shutdownabort), the cache sequence will be lost. So you can use NoCache to prevent this when you create sequence.

Automatic incrementing of the Oracle Database creation table ID field