-- Create table
create table TBL_BOS_AP
(
? APID???????????? VARCHAR2(32) not null,
? APIMSI?????????? VARCHAR2(32) not null,
? APUSIM?????????? VARCHAR2(32) not null,
? CUSTOMERID?????? VARCHAR2(32) not null,
? STATUS?????????? CHAR(1) default 1 not null,
? OPERATIONTIME??? DATE,
? SERVICEATTRIBUTE VARCHAR2(2) not null,
? SERVICESORT????? VARCHAR2(2) not null,
? ENTERPRISEID???? VARCHAR2(3),
? ENTERPRISENAME?? VARCHAR2(32),
? DEVICETYPE?????? VARCHAR2(2) not null,
? CREATETIME?????? DATE default SYSDATE,
? DESCRIPTION????? VARCHAR2(255),
? OPTSRC?????????? CHAR(1) default 1
)

-- Add comments to the table
comment on table TBL_BOS_AP
? is '客户AP表';
-- Add comments to the columns
comment on column TBL_BOS_AP.APID
? is 'AP标识符';
comment on column TBL_BOS_AP.APIMSI
? is 'AP IMSI';
comment on column TBL_BOS_AP.APUSIM
? is 'AP号码';
comment on column TBL_BOS_AP.CUSTOMERID
? is '所属客户（FK）';
comment on column TBL_BOS_AP.STATUS
? is '状态(1:在用,2:暂停,3:注销)';
comment on column TBL_BOS_AP.OPERATIONTIME
? is '处理时间(STATUS=1,是开通时间；STATUS=2,是暂停时间；STATUS=3，注销时间)';
comment on column TBL_BOS_AP.SERVICEATTRIBUTE
? is '服务属性 (1 免费2 收费)';
comment on column TBL_BOS_AP.SERVICESORT
? is '业务分类 (1 个人2 企业)';
comment on column TBL_BOS_AP.ENTERPRISEID
? is '企业代码 (当ServiceSort为2时必填)';
comment on column TBL_BOS_AP.ENTERPRISENAME
? is '企业名称';
comment on column TBL_BOS_AP.DEVICETYPE
? is '机器类型
1 普通机
2 短信机
3 同振机
4 安防机
';
comment on column TBL_BOS_AP.CREATETIME
? is '创建时间';
comment on column TBL_BOS_AP.DESCRIPTION
? is '业务描述';
comment on column TBL_BOS_AP.OPTSRC
? is '操作来源（1：BOSS；2：商客短信平台）';
-- Create/Recreate primary, unique and foreign key constraints
alter table TBL_BOS_AP
? add constraint PK_APID primary key (APID);

alter table TBL_BOS_AP
? add constraint FK_CUSTOMERID foreign key (CUSTOMERID)
? references TBL_BOS_CUSTOMER (CUSTOMERID);

?

自增长键：

1.建sequence

CREATE SEQUENCE SEQ_PARAMID
INCREMENT BY 1
START WITH 1?
NOMAXVALUE?
NOCYCLE ;

2.建触发器
CREATE TRIGGER TRIG_TBL_SYS_PARAM
? BEFORE insert ON TBL_SYS_PARAM
? FOR EACH ROW
begin
? select SEQ_PARAMID.nextval into :New.PARAMID from dual;
end;