oracle生成随机数函数

来源：互联网发布：手机淘宝免费模板网编辑：程序博客网时间：2024/04/28 12:51

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

函数体如下：

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;