Create an Oracle Auto-increment Field

Guidance:The SQL Server database containsAuto-increment FieldThis type. HoweverOracleBut not yet. We can use the trigger function to implement it:

1. Create a storage process for creating auto-increment Fields

Create or replace procedure pro_create_seq_col
(Tablename varchar2, columnname varchar2)
As
Strsql varchar2 (1000 );
Begin
Strsql: = 'create sequence seq _ '| tablename | 'minvalue 1 maxvalue 999999999999999999 start with 1 increment by 1 nocache ';
Execute immediate strsql;
Strsql: = 'create or replace trigger trg _ '| tablename | 'before insert on' | tablename |' for each row begin select seq _ '| tablename | '. nextval into: new. '| columnname |' from dual; end ;';
Execute immediate strsql;
End;
/

2. Display authorization when executing dynamic SQL statements in Oracle, even if the user has this permission)

Grant create any sequence to UserName;
Grant create any trigger to UserName;
3. Re-Compile the Stored Procedure pro_create_seq_col;

4. Now we can use this stored procedure to create auto-incrementing self-segments.

5. When you call the stored procedure to create an auto-increment field, the following message is displayed: the first parameter is the table name, and the second parameter is the name of the auto-increment field)

Exec pro_create_seq_col ('SB _ zsxx', 'zsxh ');
Exec pro_create_seq_col ('SB _ sbxx', 'sbxh ');
Exec pro_create_seq_col ('SB _ jkx', 'pzxh ');
Exec pro_create_seq_col ('sdspfp', 'sysfpid ');

I hope that you can master the Oracle Auto-increment field creation technology flexibly. I believe this technology will be very useful.