oracle外部密码存储过程,Oracle中存储过程产生16位卡号和1-13密码

--===================卡号GGC0000000000001---16位--密码1-13位

create sequence ggcard_seq minvalue 1 maxvalue 9999999999999 start with 1 increment by 1 ;

create or replace procedure ggcard_proc (beginNum number,endNum number)

is

type tempNum_table is table of number index by binary_integer;

type tempStr_table is table of varchar2(20) index by binary_integer;

type cardPwd_table is table of varchar2(18) index by binary_integer;

zeroStr constant varchar(13):='0000000000000';

tempNum tempNum_table;

tempStr tempStr_table;

cardPwd cardPwd_table;

begin

for i in beginNum..endNum

loop

select round(dbms_random.value (1,10000000000000)) into cardPwd(i) from dual;---随机密码

select ggcard_seq.nextval into tempNum(i) from dual;

if length(to_char(tempNum(i)))<13 then

tempStr(i):=substr(zeroStr,0,13-length(to_char(tempNum(i))));

else

tempStr(i):=to_char(tempNum(i));

end if;

tempStr(i):='GGC'||tempStr(i)||tempNum(i);

-- dbms_output.put_line(cardPwd(i)||'--'||tempStr(i));

end loop;

FORALL i in beginNum..endNum

insert into GGCardInfo(GGCardID,GGCardPWD,Stauts) values(tempStr(i),cardPwd(i),1);

-- commit;

end;