ORACLE和MYSQL查询表字段信息的sql

ORACLE:

SELECT
t1.COLUMN_NAME,
t1.DATA_TYPE,
t1.COMMENTS,
t1.NULLABLE,
(
CASE t2.CONSTRAINT_TYPE
WHEN 'P' THEN
'Y'
END
) AS "PRIMARY_KEY"
FROM
(
SELECT
t1.COLUMN_NAME,
(
CASE t1.DATA_TYPE
WHEN 'NUMBER' THEN
CONCAT (
CONCAT (
CONCAT (t1.DATA_TYPE, '('),
t1.DATA_PRECISION
),
')'
)
WHEN 'VARCHAR2' THEN
CONCAT (
CONCAT (
CONCAT (t1.DATA_TYPE, '('),
t1.DATA_LENGTH
),
')'
)
ELSE
t1.DATA_TYPE
END
) DATA_TYPE,
t1.NULLABLE,
t2.COMMENTS
FROM
user_tab_columns t1,
user_col_comments t2
WHERE
t1.TABLE_NAME = T2.TABLE_NAME
AND t1.COLUMN_NAME = t2.COLUMN_NAME
AND t1.TABLE_NAME = 'OPER'
ORDER BY
COLUMN_ID
) t1
LEFT JOIN (
SELECT
t1.column_name,
t2.CONSTRAINT_TYPE
FROM
user_cons_columns t1,
user_constraints t2
WHERE
t1.constraint_name = t2.constraint_name
AND t1.TABLE_NAME = 'OPER'
AND t2.CONSTRAINT_TYPE = 'P'
) t2 ON t1.column_name = t2.column_name

MYSQL:

SELECT
COLUMN_NAME,
COLUMN_TYPE AS "DATA_TYPE",
COLUMN_COMMENT AS "COMMENTS",
(
CASE IS_NULLABLE
WHEN "NO" THEN
"N"
WHEN "YES" THEN
"Y"
END
) AS "NULLABLE",
(
CASE COLUMN_KEY
WHEN "PRI" THEN
"Y"
END
) AS "PRIMARY_KEY"
FROM
INFORMATION_SCHEMA. COLUMNS
WHERE
TABLE_SCHEMA = "12306_ol_portal"
AND TABLE_NAME = 'dept'