Oracle通过存过维护数据库表字段注释

1.建立注释表

create table TABCOL_COMMENTS
(
  TAB_NAME     VARCHAR2(500),
  TAB_COMMENTS VARCHAR2(500),
  COL_NAME     VARCHAR2(500),
  COL_COMMENTS VARCHAR2(500)
)
tablespace BIGDATA_STG
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 256
    next 256
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Add comments to the columns 
comment on column TABCOL_COMMENTS.TAB_NAME
  is '表名';
comment on column TABCOL_COMMENTS.TAB_COMMENTS
  is '表注释';
comment on column TABCOL_COMMENTS.COL_NAME
  is '字段名';
comment on column TABCOL_COMMENTS.COL_COMMENTS
  is '字段注释';

2.建立执行存过

CREATE OR REPLACE PROCEDURE PORC_COMMENT IS
    V_LOG VARCHAR2(1000);
    CURSOR CUR_SQL IS
        WITH A AS
         (SELECT UPPER(TAB.TAB_NAME) AS TAB_NAME,
                 TAB.TAB_COMMENTS,
                 UPPER(TAB.COL_NAME) AS COL_NAME,
                 TAB.COL_COMMENTS
            FROM TABCOL_COMMENTS TAB),
        B AS
         (SELECT 'comment on column ' || TAB_NAME || '.' || COL_NAME || ' is ''' ||
                 COL_COMMENTS || '''' AS SQLS
            FROM A), --字段注释
        
        C AS
         (SELECT 'comment on table ' || TAB_NAME || ' is ''' || TAB_COMMENTS || '''' AS SQLS
            FROM A), --表注释
        D AS
         (SELECT DISTINCT SQLS FROM C)
        SELECT SQLS
          FROM B
        UNION ALL
        SELECT SQLS FROM D;
BEGIN
    EXECUTE IMMEDIATE 'truncate table tabcol_comments_log';
    FOR CUR_S IN CUR_SQL LOOP
        INSERT INTO TABCOL_COMMENTS_LOG (V_LOG) VALUES (CUR_S.SQLS);
        COMMIT;
        EXECUTE IMMEDIATE CUR_S.SQLS;
    END LOOP;
END PORC_COMMENT;