ORACLE 新建表脚本（基本规范）

新建表脚本：

CREATE TABLE DATA.SpdCallP2001ApiResult
   (	
    SpdCallP2001ApiResultId NUMBER(10,0) NOT NULL , 
    EncounterId NUMBER(10,0) DEFAULT (-1) NOT NULL ,
    AccountListId NUMBER(10,0) DEFAULT (-1) NOT NULL ,
    OrderId    NUMBER(10,0) DEFAULT (-1) NOT NULL ,
    IsSuccess NUMBER(1,0) DEFAULT (0) NOT NULL ,
    CalledTime TIMESTAMP (6) DEFAULT SYSDATE NOT NULL
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JXTBSPACE" ;
  
  CREATE UNIQUE INDEX data.PK_SpdCallP2001ApiResult ON data.SpdCallP2001ApiResult (SpdCallP2001ApiResultId) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JXTBSPACE" ;

ALTER TABLE data.SpdCallP2001ApiResult ADD CONSTRAINT PK_SpdCallP2001ApiResult PRIMARY KEY (SpdCallP2001ApiResultId)
  USING INDEX "DATA"."PK_SPDCALLP2001APIRESULT" ;

CREATE SEQUENCE  data.SpdCallP2001ApiResult_SE MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ;

/

CREATE OR REPLACE TRIGGER data.SpdCallP2001ApiResult_TR 
   BEFORE INSERT 
   ON data.SpdCallP2001ApiResult
   FOR EACH ROW
 DECLARE 
    v_newVal NUMBER(12) := 0;
    v_incval NUMBER(12) := 0;
    v_C  NUMBER(12) := 0;
    PRAGMA AUTONOMOUS_TRANSACTION;  
 BEGIN
  IF INSERTING AND nvl(:new.SpdCallP2001ApiResultId,-1)<0 THEN
    SELECT  data.SpdCallP2001ApiResult_SE.NEXTVAL INTO v_newVal FROM DUAL;
    IF v_newVal = 1 THEN 
      SELECT NVL(max(SpdCallP2001ApiResultId),0) INTO v_newVal FROM data.SpdCallP2001ApiResult;
      v_newVal := v_newVal + 1;
      
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT data.SpdCallP2001ApiResult_SE.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
    
    --跳过已存在的Id，默认生成的触发器没有这个逻辑****************
    loop
        SELECT count(0) INTO v_c FROM data.SpdCallP2001ApiResult WHERE SpdCallP2001ApiResultId=v_newVal;
        IF v_c=0 THEN
            EXIT;
        END IF;
        SELECT data.SpdCallP2001ApiResult_SE.nextval INTO v_newVal FROM dual;
    END LOOP;
    --****************
        
   sqlserver_utilities.IDENTITY  := v_newVal;    
   :new.SpdCallP2001ApiResultId := v_newVal;
   END IF;
  END;
/
ALTER TRIGGER data.SpdCallP2001ApiResult_TR ENABLE;
/