Блог

Я пытаюсь решить следующую проблему (с базой данных Oracle 11g): мне нужно получить все столбцы таблицы, используя ALL_CONS_COLUMNS таблицу и информацию о ссылочных ограничениях (имя таблицы и столбца, на которые ссылается этот столбец), например:

  No. Column        Details 
 --- -----------   ------------------------------------------------------
   1 foo_id        Type   : NUMBER(9)
                   Constr : "foo_fk" References foo_table(id)

   2 bar_id        Type   : NUMBER(9)
                   Constr : "bar_fk"  References bar_table(id)

   2 date_created  Type   : Date
  

Информация об ограничениях таблицы сохраняется ALL_CONSTRAINTS , подробное описание ограничения (имя таблицы и столбца) сохраняется ALL_CONS_COLUMNS .
На данный момент у меня есть этот код:

 SELECT
  ROWNUM as "No.",
  t.COLUMN_NAME as "Имя столбца",
  'Type:   ' || t.DATA_TYPE || (SELECT CASE
          WHEN ac.CONSTRAINT_TYPE = 'R'
            THEN chr(10) || 'Constr: ' || allcc.CONSTRAINT_NAME ||
            ' References ' || (SELECT TABLE_NAME FROM ALL_CONS_COLUMNS WHERE CONSTRAINT_NAME = ac.R_CONSTRAINT_NAME) ||
            '(' || (SELECT COLUMN_NAME FROM ALL_CONS_COLUMNS WHERE CONSTRAINT_NAME = ac.R_CONSTRAINT_NAME) || ')'
          ELSE ''
          END AS Contype FROM DUAL) As "Атрибуты",
  ac.CONSTRAINT_TYPE

FROM
  ALL_CONS_COLUMNS allcc

INNER JOIN
  ALL_TAB_COLUMNS t
  ON t.TABLE_NAME = allcc.TABLE_NAME
  AND t.COLUMN_NAME = allcc.COLUMN_NAME

INNER JOIN
  (SELECT (CASE WHEN CONSTRAINT_TYPE = 'R' THEN 'R' ELSE NULL END) as CONSTRAINT_TYPE, CONSTRAINT_NAME, R_CONSTRAINT_NAME FROM ALL_CONSTRAINTS) ac
  ON ac.CONSTRAINT_NAME = allcc.CONSTRAINT_NAME

WHERE
  t.TABLE_NAME = 'SUPPLIES';
  

Но если столбец имеет более 1 ограничения, он будет дублироваться в выходных данных для каждого ограничения:

 No. Column              Details:
1   ID_SUPPLIER         Type: NUMBER    
2   ID_SHOP             Type: NUMBER    
3   DATE_SUPPLY         Type: DATE  
4   ID                  Type: NUMBER    
5   ID_SHOP             Type: NUMBER
                        Constr: FK_SUPPLIES_SHOP References SHOPS(ID)
6   ID_SUPPLIER         Type: NUMBER
                        Constr: FK_SUPPLIES_SUPPLIER References SUPPLIERS(ID)
  

В этом примере ID_SUPPLIER дважды дублируется для 2 ограничений (ссылочных, а не нулевых).
Можно ли избежать дублирования и решить эту проблему без использования PL SQL?

Вы можете использовать оконные функции, чтобы избавиться от повторяющихся строк; здесь я использую ROW_NUMBER() :

 select rownum as no,
       v1.*
  from (SELECT t.COLUMN_NAME,
               'Type:   ' || t.DATA_TYPE ||
               (SELECT CASE
                         WHEN ac.CONSTRAINT_TYPE = 'R' THEN
                          chr(10) || 'Constr: ' || allcc.CONSTRAINT_NAME || ' References ' ||
                          (SELECT TABLE_NAME
                             FROM ALL_CONS_COLUMNS
                            WHERE CONSTRAINT_NAME = ac.R_CONSTRAINT_NAME) || '(' ||
                          (SELECT COLUMN_NAME
                             FROM ALL_CONS_COLUMNS
                            WHERE CONSTRAINT_NAME = ac.R_CONSTRAINT_NAME) || ')'
                         ELSE
                          ''
                       END AS Contype
                  FROM DUAL) As description,
               ac.CONSTRAINT_TYPE,
               row_number() over(partition by allcc.column_name order by(case constraint_type
                 when 'R' then
                  1
                 else
                  2
               end)) as rn
          FROM ALL_CONS_COLUMNS allcc
         INNER JOIN ALL_TAB_COLUMNS t
            ON t.TABLE_NAME = allcc.TABLE_NAME
           AND t.COLUMN_NAME = allcc.COLUMN_NAME
         INNER JOIN (SELECT (CASE
                             WHEN CONSTRAINT_TYPE = 'R' THEN
                              'R'
                             ELSE
                              NULL
                           END) as CONSTRAINT_TYPE,
                           CONSTRAINT_NAME,
                           R_CONSTRAINT_NAME
                      FROM ALL_CONSTRAINTS) ac
            ON ac.CONSTRAINT_NAME = allcc.CONSTRAINT_NAME
         WHERE t.TABLE_NAME = 'SUPPLIES') v1
 where rn = 1;
  

Это

• присваивает rn = 1 каждому ссылочному ограничению
• присваивает rn = 1 другим типам ограничений, если для того же столбца нет ссылочного ограничения (если только для столбца не существует другого не ссылочного ограничения; в этом случае только один из них получает rn = 1)
• присваивает rn> 1 другим типам ограничений, если присутствует ссылочное ограничение
• удаляет все строки, rn которых> 1