對于兩個給定的表 'foo' 和 'bar' 有沒有辦法撰寫一個 SQL 查詢來回傳它們之間的路徑連接？
[ foo ] ---> [ n'tables ] ---> [ bar ]
我還想檢查是否有一種方法可以知道是否有有效的連接路徑可以在不使用 ERD 的情況下連接資料庫中的兩個不同表。
uj5u.com熱心網友回復：
WITH FIRST_LEVEL_PK_FK_TITLE AS (
SELECT PK.TABLE_NAME TABLE_NAME_1
FROM USER_CONSTRAINTS FK
JOIN USER_CONSTRAINTS PK
ON PK.CONSTRAINT_NAME = FK.R_CONSTRAINT_NAME AND PK.OWNER = FK.OWNER
WHERE PK.TABLE_NAME in ( 'TABLE_NAME' ) OR FK.TABLE_NAME in ( 'TABLE_NAME')
UNION
SELECT FK.TABLE_NAME TABLE_NAME_1
FROM USER_CONSTRAINTS FK
JOIN USER_CONSTRAINTS PK
ON PK.CONSTRAINT_NAME = FK.R_CONSTRAINT_NAME AND PK.OWNER = FK.OWNER
WHERE PK.TABLE_NAME in ( 'TABLE_NAME') OR FK.TABLE_NAME in ( 'TABLE_NAME')
),
PUR AS (
SELECT CONSTRAINT_NAME,
CONSTRAINT_TYPE,
TABLE_NAME,
R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE IN ('P', 'U', 'R')
),
RELATIONS AS (
SELECT A.TABLE_NAME TABLE_NAME_REL,
B.TABLE_NAME R_TABLE_NAME,
B.CONSTRAINT_TYPE R_CONSTRAINT_TYPE
FROM PUR A JOIN PUR B ON (A.R_CONSTRAINT_NAME) = ((B.CONSTRAINT_NAME))
)
, WITH_PARENTS AS (
SELECT * FROM RELATIONS
UNION
SELECT R_TABLE_NAME, NULL, NULL
FROM RELATIONS
WHERE (R_TABLE_NAME) NOT IN (SELECT TABLE_NAME_REL
FROM RELATIONS
WHERE (TABLE_NAME_REL) != ((R_TABLE_NAME)))
),
WITH_ROOT_LEVEL AS (
SELECT DISTINCT ROOT, TABLE_NAME_REL, R_TABLE_NAME
FROM
(SELECT CONNECT_BY_ROOT(TABLE_NAME_REL) AS ROOT, TABLE_NAME_REL, R_TABLE_NAME
FROM WITH_PARENTS
START WITH R_TABLE_NAME IS NULL
CONNECT BY NOCYCLE ( R_TABLE_NAME) = ( PRIOR TABLE_NAME_REL)
)
WHERE R_TABLE_NAME IN (SELECT TABLE_NAME_1 FROM FIRST_LEVEL_PK_FK_TITLE)
OR TABLE_NAME_REL IN (SELECT TABLE_NAME_1 FROM FIRST_LEVEL_PK_FK_TITLE)
),
SECOND_LEVEL_PK_FK_TITLE AS (
SELECT DISTINCT ROOT TABLE_NAME_2 FROM WITH_ROOT_LEVEL
UNION
SELECT DISTINCT TABLE_NAME_REL TABLE_NAME_2 FROM WITH_ROOT_LEVEL
UNION
SELECT DISTINCT R_TABLE_NAME TABLE_NAME_2 FROM WITH_ROOT_LEVEL
UNION
SELECT DISTINCT TABLE_NAME_1 TABLE_NAME_2 FROM FIRST_LEVEL_PK_FK_TITLE
),
WITH_ROOT_LEVEL_2 AS (
SELECT DISTINCT ROOT_2,TABLE_NAME_REL_2, R_TABLE_NAME_2
FROM
(SELECT CONNECT_BY_ROOT(TABLE_NAME_REL) AS ROOT_2, TABLE_NAME_REL TABLE_NAME_REL_2,R_TABLE_NAME R_TABLE_NAME_2
FROM WITH_PARENTS
START WITH R_TABLE_NAME IS NULL
CONNECT BY NOCYCLE ( R_TABLE_NAME) = ( PRIOR TABLE_NAME_REL)
)
WHERE R_TABLE_NAME_2 IN (SELECT TABLE_NAME_2 FROM SECOND_LEVEL_PK_FK_TITLE)
OR TABLE_NAME_REL_2 IN (SELECT TABLE_NAME_2 FROM SECOND_LEVEL_PK_FK_TITLE)
),
THIRD_LEVEL_PK_FK_TITLE AS (
SELECT ROW_NUMBER() OVER ( ORDER BY TABLE_NAME_ALL ) TABLE_RN, TABLE_NAME_ALL
FROM (
SELECT DISTINCT ROOT_2 TABLE_NAME_ALL FROM WITH_ROOT_LEVEL_2
UNION
SELECT DISTINCT TABLE_NAME_REL_2 TABLE_NAME_ALL FROM WITH_ROOT_LEVEL_2
UNION
SELECT DISTINCT R_TABLE_NAME_2 TABLE_NAME_ALL FROM WITH_ROOT_LEVEL_2
UNION
SELECT DISTINCT TABLE_NAME_2 TABLE_NAME_ALL FROM SECOND_LEVEL_PK_FK_TITLE
)
-- WHERE TABLE_NAME_ALL IS NOT NULL AND TABLE_NAME_ALL NOT IN ('EOR_CRUISE')
),
COLS_COMMENT as (
SELECT THIRD_LEVEL_PK_FK_TITLE.TABLE_RN,
UTC.TABLE_NAME TABLE_NAME,
UTC.COLUMN_NAME COLUMN_NAME,
CASE WHEN UTC.DATA_TYPE in ('VARCHAR2','CHAR', 'CLOB','NCLOB','LONG', 'NVARCHAR2','BLOB' )
THEN 'string'
WHEN UTC.DATA_TYPE IN ('DATE','TIMESTAMP(6)','TIMESTAMP(3)')
THEN 'date'
WHEN UTC.DATA_TYPE = 'NUMBER' AND UTC.DATA_PRECISION = 28 AND UTC.DATA_SCALE = 0
THEN 'integer'
WHEN UTC.DATA_TYPE = 'NUMBER' AND UTC.DATA_SCALE = 0
THEN 'number'
WHEN UTC.DATA_TYPE = 'NUMBER' AND (UTC.DATA_SCALE > 0 OR UTC.DATA_SCALE IS NULL)
THEN 'number'
END COLUMN_TYPE,
UTC.NULLABLE NULLABLE,
COM.COMMENTS TAB_COMMENTS,
CCOM.COMMENTS COL_COMMENTS
FROM USER_TAB_COLUMNS UTC
INNER JOIN USER_TAB_COMMENTS COM ON COM.TABLE_NAME = UTC.TABLE_NAME
INNER JOIN USER_COL_COMMENTS CCOM ON CCOM.TABLE_NAME = UTC.TABLE_NAME
INNER JOIN THIRD_LEVEL_PK_FK_TITLE ON UTC.TABLE_NAME = THIRD_LEVEL_PK_FK_TITLE.TABLE_NAME_ALL
AND CCOM.COLUMN_NAME = UTC.COLUMN_NAME
ORDER BY TABLE_NAME, COLUMN_ID
),
REF_TABLE_NUMBER AS
(SELECT PK.TABLE_NAME PK_TABLE_NAME, UCC.COLUMN_NAME REF_COLUMN_NAME, FK.TABLE_NAME FK_TABLE_NAME, THIRD_LEVEL_PK_FK_TITLE.TABLE_RN REF_TABLE_RN
FROM USER_CONSTRAINTS FK
JOIN USER_CONSTRAINTS PK ON PK.CONSTRAINT_NAME = FK.R_CONSTRAINT_NAME AND PK.OWNER = FK.OWNER
JOIN USER_CONS_COLUMNS UCC ON UCC.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
JOIN THIRD_LEVEL_PK_FK_TITLE ON THIRD_LEVEL_PK_FK_TITLE.TABLE_NAME_ALL = PK.TABLE_NAME
WHERE FK.CONSTRAINT_TYPE = 'R'
),
TABLE_NAME_REFERENCE AS (
SELECT FK.TABLE_NAME FK_TABLE_NAME_REF, 'reference' TABLE_TYPE
FROM USER_CONSTRAINTS FK
JOIN USER_CONSTRAINTS PK ON PK.CONSTRAINT_NAME = FK.R_CONSTRAINT_NAME AND PK.OWNER = FK.OWNER
AND FK.CONSTRAINT_TYPE='R'
AND FK.TABLE_NAME <> PK.TABLE_NAME
GROUP BY FK.TABLE_NAME
)
,
THIRD_LEVEL_PK_FK_TITLE_MASTER AS (
SELECT ROW_NUMBER() OVER ( ORDER BY TABLE_NAME_ALL ) TABLE_RN, TABLE_NAME_ALL
FROM (
SELECT DISTINCT ROOT_2 TABLE_NAME_ALL FROM WITH_ROOT_LEVEL_2
UNION
SELECT DISTINCT TABLE_NAME_REL_2 TABLE_NAME_ALL FROM WITH_ROOT_LEVEL_2
UNION
SELECT DISTINCT R_TABLE_NAME_2 TABLE_NAME_ALL FROM WITH_ROOT_LEVEL_2
UNION
SELECT DISTINCT TABLE_NAME_2 TABLE_NAME_ALL FROM SECOND_LEVEL_PK_FK_TITLE
)
WHERE TABLE_NAME_ALL IS NOT NULL AND TABLE_NAME_ALL NOT IN ('EOR_CRUISE')
)
SELECT TABLE_RN TABLE_ID,
TABLE_NAME,
TAB_COMMENTS TABLE_COMMENT,
CASE WHEN TABLE_NAME_REFERENCE.TABLE_TYPE IS NULL
THEN 'master'
ELSE TABLE_NAME_REFERENCE.TABLE_TYPE
END TABLE_TYPE,
COLUMN_NAME,
COL_COMMENTS COLUMN_COMMENT,
CASE WHEN REF_TAB.REF_TABLE_RN IS NOT NULL
THEN 'ref'
ELSE COLUMN_TYPE
END COLUMN_TYPE,
REF_TAB.REF_TABLE_RN COLUMN_REF,
NULLABLE COLUMN_NULLABLE
FROM COLS_COMMENT
LEFT JOIN REF_TABLE_NUMBER REF_TAB ON REF_TAB.FK_TABLE_NAME=TABLE_NAME
AND REF_TAB.REF_COLUMN_NAME = COLUMN_NAME
LEFT JOIN TABLE_NAME_REFERENCE ON TABLE_NAME_REFERENCE.FK_TABLE_NAME_REF=TABLE_NAME
WHERE NVL(COL_COMMENTS, 'NOT_USED') <>'NOT_USED'
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/414341.html
標籤：
上一篇：在OracleSQL中，如何查詢以提取最新日期值而不會丟失連接表中的值？
下一篇：日期函式截斷（SysDate）