pls help with stored procedure

[learn]

hello,

I have 250 account numbers in a table (called ACCT_TAB). At any given point, I would like to issue a select statement that would give me 1 or more account numbers randomly.

In other words, I should be able to select 10 account numbers now and in 10 minutes select 15 account numbers, then in 20 minutes select 13 account numbers and so on. All in a random fashion.

e.g
select column1,column2 from ACCT_TAB where acct_num in (1,2,3,4,5,6,7,8,9,10)
select column1,column2 from ACCT_TAB where acct_num in (4,8,9,1,2,3,6,9,0,,12,45,20),etc.
But this way I would have to hard code it which I don't want to do.

I think the best way to do this is using a variable in a stored procedure but my PL/SQL is not strong.

Can anyone please give me and idea (syntax) on how to declare the variable and also how write the query to select from the variable?

Thanks,
Learn

[stecal]

How do you perform a random number generation?
How do you programmatically generate random numbers?

FROM ORACLE PL/SQL PROGRAMMING: CREATE PACKAGE AND PACKAGE BODY

Solution Description:
=====================

CREATE OR REPLACE PACKAGE Random AS
/* Random number generator. Uses the same algorithm as the
rand() function in C. */

-- Used to change the seed. From a given seed, the same
-- sequence of random numbers will be generated.
PROCEDURE ChangeSeed(p_NewSeed IN NUMBER);

-- Returns a random integer between 1 and 32767.
FUNCTION Rand RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES(rand, WNDS);

-- Same as Rand, but with a procedural interface.
PROCEDURE GetRand(p_RandomNumber OUT NUMBER);

-- Returns a random integer between 1 and p_MaxVal.
FUNCTION RandMax(p_MaxVal IN NUMBER) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES(RandMax, WNDS);

-- Same as RandMax, but with a procedural interface.
PROCEDURE GetRandMax(p_RandomNumber OUT NUMBER,
p_MaxVal IN NUMBER);
END Random;


CREATE OR REPLACE PACKAGE BODY Random AS
/* Used for calculating the next number. */
v_Multiplier CONSTANT NUMBER := 22695477;
v_Increment CONSTANT NUMBER := 1;

/* Seed used to generate random sequence. */
v_Seed NUMBER := 1;

PROCEDURE ChangeSeed(p_NewSeed IN NUMBER) IS
BEGIN
v_Seed := p_NewSeed;
END ChangeSeed;

FUNCTION Rand RETURN NUMBER IS
BEGIN
v_Seed := MOD(v_Multiplier * v_Seed + v_Increment, (2 ** 32));
RETURN BITAND(v_Seed/(2 ** 16), 32767);
END Rand;

PROCEDURE GetRand(p_RandomNumber OUT NUMBER) IS
BEGIN
-- Simply call Rand and return the value.
p_RandomNumber := Rand;
END GetRand;

FUNCTION RandMax(p_MaxVal IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN MOD(Rand, p_MaxVal) + 1;
END RandMax;

PROCEDURE GetRandMax(p_RandomNumber OUT NUMBER,
p_MaxVal IN NUMBER) IS
BEGIN
-- Simply call RandMax and return the value.
p_RandomNumber := RandMax(p_MaxVal);
END GetRandMax;

BEGIN
/* Package initialization. Initialize the seed to the current
time in seconds. */
ChangeSeed(T0_NUMBER(TO_CHAR(SYSDATE, 'SSSSS')));
END Random;


In order to retrieve a random number, you can simply call Random.Rand. The sequence of random numbers is controlled by the initial seed -- the same sequence is generated for a given seed. Thus, in order to provide more random values, we need to initialize the seed to a different value each time the package is instantiated. This is accomplished with a call to the ChangeSeed procedure from the package initialization section.


This excerpt is from the "Packages: Package Initialization" section in Chapter 5: Procedures, Functions, Packages, and Triggers of Oracle PL/SQL Programming by Scott Urman
published by the Oracle Press.