oracle批量添加指定表前缀的索引SQL语句

• Z时代
• 2024-01-10
• 分类：综合

需要输入用户名，表空间，索引个数，表名前缀，过滤的表名后缀

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;

以上是 oracle批量添加指定表前缀的索引SQL语句 的全部内容， 来源链接： utcz.com/z/533620.html