想創建了幾個View供日後查詢使用,但是SQL語法中沒辦法放入註解,管理上如果創建文件又很容易遺忘維護。
查詢相關文章後,可以再Table、View、Column上面撰寫註解,日後查詢用途時較方便!
簡易語法:(詳細請參考下方的網站)
comment on table [Schema.TableName] is '可輸入中文'
select * from USER_tab_comments where comments is not null
select * from USER_tab_comments where (comments like '%%' and table_name like '%%') and comments is not null order by table_type,table_name
備註:(不太熟,遇到的小問題標註,避免自己忘記...)
註解部份好像是跟登入的【UserID】走,如果我登入 A 帳號幫 B Schema 使用上述語法撰寫註解說明,使用下述語法後,發現無法查到該註解,需要改登入B帳號才可正常查詢。
select * from USER_tab_comments >> 只會顯示自己Table的註解。
自己的習慣,於分號前加入注解,就可以在這邊輸入說明文字,然後查看VIEW的SQL語法時也會一併看到。
CREATE OR REPLACE FORCE VIEW "A"."VIEW" ("")
AS
SELECT * FROM DUAL
/*
說明
*/ ;
引用文章:http://blog.csdn.net/bobo12082119/article/details/6239727
Oracle中用comment on命令给表或字段加以说明,语法如下:
COMMENT ON
{ TABLE [ schema. ]
{ table | view }
| COLUMN [ schema. ]
{ table. | view. | materialized_view. } column
| OPERATOR [ schema. ] operator
| INDEXTYPE [ schema. ] indextype
| MATERIALIZED VIEW materialized_view
}
IS 'text' ;
用法如下:
1.对表的说明
comment on table table_name is 'comments_on_tab_information';
2.对表中列的说明
comment on column table.column_name is 'comments_on_col_information';
3.查看表的说明
SQL> select * from user_tab_comments where TABLE_NAME='EMPLOYEES';
TABLE_NAME TABLE_TYPE COMMENTS
------------------------------ ----------- ----------
EMPLOYEES TABLE 员工表
SQL> select * from user_tab_comments where comments is not null;
TABLE_NAME TABLE_TYPE COMMENTS
------------------------------ ----------- --------------------------
EMPLOYEES TABLE 员工表
4.查看表中列的说明
SQL> select * from user_col_comments where TABLE_NAME='EMPLOYEES';
TABLE_NAME COLUMN_NAME COMMENTS
------------------------------ ------------------------------ ------------
EMPLOYEES EMPLOYEE_ID
EMPLOYEES MANAGER_ID
EMPLOYEES FIRST_NAME
EMPLOYEES LAST_NAME
EMPLOYEES TITLE
EMPLOYEES SALARY 员工薪水
SQL> select * from user_col_comments where comments is not null;
TABLE_NAME COLUMN_NAME COMMENTS
------------------------------ ------------------------------ -------------
EMPLOYEES SALARY 员工薪水
5.我们也可以从下面这些视图中查看表级和列级说明:
ALL_COL_COMMENTS
USER_COL_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS
6.删除表级说明,也就是将其置为空
SQL> comment on table employees is '';
Comment added
SQL> select * from user_tab_comments where TABLE_NAME='EMPLOYEES';
TABLE_NAME TABLE_TYPE COMMENTS
------------------------------ ----------- -------------
EMPLOYEES TABLE
7.删除列级说明,也是将其置为空
SQL> comment on column employees.salary is '';
Comment added
SQL> select * from user_col_comments where TABLE_NAME='EMPLOYEES';
TABLE_NAME COLUMN_NAME COMMENTS
------------------------------ ------------------------------ -------------
EMPLOYEES EMPLOYEE_ID
EMPLOYEES MANAGER_ID
EMPLOYEES FIRST_NAME
EMPLOYEES LAST_NAME
EMPLOYEES TITLE
EMPLOYEES SALARY
其他相關文章:
昭佑.天翔:http://tomkuo139.blogspot.tw/2010/02/oracle-database-table-view-column.html
留言列表