oracle 修改表結構及表索引 PLSQL指令碼寫法

標籤：io   os   ar   sp   on   c   ad   ef   sql   

declare
  v_rowcount integer;
  v_rowcount1 integer;
  v_rowcount2 integer;
begin
  select count(*) into v_rowcount from dual where exists(
    select * from col
      where tname = upper(‘bailcalcinfo‘)
        and cname = upper(‘operator_no‘) );
  if v_rowcount = 0 then
    execute immediate ‘alter table bailcalcinfo add (operator_no    varchar2(18) default ‘‘ ‘‘ NOT NULL)‘;
  end if;
 
  select count(*) into v_rowcount1 from user_indexes where index_name =‘IDX_BAILCALCINFO‘;
  if v_rowcount1 > 0 then
    select count(*) into v_rowcount2 from dual
    where exists(select 1 from user_ind_columns where index_name = ‘IDX_BAILCALCINFO‘ and column_name = ‘FUTU_EXCH_TYPE‘ and column_position = 1)
      and exists(select 1 from user_ind_columns where index_name = ‘IDX_BAILCALCINFO‘ and column_name = ‘CONTRACT_CODE‘ and column_position = 2)
      and exists(select 1 from user_ind_columns where index_name = ‘IDX_BAILCALCINFO‘ and column_name = ‘ENTRUST_BS‘ and column_position = 3)
      and exists(select 1 from user_ind_columns where index_name = ‘IDX_BAILCALCINFO‘ and column_name = ‘HEDGE_TYPE‘ and column_position = 4)
      and exists(select 1 from user_ind_columns where index_name = ‘IDX_BAILCALCINFO‘ and column_name = ‘OPERATOR_NO‘ and column_position = 5);
    -- 存在但是不正確，則刪除索引
    if v_rowcount2 = 0 then 
      execute immediate ‘drop index hs_futures.IDX_BAILCALCINFO‘;
    end if;
  end if;
  -- 未存在 OR 存在但是不正確，都要重建索引
  if v_rowcount1 = 0 or v_rowcount2 = 0 then 
    execute immediate ‘CREATE INDEX hs_futures.IDX_BAILCALCINFO ON hs_futures.bailcalcinfo(futu_exch_type,contract_code,entrust_bs,hedge_type,operator_no)tablespace HS_FUTURES_IDX‘;
  end if;
end;
/

oracle 修改表結構及表索引 PLSQL指令碼寫法