Auto-increment field in Oracle

Environment: PLSQL Developer 7.1.5 Oracle 11.2.0

Oracle does not specify an auto-increment column as MYSQL or MSSQLServer does. However, in Oracle, the SEQUENCE can be used to automatically increase fields. In Oracle, SEQUENCE is called a SEQUENCE. It is automatically increased every time it is obtained. It is generally used in places where the SEQUENCE numbers need to be sorted.

Before using SEQUENCE, you must first define a SEQUENCE. The syntax for defining SEQUENCE is as follows:

1. CREATE SEQUENCESequence_name
3. INCREMENTBYStep
5. STARTWITHStartvalue;

WhereSequence_nameIt is the name of a sequence. Each sequence must have a unique name;StartvalueParametersThe value is the starting number,StepThe parameter value is the step size, that is, the value that is added each time it increases automatically. OnceSEQUENCEYou can useCURRVALTo obtainSEQUENCEYou can also useNEXTVALTo addSEQUENCEAnd then returnNewSEQUENCEValue. For example:

1. Sequence_name.CURRVAL
3. Sequence_name.NEXTVAL

IfSEQUENCEYou can delete it if you do not need it:

Drop sequence sequence_name;

The following example usesSEQUENCEAn example of Automatic sequence growth. First, createSeq_PersonIdOfSEQUENCE:

1. CREATE SEQUENCESeq_PersonId
3. MINVALUE 0
5. INCREMENTBY1
7. STARTWITH0;

Note: If this sentence is not added (MINVALUE 0), This error may occur (ORA-04006: STARTCannot be lessMINVALUE). The solution is to specify the minimum value.

Then createT_PersonTable:

1. CREATE TABLET_Person
3. (
5. FId NUMBER (10)PRIMARY KEY,
7. FName VARCHAR2 (20 ),
9. FAge NUMBER (10)
11. );

Execute the precedingSQLThe statement is created successfully.T_PersonAnd then execute the followingSQLStatement directionT_PersonInsert some data into the table:

1. INSERT INTOT_Person (FId, FName, FAge)
3. VALUES(Seq_PersonId.NEXTVAL,'Tom', 18 );
7. INSERT INTOT_Person (FId, FName, FAge)
9. VALUES(Seq_PersonId.NEXTVAL,'Jim', 81 );
13. INSERT INTOT_Person (FId, FName, FAge)
15. VALUES(Seq_PersonId.NEXTVAL,'Kerry', 33 );

1 2 Next Page