CREATE OR REPLACE TYPE type_str_agg AS OBJECT
(
total VARCHAR()
STATIC FUNCTION odciaggregateinitialize(sctx IN OUT type_str_agg)
RETURN NUMBER
MEMBER FUNCTION odciaggregateiterate
(
SELF IN OUT type_str_agg
VALUE IN VARCHAR
) RETURN NUMBER
MEMBER FUNCTION odciaggregateterminate
(
SELF IN type_str_agg
returnvalue OUT VARCHAR
flags IN NUMBER
) RETURN NUMBER
MEMBER FUNCTION odciaggregatemerge
(
SELF IN OUT type_str_agg
ctx IN type_str_agg
) RETURN NUMBER
)
/
CREATE OR REPLACE TYPE BODY type_str_agg IS
STATIC FUNCTION odciaggregateinitialize(sctx IN OUT type_str_agg)
RETURN NUMBER IS
BEGIN
sctx := type_str_agg(NULL);
RETURN odciconstsuccess;
END;
MEMBER FUNCTION odciaggregateiterate
(
SELF IN OUT type_str_agg
VALUE IN VARCHAR
) RETURN NUMBER IS
BEGIN
SELFtotal := SELFtotal || VALUE;
RETURN odciconstsuccess;
END;
MEMBER FUNCTION odciaggregateterminate
(
SELF IN type_str_agg
returnvalue OUT VARCHAR
flags IN NUMBER
) RETURN NUMBER IS
BEGIN
returnvalue := SELFtotal;
RETURN odciconstsuccess;
END;
MEMBER FUNCTION odciaggregatemerge
(
SELF IN OUT type_str_agg
ctx IN type_str_agg
) RETURN NUMBER IS
BEGIN
SELFtotal := SELFtotal || ctxtotal;
RETURN odciconstsuccess;
END;
END;
/
CREATE OR REPLACE FUNCTION f_stragg(p_input VARCHAR) RETURN VARCHAR
PARALLEL_ENABLE
AGGREGATE USING type_str_agg;
/
CREATE OR REPLACE PACKAGE pkg_number_trans IS
FUNCTION f_bin_to_oct(p_str IN VARCHAR) RETURN VARCHAR;
FUNCTION f_bin_to_dec(p_str IN VARCHAR) RETURN VARCHAR;
FUNCTION f_bin_to_hex(p_str IN VARCHAR) RETURN VARCHAR;
FUNCTION f_oct_to_bin(p_str IN VARCHAR) RETURN VARCHAR;
FUNCTION f_oct_to_dec(p_str IN VARCHAR) RETURN VARCHAR;
FUNCTION f_oct_to_hex(p_str IN VARCHAR) RETURN VARCHAR;
FUNCTION f_hex_to_bin(p_str IN VARCHAR) RETURN VARCHAR;
FUNCTION f_hex_to_oct(p_str IN VARCHAR) RETURN VARCHAR;
FUNCTION f_hex_to_dec(p_str IN VARCHAR) RETURN VARCHAR;
FUNCTION f_dec_to_bin(p_int IN VARCHAR) RETURN VARCHAR;
FUNCTION f_dec_to_oct(p_int IN VARCHAR) RETURN VARCHAR;
FUNCTION f_dec_to_hex(p_int IN VARCHAR) RETURN VARCHAR;
END pkg_number_trans;
/
CREATE OR REPLACE PACKAGE BODY pkg_number_trans IS
FUNCTION f_bin_to_oct(p_str IN VARCHAR) RETURN VARCHAR IS
对象名称: f_bin_to_oct
对象描述: 二进制转换八进制
输入参数: p_str 二进制字符串
返回结果: 八进制字符串
测试用例: SELECT pkg_number_transf_bin_to_oct() FROM dual;
备 注: 需要定义f_stragg函数和type_str_agg类型
v_return VARCHAR();
v_bin VARCHAR();
BEGIN
v_bin := substr( || p_str * ceil(length(p_str) / ));
SELECT f_stragg(data) INTO v_return
FROM (SELECT (CASE upper(substr(v_bin (rownum ) * + ))
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
END) data
FROM dual
CONNECT BY rownum <= length(v_bin) / );
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_bin_to_oct;
FUNCTION f_bin_to_dec(p_str IN VARCHAR) RETURN VARCHAR IS
对象名称: f_bin_to_dec
对象描述: 二进制转换十进制
输入参数: p_str 二进制字符串
返回结果: 十进制字符串
测试用例: SELECT pkg_number_transf_bin_to_dec() FROM dual;
v_return VARCHAR();
BEGIN
SELECT SUM(data) INTO v_return
FROM (SELECT substr(p_str rownum ) * power( length(p_str) rownum) data
FROM dual
CONNECT BY rownum <= length(p_str));
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_bin_to_dec;
FUNCTION f_bin_to_hex(p_str IN VARCHAR) RETURN VARCHAR IS
对象名称: f_bin_to_hex
对象描述: 二进制转换十六进制
输入参数: p_str 二进制字符串
返回结果: 十六进制字符串
测试用例: SELECT pkg_number_transf_bin_to_oct() FROM dual;
备 注: 需要定义f_stragg函数和type_str_agg类型
v_return VARCHAR();
v_bin VARCHAR();
BEGIN
v_bin := substr( || p_str * ceil(length(p_str) / ));
SELECT f_stragg(data) INTO v_return
FROM (SELECT (CASE upper(substr(v_bin (rownum ) * + ))
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN A
WHEN THEN B
WHEN THEN C
WHEN THEN D
WHEN THEN E
WHEN THEN F
END) data
FROM dual
CONNECT BY rownum <= length(v_bin) / );
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_bin_to_hex;
FUNCTION f_oct_to_bin(p_str IN VARCHAR) RETURN VARCHAR IS
对象名称: f_oct_to_bin
对象描述: 八进制转换二进制
输入参数: p_str 八进制字符串
返回结果: 二进制字符串
测试用例: SELECT pkg_number_transf_oct_to_bin() FROM dual;
备 注: 需要定义f_stragg函数和type_str_agg类型
v_return VARCHAR();
BEGIN
SELECT to_char(to_number(f_stragg(data))) INTO v_return
FROM (SELECT (CASE upper(substr(p_str rownum ))
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
END) data
FROM dual
CONNECT BY rownum <= length(p_str));
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_oct_to_bin;
FUNCTION f_oct_to_dec(p_str IN VARCHAR) RETURN VARCHAR IS
对象名称: f_oct_to_dec
对象描述: 八进制转换十进制
输入参数: p_str 八进制字符串
返回结果: 十进制字符串
测试用例: SELECT pkg_number_transf_oct_to_dec() FROM dual;
v_return VARCHAR();
BEGIN
SELECT SUM(data) INTO v_return
FROM (SELECT substr(p_str rownum ) * power( length(p_str) rownum) data
FROM dual
CONNECT BY rownum <= length(p_str));
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_oct_to_dec;
FUNCTION f_oct_to_hex(p_str IN VARCHAR) RETURN VARCHAR IS
对象名称: f_oct_to_bin
对象描述: 八进制转换十六进制
输入参数: p_str 八进制字符串
返回结果: 十六进制字符串
测试用例: SELECT pkg_number_transf_oct_to_hex() FROM dual;
v_return VARCHAR();
v_bin VARCHAR();
BEGIN
SELECT pkg_number_transf_oct_to_bin(p_str) INTO v_bin FROM dual;
SELECT pkg_number_transf_bin_to_hex(v_bin) INTO v_return FROM dual;
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_oct_to_hex;
FUNCTION f_dec_to_bin(p_int IN VARCHAR) RETURN VARCHAR IS
对象名称: f_dec_to_bin
对象描述: 十进制转换二进制
输入参数: p_str 十进制字符串
返回结果: 二进制字符串
测试用例: SELECT pkg_number_transf_dec_to_bin() FROM dual;
v_return VARCHAR();
v_hex VARCHAR();
BEGIN
SELECT pkg_number_transf_dec_to_hex(p_int) INTO v_hex FROM dual;
SELECT pkg_number_transf_hex_to_bin(v_hex) INTO v_return FROM dual;
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_dec_to_bin;
FUNCTION f_dec_to_oct(p_int IN VARCHAR) RETURN VARCHAR IS
对象名称: f_dec_to_oct
对象描述: 十进制转换八进制
输入参数: p_str 十进制字符串
返回结果: 八进制字符串
测试用例: SELECT pkg_number_transf_dec_to_oct() FROM dual;
v_return VARCHAR();
v_bin VARCHAR();
BEGIN
SELECT pkg_number_transf_dec_to_bin(p_int) INTO v_bin FROM dual;
v_bin := substr( || v_bin * ceil(length(v_bin) / ));
SELECT f_stragg(data) INTO v_return
FROM (SELECT (CASE upper(substr(v_bin (rownum ) * + ))
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
END) data
FROM dual
CONNECT BY rownum <= length(v_bin) / );
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_dec_to_oct;
FUNCTION f_dec_to_hex(p_int IN VARCHAR) RETURN VARCHAR IS
对象名称: f_dec_to_oct
对象描述: 十进制转换十六进制
输入参数: p_str 十进制字符串
返回结果: 十六进制字符串
测试用例: SELECT pkg_number_transf_dec_to_hex() FROM dual;
v_return VARCHAR();
BEGIN
SELECT upper(TRIM(to_char(p_int xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx))) INTO v_return FROM dual;
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_dec_to_hex;
FUNCTION f_hex_to_bin(p_str IN VARCHAR) RETURN VARCHAR IS
对象名称: f_dec_to_oct
对象描述: 十六进制转换二进制
输入参数: p_str 十六进制字符串
返回结果: 二进制字符串
测试用例: SELECT pkg_number_transf_hex_to_oct(A) FROM dual;
v_return VARCHAR();
BEGIN
SELECT to_char(to_number(f_stragg(data))) INTO v_return
FROM (SELECT (CASE upper(substr(p_str rownum ))
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN THEN
WHEN A THEN
WHEN B THEN
WHEN C THEN
WHEN D THEN
WHEN E THEN
WHEN F THEN
END) data
FROM dual
CONNECT BY rownum <= length(p_str));
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_hex_to_bin;
FUNCTION f_hex_to_oct(p_str IN VARCHAR) RETURN VARCHAR IS
对象名称: f_dec_to_oct
对象描述: 十六进制转换八进制
输入参数: p_str 十六进制字符串
返回结果: 八进制字符串
测试用例: SELECT pkg_number_transf_hex_to_oct(A) FROM dual;
v_return VARCHAR();
v_bin VARCHAR();
BEGIN
SELECT pkg_number_transf_hex_to_bin(p_str) INTO v_bin FROM dual;
SELECT pkg_number_transf_bin_to_oct(v_bin) INTO v_return FROM dual;
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_hex_to_oct;
FUNCTION f_hex_to_dec(p_str IN VARCHAR) RETURN VARCHAR IS
对象名称: f_hex_to_dec
对象描述: 十六进制转换十进制
输入参数: p_str 十六进制字符串
返回结果: 十进制字符串
测试用例: SELECT pkg_number_transf_hex_to_dec(A) FROM dual;
v_return VARCHAR();
BEGIN
SELECT SUM(data) INTO v_return
FROM (SELECT (CASE upper(substr(p_str rownum ))
WHEN A THEN
WHEN B THEN
WHEN C THEN
WHEN D THEN
WHEN E THEN
WHEN F THEN
ELSE substr(p_str rownum )
END) * power( length(p_str) rownum) data
FROM dual
CONNECT BY rownum <= length(p_str));
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_hex_to_dec;
END pkg_number_trans;
/
