oracle中擷取表的指定類型的欄位

--擷取表格主鍵和類型
SELECT acc.column_name,atc.Data_Type
FROM ALL_CONSTRAINTS ac,ALL_CONS_COLUMNS acc,ALL_TAB_COLUMNS atc
WHERE ac.TABLE_NAME = UPPER('表名')
and ac.constraint_type = 'P'
and ac.constraint_name = acc.constraint_name
and ac.TABLE_NAME = atc.TABLE_NAME
and acc.column_name = atc.column_name

SELECT acc.column_name
FROM ALL_CONSTRAINTS ac,ALL_CONS_COLUMNS acc
WHERE ac.TABLE_NAME = UPPER('表名')
and ac.constraint_type = 'P'
and ac.constraint_name = acc.constraint_name

--擷取表的指定類型的欄位
SELECT    Column_Name as cloumn
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = UPPER('表名')
and Data_Type='VARCHAR2';

--擷取主鍵中包含日期類型欄位的表，也可以指定為NUMBER,VARCHAR2

SELECT acc.column_name,atc.Data_Type,atc.TABLE_NAME
FROM ALL_CONSTRAINTS ac,ALL_CONS_COLUMNS acc,ALL_TAB_COLUMNS atc
WHERE  ac.constraint_type = 'P'
and ac.constraint_name = acc.constraint_name
and ac.TABLE_NAME = atc.TABLE_NAME
and acc.column_name = atc.column_name
and atc.DATA_TYPE='DATE';