Oralce 增加 删除 主键 索引

declare
v_key varchar2(500);
v_sql varchar2(500);
begin
   select max(cu.constraint_name) into v_key
    from user_cons_columns cu, user_constraints au
   where cu.constraint_name = au.constraint_name
   and au.constraint_type = 'P'
   and au.table_name = 'temp_table ';
   
   v_sql:='alter table temp_table drop constraint '||v_key||' cascade'; 
   execute immediate v_sql;
end;
/


--处理表 索引处理  temp_table 
declare
  v_key varchar2(500);
  v_num number;
  v_sql varchar2(500);
begin
  select count (t.index_name) into v_num from user_ind_columns t where t.table_name = 'temp_table ';
  if v_num >0 then
    select distinct (t.index_name) into v_key from user_ind_columns t where t.table_name = 'temp_table ';
       if v_key is not null then
         v_sql := 'drop index ' || v_key || '';
         execute immediate v_sql;
         v_sql :='create unique index U_temp_table  on temp_table (NM, QM,ID, KSSJ, JSSJ,TJWD)'; 
         execute immediate v_sql;
       end if;
   else
       v_sql :='create unique index U_temp_table  on JW_JG_GZLJGB (NM, QM,ID, KSSJ, JSSJ,TJWD)'; 
       execute immediate v_sql;
   end if;
end;
/


--处理 索引处理 (已经创建主键对象 需要删掉主键和索引)  temp_table 
declare
  v_key varchar2(500);
  v_num number;
  v_sql varchar2(500);
begin
  select count (t.index_name) into v_num from user_ind_columns t where t.table_name = 'temp_table';
  if v_num >0 then
    select distinct (t.index_name) into v_key from user_ind_columns t where t.table_name = 'temp_table ';
       if v_key is not null then
         v_sql := 'alter table temp_table drop constraint ' || v_key || '';
         execute immediate v_sql;
         v_sql :='create unique index U_temp_table on temp_table (DM,FSDXX,FSDSX,XS)'; 
         execute immediate v_sql;
       end if;
   else
       v_sql :='create unique index U_temp_table on temp_table (DM,FSDXX,FSDSX,XS)'; 
       execute immediate v_sql;
   end if;
end;
/

创建 主键 和约束

-- Create/Recreate primary, unique and foreign key constraints 
alter table temp_table add constraint P_temp_table  primary key DM, XS);