Oracle 里进制转换

 

 

 

Oracle中10進制轉換成36進制是沒有現成函數的,本人寫了一個function,是把10進制轉換成36進制的,36進制轉換成10進制.

首先了解一下进制的转换：

         36进制到十进制： 10Y就是 1* 36*36 + 0 * 36 + 34 =1330这样就把36进制转换成10进制了

         如10转36进制可以用以下方法：

                                 1330转换为10进制数的方法是“除36取余”方法，

                                 1330/36＝商36余34，36进制最后一位为Y

                                 36/36＝商1余0，36进制右边第二位为0

                                 1/36＝商0余1，36进制右边第三位为1 

                                 商0就不继续除了

                                 最后的结果为10Y

下面我用Oracle 里的函數实现：

                                 36------》10

                                

createorreplacefunction change36To10
(
edh varchar2
)
returnvarchar2
as
v_edh varchar2(32767);
lennumber(10);--获取字符的长度
result number(38);--结果
i number;--循环长度
tempvarchar2(30);--剩下的值
begin
v_edh:=upper(edh);
result:=0;
select length(v_edh) intolenfrom dual;--获取长度
for i in0..len-1 loop
result:=result+changeEdthToNo(substr(v_edh,0,1))*power(36,(len-i-1));
v_edh:=substr(v_edh,2);
end loop;
return result;
end;

 

           10----》36  

createorreplacefunction createDecimalTothirty_six
(
createNo number
)
returnvarchar2
as
result varchar2(10000);--最后的结果
v_createNo number(38);--临时变量
remainder number(38);--余数
shang number(38);--商
v_shang number(38);--商
i number;
begin
v_createNo:=createNo;
i:=1;
While i>0 loop
if i=1then
selectfloor(v_createNo/36) into shang from dual;--获取商
select mod(v_createNo,36) into remainder from dual;--获取余数
result:=changeNoToEdh(remainder);--转换成36进制
v_shang:=shang;
else
selectfloor(v_shang/36) into shang from dual;--获取商
select mod(v_shang,36) into remainder from dual;--获取余数
result:=changeNoToEdh(remainder)||result;
v_shang:=shang;
endif;
if shang=0then
shang:=0;
exit;
endif;
i:=i+1;
end loop;
return result;
end;

----------------

辅助函数：

  字母转换成数字

 

createorreplacefunction changeEdthToNo
(
f_no char
)
returnnumber
as
begin
IF f_no='A'THEN
return10;
ELSIF f_no='B'THEN
return11;
ELSIF f_no='C'THEN
return12;
ELSIF f_no='D'THEN
return13;
ELSIF f_no='E'THEN
return14;
ELSIF f_no='F'THEN
return15;
ELSIF f_no='G'THEN
return16;
ELSIF f_no='H'THEN
return17;
ELSIF f_no='I'THEN
return18;
ELSIF f_no='J'THEN
return19;
ELSIF f_no='K'THEN
return20;
ELSIF f_no='L'THEN
return21;
ELSIF f_no='M'THEN
return22;
ELSIF f_no='N'THEN
return23;
ELSIF f_no='O'THEN
return24;
ELSIF f_no='P'THEN
return25;
ELSIF f_no='Q'THEN
return26;
ELSIF f_no='R'THEN
return27;
ELSIF f_no='S'THEN
return28;
ELSIF f_no='T'THEN
return29;
ELSIF f_no='U'THEN
return30;
ELSIF f_no='V'THEN
return31;
ELSIF f_no='W'THEN
return32;
ELSIF f_no='X'THEN
return33;
ELSIF f_no='Y'THEN
return34;
ELSIF f_no='Z'THEN
return35;
ElSIF f_no='0'or f_no='1'or f_no='2'or f_no='3'or f_no='4'or f_no='5'or f_no='6'or f_no='7'or f_no='8'or f_no='9'then
return f_no;
else
return36;
ENDIF;
end;

 

 

 

    数字转换成字母;

createorreplacefunction changeNoToEdh
(
f_no number
)
returnchar
as
begin
IF f_no=10THEN
return'A';
ELSIF f_no=11THEN
return'B';
ELSIF f_no=12THEN
return'C';
ELSIF f_no=13THEN
return'D';
ELSIF f_no=14THEN
return'E';
ELSIF f_no=15THEN
return'F';
ELSIF f_no=16THEN
return'G';
ELSIF f_no=17THEN
return'H';
ELSIF f_no=18THEN
return'I';
ELSIF f_no=19THEN
return'J';
ELSIF f_no=20THEN
return'K';
ELSIF f_no=21THEN
return'L';
ELSIF f_no=22THEN
return'M';
ELSIF f_no=23THEN
return'N';
ELSIF f_no=24THEN
return'O';
ELSIF f_no=25THEN
return'P';
ELSIF f_no=26THEN
return'Q';
ELSIF f_no=27THEN
return'R';
ELSIF f_no=28THEN
return'S';
ELSIF f_no=29THEN
return'T';
ELSIF f_no=30THEN
return'U';
ELSIF f_no=31THEN
return'V';
ELSIF f_no=32THEN
return'W';
ELSIF f_no=33THEN
return'X';
ELSIF f_no=34THEN
return'Y';
ELSIF f_no=35THEN
return'Z';
else
return f_no;
ENDIF;
end;