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;
复制代码 列备注查询结果如下: ![](https://img-blog.csdn.net/20141028111020718?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvamluYXF1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center) 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 = '单据类型';
复制代码
这里是非常简单的笔记,如果大家喜欢,欢迎收藏转载,如有疑问,请留言我哦,嘿嘿,祝学习愉快!
|
oracle, COMMENT, 查询, TABLE, 开发