Oracle序列+触发器+主键（实现主键自增）

发布时间：2020-12-12 15:02:36 所属栏目：百科来源：网络整理

导读：1.SEQUENCE（序列） -- Create sequence create sequence table_name_ID_SEQ minvalue 1 maxvalue 9999999999 start with 1 increment by 1 cache 20; 2.触发器(triggers) CREATE OR REPLACE TRIGGER " table_name_trigger " BEFORE INSERT OR UPDATE ON tabl

1.SEQUENCE（序列）

-- Create sequence
create sequence table_name_ID_SEQ
minvalue 1
maxvalue 9999999999
start with 1
increment by 1
cache 20;

2.触发器(triggers)

CREATE OR REPLACE TRIGGER " table_name_trigger" BEFORE INSERT OR UPDATE ON table_name
FOR EACH ROW
DECLARE
v_newVal NUMBER(10) := 0;
v_incval NUMBER(10) := 0;
BEGIN
IF INSERTING AND :new.id IS NULL THEN
SELECT table_name_id_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
-- If this is the first time this table have been inserted into (sequence == 1)
IF v_newVal = 1 THEN
--get the max indentity value from the table
SELECT NVL(max(id),0) INTO v_newVal FROM table_name;
v_newVal := v_newVal + 1;
--set the sequence to that value
LOOP
EXIT WHEN v_incval>=v_newVal;
SELECT table_name_id_SEQ.nextval INTO v_incval FROM dual;
END LOOP;
END IF;
--used to emulate LAST_INSERT_ID()
--mysql_utilities.identity := v_newVal;
-- assign the value from the sequence to emulate the identity column
:new.id := v_newVal;
END IF;
END;

3.主键和索引

-- Create/Recreate primary,unique and foreign key constraints
alter table table_name
add primary key (ID)
using index
tablespace QRPAY pctfree 10 initrans 2 maxtrans 255 storage ( initial 128K next 1M minextents 1 maxextents unlimited );

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!