Devart Forums

Code: Select all

CREATE PROCEDURE GETSERIAL(tabellid IN varchar2, 
TabellNamn_I IN varchar2,
antal IN integer,
Result_W out integer) IS 
CurrentId_W LOCKS.IDENT%TYPE;
tmp_number integer;
/******************************************************************************
   NAME:       getserial
   PURPOSE:    Get New id numbers for inserting new records.
******************************************************************************/
BEGIN
  begin
    select LOCKS.IDENT
      into CurrentId_W
      from LOCKS          
     where LOCKS.KIND   = 'SERIAL' 
       and LOCKS.TABLE_ = TabellNamn_I FOR UPDATE;
  exception
      when NO_DATA_FOUND then
        CurrentId_W := null;
  end;
	 	   		   	   
  if CurrentId_W is not null then
  	   tmp_number := to_number(CurrentId_W);
	   tmp_number := tmp_number + antal;
       Result_W := to_number(CurrentId_W) + 1; /* First id*/	   
	   CurrentId_W := to_char(tmp_number);
   	   update LOCKS
       set LOCKS.IDENT  = CurrentId_W
       where LOCKS.KIND   = 'SERIAL' 
       and LOCKS.TABLE_ = TabellNamn_I;	  
  ELSE
       Execute Immediate 'SELECT COUNT(*) FROM ' || TabellNamn_I into Result_W;  
       IF Result_W > 0 THEN
            Execute Immediate 'SELECT MAX(' || tabellid ||') FROM ' || TabellNamn_I into Result_W;  
       END IF;
       tmp_number := Result_W + antal;
	   CurrentId_W := to_char(tmp_number);
       INSERT INTO LOCKS(KIND,TABLE_,IDENT)
       VALUES('SERIAL',TabellNamn_I, CurrentId_W);	   
	   Result_W := Result_W + 1;  	   
   END IF;
END getserial;