Oracle使用游标为所有用户表创建主键语句 - 数据库编程

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
? ? ? --判断当前数据表是否包含字段名为ID且数据类型为NUMBER
? ? ? SELECT COUNT(*) INTO CounterData FROM dual WHERE EXISTS(SELECT * FROM user_tab_cols WHERE LOWER(COLUMN_NAME)=LOWER(keyidname) AND DATA_TYPE='NUMBER' and TABLE_NAME=myrecord.TABLE_NAME);
? ? ? if CounterData=0 then? ?
? ? ? ? begin
? ? ? ? dbms_output.put_line('--当前数据表'||myrecord.TABLE_NAME||'存在字段名为ID，但数据类型不为NUMBER的列');
? ? ? ? --先重命名字段，然后添加主键字段? ? ? ?
? ? ? ? renamestring:='execute immediate ''alter table '||myrecord.TABLE_NAME||' rename column '||keyidname||' to '||tableidname||''';';
? ? ? ? dbms_output.put_line(renamestring);
? ? ? ? --execute immediate renamestring;

? ? ? ? --添加主键字段? ? ? ? ?
? ? ? ? addstring:='execute immediate ''alter table '||myrecord.TABLE_NAME||' add '||keyidname||' NUMBER'';';
? ? ? ? dbms_output.put_line(addstring);

? ? ? ? --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(notnul

Oracle使用游标为所有用户表创建主键语句(二)