前段时间做给联通做了一个考评系统,是一个二级部门用于员工之间年度打分的。做完之后要求每个员工还自动生成一个密码,生成的密码谁也不知道,让局方去plsql上一点就成生,然后再打印出来 ,让每个经理发给员工。密码要示是8位,还得有字母和数据。不是很难。于是我在sql/sql上写了一个函数
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
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'[abcdefghijklmnpqrstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ]';
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;
直接写然后在写个存储过程调用就可以了 如下
CREATE OR REPLACE PROCEDURE P_UPDATEUSERSPWD IS
empno VARCHAR2(64);
CURSOR U_EMPNO IS
select t.empno from Ô±¹¤±í t;
BEGIN
OPEN U_EMPNO;
LOOP
FETCH U_EMPNO
INTO empno;
EXIT WHEN U_EMPNO%NOTFOUND;
update Ô±¹¤±í t
set t.userpwd = **RANDOM_PASSWORD**('LNLNLNLN')
where t.empno = empno;
END LOOP;
CLOSE U_EMPNO;
COMMIT;
end P_UPDATEUSERSPWD;
不过这样写还是复杂了 不如直接调用oracle本身自带的函数就能实现要求,还挺快:
select dbms_random.string(‘x’,8) “U_8_password” from dual;