oracle生成随机数函数

我们做编程，惯性的思维就是把生成随机数交给程序的业务层来做。很少考虑到把生成随机数的任务交给数据库去做。今天在网上看到一篇用数据库生成的随机数函数，写的很好，就转过来大家一起学习学习。

函数体如下：

CREATE OR REPLACE FUNCTION RANDOM_PASSWORD (IN_TEMPLATE IN VARCHAR2) 
 RETURN VARCHAR2 IS
 LC$CRITERIA VARCHAR2(1); 
 LC$PASSWORD VARCHAR2(500); 
 LC$PATTERN VARCHAR2(500); 
 LN$INDX NUMBER; 
 BEGIN
 /*1-Character should be UPPERCASE     =====> Abbreviation [U] 
 2- Character should be LOWERCASE      =====> Abbreviation [L] 
 3- Character should be NUMBER         =====> Abbreviation [N] 
 4- Character should be any character     =====> Abbreviation [A] 
 5- Character should be NON-ALPHANUMERIC character =====> Abbreviation [S]*/ 
   LC$CRITERIA := ''; 
   LC$PASSWORD := ''; 
   FOR I IN 1.. LENGTH(IN_TEMPLATE) LOOP 
     LC$CRITERIA := SUBSTR(IN_TEMPLATE,I,1); 
     IF UPPER(LC$CRITERIA ) = 'U' THEN 
       LC$PATTERN := q'[ABCDEFGHIJKLMNOPQRSTUVWXYZ]'; 
     ELSIF UPPER(LC$CRITERIA ) = 'L' THEN 
        LC$PATTERN := q'[abcdefghijklmnopqrstuvwxyz]'; 
     ELSIF UPPER(LC$CRITERIA ) = 'N' THEN 
        LC$PATTERN := q'[0123456789]'; 
     ELSIF UPPER(LC$CRITERIA ) = 'A' THEN 
        LC$PATTERN := q'[0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]'; 
     ELSIF UPPER(LC$CRITERIA ) = 'S' THEN 
        LC$PATTERN := q'[~!@#$%^&*()_+-}{|":;?.,<>[]/\]'; 
     ELSE
        LC$PATTERN := q'[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789]'; 
     END IF; 
     LN$INDX := TRUNC( LENGTH(LC$PATTERN) * DBMS_RANDOM.VALUE) + 1; 
     LC$PASSWORD := LC$PASSWORD || SUBSTR(LC$PATTERN,LN$INDX,1); 
   END LOOP; 
   RETURN LC$PASSWORD;
END RANDOM_PASSWORD;

使用时你可以这样调用：

 RANDOM_PASSWORD('ULNASN');

举个例子：

 SELECT RANDOM_PASSWORD ('ULNASN') FROM DUAL;