데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

테이블간 relational 관계를 SQL로 추출이 가능할까요?

작성자

빵빵이

작성일

2024-03-07 21:09

조회수

168

WITH t1 AS

(

SELECT owner, constraint_name, table_name

     , LISTAGG(column_name, ',') WITHIN GROUP(ORDER BY position) cols

  FROM user_cons_columns

 GROUP BY owner, constraint_name, table_name

)

, t2 AS

(

SELECT a.owner

     , a.constraint_name

     , a.constraint_type

     , a.table_name

     , b.cols

     , a.r_owner

     , a.r_constraint_name

     , c.table_name r_table_name

     , c.cols r_cols

  FROM user_constraints a

     , t1 b

     , t1 c

 WHERE a.constraint_type   = 'R'

   AND a.owner             = b.owner

   AND a.constraint_name   = b.constraint_name

   AND a.r_owner           = c.owner

   AND a.r_constraint_name = c.constraint_name

)

SELECT REGEXP_SUBSTR(x, '[^-]+', 1, 1) r1

     , REGEXP_SUBSTR(x, '[^-]+', 1, 2) r2

     , REGEXP_SUBSTR(x, '[^-]+', 1, 3) r3

     , REGEXP_SUBSTR(x, '[^-]+', 1, 4) r4

  FROM (SELECT SYS_CONNECT_BY_PATH( table_name||'('||  cols||')'||'>'||

                                  r_table_name||'('||r_cols||')', '-') x

          FROM t2

         CONNECT BY PRIOR r_owner = owner

                AND PRIOR r_table_name = table_name

        )

;