Oracle implementation self-growing column ____oracle

Lo Wan Small into Oracle to achieve self-growth column

A sequence (Sequence) is a database object that can be used by more than one user to produce a series of unique numbers. Sequence definitions are stored in the data dictionary, simplifying program design by providing a sequential table of unique values, which can be used to automatically generate key values for primary keys. When a sequence is invoked for the first time, it returns a predetermined value. In each subsequent query, the sequence produces a value that grows by the specified increment. The sequence can be cycled or incremented continuously until the maximum value is specified. The syntax for creating a sequence is as follows: Create sequence [pattern] sequence name [start with start number] [increment by increment][maxvalue maximum |nomaxvalue][minvalue min |nominva Number of Cycle|nocuyle][cache |nocache][order|noorder].
The sequence initiation number, maximum, minimum, and increment values can be used to determine whether the sequence is added or reduced, and how much each increment or decrease. The Nocyle option is used to determine that no more values can be generated after the sequence reaches the maximum (sequence) or minimum (minus sequence) to prevent the sequence from turning.
Create a sequence in an Oracle database that, when writing data to a database using SQL statements, uses the unique values generated by the sequence to increase the value of the primary key in the table. For example:

    Sql>create table tablename   
     
        (ID number notnull,...);     
          
    SQL >create sequence autoid increment by 1-start with 1 MaxValue 999999; 
             
    

Multiple users can share a sequence, but it is for all tables, so the resulting ordinal number is the only primary key value produced for a table that is discontinuous.
Use triggers to generate primary key values.
In a datasheet, it is sometimes necessary to automatically increase the primary key value, but in the Oracle database, there is no data type that grows automatically like MySQL's AutoIncrement. When implementing the Oracle database field customization, use the DML trigger to complete it.
Triggers (trigger) are procedures that perform these procedures when a particular database event occurs, and you can use triggers to augment the integrity of the reference. DML is a data manipulation language used by users or programmers to manipulate data in a database. Basic data operations are divided into two categories: retrieval (query) and update (INSERT, delete, modify). Triggers are similar to functions and procedures, which exist as independent identities in the database. The triggering event can be a DML (INSERT, update, or delete) operation on a database table. A DML trigger is the most widely used trigger, a trigger fired by a DML statement, that determines the type of DML trigger. The triggering events include insert (insert), update (update), and delete (delete). Any triggering event can create before triggers and after triggers for each trigger event. If you can create a before INSERT statement on a table, you will be able to take action before the Insert event occurs.
The syntax for creating triggers is as follows:

    create [or replace] trigger trigger name   
     
    {BEFORE|AFTER|INSTEADOF} fires trigger event   
     
    referencing_clause   
     
    [when Trigger_ Condition]   
     
    [for each ROW]   

Referencing_clause is used to refer to data in a row that is in a modified state, and if Trigger_condition is specified in the When clause, the condition is evaluated first. The trigger body runs only if the condition is true. With the combination of trigger and sequence, the primary key value of the table can be automatically increased when DML operation is performed. The implementation steps can refer to the following examples.

    drop table book;  
    --Create TABLE Creation table book     
    (      
       bookid varchar2 (4) primary key,  
       name Varchar2        
    );  
    --Creates the sequence create     
    sequence book_seq start with 1 increment by 1;   
     
    --Create trigger     
    or replace trigger Book_trigger      
    before Inserton book for each      
    row      
    begin      
    Select Book_seq.nextval into:new.bookId from dual;     
    End;  
    --Add data insert into book     
    (name)  values (' cc ');   
    Insert into book (name)  values (' dd ');  
     
    Commit  

Query data:Select * from book;

When you need to write a value to a primary key value in the book database table, you can use the SQL statement to implement the primary key value automatically by using the sequence + trigger.

In this case, the automatic growth column refers to the automatic growth of the primary key ID in a table.
Unlike MySQL, Oracle cannot set the automatic Growth column feature when create tables. Oracle must create a sequence sequence to automate the addition of columns.
The first step is to set up a sequence (of course, you must first build the table, add a PRIMARY KEY constraint, this column assumes the constraint named Test_sequence)
Create sequence Test_sequence
[Increment by 1]--growth step
[Start with 1]--grows from several beginnings
[Maximum MaxValue 100]--growth
[nomaxvalue]--does not set the maximum value
[cycle|nocycle];--cycle growth/non-cyclic growth
After you define sequence, you can use Test_sequence.nextval and test_sequence.currval in the INSERT statement.
Test_sequence.currval returns the value of the current sequence, but must be used after the first initialization of the Test_sequence.nextval
Test_sequence.currval.
Test_sequence.nextval increases the value of the sequence and returns the value of the incremented sequence.
You can then alter the sequence sequence to change the way it automatically increases.
Alter sequence test_sequence increment by 1 ...; The following options are the same as when you build the table.
You can also delete the sequence sequence with drop.
Drop sequence test_sequence;

MySQL is much simpler for Oracle and can be set up when the table is being built.
CREATE TABLE (
ID Int (TEN) Auto_incrementprimary key
) Auto_increment=1;
Auto_increment=1 Set auto Grow column starting from 1

Once you added a primary key to a MySQL table, the following SQL statement was usedAlterTable' Table name 'Addcolumn' Column name 'intNot NULL auto_increment comment ' primary key ' before ' a column name ';

The results encountered the following two errors: Error code:1064 you have a error in your SQL syntax;     Check the manual that corresponds to your MySQL server version for the right syntax to use near ' before column name ' at line 1 (0 ms taken)

This error is to say that there are grammatical errors near before error code:1075 incorrect table definition; There can is only one auto column and it must be defined as a key (0 ms taken)

The error is that only a primary key can specify an automatic growth column

Search the Internet for the next reason, that is to add a column, and then modify the column. I think it's very troublesome. I tried it on my own, and I got a word: alter table ' table name ' add column ' column name ' int not NULL auto_ Increment primary key comment ' primary key ' ;

The above statement indicates that a table is added to an automatically growing primary key field and the field is placed in the first column.

If you want to put it behind the other columns, you can change the top of the above SQL statement to after ' existing column name '
MySQL Auto grow column problem that
The table is like this:
CREATE TABLE Tips
(
ID int auto_increment NOT NULL,
Date varchar (ten) NULL,
Content varchar (+) null.
Primary KEY (ID)
)
I insert a piece of data
Insert into tips values ("2009/09/20", "small Tips");
The result is an error,
It should be written in this way:
Insert into Tips (date,content) VALUES ("2009/09/20", "small Tips");
Or
Insert into tips values (null, "2009/09/20", "small Tips");