oracle 資料庫設定表和欄位的別名

select * from all_tab_cols

-- 設定表別名
COMMENT ON TABLE EMPLOYEE is '僱員';
COMMENT ON TABLE PRODUCT is '產品';
COMMENT ON TABLE PRODUCT_CLASS is '產品分類';
COMMENT ON TABLE STORE is '商品';
COMMENT ON TABLE TIME_BY_DAY is '時間轉換';
--設定欄位別名
COMMENT ON COLUMN PRODUCT.PRODUCT_CLASS_ID IS '產品分類程式碼';
COMMENT ON COLUMN PRODUCT.PRODUCT_ID IS '產品程式碼';
COMMENT ON COLUMN PRODUCT.BRAND_NAME IS '產品名稱';

--取得表別名
SELECT
t1.table_name as 表名
,t2.comments as 表別名
FROM all_all_tables t1
LEFT JOIN all_tab_comments t2
ON t1.table_name=t2.table_name
WHERE t1.table_name='EMPLOYEE'

--取得欄位別名
SELECT
t1.table_name as 表名
,t3.comments as 表別名
,t1.column_name as 欄位名
,t2.comments as 欄位別名
FROM all_tab_cols t1
LEFT JOIN all_tab_comments t3
ON t1.table_name=t3.table_name
LEFT JOIN all_col_comments t2
ON t1.table_name=t2.table_name
AND t1.column_name=t2.column_name
WHERE t1.table_name='PRODUCT'