使用PL/SQL生成随机password

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

 

样例2：

SQL> select random_password ('99999999999') from dual;
 
RANDOM_PASSWORD('99999999999')
--------------------------------------------------------------------------------
agrXKng6bfe