Oracle Auto-increment Field

Sequence Creation
Create sequence name (for example, "urlimage_seq") minvalue 1 maxvalue 999999999999999999999999999 increment by 1 start with 101 Cache 10 noorder nocycle;
 

Trigger trigger
Create or replace trigger name (for example, urlimage_tri)
Before insert
Name of the On ing table (for example, urlimage)
For each row
Begin
Select the sequence name just created (for example, urlimage_seq). nextval into: New. Growth field name (for example, picid) from dual;
End;

Create a sequence
Introduction to Oracle Sequence

In Oracle, sequence is the so-called serial number, which is automatically increased every time it is obtained. It is generally used in places where the sequence numbers need to be sorted.
1. Create Sequence
First, you must have the create sequence or create any sequence permission,
Create sequence emp_sequence
Increment by 1 -- add several
Start with 1 -- count from 1
Nomaxvalue -- do not set the maximum value
Nocycle -- always accumulate without repeating
Cache 10;

Once emp_sequence is defined, you can use currval, nextval
Currval = returns the current Sequence Value
Nextval = increase the sequence value, and then return the Sequence Value
For example:
Emp_sequence.currval
Emp_sequence.nextval

Where sequence can be used:
-Select statements that do not contain subqueries, snapshot, and view
-The insert statement is in the subquery.
-In the value of the nsert statement
-Update in Set

See the following example:
Insert into EMP values
(Em1_q. nextval, 'Lewis ', 'cler', 7902, sysdate, 1200, null, 20 );

Select empseq. currval from dual;

Note that:
-The first nextval returns the initial value. The subsequent nextval automatically increases the value of your defined increment by and then returns the added value. Currval always returns the value of the current sequence, but currval can be used only after the first nextval initialization; otherwise, an error will occur. Nextval increases the sequence value once. Therefore, if you use multiple nextval values in the same statement, their values are different. Understand?

-If the cache value is specified, Oracle can place some sequence in the memory in advance, so that the access speed is faster. After the cache is obtained, Oracle automatically retrieves another group to the cache. The cache may be skipped. For example, if the database suddenly fails to be shut down (shutdown abort), the sequence in the cache will be lost. Therefore, nocache can be used to prevent this situation when creating sequence.

2. Alter Sequence
You are either the owner of the sequence, or you have the alter any sequence permission to modify the sequence. you can alter all sequence parameters except start. if you want to change the start value, you must drop sequence and re-create.
Alter sequence example
Alter sequence emp_sequence
Increment by 10
Max value 10000
Cycle -- start from scratch after 10000
Nocache;

Initialization parameters that affect sequence:
Sequence_cache_entries = sets the number of sequence that can be simultaneously cached.

It's easy to drop sequence.
Drop sequence order_seq;
 
 
 
Recently developed with vs2005 + Oracle, Oracle has never been used before, and Oracle has little knowledge. today, the boss said that to create a table, the primary key must grow automatically. so I used the SQL Server method to create it there. after half a day of exploration, I could not build it, so I went to the Internet to search... it used sequence to solve the automatic growth of columns .....
Below are some of the documents that have been sorted out...
 
Sequence:
(1). values can be automatically generated for columns in the table.
(2). The database objects are created by the user and can be shared by multiple users.
(3). It is generally used for primary keys or unique columns.

Example:
Create sequence my_seq --- create sequence name

Start with 1 --- start from 1

Increment by 1 --- increase by 1 each time

Maxvalue 999999999 --- maximum value

Minvalue 1 --- minimum value

Cycle --- cycle

Cache --- Cache

Order

Start from 1, increase 1 each time, the maximum value is 999999999, and then the cycle starts from 1.

SQL statement:
Insert into mytable values (my_seq, 'aaa ')

Insert into mytable values (my_seq, 'bbb ')

Result:
1 aaa
2 bbb

Call:
Select my_seq.nextval from mytable --- New Value

Select my_seq.currval from mytable --- Current Value
 
Create a table
Create Table Table1
(ID number primary key,
Username varchar2 (50 ));

-- Sequence Creation
Create sequence table1_seq
Minvalue 1
Max value 9999999999
Start with 1
Increment by 1
Cache 30
Order;

-- Triggers
Create or replace trigger table‑tg
Before insert on Table1
For each row
Begin
Select tabledomainseq.nextval into: New. ID from dual;
End;

-- Ignore rows
Insert into Table1 (username) values ('values 3 ');

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/good_youth/archive/2008/05/04/2383356.aspx