Analysis of auto-increment fields in Oracle

Environment: PLSQL Developer 7.1.5 Oracle 11.2.0 Oracle does not specify an automatic growth column as MYSQL or MS SQLServer does. However, in Oracle, the SEQUENCE can be used to automatically increase fields. In Oracle, SEQUENCE is called a SEQUENCE. It is automatically increased every time it is obtained. It is generally used in places where the SEQUENCE numbers need to be sorted. Before using SEQUENCE, you must first define a SEQUENCE. The syntax for defining SEQUENCE is as follows: [SQL] CREATE SEQUENCE sequence_name www.2cto.com INCREMENT BY step START WITH startvalue; where sequence_name is the name of the SEQUENCE, each sequence must have a unique name. The value of startvalue is the starting number, and the value of step is the step, that is, the value added when the sequence increases automatically. Once the SEQUENCE is defined, you can use CURRVAL to obtain the current value of the SEQUENCE. You can also use NEXTVAL to increase the SEQUENCE and then return a new SEQUENCE value. For example, [SQL] sequence_name.CURRVAL sequence_name.NEXTVAL can delete SEQUENCE if it is not required: DROP SEQUENCE sequence_name. The following is an example of auto-increasing SEQUENCE. First, CREATE a SEQUENCE: [SQL] CREATE SEQUENCE seq_PersonId www.2cto.com MINVALUE 0 INCREMENT BY 1 START WITH 0. Note: If this sentence is not added (MINVALUE 0 ), this error may occur (ORA-04006: start with cannot be smaller than MINVALUE ). The solution is to specify the minimum value. Then CREATE the T_Person TABLE: [SQL] CREATE TABLE T_Person (FId NUMBER (10) PRIMARY KEY, FName VARCHAR2 (20), FAge NUMBER (10 )); after the preceding SQL statement is executed, the T_Person table is created successfully. Then, execute the following SQL statement to INSERT some data INTO the T_Person table: [SQL] INSERT INTO T_Person (FId, FName, FAge) www.2cto.com VALUES (seq_PersonId.NEXTVAL, 'Tom ', 18); insert into T_Person (FId, FName, FAge) VALUES (seq_PersonId.NEXTVAL, 'Jim', 81); insert into T_Person (FId, FName, FAge) VALUES (seq_PersonId.NEXTV AL, 'kerry', 33); note that the INSERT statement does not set any value for the FId field, because the DBMS automatically sets a value for the FId field. After the execution is complete, view the content in the T_Person table: fid fname FAGE1 Tom 182 Jim 813 Kerry 33 uses SEQUENCE to automatically increase fields. The disadvantage is that each time a record is inserted into the table, a new field value must be obtained from the SEQUENCE explicitly, if you forget it, it will cause an error. To solve this problem, we can use triggers to CREATE a trigger on the T_Person table: [SQL] CREATE OR REPLACE TRIGGER trigger_personIdAutoInc www.2cto.com BEFORE INSERT ON T_Person FOR EACH ROW DECLARE BEGIN SELECT statement: NEW. fid from dual; END trigger_personIdAutoInc; this trigger is triggered before new records are inserted in T_Person. When the trigger is triggered, a new serial number is obtained FROM seq_PersonId and then set to the FID field. Execute the following SQL statement to INSERT some data INTO the T_Person table: [SQL] INSERT INTO T_Person (FName, FAge) VALUES ('wow', 22); INSERT INTO T_Person (FName, FAge) VALUES ('herry', 28); insert into T_Person (FName, FAge) VALUES ('gavin', 36); note that you do not need to assign VALUES to the FId field in this SQL statement. After execution, check the content in the T_Person table: fid fname fage www.2cto.com 1 Tom 182 Jim 813 Kerry 334 Wow 225 Herry 287 Gavin 36. Please delete the T_Person table and SEQUENCE: [SQL] DROP TABLE T_Person; DROP SEQUENCE seq_PersonId; Author: Wentasy