create or replace procedure proc_create_pass(cnt number)
is
/**************************************
*生成18位密码的程序---cnt---要生成个数
*----20091116--houxt
**************************************/
err varchar2(80);
oncom varchar2(2);
rowcom number;
begin

rowcom:=0;
oncom:='1';
insert into tb_proc_log(proc_name,on_commit,com_date) values ('proc_create_pass',oncom||'start***********',sysdate);
commit;
------------------
oncom:='2';

--if rowcom <>
execute immediate 'insert into user_pass(pass_no,pass_word,cre_date,pass_state) '
||' select pass.nextval,aa.pass_word,sysdate,''1'' from '
||' (select distinct TRUNC(DBMS_RANDOM.VALUE(100000000000000000,999999999999999999)) as pass_word '
||' from all_source where rownum<='||cnt||'-'||rowcom||') aa left join user_pass b '
||' on aa.pass_word=b.pass_word '
||' where b.pass_word is null ';
rowcom:=rowcom+sql%rowcount;
commit;
-- end if;
if rowcom goto aa;
end if;
-----------------
insert into tb_proc_log(proc_name,on_commit,com_date) values ('proc_create_pass',oncom||'ok',sysdate);
commit;
exception when others then
err:=substr(sqlerrm,1,80);
insert into tb_proc_log(proc_name,on_commit,com_date) values ('proc_create_pass',err||'no',sysdate);
commit;
end;