【DBMS_RANDOM】從 DBMS_RANDOM建立指令碼獲得隨機資訊的生成方法
1.使用desc命令檢視dbms_random包的內容
sys@ora10g> desc dbms_random
PROCEDURE INITIALIZE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
VAL BINARY_INTEGER IN
FUNCTION NORMAL RETURNS NUMBER
FUNCTION RANDOM RETURNS BINARY_INTEGER
PROCEDURE SEED
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
VAL BINARY_INTEGER IN
PROCEDURE SEED
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
VAL VARCHAR2 IN
FUNCTION STRING RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OPT CHAR IN
LEN NUMBER IN
PROCEDURE TERMINATE
FUNCTION VALUE RETURNS NUMBER
FUNCTION VALUE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOW NUMBER IN
HIGH NUMBER IN
2.關於該包的建立指令碼
CREATE OR REPLACE PACKAGE BODY SYS.dbms_random AS
mem num_array; -- big internal state hidden from the user
counter BINARY_INTEGER := 55;-- counter through the results
saved_norm NUMBER := NULL; -- unused random normally distributed value
need_init BOOLEAN := TRUE; -- do we still need to initialize
-- Seed the random number generator with a binary_integer
PROCEDURE seed(val IN BINARY_INTEGER) IS
BEGIN
seed(TO_CHAR(val));
END seed;
-- Seed the random number generator with a string.
PROCEDURE seed(val IN VARCHAR2) IS
junk VARCHAR2(2000);
piece VARCHAR2(20);
randval NUMBER;
mytemp NUMBER;
j BINARY_INTEGER;
BEGIN
need_init := FALSE;
saved_norm := NULL;
counter := 0;
junk := TO_SINGLE_BYTE(val);
FOR i IN 0..54 LOOP
piece := SUBSTR(junk,1,19);
randval := 0;
j := 1;
-- convert 19 characters to a 38-digit number
FOR j IN 1..19 LOOP
randval := 1e2*randval + NVL(ASCII(SUBSTR(piece,j,1)),0.0);
END LOOP;
-- try to avoid lots of zeros
randval := randval*1e-38+i*.01020304050607080910111213141516171819;
mem(i) := randval - TRUNC(randval);
-- we've handled these first 19 characters already; move on
junk := SUBSTR(junk,20);
END LOOP;
randval := mem(54);
FOR j IN 0..10 LOOP
FOR i IN 0..54 LOOP
-- barrelshift mem(i-1) by 24 digits
randval := randval * 1e24;
mytemp := TRUNC(randval);
randval := (randval - mytemp) + (mytemp * 1e-38);
-- add it to mem(i)
randval := mem(i)+randval;
IF (randval >= 1.0) THEN
randval := randval - 1.0;
END IF;
-- record the result
mem(i) := randval;
END LOOP;
END LOOP;
END seed;
-- give values to the user
-- Delayed Fibonacci, pilfered from Knuth volume 2
FUNCTION value RETURN NUMBER IS
randval NUMBER;
BEGIN
counter := counter + 1;
IF counter >= 55 THEN
-- initialize if needed
IF (need_init = TRUE) THEN
seed(TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS') ||
USER || USERENV('SESSIONID'));
ELSE
-- need to generate 55 more results
FOR i IN 0..30 LOOP
randval := mem(i+24) + mem(i);
IF (randval >= 1.0) THEN
randval := randval - 1.0;
END IF;
mem(i) := randval;
END LOOP;
FOR i IN 31..54 LOOP
randval := mem(i-31) + mem(i);
IF (randval >= 1.0) THEN
randval := randval - 1.0;
END IF;
mem(i) := randval;
END LOOP;
END IF;
counter := 0;
END IF;
RETURN mem(counter);
END value;
-- Random 38-digit number between LOW and HIGH.
FUNCTION value ( low in NUMBER, high in NUMBER) RETURN NUMBER is
BEGIN
RETURN (value*(high-low))+low;
END value;
-- Random numbers in a normal distribution.
-- Pilfered from Knuth volume 2.
FUNCTION normal RETURN NUMBER is -- 38 decimal places: Mean 0, Variance 1
v1 NUMBER;
v2 NUMBER;
r2 NUMBER;
fac NUMBER;
BEGIN
IF saved_norm is not NULL THEN -- saved from last time
v1 := saved_norm; -- to be returned this time
saved_norm := NULL;
ELSE
r2 := 2;
-- Find two independent uniform. variables
WHILE r2 > 1 OR r2 = 0 LOOP
v1 := value();
v1 := v1 + v1 - 1;
v2 := value();
v2 := v2 + v2 - 1;
r2 := v1*v1 + v2*v2; -- r2 is radius
END LOOP; -- 0 < r2 <= 1: in unit circle
/* Now derive two independent normally-distributed variables */
fac := sqrt(-2*ln(r2)/r2);
v1 := v1*fac; -- to be returned this time
saved_norm := v2*fac; -- to be saved for next time
END IF;
RETURN v1;
END normal;
-- Random string. Pilfered from Chris Ellis.
FUNCTION string (opt char, len NUMBER)
RETURN VARCHAR2 is -- string of characters
optx char (1) := lower(opt);
rng NUMBER;
n BINARY_INTEGER;
ccs VARCHAR2 (128); -- candidate character subset
xstr VARCHAR2 (4000) := NULL;
BEGIN
IF ptx = 'u' THEN -- upper case alpha characters only
ccs := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
rng := 26;
ELSIF ptx = 'l' THEN -- lower case alpha characters only
ccs := 'abcdefghijklmnopqrstuvwxyz';
rng := 26;
ELSIF ptx = 'a' THEN -- alpha characters only (mixed case)
ccs := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' ||
'abcdefghijklmnopqrstuvwxyz';
rng := 52;
ELSIF ptx = 'x' THEN -- any alpha-numeric characters (upper)
ccs := '0123456789' ||
'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
rng := 36;
ELSIF ptx = 'p' THEN -- any printable char (ASCII subset)
ccs := ' !"#$%&''()*+,-./' || '0123456789' || ':;<=>?@' ||
'ABCDEFGHIJKLMNOPQRSTUVWXYZ' || '[\]^_`' ||
'abcdefghijklmnopqrstuvwxyz' || '{|}~' ;
rng := 95;
ELSE
ccs := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
rng := 26; -- default to upper case
END IF;
FOR i IN 1 .. least(len,4000) LOOP
/* Get random integer within specified range */
n := TRUNC(rng * value) + 1;
/* Append character to string */
xstr := xstr || SUBSTR(ccs,n,1);
END LOOP;
RETURN xstr;
END string;
-- For compatibility with 8.1
PROCEDURE initialize(val IN BINARY_INTEGER) IS
BEGIN
seed(to_char(val));
END initialize;
-- For compatibility with 8.1
-- Random binary_integer, -power(2,31) <= Random < power(2,31)
-- Delayed Fibonacci, pilfered from Knuth volume 2
FUNCTION random RETURN BINARY_INTEGER IS
BEGIN
RETURN TRUNC(Value*4294967296)-2147483648;
END random;
-- For compatibility with 8.1
PROCEDURE terminate IS
BEGIN
NULL;
END terminate;
END dbms_random;
/
3.從建立過程中抽取出有關的函式和儲存過程
FUNCTION value RETURN NUMBER IS
FUNCTION value ( low in NUMBER, high in NUMBER) RETURN NUMBER is
FUNCTION normal RETURN NUMBER is -- 38 decimal places: Mean 0, Variance 1
FUNCTION string (opt char, len NUMBER)
FUNCTION random RETURN BINARY_INTEGER IS
PROCEDURE seed(val IN BINARY_INTEGER) IS
PROCEDURE seed(val IN VARCHAR2) IS
PROCEDURE initialize(val IN BINARY_INTEGER) IS
PROCEDURE terminate IS
可以從上述抽取出來的資訊中，可以快速的得到具體函式和儲存過程需要的引數資訊。這些資訊與DESC獲得的資訊相結合，可以比較便捷的獲得隨機資訊語句的語法。這也是一個自救的過程。
4.小結
這裡沒有給出dbms_random包的具體使用方法，Oracle使用這個包實現了隨機資訊的生成。
這裡給出的一個思路：從源頭去理解功能的實現方法，我們會得到的更多。
Good luck.
secooler
10.09.15
-- The End --
sys@ora10g> desc dbms_random
PROCEDURE INITIALIZE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
VAL BINARY_INTEGER IN
FUNCTION NORMAL RETURNS NUMBER
FUNCTION RANDOM RETURNS BINARY_INTEGER
PROCEDURE SEED
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
VAL BINARY_INTEGER IN
PROCEDURE SEED
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
VAL VARCHAR2 IN
FUNCTION STRING RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OPT CHAR IN
LEN NUMBER IN
PROCEDURE TERMINATE
FUNCTION VALUE RETURNS NUMBER
FUNCTION VALUE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LOW NUMBER IN
HIGH NUMBER IN
2.關於該包的建立指令碼
CREATE OR REPLACE PACKAGE BODY SYS.dbms_random AS
mem num_array; -- big internal state hidden from the user
counter BINARY_INTEGER := 55;-- counter through the results
saved_norm NUMBER := NULL; -- unused random normally distributed value
need_init BOOLEAN := TRUE; -- do we still need to initialize
-- Seed the random number generator with a binary_integer
PROCEDURE seed(val IN BINARY_INTEGER) IS
BEGIN
seed(TO_CHAR(val));
END seed;
-- Seed the random number generator with a string.
PROCEDURE seed(val IN VARCHAR2) IS
junk VARCHAR2(2000);
piece VARCHAR2(20);
randval NUMBER;
mytemp NUMBER;
j BINARY_INTEGER;
BEGIN
need_init := FALSE;
saved_norm := NULL;
counter := 0;
junk := TO_SINGLE_BYTE(val);
FOR i IN 0..54 LOOP
piece := SUBSTR(junk,1,19);
randval := 0;
j := 1;
-- convert 19 characters to a 38-digit number
FOR j IN 1..19 LOOP
randval := 1e2*randval + NVL(ASCII(SUBSTR(piece,j,1)),0.0);
END LOOP;
-- try to avoid lots of zeros
randval := randval*1e-38+i*.01020304050607080910111213141516171819;
mem(i) := randval - TRUNC(randval);
-- we've handled these first 19 characters already; move on
junk := SUBSTR(junk,20);
END LOOP;
randval := mem(54);
FOR j IN 0..10 LOOP
FOR i IN 0..54 LOOP
-- barrelshift mem(i-1) by 24 digits
randval := randval * 1e24;
mytemp := TRUNC(randval);
randval := (randval - mytemp) + (mytemp * 1e-38);
-- add it to mem(i)
randval := mem(i)+randval;
IF (randval >= 1.0) THEN
randval := randval - 1.0;
END IF;
-- record the result
mem(i) := randval;
END LOOP;
END LOOP;
END seed;
-- give values to the user
-- Delayed Fibonacci, pilfered from Knuth volume 2
FUNCTION value RETURN NUMBER IS
randval NUMBER;
BEGIN
counter := counter + 1;
IF counter >= 55 THEN
-- initialize if needed
IF (need_init = TRUE) THEN
seed(TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS') ||
USER || USERENV('SESSIONID'));
ELSE
-- need to generate 55 more results
FOR i IN 0..30 LOOP
randval := mem(i+24) + mem(i);
IF (randval >= 1.0) THEN
randval := randval - 1.0;
END IF;
mem(i) := randval;
END LOOP;
FOR i IN 31..54 LOOP
randval := mem(i-31) + mem(i);
IF (randval >= 1.0) THEN
randval := randval - 1.0;
END IF;
mem(i) := randval;
END LOOP;
END IF;
counter := 0;
END IF;
RETURN mem(counter);
END value;
-- Random 38-digit number between LOW and HIGH.
FUNCTION value ( low in NUMBER, high in NUMBER) RETURN NUMBER is
BEGIN
RETURN (value*(high-low))+low;
END value;
-- Random numbers in a normal distribution.
-- Pilfered from Knuth volume 2.
FUNCTION normal RETURN NUMBER is -- 38 decimal places: Mean 0, Variance 1
v1 NUMBER;
v2 NUMBER;
r2 NUMBER;
fac NUMBER;
BEGIN
IF saved_norm is not NULL THEN -- saved from last time
v1 := saved_norm; -- to be returned this time
saved_norm := NULL;
ELSE
r2 := 2;
-- Find two independent uniform. variables
WHILE r2 > 1 OR r2 = 0 LOOP
v1 := value();
v1 := v1 + v1 - 1;
v2 := value();
v2 := v2 + v2 - 1;
r2 := v1*v1 + v2*v2; -- r2 is radius
END LOOP; -- 0 < r2 <= 1: in unit circle
/* Now derive two independent normally-distributed variables */
fac := sqrt(-2*ln(r2)/r2);
v1 := v1*fac; -- to be returned this time
saved_norm := v2*fac; -- to be saved for next time
END IF;
RETURN v1;
END normal;
-- Random string. Pilfered from Chris Ellis.
FUNCTION string (opt char, len NUMBER)
RETURN VARCHAR2 is -- string of
optx char (1) := lower(opt);
rng NUMBER;
n BINARY_INTEGER;
ccs VARCHAR2 (128); -- candidate character subset
xstr VARCHAR2 (4000) := NULL;
BEGIN
IF ptx = 'u' THEN -- upper case alpha characters only
ccs := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
rng := 26;
ELSIF ptx = 'l' THEN -- lower case alpha characters only
ccs := 'abcdefghijklmnopqrstuvwxyz';
rng := 26;
ELSIF ptx = 'a' THEN -- alpha characters only (mixed case)
ccs := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' ||
'abcdefghijklmnopqrstuvwxyz';
rng := 52;
ELSIF ptx = 'x' THEN -- any alpha-numeric characters (upper)
ccs := '0123456789' ||
'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
rng := 36;
ELSIF ptx = 'p' THEN -- any printable char (ASCII subset)
ccs := ' !"#$%&''()*+,-./' || '0123456789' || ':;<=>?@' ||
'ABCDEFGHIJKLMNOPQRSTUVWXYZ' || '[\]^_`' ||
'abcdefghijklmnopqrstuvwxyz' || '{|}~' ;
rng := 95;
ELSE
ccs := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
rng := 26; -- default to upper case
END IF;
FOR i IN 1 .. least(len,4000) LOOP
/* Get random integer within specified range */
n := TRUNC(rng * value) + 1;
/* Append character to string */
xstr := xstr || SUBSTR(ccs,n,1);
END LOOP;
RETURN xstr;
END string;
-- For compatibility with 8.1
PROCEDURE initialize(val IN BINARY_INTEGER) IS
BEGIN
seed(to_char(val));
END initialize;
-- For compatibility with 8.1
-- Random binary_integer, -power(2,31) <= Random < power(2,31)
-- Delayed Fibonacci, pilfered from Knuth volume 2
FUNCTION random RETURN BINARY_INTEGER IS
BEGIN
RETURN TRUNC(Value*4294967296)-2147483648;
END random;
-- For compatibility with 8.1
PROCEDURE terminate IS
BEGIN
NULL;
END terminate;
END dbms_random;
/
3.從建立過程中抽取出有關的函式和儲存過程
FUNCTION value RETURN NUMBER IS
FUNCTION value ( low in NUMBER, high in NUMBER) RETURN NUMBER is
FUNCTION normal RETURN NUMBER is -- 38 decimal places: Mean 0, Variance 1
FUNCTION string (opt char, len NUMBER)
FUNCTION random RETURN BINARY_INTEGER IS
PROCEDURE seed(val IN BINARY_INTEGER) IS
PROCEDURE seed(val IN VARCHAR2) IS
PROCEDURE initialize(val IN BINARY_INTEGER) IS
PROCEDURE terminate IS
可以從上述抽取出來的資訊中，可以快速的得到具體函式和儲存過程需要的引數資訊。這些資訊與DESC獲得的資訊相結合，可以比較便捷的獲得隨機資訊語句的語法。這也是一個自救的過程。
4.小結
這裡沒有給出dbms_random包的具體使用方法，Oracle使用這個包實現了隨機資訊的生成。
這裡給出的一個思路：從源頭去理解功能的實現方法，我們會得到的更多。
Good luck.
secooler
10.09.15
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-674183/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【DBMS_RANDOM】使用 DBMS_RANDOM包生成隨機字串random隨機字串
- dbms_random包呼叫隨機數的方法：random隨機
- oracle隨機數 — dbms_randomOracle隨機random
- dbms_randomrandom
- DBMS_RANDOM使用random
- Oracle系統包dbms_randomOraclerandom
- oracle系統包——dbms_random用法Oraclerandom
- 【SQL】如何獲得表及欄位的說明資訊（comment）的建立指令碼SQL指令碼
- dbms_random在Oracle 10.2.0.4下的bugrandomOracle
- 案例四：Shell指令碼生成隨機密碼指令碼隨機密碼
- ruby指令碼，隨機生成複雜密碼指令碼隨機密碼
- 使用DBMS_RANDOM過程引發的問題random
- PHP生成隨機密碼的幾種方法PHP隨機密碼
- 隨機生成使用者資訊（號碼、名字）隨機
- 生成隨機整數方法隨機
- Python隨機生成驗證碼的兩種方法Python隨機
- python生成隨機數的方法Python隨機
- powershell 生成隨機使用者資訊隨機
- 獲得ip地理資訊的幾種方法【最全】
- javascript隨機生成字串，可以用來生成隨機密碼JavaScript隨機字串密碼
- 獲得Azure訂閱LoadBalancer的指令碼指令碼
- JS生成隨機密碼JS隨機密碼
- Linux 系統生成隨機密碼的10種方法Linux隨機密碼
- 八種在 Linux 上生成隨機密碼的方法Linux隨機密碼
- Linux 命令列生成隨機密碼的十種方法Linux命令列隨機密碼
- 幾種生成隨機數方法隨機
- numpy各種生成隨機數的方法隨機
- 見過最全的獲取資料庫資訊的指令碼--生成html的報告資料庫指令碼HTML
- 利用 Linux 系統生成隨機密碼的 10 種方法Linux隨機密碼
- PHP生成隨機密碼的4種方法及效能對比PHP隨機密碼
- 利用Linux系統生成隨機密碼的10種方法Linux隨機密碼
- python獲得本機硬體資訊Python
- Linux 生成隨機密碼Linux隨機密碼
- php生成唯一隨機碼PHP隨機
- 使用mkpasswd生成隨機密碼隨機密碼
- ArcGIS如何自動獲得隨機取樣點？隨機
- Python隨機數生成方法Python隨機
- .NET Interop: 從IErrorInfo錯誤物件獲得託管程式碼的異常資訊Error物件