【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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 案例四：Shell指令碼生成隨機密碼指令碼隨機密碼
- 隨機生成使用者資訊（號碼、名字）隨機
- Linux 系統生成隨機密碼的10種方法Linux隨機密碼
- Linux 生成隨機密碼Linux隨機密碼
- JS生成隨機密碼JS隨機密碼
- 幾種生成隨機數方法隨機
- numpy各種生成隨機數的方法隨機
- 短視訊開發，生成隨機的驗證碼數字隨機
- MySQL建立隨機生成電話的儲存函式MySql隨機儲存函式
- 如何在linux中建立特定的指令碼註釋資訊Linux指令碼
- php生成唯一隨機碼PHP隨機
- 基於隨機定位的地圖資訊獲取方式隨機地圖
- shell指令碼整合json數值輸出從而獲取硬體資訊指令碼JSON
- ArcGIS如何自動獲得隨機取樣點？隨機
- Linux系統自動隨機生成複雜密碼方法Linux隨機密碼
- iOS使用指令碼跟隨工程程式碼動態生成FrameworkiOS指令碼Framework
- Django Models隨機獲取指定數量資料方法Django隨機
- LLIconVersioning-一個可以建立app版本資訊icon的指令碼APP指令碼
- ABAP 從檢視獲得資料
- 應用：隨機生成驗證碼隨機
- C# 生成隨機數，呼叫Random方法C#隨機random
- Java從List中獲取隨機元素Java隨機
- vbs指令碼獲取Am註冊路徑資訊指令碼
- SQL Server映象自動生成指令碼方法SQLServer指令碼
- Java之獲取隨機數的4種方法Java隨機
- Linux系統生成隨機密碼的8種方法！Linux運維課程Linux隨機密碼運維
- python生成隨機數、隨機字串Python隨機字串
- 直播軟體開發，JS生成隨機字串的方法JS隨機字串
- 偽隨機數是什麼？偽隨機數生成方法有哪些？隨機
- 在 WordPress 中排隊 CSS 和 JS 指令碼以獲得更好的效能CSSJS指令碼
- 從Linux核心中獲取真隨機數Linux隨機
- 一個獲得投資人青睞的方法
- Android的CameraX獲得相機支援解析度的方法Android
- [20221216]建立修改表統計資訊minmaxtab.sql指令碼.txtSQL指令碼
- [20221216]建立修改表統計資訊modtab.sql指令碼.txtSQL指令碼
- Python如何隨機生成1到100的隨機數?Python隨機
- redolog生成指令碼指令碼
- php生成一個可選位數的隨機碼PHP隨機
- 隨機生成一個指定長度的驗證碼隨機