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; /