Oracle上没有自增字段,可以使用索引和触发器来达到此目的 第一步:创建SEQUENCE create sequence s_policy_id increment by 1 start with 1 maxvalue 999999999; 第二步:创建一个基于该表的before insert 触发器,在触发器中使用该SEQUENCE ----t_pol_main为插入的表名,其中policy_id 为此表的一个要自动增加的字段 create or replace trigger tri_policy_id before insert on t_pol_main referencing old as old new as new for each row declare num number; begin select N_POLICY_ID.Nextval into num from dual; :new.policy_id := num; end;
思路1:使用序列 创建序列: CREATE SEQUENCE seq_name INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999999999 CYCLE NOORDER CACHE 50 / 使用序列: SELECT LTRIM(TO_CHAR(seq_name.NEXTVAL,'000000000')) INTO v_var FROM DUAL;思路2: 建一张表id_tb存储 最大序号 插入数据时 SELECT ID INTO V_ID FROM ID_TB WHERE ROWNUM = 1 FOR UPDATE; INSERT INTO TABLE_NAME(ID,...) VALUES(V_ID,...); UPDATE SET ID_TB ID = ID+1; COMMIT;
第一步:创建SEQUENCE
create sequence s_policy_id increment by 1 start with 1 maxvalue 999999999;
第二步:创建一个基于该表的before insert 触发器,在触发器中使用该SEQUENCE
----t_pol_main为插入的表名,其中policy_id 为此表的一个要自动增加的字段
create or replace trigger tri_policy_id
before insert on t_pol_main
referencing old as old new as new
for each row
declare
num number;
begin
select N_POLICY_ID.Nextval into num from dual;
:new.policy_id := num;
end;
创建序列:
CREATE SEQUENCE seq_name
INCREMENT BY 1
START WITH 1
MINVALUE 1
MAXVALUE 999999999
CYCLE
NOORDER
CACHE 50
/
使用序列:
SELECT LTRIM(TO_CHAR(seq_name.NEXTVAL,'000000000')) INTO v_var FROM DUAL;思路2: 建一张表id_tb存储 最大序号
插入数据时
SELECT ID INTO V_ID FROM ID_TB WHERE ROWNUM = 1 FOR UPDATE;
INSERT INTO TABLE_NAME(ID,...) VALUES(V_ID,...);
UPDATE SET ID_TB ID = ID+1;
COMMIT;