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;declareaddstring 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;更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址