用触发器来实现Oracle的自增长列

1， 添加id列

-- ############################################### -- add ID column for XXXXXXTABLE -- ############################################### declare v_cnt binary_integer := 0; begin select count(1) into v_cnt from user_tab_columns e where e.table_name = 'XXXXXXTABLE' and e.column_name = 'ID'; if( v_cnt = 0 ) then execute immediate 'alter table XXXXXXTABLE add ( ID int )'; end if; end; /

2，给ID列赋值

-- ############################################### -- set ID value with FXNDF_FIXING_POST_SEQ for XXXXXXTABLE -- ############################################### merge into XXXXXXTABLE u using ( select rowid rid, rownum rnum from XXXXXXTABLE ) s on (u.rowid = s.rid) when matched then update set u.id = s.rnum ; commit;

3， 添加序列

-- ############################################### -- add FXNDF_FIXING_POST_SEQ -- ############################################### declare v_cnt binary_integer := 0; v_sql varchar2(500) := ''; v_current_max int :=0; begin select count(1) into v_cnt from user_sequences e where e.sequence_name = 'XXXXXXTABLE_SEQ'; if( v_cnt = 0 ) then select max(ID) into v_current_max from XXXXXXTABLE; select nvl(v_current_max,0) + 1 into v_current_max from dual; v_sql := 'CREATE SEQUENCE XXXXXXTABLE_SEQ MINVALUE ' || v_current_max || ' MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH ' || v_current_max; execute immediate v_sql; end if; end; /

4， 添加主键

-- ############################################### -- add unit key on id for XXXXXXTABLE -- ############################################### declare v_cnt binary_integer :=0; begin select count(1) into v_cnt from user_constraints e where e.table_name = 'XXXXXXTABLE' and e.constraint_name = 'PK_XXXXXXTABLE'; if( v_cnt = 0 ) then execute immediate 'alter table XXXXXXTABLE add constraint PK_XXXXXXTABLE primary key (ID)'; end if; end; /

5， 添加before 触发器

-- ############################################### -- add before insert trigger for XXXXXXTABLE -- ############################################### CREATE OR REPLACE TRIGGER TRG_XXXXXXTABLE_INST before insert ON XXXXXXTABLE for each row begin select XXXXXXTABLE_SEQ.nextval into:new.ID from dual; end TRG_XXXXXXTABLE_INST; /

 

转载于:https://www.cnblogs.com/BenWong/p/3171844.html