Oracle comment添加注释
Oracle 的 COMMENT 语句可以给一个列、表、视图或快照添加一个最多 2K 字节的注释。注释被存储在数据字典中，并且可以通过数据字典视图 DBA_COL_COMMENTS （列的注释）和 DBA_TAB_COMMENTS （表的注释）查看 COMMENTS 列。 COMMENT 语句的语法：
COMMENT ON TABLE tb | COLUMN tb.cols IS 'text';
其中， tb 是表的名字， cols 是表中列的名字， text 是注释的文本。可以用设置注释为空串（ '' ）的办法从数据库中删除一个注释，例如：
COMMENT ON TABLE employees IS ''; COMMENT ON TABLE HR.employees IS 'Employee Information'; COMMENT ON COLUMN HR.employees.FIRST_NAME IS 'Employee FIRST_NAME';
对于 SYS 用户下的表，只有拥有 ALTER 权限的普通用户才可以对其进行 COMMENT 操作。对于普通用户下的表，拥有“ COMMENT ANY TABLE ”或 ALTER 权限的普通用户都可以执行 COMMENT 操作。示例如下：
SYS@test18c> create table t as select * from dual; Table created. SYS@test18c> create user lhr33 identified by lhr; User created. SYS@test18c> grant create session to lhr33; Grant succeeded. SYS@test18c> GRANT SELECT ON sys.t to lhr33; Grant succeeded. SYS@test18c> conn lhr33/lhr Connected. LHR33@test18c> LHR33@test18c> select * from sys.t; DU -- X LHR33@test18c> comment on column sys.t.dummy is 'aa'; comment on column sys.t.dummy is 'aa' * ERROR at line 1: ORA-01031: insufficient privileges LHR33@test18c> comment on table sys.t is 'aa'; comment on table sys.t is 'aa' * ERROR at line 1: ORA-01031: insufficient privileges LHR33@test18c> conn / as sysdba Connected. SYS@test18c> grant alter on sys.t to lhr33; Grant succeeded. SYS@test18c> conn lhr33/lhr Connected. LHR33@test18c> LHR33@test18c> comment on table sys.t is 'aa'; Comment created. LHR33@test18c> comment on column sys.t.dummy is 'aa'; Comment created. LHR33@test18c> conn / as sysdba Connected. SYS@test18c> revoke alter on sys.t from lhr33; Revoke succeeded. SYS@test18c> conn lhr33/lhr Connected. LHR33@test18c> comment on column sys.t.dummy is 'aa'; comment on column sys.t.dummy is 'aa' * ERROR at line 1: ORA-01031: insufficient privileges LHR33@test18c> conn / as sysdba Connected. SYS@test18c> grant COMMENT ANY TABLE to lhr33; Grant succeeded. SYS@test18c> conn lhr33/lhr Connected. LHR33@test18c> comment on column sys.t.dummy is 'aa'; comment on column sys.t.dummy is 'aa' * ERROR at line 1: ORA-01031: insufficient privileges LHR33@test18c> comment on table sys.t is 'aa'; comment on table sys.t is 'aa' * ERROR at line 1: ORA-01031: insufficient privileges LHR33@test18c> conn / as sysdba Connected. SYS@test18c> grant alter on sys.t to lhr33; Grant succeeded. SYS@test18c> conn lhr33/lhr Connected. LHR33@test18c> comment on table sys.t is 'aa'; Comment created.
对于普通用户下的表，拥有 “ COMMENT ANY TABLE ”或 ALTER 权限的用户都可以执行 COMMENT 操作：
LHR33@test18c> conn lhr/lhr Connected. LHR@test18c> create table bb as select * from dual; Table created. LHR@test18c> conn lhr33/lhr Connected. LHR33@test18c> comment on table lhr.bb is 'bb'; Comment created. LHR33@test18c> select * from lhr.bb; select * from lhr.bb * ERROR at line 1: ORA-01031: insufficient privileges LHR33@test18c> conn / as sysdba Connected. SYS@test18c> revoke COMMENT ANY TABLE from lhr33; Revoke succeeded. SYS@test18c> conn lhr33/lhr Connected. LHR33@test18c> comment on table lhr.bb is 'bb'; comment on table lhr.bb is 'bb' * ERROR at line 1: ORA-00942: table or view does not exist LHR33@test18c> conn / as sysdba Connected. SYS@test18c> grant select on lhr.bb to lhr33; Grant succeeded. SYS@test18c> conn lhr33/lhr Connected. LHR33@test18c> comment on table lhr.bb is 'bb'; comment on table lhr.bb is 'bb' * ERROR at line 1: ORA-01031: insufficient privileges LHR33@test18c> conn lhr/lhr Connected. LHR@test18c> grant alter on lhr.bb to lhr33; Grant succeeded. LHR@test18c> conn lhr33/lhr Connected. LHR33@test18c> comment on table lhr.bb is 'bb'; Comment created.
视图 DBA_COL_COMMENTS 和 DBA_TAB_COMMENTS 在做开发时非常实用，举例如下：
create table SCOTT.G_PROD_USER_CONF
(
func_type VARCHAR2(20) not null,
func_sub_type VARCHAR2(20) not null,
userid VARCHAR2(20) not null,
username VARCHAR2(50) not null,
sendtype VARCHAR2(20) not null,
email_address VARCHAR2(500)
);
-- Add comments to the table
comment on table SCOTT.G_PROD_USER_CONF is '系统功能人员配置';
-- Add comments to the columns
comment on column SCOTT.G_PROD_USER_CONF.func_type is '功能类型 ';
comment on column SCOTT.G_PROD_USER_CONF.func_sub_type is '功能子类型 1=收件人 2=抄送人 3=密送人 4=发件人';
comment on column SCOTT.G_PROD_USER_CONF.userid is '员工工号';
comment on column SCOTT.G_PROD_USER_CONF.username is '员工姓名';
comment on column SCOTT.G_PROD_USER_CONF.sendtype is '发送类型1：短信2：邮件3：通知公告';
comment on column SCOTT.G_PROD_USER_CONF.email_address is '电子邮箱';
-- Grant/Revoke object privileges
grant select, insert, update, delete, references, alter, index on SCOTT.G_PROD_USER_CONF to PUBLIC;
通过视图可以查询出一些有用的SQL语句：
SELECT * FROM DBA_TAB_COMMENTS D WHERE D.TABLE_NAME = 'G_PROD_USER_CONF';
SELECT 'A.' || D.COLUMN_NAME || ',',
'--' || D.COMMENTS,
'P_' || DTC.COLUMN_NAME || ' ' || DTC.DATA_TYPE || ',',
'P_' || DTC.COLUMN_NAME || ' ' || DTC.DATA_TYPE || ',' || ' --' ||
D.COMMENTS 入参,
'A.' || D.COLUMN_NAME || ' ' || D.COLUMN_NAME || ',' || '--' ||
D.COMMENTS 查询,
'P_' || D.COLUMN_NAME || ',' || '--' || D.COMMENTS 插入,
'A.' || D.COLUMN_NAME || '=' || 'P_' || DTC.COLUMN_NAME || ', --' ||
D.COMMENTS 更新,
D.COLUMN_NAME || ', --' || D.COMMENTS,
D.COMMENTS,
DECODE(DTC.DATA_TYPE, 'DATE', 'DATE', '') DATA_TYPE,
'--' || D.COMMENTS || CHR(10) ||
' v_sql := v_sql || fun_sqlparam(p_' || D.COLUMN_NAME ||
', '' and A.' || D.COLUMN_NAME || (CASE
WHEN DTC.DATA_TYPE = 'VARCHAR2' THEN
Q'[ = ''{0}'' '); ]'
ELSE
Q'[ = {0} ');]'
END) WHERE条件
FROM DBA_COL_COMMENTS D, DBA_TAB_COLS DTC
WHERE D.TABLE_NAME = DTC.TABLE_NAME
AND D.COLUMN_NAME = DTC.COLUMN_NAME
AND D.OWNER = DTC.OWNER
AND D.TABLE_NAME = 'G_PROD_USER_CONF'
AND D.OWNER = 'SCOTT'
ORDER BY DTC.COLUMN_ID;
返回结果，只列举部分：
COMMENT
Purpose
Use the COMMENT statement to add to the data dictionary a comment about a table or table column, view, materialized view, operator, indextype, mining model, or edition.
To drop a comment from the database, set it to the empty string ' '.
See Also:
"Comments" for more information on associating comments with SQL statements and schema objects
Oracle Database Reference for information on the data dictionary views that display comments
Prerequisites
The object about which you are adding a comment must be in your own schema or:
To add a comment to a table, view, or materialized view, you must have COMMENT ANY TABLE system privilege.
To add a comment to an indextype, you must have the CREATE ANY INDEXTYPE system privilege.
To add a comment to an operator, you must have the CREATE ANY OPERATOR system privilege.
To add a comment to an edition, you must have the CREATE ANY EDITION system privilege, granted either directly or through a role.
Syntax
comment ::=
Semantics
COLUMN Clause
Specify the name of the column of a table, view, or materialized view to be commented. If you omit schema , then Oracle Database assumes the table, view, or materialized view is in your own schema.
You can view the comments on a particular table or column by querying the data dictionary views USER_TAB_COMMENTS , DBA_TAB_COMMENTS , or ALL_TAB_COMMENTS or USER_COL_COMMENTS , DBA_COL_COMMENTS , or ALL_COL_COMMENTS .
EDITION Clause
Specify the name of an existing edition to be commented.
You can query the data dictionary view ALL_EDITION_COMMENTS to view comments associated with editions that are accessible to the current user. You can query DBA_EDITION_COMMENTS to view comments associated with all editions in the database.
TABLE Clause
Specify the schema and name of the table or materialized view to be commented. If you omit schema , then Oracle Database assumes the table or materialized view is in your own schema.
Note:
In earlier releases, you could use this clause to create a comment on a materialized view. You should now use the COMMENT ON MATERIALIZED VIEW clause for materialized views.
INDEXTYPE Clause
Specify the name of the indextype to be commented. If you omit schema , then Oracle Database assumes the indextype is in your own schema.
You can view the comments on a particular indextype by querying the data dictionary views USER_INDEXTYPE_COMMENTS , DBA_INDEXTYPE_COMMENTS , or ALL_INDEXTYPE_COMMENTS .
MATERIALIZED VIEW Clause
Specify the name of the materialized view to be commented. If you omit schema , then Oracle Database assumes the materialized view is in your own schema.
You can view the comments on a particular materialized view by querying the data dictionary views USER_MVIEW_COMMENTS , DBA_MVIEW_COMMENTS , or ALL_MVIEW_COMMENTS .
MINING MODEL
Specify the name of the mining model to be commented. You must have the COMMENT ANY MINING MODEL system privilege to specify this clause.
OPERATOR Clause
Specify the name of the operator to be commented. If you omit schema , then Oracle Database assumes the operator is in your own schema.
You can view the comments on a particular operator by querying the data dictionary views USER_OPERATOR_COMMENTS , DBA_OPERATOR_COMMENTS , or ALL_OPERATOR_COMMENTS .
IS ' string '
Specify the text of the comment. Refer to "Text Literals" for a syntax description of 'string' .
Example
Creating Comments: Example To insert an explanatory remark on the job_id column of the employees table, you might issue the following statement:
COMMENT ON COLUMN employees.job_id IS 'abbreviated job title';
To drop this comment from the database, issue the following statement:
COMMENT ON COLUMN employees.job_id IS ' ';