오라클에서 난수 발생하기

 

오라클에서 난수 발생하기

 

Below are some examples of using the package.

E.g.: Generating a random number (positive or negative)

SQL> select dbms_random.random from dual;


RANDOM
_____________
1393936551

 

---

E.g.: Generating a random number between 0 and 1.

SQL> select dbms_random.value from dual;

VALUE
_____________
1

 

---

E.g.: Generating a random number from a range, between 1 to 1000.

SQL> select dbms_random.value(1,1000) num from dual;

NUM
_____________
611

 

---

E.g.: Generating a 12 digit random number.

SQL> select dbms_random.value(100000000000, 999999999999) num from dual;

NUM
_____________
175055628780

 

---

E.g.: Generating an upper case string of 20 characters

SQL> select dbms_random.string('U', 20) str from dual;

STR
_______________________
VUOQOSTLHCKIPIADIZTD

 

---

E.g.: Generating a lower case string of 20 characters

SQL> select dbms_random.string('L', 20) str from dual;

STR
____________________
xpoovuspmehvcptdtzcz

 

---

E.g.: Generating an alphanumeric string of 20 characters. There is a bug in Oracle 8i that results in special (non-alphanumeric) characters such as ']' in the string. This is resolved in Oracle 9i.

SQL> select dbms_random.string('A', 20) str from dual;

STR
__________________
sTjERojjL^OlTaIc]PLB

 

---

E.g.: Generating an upper case alphanumeric string of 20 characters

SQL> select dbms_random.string('X', 20) str from dual;

STR
________________________
SQ3E3B3NRBIP:GOGAKSC

 

---

E.g.: Generating a string of printable 20 characters. This will output a string of all characters that could possibly be printed.

SQL> select dbms_random.string('P', 20) str from dual;

STR
___________________
*Yw>IKzsj\uI8K[IQPag

 

---

E.g.: Example for calling the dbms_random package and setting the seed for generating the same set of random numbers in different sessions. Please note that the same random numbers are generated in different sessions. Though I have found this to work on most accounts, in some cases, the first number generated was different in different sessions and the remaining were same. I recommend not using this option in any of production code until it is properly 　document by Oracle.

jaJA>declare
2     l_num    number;
3  begin   
4    l_num := dbms_random.random;
5    dbms_output.put_line(l_num);   
6    dbms_random.seed('amar testing 67890');   
7    l_num := dbms_random.random;   
8    dbms_output.put_line(l_num);   
9  end;  
10  / 483791552 478774329  PL/SQL procedure successfully completed.

 

---

* 통계분석연구회 :

http://cafe.daum.net/statsas