Oracle 数据库 建表、注释、授权、同名语法

Oracle 数据库 建表、注释、授权、同名语法

• 创建数据表

  
  CREATE TABLE ....._ADM....._RECORD (
    PKID VARCHAR2(32) PRIMARY KEY NOT NULL,
    APP_NAME VARCHAR2(100) NOT NULL,
    MODEL_NAME VARCHAR2(100) NOT NULL,
    MODEL_ID VARCHAR(100) NOT NULL,
    TARGET VARCHAR2(10),
    OPT_TYPE NUMBER(2) NOT NULL,
    TODO_KEY VARCHAR2(32),
    PARMA2 VARCHAR2(50),
    MESSAGE VARCHAR2(2000),
    TODO_TYPE NUMBER(2),
    SETTODO_DATE DATE
  );
  

• 给表添加注释

  
  
  COMMENT ON TABLE ITUBM_UMC_ADM.MSGM_SETTODO_RECORD IS '用户置....记录表';
  
  -- 给列添加注释
  COMMENT ON COLUMN ITUBM_UMC_ADM.MSGM_SETTODO_RECORD.PKID IS  '主键id';
  COMMENT ON COLUMN ITUBM_UMC_ADM.MSGM_SETTODO_RECORD.APP_NAME IS '..来源';
  COMMENT ON COLUMN ITUBM_UMC_ADM.MSGM_SETTODO_RECORD.MODEL_NAME IS '模块名';
  COMMENT ON COLUMN ITUBM_UMC_ADM.MSGM_SETTODO_RECORD.MODEL_ID IS '..唯一标识';
  COMMENT ON COLUMN ITUBM_UMC_ADM.MSGM_SETTODO_RECORD.TARGET IS '..所属人';
  COMMENT ON COLUMN ITUBM_UMC_ADM.MSGM_SETTODO_RECORD.OPT_TYPE IS '..操作类型';
  COMMENT ON COLUMN ITUBM_UMC_ADM.MSGM_SETTODO_RECORD.TODO_KEY IS '..关键字';
  COMMENT ON COLUMN ITUBM_UMC_ADM.MSGM_SETTODO_RECORD.PARMA2 IS '..参数2';
  COMMENT ON COLUMN ITUBM_UMC_ADM.MSGM_SETTODO_RECORD.MESSAGE IS '....内容';
  COMMENT ON COLUMN ITUBM_UMC_ADM.MSGM_SETTODO_RECORD.TODO_TYPE IS '..类型';
  COMMENT ON COLUMN ITUBM_UMC_ADM.MSGM_SETTODO_RECORD.SETTODO_DATE IS '...日期';
  
  

• 授权 & 同名

  -- 给用户....._APP授权 查询
  GRANT SELECT ON ...._ADM......_RECORD TO ...._APP
  
  -- 给用户..._APP授权 插入
  GRANT INSERT ON ..._ADM...._RECORD TO ...._APP
  
  -- 在用户...._APP下创建同名数据表
  create synonym ...._RECORD for ..._ADM....._RECORD;

关注公众号：有点建树，做更多交流。

本文由mdnice多平台发布