Oracle random number and random sampling | Develop Paper
Oracle random number and random sampling
1 Take random number
OralceAll operations on random numbers are encapsulated in thePL/SQLpackageDBMS_RANDOMIt is very convenient for us to use.It has the follow function s:
Among them, initialize, random, terminateFunction inOracle11gIt is not recommended in, but chiefly used for back compatibility. The stick to is an exemplar of each function。
1.1 INITIALIZE
Initialize with a seed valueDBMS_RANDOMBag .
By default, DBMS_RANDOMThe software is initialized according to the drug user, time and school term. In this way, tied for the lapp argument, the values generated each prison term will be different. however, this will cause a trouble. In the test environment, if I want to generate the same random sequence each time, what should I do ? INITIALIZE work By setting the same seed prize, the random sequence generated each fourth dimension will be the lapp .
grammar :
DBMS_RANDOM.INITIALIZE (val IN BINARY_INTEGER);
give an exemplar :
BEGIN
DBMS_RANDOM.INITIALIZE(100);
FOR I IN 1 .. 10 LOOP
DBMS_OUTPUT.PUT_LINE(DBMS_RANDOM.RANDOM);
END LOOP;
END;
View Code ————————–
163284779
751599369
659804475
1131809137
-865013504
-407075626
2128226600
-448154892
-1371178596
472933400
even in different sessions, different users, randomly generated10All the values are the same .
1.2 NORMAL
NORMALFunction returns a set of numbers that are normally distributed. The standard deviation of the normal distribution is1The expected rate is0. The prize returned by this routine contains68 % Is between-1And+1between,95 % be situated between-2And+2between,99 % be situated between-3And+3between .
grammar :
DBMS_RANDOM.NORMAL RETURN NUMBER;
give an exemplar :
SELECT DBMS_RANDOM.NORMAL FROM DUAL;
————–
0.321082787751054
1.3 RANDOM
The range of return values is : [ -2^31, 2^31 ), returns an integer .
grammar :
DBMS_RANDOM.RANDOM RETURN binary_integer;
give an case :
SELECT DBMS_RANDOM.RANDOM FROM DUAL;
————-
1632572475
SELECT ABS(MOD(DBMS_ RANDOM.RANDOM, 100)) from dual; -- get random integers from 0 to 100
————–
51
1.4 SEED
Functions andInitiate processSimilar, in fact, Initiate processElimination, recommended substitutionprocessThat isSeed store procedure, AndInitiate processThe difference is thatSeed processBoth numeric and fictional character values are supported as seed values, whileInitiate processOnly numeric values are supported .
grammar :
DBMS_RANDOM.SEED (val IN BINARY_INTEGER);
DBMS_ RANDOM.SEED (Val in VARCHAR2); -- the maximum range of VARCHAR2 is 2000
give an model :
BEGIN
DBMS_RANDOM.SEED('hello');
FOR I IN 1 .. 10 LOOP
DBMS_OUTPUT.PUT_LINE(DBMS_RANDOM.VALUE);
END LOOP;
END;
View Code
Read more: How to Set Up and Use ‘Find My’ on Mac
——————-
58
71
33
4
39
53
93
37
20
5
5. STRING
randomly generated string,Grammar :
DBMS_RANDOM.STRING(
Format of opt in char, - string
Len in number -- length of string
) RETURN VARCHAR2;
–opt The format of the string:
– ‘ uranium ’ or ‘ U ’ : a string of capital letters
– ‘ lambert ’ or ‘ L ’ : string of small letter letters
– ‘ a ’ or ‘ A ’ : any subject insensitive string
– ‘ x ’ or ‘ X ’ : a string of letters or numbers in any case
– ‘ p ’ or ‘ P ’ : any array of output characters
give an exercise :
SELECT DBMS_RANDOM.STRING('u', 10) VALUE FROM DUAL;
--------------------
PSXFAKZZTR
SELECT DBMS_RANDOM.STRING('l', 10) VALUE FROM DUAL;
--------------------
elnircffly
SELECT DBMS_RANDOM.STRING('a', 10) VALUE FROM DUAL;
-------------------
vGuYnPoZNk
SELECT DBMS_RANDOM.STRING('x', 10) VALUE FROM DUAL;
--------------------
LH7Q36NLPR
SELECT DBMS_RANDOM.STRING('p', 10) VALUE FROM DUAL;
--------------------
IuX4B8lQ9p
View Code
6. TERMINATE
After useDBMS_RANDOMAfter packing material, use theprocessTo end. Theprocessstay11gR1It is not recommended to use .
grammar :
DBMS_RANDOM.TERMINATE;--For compatibility with 8.1
7. VALUE
This serve is most normally used, and its grammar by:
DBMS_RANDOM.VALUE
Return number; -- [0,1], with 38 decimal places
DBMS_RANDOM.VALUE(
Low in number, - Minimum
High in number -- Maximum
) RETURN NUMBER;
give an example :
SELECT DBMS_RANDOM.VALUE FROM DUAL;
----------------------
0.452943599091639
SELECT DBMS_RANDOM.VALUE(10, 20) FROM DUAL;
------------------------
18.4659055244849
2 Random data
OracleGenerally, there are two methods of random data collection
2.1 Fast random data acquisition
useoracleOfsample ( [ sample_percent ] ) orsample bloc ( sample_percent ) Methods ( sampling table scanning ) ( sample board scan ) ),Fast random data acquisition,Recommended:
SELECT * FROM EBILL_ ELECTRONIC_ Bill sample (1) where rownum < = 10; -- the parameter indicates the sampling percentage. By default, the first 10 items are selected
similarly, sample_block:
SELECT * FROM EBILL_ ELECTRONIC_ Bill sample block (1) where rownum < = 10; -- Sampling percentage of data storage interval of parameter table, required
We should pay attention to the follow points :
1.sample_percentIs a number that defines the percentage of records in the consequence set to the total number of records. The value should be in [ 0.000001,99.999999 ] between .
2.sampleIt is only effective for single table and can not be used for table connection and remote control postpone
3.sampleWill makeSQLAutomatic useCBO(Cost based optimizer )
Read more: A Few Thoughts on Cryptographic Engineering
2.2 Random data
Using DBMS_ random.valueTake data randomly and scan the whole table(Full board Scan), slow, not recommended :
SELECT *
FROM (SELECT * FROM EBILL_ELECTRONIC_BILL ORDER BY DBMS_RANDOM.VALUE)
WHERE ROWNUM <= 10