使用PL/SQL產生隨機密碼，plsql產生

使用PL/SQL產生隨機密碼，plsql產生
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

本文地址：http://blog.csdn.net/sunansheng/article/details/46328941