【技术】得到外键对应的索引MatchForeignKeyIndex

 

 

Create Or Replace FUNCTION MATCHFOREIGNKEYINDEX( i_constraint_name In varchar , i_owner In varchar default null )
return varchar2
is
  
  FK_owner varchar2(100);         -- 外键的拥有者 
  Fk_table_name varchar2(200);    -- 外键作用的表名字 
  
  index_name varchar2(200) := '';       -- 和外键匹配的索引名字 
  index_number Integer := 0;    -- 可以匹配上的索引数量 
  
begin

 -- 如果没有传入用户名，以当前登录用户作为用户名 
 If ( i_owner is null ) then
    SELECT SYS_CONTEXT ('USERENV', 'CURRENT_USER') CURRENT_USER
      INTO FK_owner
      FROM DUAL;
 else
     FK_owner := i_owner;
 end if; 
 
 -- 检查这个约束是不是存在，是不是外键
 Select owner, table_name  
   into Fk_owner, Fk_table_name
   from all_constraints
   where constraint_type='R' 
     and constraint_name= i_constraint_name
     and owner = FK_owner;

  -- 如果没有找到这条 外键约束，报异常 
  If (SQL%NOTFOUND) 
  Then
    RAISE_APPLICATION_ERROR(-20001, 'Not Found this constraint');
  end if;
  
  -- 找到了这条外键约束，直接和索引进行匹配
   
-- 首先确定Constraint和Index都是作用在一张表上的 
-- Constraint Table Name = Index Table Name
-- Constraint Table Owner = Index Table Owner 

-- Constraint Owner = 传入值
-- Index Owner :　可以任意（这里就不检查Constraint Owner是否可以读写了） 

-- 在Constraint Column中有的字段、顺序要一样 
SELECT count( distinct index_name )
  Into index_number
  FROM all_cons_columns acc, all_ind_columns aic
 WHERE 
   acc.owner = aic.table_owner
   and acc.table_name = aic.table_name
   AND acc.column_name = aic.column_name
   AND acc.POSITION = aic.column_position
   AND acc.owner = fk_owner
   and acc.constraint_name = i_constraint_name;

Case index_number
When 0 then 
  index_name := '';
When 1 then 
  SELECT distinct index_name
    Into index_name
    FROM all_cons_columns acc, all_ind_columns aic
   WHERE 
     acc.owner = aic.table_owner
     and acc.table_name = aic.table_name
     AND acc.column_name = aic.column_name
     AND acc.POSITION = aic.column_position
     AND acc.owner = fk_owner
     and acc.constraint_name = i_constraint_name;
Else 
     index_name := '有多项' || to_char(index_number) ;
End Case; 
     
return (index_name);
End;