plsql oracle加索引,oracle 修改表结构及表索引 PLSQL脚本写法

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;

/

原文：http://www.cnblogs.com/lonsine/p/3974232.html