в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:55:22Z

1 версия

Новая страница

==Call dbms_random.normal to get a random number==



<source lang="sql">

SQL>
SQL>
SQL> begin
2 for i in 1 .. 10 loop
3 dbms_output.put_line( dbms_random.normal );
4 end loop;
5 end;
6 /
1.54614544732963133287654886045271895089
.4317388077546154281139738992526089097407
-.3932262942586239869251307222842090454052
-1.6670328488404622986050198363653775926
.6688556901515673452664296942201359807998
1.33788099021421741537616822012046180008
.4640589645668300518621566089428065420796
-.2593943569301190968371056213308217873704
-.928080380810723360417809948629051042821
.3610354349426925924563975682781230663073
PL/SQL procedure successfully completed.
SQL> --

</source>

==dbms_random.value==



<source lang="sql">

SQL>
SQL> set serverout on
SQL> exec dbms_output.put_line( dbms_random.value );
.02384074069949541946799357018112732833
PL/SQL procedure successfully completed.
SQL>
SQL> set serverout on
SQL> exec dbms_output.put_line( dbms_random.value );
.9204517146492220886865737108916710535
PL/SQL procedure successfully completed.
SQL> --

</source>

==Insert random numbers into a table==



<source lang="sql">

SQL>
SQL> create table t( c1 int, c2 int, c3 int, c4 int ) storage ( freelists 10 );
Table created.
SQL>
SQL>
SQL> set echo on
SQL>
SQL>
SQL> declare
2 myNumber number;
3 begin
4 for i in 1 .. 10
5 loop
6 myNumber := dbms_random.random;
7 insert into t values( myNumber, myNumber, myNumber, myNumber );
8 end loop;
9 commit;
10 end;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from t;
C1 C2 C3 C4
---------- ---------- ---------- ----------
-1.731E+09 -1.731E+09 -1.731E+09 -1.731E+09
-1.831E+09 -1.831E+09 -1.831E+09 -1.831E+09
1272757711 1272757711 1272757711 1272757711
291563057 291563057 291563057 291563057
1906157085 1906157085 1906157085 1906157085
180671850 180671850 180671850 180671850
1011541972 1011541972 1011541972 1011541972
-1.686E+09 -1.686E+09 -1.686E+09 -1.686E+09
-863840673 -863840673 -863840673 -863840673
1602415758 1602415758 1602415758 1602415758
10 rows selected.
SQL>
SQL> drop table t;
Table dropped.
SQL> --

</source>

==Insert random value to table==



<source lang="sql">

SQL>
SQL> create table emp (
2 id number(6) );
Table created.
SQL>
SQL> alter table emp
2 add constraint emp_pk
3 primary key (id);
Table altered.
SQL>
SQL>
SQL> create or replace procedure gen_emp is
2 v_new_cid emp.id%type;
3 begin
4 loop
5 begin
6 v_new_cid := round(dbms_random.value(1000000,9999999));
7 insert into emp values (v_new_cid);
8 exit;
9 exception when dup_val_on_index then
10 null;
11 end;
12 end loop;
13 end;
14 /
Procedure created.
SQL>
SQL>
SQL> drop table emp;
Table dropped.

</source>

==mod(abs(dbms_random.random),50000)+1==



<source lang="sql">

SQL>
SQL> create table random ( x int );
Table created.
SQL>
SQL> begin
2 for i in 1 .. 100000
3 loop
4 insert into random values
5 ( mod(abs(dbms_random.random),50000)+1 );
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> drop table random;
Table dropped.
SQL> --

</source>

==Random string with dbms_random.string==



<source lang="sql">

SQL> COLUMN mixed_up NEW_VALUE mixed_up_sub_var
SQL> SELECT dbms_random.string("X",30) mixed_up
2 FROM dual;
3X0P1I67MKLZM7934O6JGUJSE3Q9A2
1 row selected.
SQL>
SQL> DEFINE mixed_up_sub_var
DEFINE MIXED_UP_SUB_VAR = "3X0P1I67MKLZM7934O6JGUJSE3Q9A2" (CHAR)
SQL>
SQL>
SQL>

</source>

==ROUND a DBMS_RANDOM.VALUE==



<source lang="sql">

SQL>
SQL> CREATE OR REPLACE FUNCTION get_num (
2 p_highval NUMBER,
3 p_lowval NUMBER := 0,
4 p_scale PLS_INTEGER := 0
5 )
6 RETURN NUMBER
7 IS
8 l_ret NUMBER;
9 BEGIN
10 l_ret := ROUND (DBMS_RANDOM.VALUE (p_lowval, p_highval), p_scale);
11 RETURN l_ret;
12 END;
13 /
Function created.
SQL>
SQL> select get_num(5,2,2) from dual;
GET_NUM(5,2,2)
--------------
4.27

</source>

==Round a random value from dbms_random.value==



<source lang="sql">

SQL>
SQL>
SQL> begin
2 for i in 1 .. 10 loop
3 dbms_output.put_line( round( dbms_random.value*100 ) );
4 end loop;
5 end;
6 /
63
62
7
72
82
80
88
42
30
67
PL/SQL procedure successfully completed.
SQL>
SQL> --

</source>

==Seed a random value with dbms_random.seed==



<source lang="sql">

SQL>
SQL> set serverout on
SQL> exec dbms_random.seed( to_char( sysdate, "YYYYMMDD" ) );
PL/SQL procedure successfully completed.
SQL> exec dbms_output.put_line( dbms_random.value );
.09697957992149808336891757660029645577
PL/SQL procedure successfully completed.
SQL> exec dbms_output.put_line( dbms_random.value );
.07915501486927581930051571448998022468
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> set serverout on
SQL> exec dbms_random.seed( to_char( sysdate, "YYYYMMDD" ) );
PL/SQL procedure successfully completed.
SQL> exec dbms_output.put_line( dbms_random.value );
.09697957992149808336891757660029645577
PL/SQL procedure successfully completed.
SQL> exec dbms_output.put_line( dbms_random.value );
.07915501486927581930051571448998022468
PL/SQL procedure successfully completed.
SQL> --

</source>

==This script illustrates the use of the DBMS_RANDOM package.==



<source lang="sql">

SQL>
SQL> set serveroutput on
SQL>
First call RANDOM without initializing the seed. This will
raise ORA-1426.
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(DBMS_RANDOM.RANDOM);
3 END;
4 /
503520765
PL/SQL procedure successfully completed.
SQL>
Now initialize, and print out 10 random numbers.
SQL> BEGIN
2 DBMS_RANDOM.INITIALIZE(12345);
3 FOR v_Count IN 1..10 LOOP
4 DBMS_OUTPUT.PUT_LINE(DBMS_RANDOM.RANDOM);
5 END LOOP;
6 END;
7 /
-1817329670
-610488908
1775775349
-1974231734
-861483099
1044932800
1916512750
808190358
-1880806196
-1406274277
PL/SQL procedure successfully completed.
SQL>

</source>

==trunc a random value from dbms_random.value==



<source lang="sql">

SQL>
SQL> begin
2 for i in 1 .. 10 loop
3 dbms_output.put_line( trunc( dbms_random.value( 1, 101 ) ) );
4 end loop;
5 end;
6 /
15
17
28
52
25
19
55
35
18
48
PL/SQL procedure successfully completed.
SQL> --

</source>

==Use dbms_random.string to create random string==



<source lang="sql">

SQL>
SQL>
SQL> exec dbms_output.put_line( dbms_random.string( "A", 20 ) );
iRhhzYvyWLroMPNCvSdx
PL/SQL procedure successfully completed.
SQL> exec dbms_output.put_line( dbms_random.string( "x", 20 ) );
V5HN9YL3C6GJN9BNP4R9
PL/SQL procedure successfully completed.
SQL> exec dbms_output.put_line( dbms_random.string( "P", 20 ) );
w6aH% ZB[3bjH[;x;6h@
PL/SQL procedure successfully completed.
SQL> exec dbms_output.put_line( dbms_random.string( "l", 20 ) );
rjncmdlykahvpgjyecax
PL/SQL procedure successfully completed.
SQL> exec dbms_output.put_line( dbms_random.string( "u", 20 ) );
EHXUUTMYMBAJMHIKKDJM
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> --

</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/System Packages/DBMS RANDOM - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия