oracle comment on的用法

朱雀 2022-08-27 12:51 30阅读 0赞

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://www.2cto.com/database/201109/106249.html