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
CREATE OR REPLACE FUNCTION f_hex_2_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS ----------------------------------------------------------------------------------------------------------------------