oracle Comment添加表备注和列备注

来源：互联网发布：云计算视频教程百度云编辑：程序博客网时间：2024/04/30 03:02

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 asselect 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$ cwhere 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 asselect o.name, c.name, co.comment$from sys."_CURRENT_EDITION_OBJ" o, sys.col$ c, sys.com$ cowhere 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 = '单据类型';

这里是非常简单的笔记，如果大家喜欢，欢迎收藏转载，如有疑问，请留言我哦，嘿嘿，祝学习愉快！

0 0