工作日志——数据库建表注意事项

     每个数据库存在很多用户，不同的用户有不同的权限。作为工作中的一般用户，需要给他的只有SELECT 权限就好。

例如：我需要以center_admin的用户建一张SYS_TABLEDESC表

1、新建表语句

create table SYS_TABLEDESC
(
  tablename   VARCHAR2(64) PRIMARY KEY,
  tabledesc   VARCHAR2(100) NOT NULL,
  tabletype   VARCHAR2(20) not null,
  tablestate  VARCHAR2(64) not null,
  pkname      VARCHAR2(64) not null,
  uniquename  VARCHAR2(64) not null,
  uptime      VARCHAR2(100) not null,
  upfrequency VARCHAR2(100) not null,
  tableseries VARCHAR2(200) not null,
  remark      VARCHAR2(2000)
);

2、给列添加注释
comment on column SYS_TABLEDESC.tablename
  is '表名';
comment on column SYS_TABLEDESC.tabledesc
  is '表中文名';
comment on column SYS_TABLEDESC.tabletype
  is '表类型';
comment on column SYS_TABLEDESC.tablestate
  is '表状态';
comment on column SYS_TABLEDESC.pkname
  is '主键';
comment on column SYS_TABLEDESC.uniquename
  is '唯一索引';
comment on column SYS_TABLEDESC.uptime
  is '更新时间';
comment on column SYS_TABLEDESC.upfrequency
  is '更新频率';
comment on column SYS_TABLEDESC.tableseries
  is '所属系列';
comment on column SYS_TABLEDESC.remark
  is '说明';
 

3、给center_read用户授予权限
GRANT SELECT ON SYS_TABLEDESC TO center_read;

4、建立同义词（建立同义词可以方便其他用户使用该表）

CREATE PUBLIC SYNONYM sys_tabledesc FOR center_admin.sys_tabledesc;

DROP  PUBLIC SYNONYM sys_tabledesc

5、插入数据
INSERT INTO  SYS_TABLEDESC
  (TABLENAME,
   TABLEDESC,
   TABLETYPE,
   TABLESTATE,
   PKNAME,
   UNIQUENAME,
   UPTIME,
   UPFREQUENCY,
   TABLESERIES,
   REMARK)
VALUES
  ('SK_BASICINFO',
   '股票基本信息表',
   '基础表',
   '正常',
   'ID',
   'SECODE+LISTDATE',
   '0:00(周一至周日',
   '每小时一次',
   '基本面数据 \ 基础数据 \ 股票/上市公司 \ 股票基本资料',
   NULL);

6、更新表中数据

MERGE INTO SYS_TABLEDESC M
USING (SELECT C.TABLE_NAME,
              (MAX(DECODE(RN, 1, COLUMN_NAME, NULL)) ||
              MAX(DECODE(RN, 2, ',' || COLUMN_NAME, NULL)) ||
              MAX(DECODE(RN, 3, ',' || COLUMN_NAME, NULL)) ||
              MAX(DECODE(RN, 4, ',' || COLUMN_NAME, NULL)) ||
              MAX(DECODE(RN, 5, ',' || COLUMN_NAME, NULL)) ||
              MAX(DECODE(RN, 6, ',' || COLUMN_NAME, NULL)) ||
              MAX(DECODE(RN, 7, ',' || COLUMN_NAME, NULL)) ||
              MAX(DECODE(RN, 8, ',' || COLUMN_NAME, NULL)) ||
              MAX(DECODE(RN, 9, ',' || COLUMN_NAME, NULL))) COLUMN_NAME
         FROM (SELECT A.TABLE_NAME,
                      B.COLUMN_NAME,
                      ROW_NUMBER() OVER(PARTITION BY A.TABLE_NAME ORDER BY A.TABLE_NAME) RN
                 FROM ALL_INDEXES@CENTERDB144 A
                 JOIN ALL_IND_COLUMNS@CENTERDB144 B
                   ON A.TABLE_NAME = B.TABLE_NAME
                  AND A.INDEX_NAME = B.INDEX_NAME
                WHERE UNIQUENESS = 'UNIQUE'
                  AND OWNER = 'CENTER_ADMIN'
                  AND B.COLUMN_NAME NOT IN ('UPDATEID', 'ID')
                  AND A.TABLE_NAME NOT IN
                      ('MMD_FRAQUOTE',
                       'NWS_NEWS_NPUB',
                       'NWS_NEWS',
                       'NWS_FINANCIALINFO',
                       'MMD_IRSPRICINGQUOTE')) C
        GROUP BY C.TABLE_NAME) N
ON (M.TABLENAME = N.TABLE_NAME)
WHEN MATCHED THEN
  UPDATE SET M.UNIQUENAME = M.UNIQUENAME || ' 或 ' || N.COLUMN_NAME