Oracle实现ID自增长

目录

1.创建表结构，并添加注释

2.创建表对应的序列

3.创建表触发器

---

1.创建表结构，并添加注释

 CREATE TABLE "KETTLE_SCHEDULER"."K_REPOSITORY" 
   (	"ID" VARCHAR2(32 BYTE) DEFAULT sys_guid() NOT NULL ENABLE, 
	"REP_NAME" NVARCHAR2(50), 
	"REP_TYPE" NVARCHAR2(10), 
	"REP_USERNAME" NVARCHAR2(50), 
	"REP_PASSWORD" NVARCHAR2(50), 
	"REP_BASE_PATH" NVARCHAR2(500), 
	"DB_TYPE" NVARCHAR2(10), 
	"DB_ACCESS" NVARCHAR2(10), 
	"DB_HOST" NVARCHAR2(50), 
	"DB_PORT" NVARCHAR2(10), 
	"DB_NAME" NVARCHAR2(20), 
	"DB_USERNAME" NVARCHAR2(50), 
	"DB_PASSWORD" NVARCHAR2(50), 
	"ADD_TIME" DATE, 
	"ADD_USER" NUMBER(11,0), 
	"EDIT_TIME" DATE, 
	"EDIT_USER" NUMBER(11,0), 
	"DEL_FLAG" NUMBER(11,0), 
	 CONSTRAINT "SYS_C0019600" CHECK ("ID" IS NOT NULL) ENABLE, 
	 CONSTRAINT "SYS_C0019611" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  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 "USERS"  ENABLE
   ) 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 "KETTLE" ;
 

   COMMENT ON COLUMN "KETTLE_SCHEDULER"."K_REPOSITORY"."ID" IS 'ID';
 
   COMMENT ON COLUMN "KETTLE_SCHEDULER"."K_REPOSITORY"."REP_NAME" IS '资源库名称';
 
   COMMENT ON COLUMN "KETTLE_SCHEDULER"."K_REPOSITORY"."REP_TYPE" IS '资源库类型: fileRep-文件, dbRep-数据库';
 
   COMMENT ON COLUMN "KETTLE_SCHEDULER"."K_REPOSITORY"."REP_USERNAME" IS '登录用户名';
 
   COMMENT ON COLUMN "KETTLE_SCHEDULER"."K_REPOSITORY"."REP_PASSWORD" IS '登录密码';
 
   COMMENT ON COLUMN "KETTLE_SCHEDULER"."K_REPOSITORY"."REP_BASE_PATH" IS '文件资源库路径: rep_type=fileRep生效';
 
   COMMENT ON COLUMN "KETTLE_SCHEDULER"."K_REPOSITORY"."DB_TYPE" IS '资源库数据库类型（MYSQL、ORACLE）';
 
   COMMENT ON COLUMN "KETTLE_SCHEDULER"."K_REPOSITORY"."DB_ACCESS" IS '资源库数据库访问模式（Native, ODBC, OCI, Plugin, JNDI)';
 
   COMMENT ON COLUMN "KETTLE_SCHEDULER"."K_REPOSITORY"."DB_HOST" IS '资源库数据库主机名或者IP地址';
 
   COMMENT ON COLUMN "KETTLE_SCHEDULER"."K_REPOSITORY"."DB_PORT" IS '资源库数据库端口号';
 
   COMMENT ON COLUMN "KETTLE_SCHEDULER"."K_REPOSITORY"."DB_NAME" IS '资源库数据库名称';
 
   COMMENT ON COLUMN "KETTLE_SCHEDULER"."K_REPOSITORY"."DB_USERNAME" IS '数据库登录账号';
 
   COMMENT ON COLUMN "KETTLE_SCHEDULER"."K_REPOSITORY"."DB_PASSWORD" IS '数据库登录密码';
 
   COMMENT ON COLUMN "KETTLE_SCHEDULER"."K_REPOSITORY"."ADD_TIME" IS '添加时间';
 
   COMMENT ON COLUMN "KETTLE_SCHEDULER"."K_REPOSITORY"."ADD_USER" IS '添加者';
 
   COMMENT ON COLUMN "KETTLE_SCHEDULER"."K_REPOSITORY"."EDIT_TIME" IS '编辑时间';
 
   COMMENT ON COLUMN "KETTLE_SCHEDULER"."K_REPOSITORY"."EDIT_USER" IS '编辑者';
 
   COMMENT ON COLUMN "KETTLE_SCHEDULER"."K_REPOSITORY"."DEL_FLAG" IS '是否删除（0：存在；1：删除）';
 
   COMMENT ON TABLE "KETTLE_SCHEDULER"."K_REPOSITORY"  IS '资源库表';
 

  CREATE UNIQUE INDEX "KETTLE_SCHEDULER"."REP_NAME_UNIQUE_INDEX" ON "KETTLE_SCHEDULER"."K_REPOSITORY" ("REP_NAME") 
  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 "KETTLE" ;

2.创建表对应的序列

   CREATE SEQUENCE  "KETTLE_SCHEDULER"."SEQ_REPOSITORY"  MINVALUE 1 MAXVALUE 99999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER  NOCYCLE ;
 

3.创建表触发器

CREATE OR REPLACE TRIGGER "KETTLE_SCHEDULER"."REPOSITORY_TRIGGER" 
BEFORE INSERT ON K_REPOSITORY for each row
BEGIN
  select SEQ_REPOSITORY.Nextval into:new.id from dual;
END;