oracle完整建表,Oracle数据库建表完整sql

-- CREATE TABLE 创建小程序卡片表

create table GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD

(

ID_GIM_CLOUD_MINIPROGRAM_CARD VARCHAR2(32) not null,

TEMPLATE_NO VARCHAR2(50) not null,

DESCRIPTION VARCHAR2(200),

BELONG_TO VARCHAR2(50),

TEMPLATE_PRIO VARCHAR2(3),

LOAD_PERIOD_BEGIN VARCHAR2(20),

LOAD_PERIOD_END VARCHAR2(10),

SEND_PERIOD_BEGIN VARCHAR2(10),

SEND_PERIOD_END VARCHAR2(10),

TITLE VARCHAR2(20) not null,

URL VARCHAR2(50) not null,

COVER_URL VARCHAR2(50) not null,

AGENT_FLAG char(2) not null,

ID_GIM_AGENT_GROUP VARCHAR2(32) not null,

PUSH_URL_NO VARCHAR2(100),

WX_TEMPLATE VARCHAR2(255),

CREATED_BY VARCHAR2(100) not null,

CREATED_DATE DATE not null,

UPDATED_BY VARCHAR2(100) not null,

UPDATED_DATE DATE not null

);

-- Add comments to the table

comment on table GIM_CLOUD_MINIPROGRAM_CARD

is '小程序卡片模板表';

-- Add comments to the columns

comment on column GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD.ID_GIM_CLOUD_MINIPROGRAM_CARD

is '主键';

comment on column GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD.TEMPLATE_NO

is '模板号';

comment on column GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD.DESCRIPTION

is '模板说明';

comment on column GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD.BELONG_TO

is '所属系统';

comment on column GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD.TEMPLATE_PRIO

is '优先级';

comment on column GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD.LOAD_PERIOD_BEGIN

is '取数时间段开始时间';

comment on column GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD.LOAD_PERIOD_END

is '取数时间段结束时间';

comment on column GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD.SEND_PERIOD_BEGIN

is '发送时间段开始时间';

comment on column GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD.SEND_PERIOD_END

is '发送时间段结束时间';

comment on column GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD.TITLE

is '标题';

comment on column GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD.URL

is '小程序路径';

comment on column GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD.COVER_URL

is '封面url';

comment on column GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD.AGENT_FLAG

is '坐席端是否可用';

comment on column GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD.ID_GIM_AGENT_GROUP

is '坐席组id';

comment on column GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD.PUSH_URL_NO

is '推送目的地url编号';

comment on column GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD.WX_TEMPLATE

is '对应的微信模板号';

comment on column GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD.CREATED_BY

is '创建人';

comment on column GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD.CREATED_DATE

is '创建时间';

comment on column GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD.UPDATED_BY

is '最后修改人';

comment on column GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD.UPDATED_DATE

is '最后修改时间';

--CREAT PUBLIC SYNONYM 创建同义词

CREATE PUBLIC SYNONYM GIM_CLOUD_MINIPROGRAM_CARD FOR GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD;

-- Create/Recreate primary, unique and foreign key constraints 创建索引

CREATE unique INDEX GIMDATA.PK_GIM_CLOUD_MINIPROGRAM_CARD ON GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD (ID_GIM_CLOUD_MINIPROGRAM_CARD) INITRANS 16;

-- 主键

ALTER TABLE GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD ADD CONSTRAINT PK_GIM_CLOUD_MINIPROGRAM_CARD PRIMARY KEY (ID_GIM_CLOUD_MINIPROGRAM_CARD)

USING INDEX GIMDATA.PK_GIM_CLOUD_MINIPROGRAM_CARD;

--创建UM,BUTTON_ID联合索引

create index GIMDATA.INDEX_GRWX_UM_BUTTON_ID ON GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD (UM,BUTTON_ID) INITRANS 16;

-- Grant/Revoke object privileges 授权

GRANT SELECT,DELETE,UPDATE,INSERT ON GIMDATA.GIM_CLOUD_MINIPROGRAM_CARD TO NETSNCHRMSTJS;

GRANT SELECT, INSERT, UPDATE, DELETE ON GIM_CLOUD_MINIPROGRAM_CARD TO GIMLOGTMP,GIMROPR,GIMOPR,PUB_TEST,R_GIMDATA_DML;

GRANT SELECT ON GIM_CLOUD_MINIPROGRAM_CARD TO R_GIMDATA_QRY,DSPDSG,GBDSQP,MISIOCDSG,NEWCHDSG,R_GIMDATA_DEV_QRY,SASCCARDKTL,PA18SHOPESDMKTL,PADHDPSQP;