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;

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