Oracle查詢數據表結構(欄位,類型,大小,備註)

作用:想要生成整個Oracle資料庫所有表結構WORD文檔(資料庫設計說明書)

Oracle資料庫字典介紹
    Oracle數據字典是有表和視圖組成的，存儲有關資料庫結構信息的一些資料庫對象。資料庫字典描述了實際數據是如何組織的。對它們可以象處理其他資料庫表或視圖一樣進行查詢，但不能進行任何修改。
    Oracle資料庫字典通常是在創建和安裝資料庫時被創建的，Oracle數據字典是Oracle資料庫系統工作的基礎，沒有數據字典的支持，Oracle資料庫系統就不能進行任何工作。
    在Oracle資料庫字典中，許多視圖都有三個不同的實例，它們的首碼分別為"USER_"、"ALL_"及"DBA_"。"USER_"為首碼的資料庫字典視圖通常記錄執行查詢的帳戶所擁有的對象的信息，"ALL_"為首碼的資料庫字典視圖通常記錄包括執行查詢的帳戶所擁有的對象的信息及授權至PUBLIC的帳戶用戶所擁有的對象的信息，"DBA_"為首碼的資料庫字典視圖則包含所有資料庫對象的信息，而不管其所有者。其他的字典視圖中主要的是V$視圖，之所以這樣叫是因為他們都是以V$或GV$開頭的。V$視圖是基於X$虛擬視圖的。V$視圖是SYS用戶所擁有的，在預設狀況下，只有SYS用戶和擁有DBA系統許可權的用戶可以看到所有的視圖，沒有DBA許可權的用戶可以看到USER_和ALL_視圖，但不能看到DBA_視圖。與DBA_,ALL,和USER_視圖中面向資料庫信息相反，這些視圖可視的給出了面向實例的信息。
    在Oracle的絕大多數數據字典視圖中都有象DBA_TABLES,ALL_TABLES和USER_TABLES這樣的視圖家族。Oracle中有超過100個視圖家族，下表列出了最重要和最常用的視圖家族，需要註意的是每個視圖家族都有一個DBA_,一個ALL_一個USER_視圖。

    在Oracle中還有一些不常用的數據字典表，但這些表不是真正的字典家族，他們都是一些重要的單一的視圖。這些視圖見下表:

Oracle資料庫字典的應用
  藉助Oracle數據字典，利用Oracle的DDL語句，我們可以做很多事情，幾乎所有的Oracle開發輔助工具都是利用這一點進行設計的。作者將通過如何取得資料庫表欄位信息來說明。
首先我們定義一個資料庫表，資料庫表結構如下：

創建表的SQL語句如下
-- 創建數據表
create table TABLE_TEST
(
    NAME varchar2(40) not null,
    SEX varchar2(1) default ''''Y'''' not null,
    BIRTHDAY date not null,
    HEIGHT number(3,2),
    WEIGHT number(3,2),
    MEMO blob
);
-- 給列添加備註
  comment on column TABLE_TEST.NAME is ''''姓名'''';
  comment on column TABLE_TEST.SEX is ''''性別'''';
  comment on column TABLE_TEST.BIRTHDAY is ''''生日'''';
  comment on column TABLE_TEST.HEIGHT is ''''身高'''';
  comment on column TABLE_TEST.WEIGHT is ''''體重'''';
  comment on column TABLE_TEST.MEMO is ''''備註'''';
-- 創建約束關係 主鍵 外鍵 其他
  alter table TABLE_TEST add constraint TB_TEST_P_NAME primary key (NAME);

數據表創建完畢，執行下列SQL語句：
select
    A.column_name 欄位名,A.data_type 數據類型,A.data_length 長度,A.data_precision 整數位,
    A.Data_Scale 小數位,A.nullable 允許空值,A.Data_default 預設值,B.comments 備註
from
    user_tab_columns A,user_col_comments B
where
    A.Table_Name = B.Table_Name
    and A.Column_Name = B.Column_Name
    and A.Table_Name = ''''TABLE_TEST''''

我們可以得出一下結果：

欄位名

數據類型

長度

整數位

小數位

允許空值

預設值

備註

NAME

VARCHAR2

40

N

<Long>

姓名

SEX

VARCHAR2

1

N

<Long>

性別

BIRTHDAY

DATE

7

N

<Long>

生日

HEIGHT

NUMBER

22

3

2

Y

<Long>

身高

WEIGHT

NUMBER

22

3

2

Y

<Long>

體重

MEMO

BLOB

4000

Y

<Long>

備註

這樣，我們在進行程式設計時，通過一條簡單的SQL語句，然好通過Ole調用Word，便可為最終用戶導出完整的資料庫表字典文檔。
再執行下列SQL語句:
select
    INDEX_NAME 索引名,INDEX_TYPE 索引類型,UNIQUENESS 索引類別
from
    user_indexes
where
    TABLE_NAME = ''''TABLE_TEST''''

得到結果如下(註：SYS_IL0000031226C00006$$索引為系統在創建資料庫表時自動創建的，用於資料庫表內容的維護):

索引名

索引類型

索引類別

1

SYS_IL0000031226C00006$$

LOB

UNIQUE

2

TB_TEST_P_NAME

NORMAL

UNIQUE

執行下列SQL語句，我們將得到更多的關於資料庫表結構的信息：
select
    A.column_name 欄位名,A.data_type 數據類型,A.data_length 長度,A.data_precision 整數位,
    A.Data_Scale 小數位,A.nullable 允許空值,A.Data_default 預設值,B.comments 備註,
    C.IndexCount 索引次數
from
    user_tab_columns A,
    user_col_comments B,
    (select count(*) IndexCount,Column_Name from User_Ind_Columns where Table_Name = ''''TABLE_TEST'''' group by Column_Name) C
where
    A.Table_Name = B.Table_Name
    and A.Column_Name = B.Column_Name
    and A.Column_Name = C.Column_Name(+)
    and A.Table_Name = ''''TABLE_TEST''''

關註流行國外網站

facebook:http://www.fb-on.com

facebook官網:http://www.facebookzh.com

facebook:http://www.cn-face-book.com

youtube:http://www.youtubezh.com

twitter:http://www.twitterzh.com

得到結果如下:

欄位名

數據類型

長度

整數位

小數位

允許空值

預設值

備註

索引次數

BIRTHDAY

DATE

7

N

<Long>

生日

HEIGHT

NUMBER

22

3

2

Y

<Long>

身高

MEMO

BLOB

4000

Y

<Long>

備註

NAME

VARCHAR2

40

N

<Long>

姓名

1

SEX

VARCHAR2

1

N

<Long>

性別

WEIGHT

NUMBER

22

3

2

Y

<Long>

體重

    當然Oracle數據字典的應用遠不止這些，通過Oracle資料庫字典的支持，我們可以得到Oracle資料庫結構的所有信息，著名的資料庫開發工具PL/SQL Developer完全就是基於Oracle的資料庫字典實現的。