oracle开发过程中,经常会用到comment on table is 和comment on column is来添加备注,以便与查看表和列的作用。
添加标注备注的方法如下
--添加表备注
COMMENT ON TABLE b_so IS '发货订单';
添加列备注
--添加列注释
COMMENT ON column b_so.c_customerup_id IS '上级经销商';
查询表备注
--查询表备注
SELECT * FROM user_tab_comments WHERE comments IS NOT NULL;
查询效果如下:
--查询列备注
SELECT * FROM user_col_comments WHERE comments IS NOT NULL;
列备注查询结果如下:
oracle数据库中,表备注和列备注对应的视图为user_tab_comments和user_col_comments;详细代码如下:
表备注视图:
create or replace view sys.user_tab_comments as
select o.name,
decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 'UNDEFINED'),
c.comment$
from sys."_CURRENT_EDITION_OBJ" o, sys.com$ c
where o.owner# = userenv('SCHEMAID')
and bitand(o.flags,128) = 0
and (o.type# in (4) /* view */
or
(o.type# = 2 /* tables */
AND /* excluding iot-overflow, nested or mv container tables */
not exists (select null
from sys.tab$ t
where t.obj# = o.obj#
and (bitand(t.property, 512) = 512 or
bitand(t.property, 8192) = 8192 OR
bitand(t.property, 67108864) = 67108864))))
and o.obj# = c.obj#(+)
and c.col#(+) is null;
comment on column sys.USER_TAB_COMMENTS.TABLE_NAME is 'Name of the object';
comment on column sys.USER_TAB_COMMENTS.TABLE_TYPE is 'Type of the object: "TABLE" or "VIEW"';
comment on column sys.USER_TAB_COMMENTS.COMMENTS is 'Comment on the object';
列备注视图:
create or replace view sys.user_col_comments as
select o.name, c.name, co.comment$
from sys."_CURRENT_EDITION_OBJ" o, sys.col$ c, sys.com$ co
where o.owner# = userenv('SCHEMAID')
and o.type# in (2, 4)
and o.obj# = c.obj#
and c.obj# = co.obj#(+)
and c.intcol# = co.col#(+)
and bitand(c.property, 32) = 0 /* not hidden column */;
comment on column sys.USER_COL_COMMENTS.TABLE_NAME is 'Object name';
comment on column sys.USER_COL_COMMENTS.COLUMN_NAME is 'Column name';
comment on column sys.USER_COL_COMMENTS.COMMENTS is 'Comment on the column';
oracle数据库查询备注的常用方法:
**************oracle表备注查询(comment on table)**************
通过表名查询表备注
--通过表名查询表
SELECT *
FROM user_tab_comments
WHERE comments IS NOT NULL
AND table_name = upper('v_fa_customer')
AND rownum = 1;
通过备注查表名
--通过备注查表名
SELECT *
FROM user_tab_comments
WHERE comments = '跨级销售单';
通过表类型查备注
--通过表类型查表备注
SELECT DISTINCT table_type FROM user_tab_comments WHERE table_type=UPPER('table');
--通过表类型查表备注
SELECT DISTINCT table_type FROM user_tab_comments WHERE table_type=UPPER('view');
**************oracle列备注查询(comments column)**************
通过列名查列备注
--通过列名查列备注
SELECT *
FROM user_col_comments
WHERE column_name = upper('c_customerup_id')
AND comments IS NOT NULL;
通过表名查备注
--通过表名查备注
SELECT *
FROM user_col_comments
AND comments IS NOT NULL
AND table_name = upper('b_so');
通过列名查备注
--通过列名查备注
SELECT *
FROM user_col_comments
WHERE comments IS NOT NULL
AND column_name =UPPER( 'docno');
通过备注查列名和表名
--通过备注查列名和表名
SELECT *
FROM user_col_comments
WHERE comments = '单据类型';
这里是非常简单的笔记,如果大家喜欢,欢迎收藏转载,如有疑问,请留言我哦,嘿嘿,祝学习愉快!