oracle 62进制序列号

最新推荐文章于 2021-04-13 01:35:39 发布

阮減显

最新推荐文章于 2021-04-13 01:35:39 发布

阅读量711

点赞数

分类专栏： database

本文为博主自创文章，不确保代码或者观点正确，如因引用文章中的代码或者观点造成的损失，恕不负责。

本文链接：https://blog.csdn.net/lewy7/article/details/50037207

版权

database 专栏收录该内容

30 篇文章 1 订阅

订阅专栏

create or replace function GetSerial62(v_lpad number default 0)
return varchar2
IS
v_tmp number(38,0);
v_str char(62);
v_result varchar2(128);
BEGIN
v_str := '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
v_tmp := SEQ_Serial_62.nextval;
v_result := '';

if v_lpad > 128 then
   return v_result;
end if;

while v_tmp <> 0  loop
    v_result := v_result||substr(v_str,mod(v_tmp,62)+1,1);
    v_tmp := trunc(v_tmp / 62);
end loop;

select reverse(v_result) into v_result from dual;
if v_lpad > length(v_result) then
     v_result := lpad(v_result,v_lpad,'0');
end if;

--DBMS_OUTPUT.PUT_LINE('====['||v_result||']==');
return v_result;
end GetSerial62;

create or replace function GetSerial62(v_serial number,v_lpad number default 0)
return varchar2
IS
v_tmp number(38,0);
v_number_tmp number(10);
v_char_tmp char(1);
v_result varchar2(128);
BEGIN

v_tmp := v_serial;
v_result := '';

if v_lpad > 128 then
   return v_result;
end if;

while v_tmp <> 0  loop
    v_number_tmp := mod(v_tmp,62);
    
    select decode(v_number_tmp,0 ,'0',1 ,'1',2 ,'2',3 ,'3',4 ,'4',5 ,'5',
                                      6 ,'6',7 ,'7',8 ,'8',9 ,'9',10,'a',11,'b',
                                      12,'c',13,'d',14,'e',15,'f',16,'g',17,'h',
                                      18,'i',19,'j',20,'k',21,'l',22,'m',23,'n',
                                      24,'o',25,'p',26,'q',27,'r',28,'s',29,'t',
                                      30,'u',31,'v',32,'w',33,'x',34,'y',35,'z',
                                      36,'A',37,'B',38,'C',39,'D',40,'E',41,'F',
                                      42,'G',43,'H',44,'I',45,'J',46,'K',47,'L',
                                      48,'M',49,'N',50,'O',51,'P',52,'Q',53,'R',
                                      54,'S',55,'T',56,'U',57,'V',58,'W',59,'X',
                                      60,'Y',61,'Z','')  into v_char_tmp from dual;
    
    v_result := v_result||v_char_tmp;
    v_tmp := trunc(v_tmp / 62);
end loop;

select reverse(v_result) into v_result from dual;
if v_lpad > length(v_result) then 
     v_result := lpad(v_result,v_lpad,'0');
end if;

--DBMS_OUTPUT.PUT_LINE('====['||v_result||']==');
return v_result;
end GetSerial62;