CREATE OR REPLACE TRIGGER t_demo
BEFORE INSERT ON a
FOR EACH ROW
declare
com_chr varchar2(20);
BEGIN
select max(substr(col1,instr(com_chr,'-',3)+1)) into com_chr from a where col1 like :new.col1||'%';
:new.col1 := :new.col1 || to_char(to_number(com_chr)+1);
expetion when no_data_found then
:new.col1 := :new.col1 || '1'
END;
/
BEFORE INSERT ON a
FOR EACH ROW
declare
com_chr varchar2(20);
BEGIN
select max(substr(col1,instr(com_chr,'-',3)+1)) into com_chr from a where col1 like :new.col1||'%';
:new.col1 := :new.col1 || to_char(to_number(com_chr)+1);
expetion when no_data_found then
:new.col1 := :new.col1 || '1'
END;
/
create or replace procedure insert_t1
(
IN_aaa IN varchar2,
IN_bbb IN varchar2,
IN_ccc IN varchar2
)
as
v_ddd number(4);
begin
select nvl(min(ddd),0) into v_ddd from t1 where aaa = IN_aaa and bbb = IN_bbb and ccc = IN_ccc;
if v_ddd <> 1 then
v_ddd := 1;
else
select min(ddd) + 1 into v_ddd from t1 a where aaa = IN_aaa and bbb = IN_bbb and ccc = IN_ccc and not exists(select 1 from t1 b where aaa = IN_aaa and bbb = IN_bbb and ccc = IN_ccc and b.ddd = a.ddd + 1);
end if;
insert into t1 values(IN_aaa,IN_bbb,IN_ccc,v_ddd);
commit;
exception
when others then
rollback;
end;
/插入纪录时,调用
exec insert_t1('qwe','asd','zxc');
即可
CREATE OR REPLACE TRIGGER t_demo
BEFORE INSERT ON t1
FOR EACH ROW
declare
v_ddd number(4);
BEGIN
select nvl(min(ddd),0) into v_ddd from t1 where aaa = :new.aaa and bbb = :new.bbb and ccc = :new.ccc;
if v_ddd <> 1 then
:new.ddd := 1;
else
select min(ddd) + 1 into :new.ddd from t1 a where aaa = :new.aaa and bbb = :new.bbb and ccc = :new.ccc and not exists(select 1 from t1 b where aaa = :new.aaa and bbb = :new.bbb and ccc = :new.ccc and b.ddd = a.ddd + 1);
end if;
END;
/插入时调用
insert into t1 values('qwe','asd','zxc',0);第四个参数随便填个数字就行了。
BEFORE INSERT ON yourtable
FOR EACH ROW
declare
d_value number(4);
BEGIN
select NVL(min(ddd),0) into d_value from yourtable a where
aaa=:new.aaa and bbb=:new.bbb and ccc=:new.ccc and
not exists ( select 1 from yourtable b where b.aaa=:new.aaa
and b.bbb=:new.bbb and b.ccc=:new.ccc and b.ddd=a.ddd+1 );
if d_value = 0 then
select NVL(max(ddd),0) into d_value from yourtable where
aaa=:new.aaa and bbb=:new.bbb and ccc=:new.ccc;
end if;
:new.ddd := d_value + 1;END;
/