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