oracle 建表

create table contact (
contact_id number(10) not null,
contact_name varchar2(50) not null,
contact_telephone varchar2(20) not null,
constraint pk_contact primary key (contact_id));

COMMENT ON TABLE "CONTACT" IS '联系人表';
COMMENT ON COLUMN "CONTACT"."CONTACT_ID" IS '联系人ID';
COMMENT ON COLUMN "CONTACT"."CONTACT_NAME" IS '联系人姓名';
COMMENT ON COLUMN "CONTACT"."CONTACT_TELEPHONE" IS '联系方式';

create table contact_group (
contact_group_id number(10) not null,
contact_group_code char(2) not null,
contact_group_name varchar2(50) not null,
last_update_time char(14) not null,
contact_group_type char(1) not null,
constraint pk_contact_group primary key (contact_group_id));

COMMENT ON TABLE "CONTACT_GROUP" IS '联系组表';
COMMENT ON COLUMN "CONTACT_GROUP"."CONTACT_GROUP_ID" IS '联系组ID';
COMMENT ON COLUMN "CONTACT_GROUP"."CONTACT_GROUP_CODE" IS '编号';
COMMENT ON COLUMN "CONTACT_GROUP"."CONTACT_GROUP_NAME" IS '联系组名称';
COMMENT ON COLUMN "CONTACT_GROUP"."LAST_UPDATE_TIME" IS '最后更新时间';
COMMENT ON COLUMN "CONTACT_GROUP"."CONTACT_GROUP_TYPE" IS '联系组类型 0邮件组 1手机组';

create table contact_group_person (
contact_id number(10) not null,
contact_group_id number(10) not null,
constraint pk_contact_group_person primary key(contact_id,contact_group_id));

COMMENT ON TABLE "CONTACT_GROUP_PERSON" IS '联系组与联系人中间表';
COMMENT ON COLUMN "CONTACT_GROUP_PERSON"."CONTACT_ID" IS '联系人ID';
COMMENT ON COLUMN "CONTACT_GROUP_PERSON"."CONTACT_GROUP_ID" IS '联系组ID';

create table message_template (
message_template_id number(10) not null,
message_template_code varchar2(2) not null,
message_template_type char(1) not null,
message_template_name varchar2(12) not null,
business_type varchar2(1) not null,
sys_id char(2) not null,
sub_sys_id char(2) not null,
err_code varchar2(8) not null,
content varchar2(512) not null,
contact_group_id number(10) not null,
constraint uk_message_template UNIQUE(message_template_code) ,
constraint pk_message_template primary key(message_template_id));

COMMENT ON TABLE "MESSAGE_TEMPLATE" IS '信息模板表';
COMMENT ON COLUMN "MESSAGE_TEMPLATE"."MESSAGE_TEMPLATE_ID" IS '信息模板ID';
COMMENT ON COLUMN "MESSAGE_TEMPLATE"."MESSAGE_TEMPLATE_CODE" IS '编号';
COMMENT ON COLUMN "MESSAGE_TEMPLATE"."MESSAGE_TEMPLATE_TYPE" IS '模板类型 0邮件组 1手机组';
COMMENT ON COLUMN "MESSAGE_TEMPLATE"."MESSAGE_TEMPLATE_NAME" IS '模板名称';
COMMENT ON COLUMN "MESSAGE_TEMPLATE"."BUSINESS_TYPE" IS '业务类型 0 POS 1 网银 2 电话支付 3 其他';
COMMENT ON COLUMN "MESSAGE_TEMPLATE"."SYS_ID" IS '系统号';
COMMENT ON COLUMN "MESSAGE_TEMPLATE"."SUB_SYS_ID" IS '子系统号';
COMMENT ON COLUMN "MESSAGE_TEMPLATE"."ERR_CODE" IS '错误码';
COMMENT ON COLUMN "MESSAGE_TEMPLATE"."CONTENT" IS '模板内容';
COMMENT ON COLUMN "MESSAGE_TEMPLATE"."CONTACT_GROUP_ID" IS '联系组ID';

alter table err_code_info add mail_group_flag char(1) default 'N';
alter table err_code_info add mob_group_flag char(1) default 'N';

COMMENT ON COLUMN "ERR_CODE_INFO"."MAIL_GROUP_FLAG" IS '邮件组标志 Y有效 N无效';
COMMENT ON COLUMN "ERR_CODE_INFO"."MOB_GROUP_FLAG" IS '手机组标志 Y有效 N无效';