A comb,

Incrementing serial numbers is a common problem in development, which can be achieved through sequences. The stored procedure is executed by a scheduled task to periodically reset the starting number and the value of the increment in the sequence.

2. Stored procedures

The input parameter V_SEQNAME is passed the sequence to be reset

create or replace procedure SEQ_RESET(V_SEQNAME varchar2) as
  N    number(10);
  TSQL varchar2(100);
begin
  execute immediate 'select ' || V_SEQNAME || '.nextval from dual'
    into N;
  N    := -N;
  TSQL := 'alter sequence ' || V_SEQNAME || ' increment by ' || N;
  execute immediate TSQL;
  execute immediate 'select ' || V_SEQNAME || '.nextval from dual'
    into N;
  TSQL := 'alter sequence ' || V_SEQNAME || ' increment by 1';
  execute immediate TSQL;
end SEQ_RESET;
Copy the code

Create a sequence table

4. Scheduled tasks

Create a scheduled task and control the time to reset. You can perform the reset stored procedure all the time, every day, every month and so on to reset the sequence. VINDA_SEQ is the sequence table.

Fifth, use

Nextval into NextCode from sys.dual; select vinda_seq.nextval into nextcode from sys.dual; Assign to the variable nextcode, then :new.ATTRIBUTE5:=nextcode; ATTRIBUTE5 holds the value of the serial number.

create or replace trigger vinda_prodcheck_info_TRIGER
  before insert on vinda_prodcheck_info   
  for each row
declare
  nextid number;
  nextcode number;
begin
  IF :new.RECORD_ID IS NULL or :new.RECORD_ID=0 THEN 
    select vinda_prodcheck_info_SEQ.nextval 
    into nextid
    from sys.dual;
    select VINDA_SEQ.nextval 
    into nextcode
    from sys.dual;
    :new.RECORD_ID:=nextid;
    :new.ATTRIBUTE5:=nextcode;
  end if;
end vinda_prodcheck_info_TRIGER;
Copy the code

Sixth, the end

Other blogs:

Install 18.03.0 + rancher1.6.17 dockerContainer virtualization deployment

Vue. Js +iview global loading public method

Welcome to leave a comment