-- @(#)$Id: ifaq04c.htm,v 1.11 1999/01/09 14:30:06 root Exp $ -- -- Simple emulation of SRAND and RAND in SPL -- Using random number generator suggested by C standard (ISO 9899:1990) 


CREATE PROCEDURE sp_setseed(n INTEGER)
  DEFINE GLOBAL seed DECIMAL( 10 ) DEFAULT  1 ;
  LET seed = n;
END PROCEDURE;

CREATE PROCEDURE sp_random() RETURNING INTEGER;
	DEFINE GLOBAL seed DECIMAL( 10 ) DEFAULT  1 ;
	DEFINE d DECIMAL( 20 , 0 );
	LET d = (seed *  1103515245 ) +  12345 ;
	-- MOD function does not handle 20-digit values...  Dammit!!
	LET seed = d -  4294967296  * TRUNC(d /  4294967296 );
	RETURN MOD(TRUNC(seed /  65536 ),  32768 );
END PROCEDURE;

CREATE PROCEDURE my_random(nMax INTEGER) RETURNING INTEGER;
  RETURN TRUNC(sp_random() /  32767  * nMax);
END PROCEDURE;

create procedure my_test_random(
  nCount INTEGER DEFAULT  1000000 
)
  define i, c integer;
  let i = nCount;
  while i >  0 
    let c = my_random( 1000 );
    let i = i -  1 ;
  end while;
end procedure;


create procedure my_test_sp_random(
  nCount INTEGER DEFAULT  1000000 
)
  define i, c integer;
  let i = nCount;
  while i >  0 
    let c = sp_random();
    let i = i -  1 ;
  end while;
end procedure;

-- тест используемого rand
execute procedure my_test_random( 1000000 ); -- 35.5 сек

-- убираем один вложенный вызов SP
execute procedure my_test_sp_random( 1000000 ); -  23 . 0  сек

create table t1 (
  c1 integer not null, 
  c2 integer not null,
  c3 integer not null,
  c4 integer not null,
  primary key (c1, c4)
); 

create index t1i1 on t1 (c1, c3, c2);

create temp table t2(
  c1 integer not null, 
  c2 integer not null, 
  c3 integer not null)
with no log;


create procedure my_fill_test(
)

  define i integer;
  define j integer;

  delete from t1;
  let i =  1000 ;
  while i >  0 
    let j =  20 ;
    while j >  0  
      insert into t1(c1, c2, c3, c4)
        values (i, my_random( 1000 ), my_random( 1000 ), j);
      let j = j -  1 ;
    end while;
    let i = i -  1 ;
  end while;
end procedure;

create procedure my_test(
  nCount INTEGER DEFAULT  1000000 
)

  define p integer;
  define i integer;
  define j integer;
  define cur_c2 integer;
  define k integer;
	
  delete from t2;
	
  let p = my_random( 1000 );
  let i = nCount;
  while i >  0 
    let j = my_random( 1000 );
<<lbl_each_t>>
    let k =  1 ;
    foreach
      select {+ FIRST_ROWS }
             c2 
        into cur_c2
        from t1
          where c1 = p
        order by abs(c3 - j) desc

        let k = k +  1 ;
        if k >  10  then
          exit foreach;
        end if;
        insert into t2 (c1, c2, c3)
          values (p, cur_c2, j);
        if cur_c2 != p then
          let p = cur_c2;
          if my_random( 10 ) >  4  then
            goto lbl_each_t;
          end if;
        end if;
    end foreach;
    let i = i -  1 ;
  end while;
end procedure;

execute procedure sp_setseed( 3121234 );
execute procedure my_fill_test(); -- 3 сек

execute procedure my_test( 1000000 ); -- 01:05.141
select count(*) from t2; -- 2849