oracle批量添加指定表字首的索引SQL語句
需要輸入使用者名，表空間，索引個數，表名字首，過濾的表名字尾
##需要輸入使用者名，表空間，索引個數，表名字首，過濾的表名字尾
declare
--使用者名
userName varchar2(50) :='apps';
--表空間
tablespaceName varchar2(50) :='APPS_TS_TX_IDX';
--索引個數
rowN number :=3;
--過濾表名字尾，該表不添加索引，例如臨時表
tableLastName varchar2(10) :=upper('_temp');
tableLastName1 varchar2(10) :=upper('_te');
--表名字首
cursor vdata is select t.table_name from user_tables t where t.TABLE_NAME like upper('SRM_POS_%')
and t.TABLE_NAME not like '%'||tableLastName
and t.TABLE_NAME not like '%'||tableLastName1
order by t.TABLE_NAME;
--聲明變量,記錄一行數
v vdata%rowtype;
n number;
--索引名，限制最大程度為30字元
indexName varchar2(30);
temp varchar2(27);
k number;
begin
--打開遊标
open vdata;
--循環周遊取資料
loop
fetch vdata into v;
exit when vdata%NOTFOUND;
select count(*) into n from user_ind_columns t where t.TABLE_NAME=upper(v.table_name);
if n
k := 1;
--dbms_output.put_line('表名：-----'||v.table_name);
select length(v.table_name) into n from dual;
if n>27 then
temp := substr(v.table_name,0,27);
else
temp := v.table_name;
end if;
indexName := temp||'_N'||k;
for cur in (select t.column_name,t.TABLE_NAME from user_tab_columns t where t.table_name = upper(v.table_name) ---表名
and instr(lower(t.column_name), 'attribute') = 0
and instr(lower(t.COLUMN_NAME),'last_update') = 0
and instr(lower(t.COLUMN_NAME),'version_num') = 0
and instr(lower(t.COLUMN_NAME),'created_by') = 0
and instr(lower(t.COLUMN_NAME),'creation_date') = 0
and t.COLUMN_ID<=rowN) loop
--判斷該字段是否已經建立索引
select count(*) into n from user_ind_columns t where t.TABLE_NAME=upper(v.table_name) and t.COLUMN_NAME=upper(cur.column_name);
--未建立索引
if n<1 then
--判斷索引名是否存在
select count(*) into n from user_ind_columns t where t.INDEX_NAME=upper(indexName);
while n>0 loop
k := k+1;
indexName := temp||'_N'||k;
select count(*) into n from user_ind_columns t where t.INDEX_NAME=upper(indexName);
end loop;
--列印建立索引語句
dbms_output.put_line('Create Index '||userName||'.'||indexName||' ON '||userName||'.'||cur.table_name||'('||cur.column_name||')'||' tablespace '||tablespaceName||';');
k := k+1;
indexName := temp||'_N'||k;
end if;
end loop;
end if;
end loop;
--關閉遊标
close vdata;
end;