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

应用场合：数据表新增自增一主键能加快数据表的访问速度，而且是整形的索引速度最快。本程序适合在导入Oracle数据库时删除不存在主键的情况下运行。

代码说明：所有的表主键字段名都设置为ID,如果已存在ID字段，则判断是否是整形，如果不是就重命名字段为[表名ID]，然后新增ID，如果不存在则直接添加自增一ID的主键

操作说明：打开PQSQL连接数据库后直接执行下面的详细脚本代码运行即可，脚本有风险(会删除原来的索引跟主键约束)，请不要轻易在正式运行的数据库上直接执行

--Oracle使用游标为所有用户表创建主键语句
--参考语句如下：
--查询所有主键约束select * from user_constraints
--查询所有序列select * from user_sequences;
--查询所有触发器select * from user_triggers;
--查询触发器的用户select distinct(table_owner) from user_triggers;

declare

addstring NVARCHAR2(2000):=' '; --定义添加字段变量 
renamestring NVARCHAR2(2000):=' '; --定义重命名字段变量 
tablestring NVARCHAR2(2000):=' '; --定义序列变量
keyidname NVARCHAR2(255):='ID'; --定义主键字段名变量
tableidname NVARCHAR2(255):=' '; --定义新的字段名变量
trigerstring NVARCHAR2(2000):=' '; --定义创建触发器字符串变量   
trgname NVARCHAR2(255):=' '; --定义触发器名称变量 
seqstring NVARCHAR2(2000):=' '; --定义创建序列字符串变量   
seqname NVARCHAR2(255):=' '; --定义序列名称变量
pkname NVARCHAR2(255):=' '; --定义主键索引名称变量

constring NVARCHAR2(2000):=' '; --定义索引变量 
notnullstring NVARCHAR2(2000):=' '; --定义主键不为空变量 

cursor mycursor is select * from user_tables where TABLESPACE_NAME='SZGABL' ORDER BY TABLE_NAME; --定义游标获取所所有用户数据表名称
myrecord mycursor%rowtype;  --定义游标记录类型
CounterName int :=0;  --定义是否存在对应的列名变量
CounterData int :=0;  --定义是否存在对应的数据类型

begin 

dbms_output.put_line('declare counter int :=0;begin ');

open mycursor;  --打开游标 
if mycursor%isopen  then  --判断打开成功 
loop --循环获取记录集   
fetch mycursor into myrecord; --获取游标中的记录       

if mycursor%found then  --游标的found属性判断是否有记录 
begin
  --获取有效的数据表名
  select replace(myrecord.TABLE_NAME,'TB_','') into tablestring from dual;
  select 'SEQ_'||tablestring into seqname from dual;
  select 'TRG_'||tablestring into trgname from dual;
  select 'PK_'||tablestring into pkname from dual; 
  select tablestring||UPPER(keyidname) into tableidname from dual;
 
  --判断当前数据表是否包含字段名为ID的列
  SELECT COUNT(*) INTO CounterName FROM dual WHERE EXISTS(SELECT * FROM user_tab_cols WHERE LOWER(COLUMN_NAME)=LOWER(keyidname) and TABLE_NAME=myrecord.TABLE_NAME);
  if CounterName=0 then
    begin
    dbms_output.put_line('--当前数据表'||myrecord.TABLE_NAME||'不存在字段名为ID的列');
        --添加主键字段
        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(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(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;