建表、注释、主键、外键、自增长键

-- 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;