Oracle self-Adding column creation method collection

Oracle self-Adding column creation method collection

Oracle does not have such features as the self-adding fields, but it can be implemented by triggers (trigger) and sequences (sequence).

First build a test table:

CREATE TABLE Userlogin

(

ID Number (6) is not NULL,

Name VARCHAR2 NOT NULL primary key

)

Tablespace users

/

First step: Create sequence

Create sequence Userlogin_seq increment by 1-start with 1 MinValue 1 MaxValue 9999999999999 the order;

Step two: Create a before insert trigger that is based on the table, and use the newly created sequence in the trigger

Create or Replace Trigger Userlogin_trigger

Before insert on Userlogin

For each row

Begin

Select Userlogin_seq.nextval into:new.id from Sys.dual;

End

/

Step three: Test in the Userlogin table

Write an INSERT statement, insert a record, look at the ID field has not increased, since the increase is OK.

Oracle does not have such functionality as the Oracle Self-add field, but it can be implemented by triggers (trigger) and sequences (sequence).

First build a test table:

CREATE TABLE Userlogin

(

ID Number (6) is not NULL,

Name VARCHAR2 NOT NULL primary key

)

Tablespace users

/

First step: Create sequence

Create sequence Userlogin_seq increment by 1-start with 1 MinValue 1 MaxValue 999999999 the order;

Step two: Create a before insert trigger that is based on the table, and use the newly created sequence in the trigger

Create or Replace Trigger Userlogin_trigger

Before insert on Userlogin

For each row

Begin

Select Userlogin_seq.nextval into:new.id from Sys.dual;

End

/

Step three: Test in the Userlogin table

Write an INSERT statement, insert a record, look at the ID field has not increased, since the increase is OK.

Oracle Sequence Knowledge:

In Oracle sequence is called the serial number, each time it will automatically increase, generally used in the need to order by serial number of places.

1, Create Sequence

You must first have create sequence or create any sequence permissions,

CREATE SEQUENCE emp_sequence

INCREMENT by 1--add a few at a time

Start with 1-counting from 1

Nomaxvalue--Do not set maximum value

Nocycle--Cumulative, not cyclic

CACHE 10;

Once the emp_sequence is defined, you can use the Currval,nextval

Currval= returns the current value of sequence

nextval= increase the value of sequence, and then return sequence value

Like what:

Emp_sequence. Currval

Emp_sequence. Nextval

Where you can use sequence:

-Do not include subqueries, snaps tutorial hot, view SELECT statements

-Subquery in INSERT statement

-The values of the Nsert statement

-In the set of the UPDATE

You can see the following example:

INSERT into EMP VALUES

(Empseq.nextval, ' LEWIS ', ' clerk ', 7902, Sysdate, 1200, NULL, 20);

SELECT Empseq.currval from DUAL;

But be aware of:

-The first time the Nextval returns the initial value, then the nextval automatically increases the increment by value you define and then returns the added value. Currval always returns the value of the current sequence, but cannot use currval after the first nextval initialization, otherwise there will be an error. Once nextval will add a sequence value, so if you use multiple nextval within the same statement, the value is different. Got it?

-If the cache value is specified, Oracle can place some sequence in memory in advance so that the access is faster. Cache inside of the finished, Oracle automatically take a group to cache. Use cache may jump the number, such as the database tutorial suddenly abnormal down (shutdown abort), the cache in the sequence will be lost. So you can use NoCache to prevent this when you create sequence.

2. Alter Sequence

You are either owner of the SEQUENCE or have alter any SEQUENCE permission to change the SEQUENCE. You can alter all sequence parameters except start to. If you want to change the start value, you must drop sequence and then re-create.

An example of Alter sequence

ALTER SEQUENCE emp_sequence

INCREMENT by 10

MAXVALUE 10000

CYCLE--Start from scratch after 10000.

NoCache;

The initialization parameters that affect sequence:

Sequence_cache_entries = Sets the number of SEQUENCE that can be CACHE simultaneously.

Can be very simple drop Sequence

DROP SEQUENCE Order_seq;

Note : Please pay attention to the triple programming Tutorials section for more wonderful articles .