• oracle进制转换


    源代码

    CREATE OR REPLACE PACKAGE Base_Num_Change IS

    --=============================================

    --1.0 16进制转10进制

    FUNCTION Hex_To_Dec(p_Str VARCHAR2) RETURN NUMBER;

    --1.1 16进制转8进制

    FUNCTION Hex_To_Oct(p_Str VARCHAR2) RETURN VARCHAR2;

    --1.2 16进制转2进制

    FUNCTION Hex_To_Bin(p_Str VARCHAR2) RETURN VARCHAR2;

    --2.0 10进制转16进制

    FUNCTION Dec_To_Hex(p_Dec NUMBER) RETURN VARCHAR2;

    --2.1 10进制转8进制

    FUNCTION Dec_To_Oct(p_Dec NUMBER) RETURN VARCHAR2;

    --2.2 10进制转2进制

    FUNCTION Dec_To_Bin(p_Dec NUMBER) RETURN VARCHAR2;

    --3.0 8进制转16进制

    FUNCTION Oct_To_Hex(p_Str VARCHAR2) RETURN VARCHAR2;

    --3.1 8进制转10进制

    FUNCTION Oct_To_Dec(p_Str VARCHAR2) RETURN NUMBER;

    --3.2 8进制转2进制

    FUNCTION Oct_To_Bin(p_Str VARCHAR2) RETURN VARCHAR2;

    --4.0 2进制转16进制

    FUNCTION Bin_To_Hex(p_Str VARCHAR2) RETURN VARCHAR2;

    --4.1 2进制转10进制

    FUNCTION Bin_To_Dec(p_Str VARCHAR2) RETURN NUMBER;

    --4.2 2进制转8进制

    FUNCTION Bin_To_Oct(p_Str VARCHAR2) RETURN VARCHAR2;


    END Base_Num_Change;

    /

    CREATE OR REPLACE PACKAGE BODY Base_Num_Change IS

    --=============================================

    --1.0 16进制转10进制

    FUNCTION Hex_To_Dec(p_Str VARCHAR2) RETURN NUMBER AS

    p_Len NUMBER(9);

    p_Char VARCHAR2(1);

    p_Sub_Out NUMBER(3);

    p_Sub_Str VARCHAR2(200) := Upper(p_Str);

    p_Out NUMBER(18) := 0;

    p_Integrity EXCEPTION;

    p_Errstr VARCHAR2(200);

    BEGIN

    IF p_Str IS NULL

    THEN

    RETURN NULL;

    END IF;

    IF Ltrim(p_Sub_Str, '0123456789ABCDEF') IS NOT NULL

    THEN

    p_Errstr := p_Str || '包含非法字符，无法转换为2进制！';

    RAISE p_Integrity;

    END IF;

    --得到基本数据

    p_Len := Length(p_Str);

    FOR i IN 1 .. p_Len

    LOOP

    p_Char := Substr(p_Sub_Str, 1, 1);

    SELECT Decode(p_Char,

    'A',

    10,

    'B',

    11,

    'C',

    12,

    'D',

    13,

    'E',

    14,

    'F',

    15,

    To_Number(p_Char))

    INTO p_Sub_Out

    FROM Dual;

    p_Sub_Str := Substr(p_Sub_Str, 2);

    p_Out := p_Out * 16 + p_Sub_Out;

    END LOOP;

    --

    RETURN p_Out;

    EXCEPTION

    WHEN p_Integrity THEN

    Raise_Application_Error(-20001, p_Errstr);

    END Hex_To_Dec;

    --1.1 16进制转8进制

    FUNCTION Hex_To_Oct(p_Str VARCHAR2) RETURN VARCHAR2 AS

    p_Out VARCHAR2(2000);

    p_Integrity EXCEPTION;

    p_Errstr VARCHAR2(200);

    BEGIN

    --得到基本数据

    p_Out := Bin_To_Oct(Hex_To_Bin(p_Str));

    --

    RETURN p_Out;

    EXCEPTION

    WHEN p_Integrity THEN

    Raise_Application_Error(-20001, p_Errstr);

    END Hex_To_Oct;


    --1.2 16进制转2进制

    FUNCTION Hex_To_Bin(p_Str VARCHAR2) RETURN VARCHAR2 AS

    p_Len NUMBER(9);

    p_Char VARCHAR2(1);

    p_Sub_Out VARCHAR2(4);

    p_Sub_Str VARCHAR2(200) := Upper(p_Str);

    p_Out VARCHAR2(2000);

    p_Integrity EXCEPTION;

    p_Errstr VARCHAR2(200);

    BEGIN

    IF p_Str IS NULL

    THEN

    RETURN NULL;

    END IF;

    IF Ltrim(p_Sub_Str, '0123456789ABCDEF') IS NOT NULL

    THEN

    p_Errstr := p_Str || '包含非法字符，无法转换为2进制！';

    RAISE p_Integrity;

    END IF;

    --得到基本数据

    p_Len := Length(p_Str);

    FOR i IN 1 .. p_Len

    LOOP

    p_Char := Substr(p_Sub_Str, 1, 1);

    SELECT Decode(p_Char,

    '0',

    '0000',

    '1',

    '0001',

    '2',

    '0010',

    '3',

    '0011',

    '4',

    '0100',

    '5',

    '0101',

    '6',

    '0110',

    '7',

    '0111',

    '8',

    '1000',

    '9',

    '1001',

    'A',

    '1010',

    'B',

    '1011',

    'C',

    '1100',

    'D',

    '1101',

    'E',

    '1110',

    'F',

    '1111',

    '2222')

    INTO p_Sub_Out

    FROM Dual;

    p_Sub_Str := Substr(p_Sub_Str, 2);

    p_Out := p_Out || p_Sub_Out;

    END LOOP;

    --

    RETURN p_Out;

    EXCEPTION

    WHEN p_Integrity THEN

    Raise_Application_Error(-20001, p_Errstr);

    END Hex_To_Bin;


    --2.0 10进制转16进制

    FUNCTION Dec_To_Hex(p_Dec NUMBER) RETURN VARCHAR2 AS

    p_Out VARCHAR2(2000);

    p_Integrity EXCEPTION;

    p_Errstr VARCHAR2(200);

    BEGIN

    --得到基本数据

    p_Out := Bin_To_Hex(Dec_To_Bin(p_Dec));

    --

    RETURN p_Out;

    EXCEPTION

    WHEN p_Integrity THEN

    Raise_Application_Error(-20001, p_Errstr);

    END Dec_To_Hex;

    --2.1 10进制转8进制

    FUNCTION Dec_To_Oct(p_Dec NUMBER) RETURN VARCHAR2 AS

    p_Out VARCHAR2(2000);

    p_Integrity EXCEPTION;

    p_Errstr VARCHAR2(200);

    BEGIN

    --得到基本数据

    p_Out := Bin_To_Oct(Dec_To_Bin(p_Dec));

    --

    RETURN p_Out;

    EXCEPTION

    WHEN p_Integrity THEN

    Raise_Application_Error(-20001, p_Errstr);

    END Dec_To_Oct;

    --2.2 10进制转2进制

    FUNCTION Dec_To_Bin(p_Dec NUMBER) RETURN VARCHAR2 AS

    p_Sub_Out VARCHAR2(4);

    p_Sub_Dec NUMBER(18) := p_Dec;

    p_Out VARCHAR2(2000);

    p_Integrity EXCEPTION;

    p_Errstr VARCHAR2(200);

    BEGIN

    IF p_Dec IS NULL

    THEN

    RETURN NULL;

    END IF;

    IF Trunc(p_Dec) <> p_Dec

    THEN

    p_Errstr := p_Dec || '不是整数，无法转换为2进制！';

    RAISE p_Integrity;

    END IF;

     

    LOOP

    p_Sub_Out := MOD(p_Sub_Dec, 2);

    p_Sub_Dec := Trunc(p_Sub_Dec / 2);

    p_Out := p_Sub_Out || p_Out;

    EXIT WHEN(p_Sub_Dec = 0);

    END LOOP;

    --

    RETURN p_Out;

    EXCEPTION

    WHEN p_Integrity THEN

    Raise_Application_Error(-20001, p_Errstr);

    END Dec_To_Bin;

    --3.0 8进制转16进制

    FUNCTION Oct_To_Hex(p_Str VARCHAR2) RETURN VARCHAR2 AS

    p_Out VARCHAR2(2000);

    p_Integrity EXCEPTION;

    p_Errstr VARCHAR2(200);

    BEGIN

    --得到基本数据

    p_Out := Bin_To_Hex(Oct_To_Bin(p_Str));

    --

    RETURN p_Out;

    EXCEPTION

    WHEN p_Integrity THEN

    Raise_Application_Error(-20001, p_Errstr);

    END Oct_To_Hex;

    --3.1 8进制转10进制

    FUNCTION Oct_To_Dec(p_Str VARCHAR2) RETURN NUMBER AS

    p_Len NUMBER(9);

    p_Char VARCHAR2(1);

    p_Sub_Out NUMBER(3);

    p_Sub_Str VARCHAR2(200) := Upper(p_Str);

    p_Out NUMBER(18) := 0;

    p_Integrity EXCEPTION;

    p_Errstr VARCHAR2(200);

    BEGIN

    IF p_Str IS NULL

    THEN

    RETURN NULL;

    END IF;

    IF Ltrim(p_Sub_Str, '01234567') IS NOT NULL

    THEN

    p_Errstr := p_Str || '包含非法字符，无法转换为2进制！';

    RAISE p_Integrity;

    END IF;

    --得到基本数据

    p_Len := Length(p_Str);

    FOR i IN 1 .. p_Len

    LOOP

    p_Char := Substr(p_Sub_Str, 1, 1);

    p_Sub_Out := To_Number(p_Char);

    p_Sub_Str := Substr(p_Sub_Str, 2);

    p_Out := p_Out * 8 + p_Sub_Out;

    END LOOP;

    --

    RETURN p_Out;

    EXCEPTION

    WHEN p_Integrity THEN

    Raise_Application_Error(-20001, p_Errstr);

    END Oct_To_Dec;

    --3.2 8进制转2进制

    FUNCTION Oct_To_Bin(p_Str VARCHAR2) RETURN VARCHAR2 AS

    p_Len NUMBER(9);

    p_Char VARCHAR2(1);

    p_Sub_Out VARCHAR2(4);

    p_Sub_Str VARCHAR2(200) := Upper(p_Str);

    p_Out VARCHAR2(2000);

    p_Integrity EXCEPTION;

    p_Errstr VARCHAR2(200);

    BEGIN

    IF p_Str IS NULL

    THEN

    RETURN NULL;

    END IF;

    IF Ltrim(p_Sub_Str, '01234567') IS NOT NULL

    THEN

    p_Errstr := p_Str || '包含非法字符，无法转换为2进制！';

    RAISE p_Integrity;

    END IF;

    --得到基本数据

    p_Len := Length(p_Str);

    FOR i IN 1 .. p_Len

    LOOP

    p_Char := Substr(p_Sub_Str, 1, 1);

    SELECT Decode(p_Char,

    '0',

    '000',

    '1',

    '001',

    '2',

    '010',

    '3',

    '011',

    '4',

    '100',

    '5',

    '101',

    '6',

    '110',

    '7',

    '111',

    '222')

    INTO p_Sub_Out

    FROM Dual;

    p_Sub_Str := Substr(p_Sub_Str, 2);

    p_Out := p_Out || p_Sub_Out;

    END LOOP;

    --

    RETURN p_Out;

    EXCEPTION

    WHEN p_Integrity THEN

    Raise_Application_Error(-20001, p_Errstr);

    END Oct_To_Bin;

    --4.0 2进制转16进制

    FUNCTION Bin_To_Hex(p_Str VARCHAR2) RETURN VARCHAR2 AS

    p_Len NUMBER(9);

    p_Char VARCHAR2(4);

    p_Sub_Out VARCHAR2(4);

    p_Sub_Str VARCHAR2(200) := Upper(p_Str);

    p_Out VARCHAR2(2000);

    p_Integrity EXCEPTION;

    p_Errstr VARCHAR2(200);

    BEGIN

    IF p_Str IS NULL

    THEN

    RETURN NULL;

    END IF;

    IF Ltrim(p_Sub_Str, '01') IS NOT NULL

    THEN

    p_Errstr := p_Str || '包含非法字符，无法转换为16进制！';

    RAISE p_Integrity;

    END IF;

    --得到基本数据

    p_Len := Ceil(Length(p_Str) / 4);

    FOR i IN 1 .. p_Len

    LOOP

    p_Char := Lpad(Substr(p_Sub_Str,(CASE WHEN Length(p_Str) + 1 - 4 * i > 1 THEN Length(p_Str) + 1 - 4 * i ELSE 1 END)), 4, '0');

    SELECT Decode(p_Char,

    '0000',

    '0',

    '0001',

    '1',

    '0010',

    '2',

    '0011',

    '3',

    '0100',

    '4',

    '0101',

    '5',

    '0110',

    '6',

    '0111',

    '7',

    '1000',

    '8',

    '1001',

    '9',

    '1010',

    'A',

    '1011',

    'B',

    '1100',

    'C',

    '1101',

    'D',

    '1110',

    'E',

    '1111',

    'F',

    'G')

    INTO p_Sub_Out

    FROM Dual;

    p_Sub_Str := Substr(p_Sub_Str, 1, Length(p_Sub_Str) - 4);

    p_Out := p_Sub_Out || p_Out;

    END LOOP;

    --

    RETURN p_Out;

    EXCEPTION

    WHEN p_Integrity THEN

    Raise_Application_Error(-20001, p_Errstr);

    END Bin_To_Hex;

    --4.1 2进制转10进制

    FUNCTION Bin_To_Dec(p_Str VARCHAR2) RETURN NUMBER AS

    p_Len NUMBER(9);

    p_Char VARCHAR2(1);

    p_Sub_Out NUMBER(3);

    p_Sub_Str VARCHAR2(200) := Upper(p_Str);

    p_Out NUMBER(18) := 0;

    p_Integrity EXCEPTION;

    p_Errstr VARCHAR2(200);

    BEGIN

    IF p_Str IS NULL

    THEN

    RETURN NULL;

    END IF;

    IF Ltrim(p_Sub_Str, '01') IS NOT NULL

    THEN

    p_Errstr := p_Str || '包含非法字符，无法转换为10进制！';

    RAISE p_Integrity;

    END IF;

    --得到基本数据

    p_Len := Length(p_Str);

    FOR i IN 1 .. p_Len

    LOOP

    p_Char := Substr(p_Sub_Str, 1, 1);

    p_Sub_Out := To_Number(p_Char);

    p_Sub_Str := Substr(p_Sub_Str, 2);

    p_Out := p_Out * 2 + p_Sub_Out;

    END LOOP;

    --

    RETURN p_Out;

    EXCEPTION

    WHEN p_Integrity THEN

    Raise_Application_Error(-20001, p_Errstr);

    END Bin_To_Dec;

    --4.2 2进制转8进制

    FUNCTION Bin_To_Oct(p_Str VARCHAR2) RETURN VARCHAR2 AS

    p_Len NUMBER(9);

    p_Char VARCHAR2(3);

    p_Sub_Out VARCHAR2(4);

    p_Sub_Str VARCHAR2(200) := Upper(p_Str);

    p_Out VARCHAR2(2000);

    p_Integrity EXCEPTION;

    p_Errstr VARCHAR2(200);

    BEGIN

    IF p_Str IS NULL

    THEN

    RETURN NULL;

    END IF;

    IF Ltrim(p_Sub_Str, '01') IS NOT NULL

    THEN

    p_Errstr := p_Str || '包含非法字符，无法转换为8进制！';

    RAISE p_Integrity;

    END IF;

    --得到基本数据

    p_Len := Ceil(Length(p_Str) / 3);

    FOR i IN 1 .. p_Len

    LOOP

    p_Char := Lpad(Substr(p_Sub_Str,(CASE WHEN Length(p_Str) + 1 - 3 * i > 1 THEN Length(p_Str) + 1 - 3 * i ELSE 1 END)), 3, '0');

    SELECT Decode(p_Char,

    '000',

    '0',

    '001',

    '1',

    '010',

    '2',

    '011',

    '3',

    '100',

    '4',

    '101',

    '5',

    '110',

    '6',

    '111',

    '7',

    '9')

    INTO p_Sub_Out

    FROM Dual;

    p_Sub_Str := Substr(p_Sub_Str, 1, Length(p_Sub_Str) - 3);

    p_Out := p_Sub_Out || p_Out;

    END LOOP;

    --

    RETURN p_Out;

    EXCEPTION

    WHEN p_Integrity THEN

    Raise_Application_Error(-20001, p_Errstr);

    END Bin_To_Oct;


    END Base_Num_Change;


    /

    16进制转10进制

    SQL> select Base_Num_Change.Hex_To_Dec('ABC') from dual;


    BASE_NUM_CHANGE.HEX_TO_DEC('ABC')

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

    2748

    10进制转2进制

    SQL> select Base_Num_Change.Dec_To_Bin(2748) from dual;


    BASE_NUM_CHANGE.DEC_TO_BIN(2748)

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

    101010111100

    2进制转10进制

    SQL> select Base_Num_Change.Bin_To_Dec(101010111100) from dual;


    BASE_NUM_CHANGE.BIN_TO_DEC(101010111100)

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

    2748

    2进制转16进制


    SQL> select Base_Num_Change.Bin_To_Hex(101010111100) from dual;


    BASE_NUM_CHANGE.BIN_TO_HEX(101010111100)

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

    ABC

    其实，Oracle自带函数 to_number()，就可以搞定


    SQL> select to_number('ABC','XXX') from dual;


    TO_NUMBER('ABC','XXX')

    2748

    10进制转16进制

    SQL> select Base_Num_Change.Dec_To_Hex(2748) from dual;


    BASE_NUM_CHANGE.DEC_TO_HEX(2748)

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

    ABC


    其实，Oracle自带to_char()函数


    SQL> select to_char('2748','XXX') from dual;


    TO_C

    ----

    ABC

    16进制转2进制

    SQL> select Base_Num_Change.Hex_To_Bin('ABC') from dual;


    BASE_NUM_CHANGE.HEX_TO_BIN('ABC')

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

    101010111100


     
  • 相关阅读:
    B16-高可用OpenStack（t版）集群分布式存储Ceph部署
    B15-openstack高可用(t版)-cinder计算节点集群
    B14-openstack高可用(t版)-cinder控制节点集群
    B13-openstack高可用(t版)-horazion计算节点集群
    B12-openstack高可用(t版)-Neutron计算节点集群
    B11-openstack高可用(t版)-Neutron控制/网络节点集群
    mysql（windows 10 安装）
    docker 容器 centos + tomcat + jdk
    docker 安装
    docker 把镜像打包成文件
  • 原文地址：https://www.cnblogs.com/iyoume2008/p/6110562.html
Copyright © 2020-2023  润新知