关于ORACLE自增长

建完表之后要建SEQUENCES,再然后建TRIGGERS

 

下面是代码参考：

-- Create table
create table NEP_SYS_SENDSMS
(
  ID             NUMBER(19) not null,
  MOBILE         VARCHAR2(20) not null,
  CONTENT        VARCHAR2(150) not null,
  DESCRIPTION    VARCHAR2(200),
  SMS_WRITE_TIME DATE not null,
  FLAG           NUMBER(2)
)
tablespace NEP_SUBSTATION
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints
alter table NEP_SYS_SENDSMS
  add constraint PRIMARY00021 primary key (ID)
  using index
  tablespace NEP_SUBSTATION
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

-- Create sequence
create sequence NEP_SYS_SENDSMS_SEQ
minvalue 1
maxvalue 9999999999999999999999999999
start with 9
increment by 1
nocache
order;

 

--create triggers

create or replace trigger NEP_SYS_SENDSMD
  before insert on nep_sys_sendsms 
  for each row
declare
  -- local variables here
  last_Sequence NUMBER;
  last_InsertID NUMBER;
begin
  IF (:NEW."ID" IS NULL)
  THEN SELECT "NEP_SYS_SENDSMS_SEQ".NEXTVAL INTO :NEW."ID" FROM DUAL;
  ELSE SELECT Last_Number-1 INTO last_Sequence FROM User_Sequences WHERE UPPER(Sequence_Name) = UPPER('NEP_SYS_SENDSMS_SEQ');
  SELECT :NEW."ID" INTO last_InsertID FROM DUAL;
  WHILE (last_InsertID > last_Sequence)
  LOOP
  SELECT "NEP_SYS_SENDSMS_SEQ".NEXTVAL INTO last_Sequence FROM DUAL;
  END LOOP;
  END IF;
end NEP_SYS_SENDSMD;