xref: /Universal-ctags/Units/parser-sql.r/random.sql.d/input.sql (revision ac0bc2347d23a84cbef9bc0e024621f9a30eb358) 
1/*
2------------------------------------------------------------------------------
3URL:       http://www.orafaq.com/scripts/plsql/random.txt
4Filename:  random.txt
5Purpose:   Random number/ string generator package
6Author:    Unknown
7Original:  http://www.orafaq.org/scripts/sql/random.txt
8Edits:
9 19990908 Phil Rand <prand@spu.edu> Added functions rand_string(), smaller().
10------------------------------------------------------------------------------
11*/
12
13create or replace package random
14is
15   procedure srand(new_seed in number);
16   procedure get_rand(r OUT number);
17   procedure get_rand_max(r OUT number, n IN number);
18   function  rand return number;
19   function  rand_max(n IN number) return number;
20   function  rand_string(ssiz IN number) return varchar2;
21   function  smaller(x IN number, y IN number) return number;
22   pragma restrict_references(rand, WNDS);
23   pragma restrict_references(rand_max, WNDS);
24   pragma restrict_references(random, WNDS, RNPS);
25   pragma restrict_references(rand_string, WNDS);
26   pragma restrict_references(smaller, WNDS);
27end random;
28/
29
30create or replace package body random
31is
32   multiplier   constant number := 22695477;
33   increment    constant number := 1;
34   "2^32"       constant number := 2 ** 32;
35   "2^16"       constant number := 2 ** 16;
36   "0x7fff"     constant number := 32767;
37   Seed         number          := 1;
38
39   function  smaller(x IN number, y IN number) return number is
40   begin
41	if x <= y then
42	    return x;
43	else
44	    return y;
45	end if;
46   end smaller;
47
48   function rand_string(ssiz IN number) return varchar2 is
49     i      number;
50     m      number;
51     c      char;
52     result varchar2(2000) := '';
53   begin
54	m := smaller(ssiz,2000);
55	for i in 1..m loop
56	    c := substr('abcdefghijklmnopqrstuvwxyz0123456789',rand_max(36),1);
57	    result := result || c;
58        end loop;
59	return result;
60   end rand_string;
61
62   procedure srand(new_seed in number) is
63   begin
64     Seed := new_seed;
65   end srand;
66
67   function rand return number is
68   begin
69     Seed := mod(multiplier * Seed + increment, "2^32");
70     return bitand(Seed/"2^16", "0x7fff");
71   end rand;
72
73   procedure get_rand(r OUT number) is
74   begin
75     r := rand;
76   end get_rand;
77
78   function rand_max(n IN number) return number is
79   begin
80     return mod(rand, n) + 1;
81   end rand_max;
82
83   procedure get_rand_max(r OUT number, n IN number) is
84   begin
85     r := rand_max(n);
86   end get_rand_max;
87
88begin
89   select userenv('SESSIONID')
90   into   Seed
91   from   dual;
92end random;
93/
94
95-- Some examples:
96select random.rand_max(10) from dual;
97select random.rand_max(10) from dual;
98select random.rand_string(20) from dual;
99select random.rand_string(20) from dual;
100
101