Using Auto-increment fields in Oracle

Using Auto-increment fields in Oracle

As we all know, Oracle does not support the auto_increment field, but you can create a sequence object to use the auto-incrementing sequence. The syntax is:

SQL Code

1. Create sequence seq_subs_id
   
2. Increment by 1
   
3. Start with 1
   
4. Max value 10000000000
   
5. Nocycle;

Seq_subs_id indicates the sequential column name, increment by 1 indicates the step size is 1, start with 1 indicates the initial value is 1, and maxvalue indicates the maximum value nocycle indicates the non-cyclic value.

To view the created sequence, run the following statement:

SQL code

1. Select * From user_sequences;

The returned sequence_name is the sequential column name, And last_number is the current value.

To obtain the next ID, you can use:

SQL code

1. Select sequence name. nextval from Table Name

This statement returns multiple serial numbers (cached). To obtain a sequence number, use the table nameDual (virtual table)You can.

Use pear: db operation

Pear: used by DBNextid ($ seqname)To encapsulate the auto-incrementing sequence operation. For MySQL, pear also abandons the field auto-incrementing function, and uses the method of creating a table to manage the auto-incrementing ID. in Oracle, the sequence object is directly used for operations.

In pear: DB, You need to obtain the next ID, whether using MySQL or Oracle, you can use $ db-> nextid ($ seqname, generally, $ seqname is the table name. When the specified sequence does not exist, you can use the second parameter to specify automatic creation.

In pear, nextid processes $ seqname in the specified format. By default, the suffix "_ seq" is added. Therefore, if you operate on an existing sequence created using other methods, conflicts may occur. To change this format, use

PHP code

1. $ Db-> setoption ('seqname _ format', "seq _ % s"); // with this modification, nextid ('table1 ') the sequence in the operation is changed to seq_table1 (table1_seq by default)