应用场合：数据表新增自增一主键能加快数据表的访问速度，而且是整形的索引速度最快。本程序适合在导入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);
人的价值，在遭受诱-惑的一瞬间被决定
