大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说oracle批量添加指定表前缀的索引SQL语句[通俗易懂],希望您对编程的造诣更进一步.
需要输入用户名，表空间，索引个数，表名前缀，过滤的表名后缀
declare
--用户名
userName varchar2(50) :="snsrm";
--表空间
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<rowN then
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.comments,t.TABLE_NAME from all_col_comments 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 rownum<=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=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=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;代码100分
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/8473.html
