Oracle 進制轉換完全手冊：內建函數與自訂函數實現 2/8/10/16 進制互轉

一、16 進制 → 10 進制：TO_NUMBER

SELECT TO_NUMBER('19F', 'XXX') FROM dual;  -- 415
SELECT TO_NUMBER('F', 'XX') FROM dual;     -- 15

二、10 進制 → 16 進制：TO_CHAR

SELECT TO_CHAR(123, 'XXX') FROM dual;   -- 7B
SELECT TO_CHAR(4567, 'XXXX') FROM dual; -- 11D7

三、2 進制 → 10 進制：BIN_TO_NUM (9i+)

SELECT BIN_TO_NUM(1,1,0,1) FROM dual;   -- 13
SELECT BIN_TO_NUM(1,1,1,0,1) FROM dual; -- 29

四、通用自訂函數（來源 AskTom）

任意進制 → 字串：

CREATE OR REPLACE FUNCTION to_base(p_dec IN NUMBER, p_base IN NUMBER) RETURN VARCHAR2 IS
  l_str VARCHAR2(255) DEFAULT NULL;
  l_num NUMBER DEFAULT p_dec;
  l_hex VARCHAR2(16) DEFAULT '0123456789ABCDEF';
BEGIN
  IF TRUNC(p_dec) != p_dec OR p_dec < 0 THEN RAISE PROGRAM_ERROR; END IF;
  LOOP
    l_str := SUBSTR(l_hex, MOD(l_num, p_base)+1, 1) || l_str;
    l_num := TRUNC(l_num / p_base);
    EXIT WHEN l_num = 0;
  END LOOP;
  RETURN l_str;
END to_base;

字串 → 10 進制：

CREATE OR REPLACE FUNCTION to_dec(p_str IN VARCHAR2, p_from_base IN NUMBER DEFAULT 16) RETURN NUMBER IS
  l_num NUMBER DEFAULT 0;
  l_hex VARCHAR2(16) DEFAULT '0123456789ABCDEF';
BEGIN
  FOR i IN 1..LENGTH(p_str) LOOP
    l_num := l_num * p_from_base + INSTR(l_hex, UPPER(SUBSTR(p_str, i, 1))) - 1;
  END LOOP;
  RETURN l_num;
END to_dec;

封裝常用轉換：

CREATE OR REPLACE FUNCTION to_hex(p_dec IN NUMBER) RETURN VARCHAR2 IS BEGIN RETURN to_base(p_dec, 16); END;
CREATE OR REPLACE FUNCTION to_bin(p_dec IN NUMBER) RETURN VARCHAR2 IS BEGIN RETURN to_base(p_dec, 2); END;
CREATE OR REPLACE FUNCTION to_oct(p_dec IN NUMBER) RETURN VARCHAR2 IS BEGIN RETURN to_base(p_dec, 8); END;