--execute immediate addstring;
--创建一个序列
seqstring:='select count(*) into counter from dual where exists(select * from user_sequences where sequence_name='''||seqname||''');if counter>0 then execute immediate ''drop sequence '||seqname||'''; end if; execute immediate '' create sequence SEQ_'||tablestring||' INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE'';';
dbms_output.put_line(seqstring);
--execute immediate seqstring;
--创建一个触发器
trigerstring:='select count(*) into counter from dual where exists(select * from user_triggers where trigger_name='''||trgname||''');if counter>0 then execute immediate ''drop trigger '||trgname||'''; end if; execute immediate '' create trigger TRG_'||tablestring||' BEFORE INSERT ON '||myrecord.TABLE_NAME||' FOR EACH ROW WHEN (new.'||keyidname||' is null) begin select '||seqname||'.nextval into: new.'||keyidname||' from dual; end'';';
dbms_output.put_line(trigerstring);
--execute immediate trigerstring;
--添加主键约束
constring:='select count(*) into counter from dual where exists(select * from user_constraints where constraint_name='''||pkname||''');if counter>0 then execute immediate ''drop constraint '||pkname||'''; end if; execute immediate ''alter table '||myrecord.TABLE_NAME||' add constraint '||pkname||' primary key('||keyidname||')'';';
dbms_output.put_line(constring);
--execute immediate constring;
--更新主键不为空
notnullstring:='select count(*) into counter from dual where exists(select * from user_tab_cols where table_name='''||myrecord.TABLE_NAME||''' and column_name='''||keyidname||''' AND NULLABLE=''Y'' );if counter>0 then execute immediate ''alter table '||myrecord.TABLE_NAME||' modify '||keyidname||' not null''; end if;';
dbms_output.put_line(notnullstring);
--execute immediate notnullstring;
end;
else
begin
dbms_output.put_line('--当前数据表'||myrecord.TABLE_NAME||'存在字段名为ID，且数据类型为NUMBER的列');
--创建一个序列
seqstring:='select count(*) into counter from dual where exists(select * from user_sequences where sequence_name='''||seqname||''');if counter>0 then execute immediate ''drop sequence '||seqname||'''; end if; execute immediate '' create sequence SEQ_'||tablestring||' INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE'';';
dbms_output.put_line(seqstring);
--execute immediate seqstring;
--创建一个触发器
trigerstring:='select count(*) into counter from dual where exists(select * from user_triggers where trigger_name='''||trgname||''');if counter>0 then execute immediate ''drop trigger '||trgname||'''; end if; execute immediate '' create trigger TRG_'||tablestring||' BEFORE INSERT ON '||myrecord.TABLE_NAME||' FOR EACH ROW WHEN (new.'||keyidname||' is null) begin select '||seqname||'.nextval into: new.'||keyidname||' from dual; end'';';
dbms_output.put_line(trigerstring);
--execute immediate trigerstring;
--添加主键约束
constring:='select count(*) into counter from dual where exists(select * from user_constraints where constraint_name='''||pkname||''');if counter>0 then execute immediate ''drop constraint '||pkname||'''; end if; execute immediate ''alter table '||myrecord.TABLE_NAME||' add constraint '||pkname||' primary key('||keyidname||')'';';
dbms_output.put_line(constring);
--execute immediate constring;
--更新主键不为空
notnullstring:='select count(*) into counter from dual where exists(select * from user_tab_cols where table_name='''||myrecord.TABLE_NAME||''' and column_name='''||keyidname||''' AND NULLABLE=''Y'' );if counter>0 then execute immediate ''alter table '||myrecord.TABLE_NAME||' modify '||keyidname||' not null''; end if;';
dbms_output.put_line(notnullstring);
--execute immediate notnullstring;
end;
end if;
end;
end if;
dbms_output.put_line('');
end;
else
exit;
end if;
end loop;
else
dbms_output.put_line('--游标没有打开');
end if;
close mycursor;
dbms_output.put_line('end;');
end;