Oracle Auto-incrementing Sequence

-- 1. Create a dedicated user under PLSQL
-- Create the user
Create user user1
Identified by \ "user1 \"
Default tablespace users
Temporary tablespace temp
Profile default;
-- Grant/Revoke role privileges
Grant DBA to user1;
-- Grant/Revoke SYSTEM privileges
Grant unlimited tablespace to user1;
------------------------------------

------------------------------------
-- Create a table
Create Table AAA (
Oid number not null,
Type Number (3)
);

 

The following sections handle auto-incrementing sequence Problems

-- Auto-incrementing Sequence
-- Step 1: Create a queue
Create SequenceAaa_oid_ Sequence
Increment by 1 -- add several
Start with 1 -- count from 1
Nomaxvalue -- do not set the maximum value
Nocycle -- always accumulate without repeating
Nocache;

-- Step 2: Create a trigger
Create or replace triggerAaa_oid_trigger
Before insert onAaaFor each row
Declare
Next_id number;
Begin
-- Get the next ID number from the sequence
SelectAaa_oid_ Sequence. Nextval into next_id from dual;
-- Use the sequence number as the primarykey
-- For there cord being inserted.
: New.Oid: = Next_id;
End;

 

Test:

Insert into AAA (type) values (2 );

Select * from AAA;