1.建Function程序
CREATE OR REPLACE FUNCTION random_password(in_template IN VARCHAR2) RETURN VARCHAR2 IS
l_criteria VARCHAR2(1);
l_password VARCHAR2(500);
l_pattern VARCHAR2(500);
l_indx NUMBER;
BEGIN
/*1-Character should be UPPERCASE =====> [U]
2- Character should be LOWERCASE =====> [L]
3- Character should be NUMBER =====> [N]
4- Character should be any character =====> [A]
5- Character should be NON-ALPHANUMERIC character =====> [S]*/
l_criteria := '';
l_password := '';
FOR i IN 1 .. length(in_template) LOOP
l_criteria := substr(in_template,
i,
1);
IF upper(l_criteria) = 'U' THEN
l_pattern := q'[ABCDEFGHIJKLMNOPQRSTUVWXYZ]';
ELSIF upper(l_criteria) = 'L' THEN
l_pattern := q'[abcdefghijklmnopqrstuvwxyz]';
ELSIF upper(l_criteria) = 'N' THEN
l_pattern := q'[0123456789]';
ELSIF upper(l_criteria) = 'A' THEN
l_pattern := q'[0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]';
ELSIF upper(l_criteria) = 'S' THEN
l_pattern := q'[~!@#$%^&*()_+-}{|":;?.,<>[]/\]';
ELSE
l_pattern := q'[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789]';
END IF;
l_indx := trunc(length(l_pattern) * dbms_random.value) + 1;
l_password := l_password || substr(l_pattern,
l_indx,
1);
END LOOP;
RETURN l_password;
END random_password;
2.用法
传入参数:
U代表随机一个大写字母
L代表随机一个小写字母
N代表随机一个数字
A代表任何字符
S代表任何特殊符号
例子1:
SQL> select random_password ('ulnasn') from dual;
RANDOM_PASSWORD('ULNASN')
--------------------------------------------------------------------------------
Hc67|8
SQL> select random_password ('99999999999') from dual;
RANDOM_PASSWORD('99999999999')
--------------------------------------------------------------------------------
agrXKng6bfe
本文地址:http://blog.csdn.net/sunansheng/article/details/46328941