connect by 十六进制转十进制

CREATE OR REPLACE FUNCTION f_hex_2_dec(in_str IN VARCHAR2) RETURN VARCHAR2 IS
  ----------------------------------------------------------------------------------------------------------------------
  -- 对象名称: f_hex_2_dec
  -- 对象描述: 十六进制转换十进制
  -- 输入参数: p_str 十六进制字符串
  -- 返回结果: 十进制字符串
  -- 测试用例: SELECT f_hex_2_dec('78A') FROM dual;
  ----------------------------------------------------------------------------------------------------------------------
  v_return VARCHAR2(4000);
BEGIN
  SELECT SUM(data)
  INTO   v_return
  FROM   (SELECT (CASE UPPER(substr(in_str, rownum, 1))
                   WHEN 'A' THEN
                    '10'
                   WHEN 'B' THEN
                    '11'
                   WHEN 'C' THEN
                    '12'
                   WHEN 'D' THEN
                    '13'
                   WHEN 'E' THEN
                    '14'
                   WHEN 'F' THEN
                    '15'
                   ELSE
                    substr(p_str, rownum, 1)
                 END) * power(16, length(in_str) - rownum) data
          FROM   dual
          CONNECT BY rownum <= length(in_str));
  RETURN v_return;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END;

SQL> SELECT f_hex_2_dec('10') FROM dual;
 
F_HEX_2_DEC('10')
--------------------------------------------------------------------------------
16