专门为你写的一个。。CREATE OR REPLACE FUNCTION H_2_B(MIDID VARCHAR2) RETURN VARCHAR2 IS RESULT1 VARCHAR2(1000); TYPE t_date IS record(A VARCHAR2(4)); TYPE t_resorce IS TABLE OF t_date; v_record t_resorce :=t_resorce();
i INTEGER; v_counter INTEGER;
BEGIN --FOR rec IN ( SELECT (CASE UPPER(SUBSTR(MIDID, ROWNUM, 1)) WHEN '0' THEN '0000' WHEN '1' THEN '0001' WHEN '2' THEN '0010' WHEN '3' THEN '0011' WHEN '4' THEN '0100' WHEN '5' THEN '0101' WHEN '6' THEN '0110' WHEN '7' THEN '0111' WHEN '8' THEN '1000' WHEN '9' THEN '1001' WHEN 'A' THEN '1010' WHEN 'B' THEN '1011' WHEN 'C' THEN '1100' WHEN 'D' THEN '1101' WHEN 'E' THEN '1110' WHEN 'F' THEN '1111' END) bulk collect INTO v_record FROM DUAL CONNECT BY ROWNUM <= LENGTH(MIDID); FOR i IN 1..v_record.count LOOP RESULT1 := RESULT1||v_record(i).a; END LOOP; RETURN(RESULT1); END H_2_B;
好久没上csdn了,感觉没以前火热了啊。
CREATE OR REPLACE FUNCTION hex2bin(i_data VARCHAR2) RETURN VARCHAR2 IS v_data VARCHAR2(2000); BEGIN with t as( SELECT '0' HEX, '0000' BINARY FROM DUAL UNION ALL SELECT '1' HEX, '0001' BINARY FROM DUAL UNION ALL SELECT '2' HEX, '0010' BINARY FROM DUAL UNION ALL SELECT '3' HEX, '0011' BINARY FROM DUAL UNION ALL SELECT '4' HEX, '0100' BINARY FROM DUAL UNION ALL SELECT '5' HEX, '0101' BINARY FROM DUAL UNION ALL SELECT '6' HEX, '0110' BINARY FROM DUAL UNION ALL SELECT '7' HEX, '0111' BINARY FROM DUAL UNION ALL SELECT '8' HEX, '1000' BINARY FROM DUAL UNION ALL SELECT '9' HEX, '1001' BINARY FROM DUAL UNION ALL SELECT 'A' HEX, '1010' BINARY FROM DUAL UNION ALL SELECT 'B' HEX, '1011' BINARY FROM DUAL UNION ALL SELECT 'C' HEX, '1100' BINARY FROM DUAL UNION ALL SELECT 'D' HEX, '1101' BINARY FROM DUAL UNION ALL SELECT 'E' HEX, '1110' BINARY FROM DUAL UNION ALL SELECT 'F' HEX, '1111' BINARY FROM DUAL ) SELECT REPLACE(MAX(sys_connect_by_path(binary, ',')), ',') INTO v_data FROM t, (SELECT str, LEVEL ln, substr(str, LEVEL, 1) digit FROM (SELECT i_data str FROM dual) CONNECT BY LEVEL <= length(str)) a WHERE t.hex = a.digit START WITH a.ln = 1 CONNECT BY PRIOR ln = ln - 1; RETURN v_data; END;
用java写个储存过程 然后用java里面的 toBinaryString() 方法
高手真多,看到qin_phoenix的简洁与直观,tangren的复杂与精妙 太佩服了!!!
CREATE OR REPLACE TYPE type_str_agg AS OBJECT ( total VARCHAR2(4000), STATIC FUNCTION odciaggregateinitialize(sctx IN OUT type_str_agg) RETURN NUMBER, MEMBER FUNCTION odciaggregateiterate ( SELF IN OUT type_str_agg, VALUE IN VARCHAR2 ) RETURN NUMBER, MEMBER FUNCTION odciaggregateterminate ( SELF IN type_str_agg, returnvalue OUT VARCHAR2, flags IN NUMBER ) RETURN NUMBER, MEMBER FUNCTION odciaggregatemerge ( SELF IN OUT type_str_agg, ctx2 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 odciconst.success; END; MEMBER FUNCTION odciaggregateiterate ( SELF IN OUT type_str_agg, VALUE IN VARCHAR2 ) RETURN NUMBER IS BEGIN SELF.total := SELF.total || VALUE; RETURN odciconst.success; END; MEMBER FUNCTION odciaggregateterminate ( SELF IN type_str_agg, returnvalue OUT VARCHAR2, flags IN NUMBER ) RETURN NUMBER IS BEGIN returnvalue := SELF.total; RETURN odciconst.success; END; MEMBER FUNCTION odciaggregatemerge ( SELF IN OUT type_str_agg, ctx2 IN type_str_agg ) RETURN NUMBER IS BEGIN SELF.total := SELF.total || ctx2.total; RETURN odciconst.success; END;END; /CREATE OR REPLACE FUNCTION f_stragg(p_input VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING type_str_agg; /CREATE OR REPLACE FUNCTION f_hex_to_bin(p_str IN VARCHAR2) RETURN VARCHAR2 IS ---------------------------------------------------------------------------------------------------------------------- -- 对象名称: f_dec_to_oct -- 对象描述: 十六进制转换二进制 -- 输入参数: p_str 十六进制字符串 -- 返回结果: 二进制字符串 -- 测试用例: SELECT pkg_number_trans.f_hex_to_oct('78A') FROM dual; ---------------------------------------------------------------------------------------------------------------------- v_return VARCHAR2(4000); BEGIN SELECT to_char(to_number(f_stragg(data1))) INTO v_return FROM (SELECT (CASE upper(substr(p_str, rownum, 1)) WHEN '0' THEN '0000' WHEN '1' THEN '0001' WHEN '2' THEN '0010' WHEN '3' THEN '0011' WHEN '4' THEN '0100' WHEN '5' THEN '0101' WHEN '6' THEN '0110' WHEN '7' THEN '0111' WHEN '8' THEN '1000' WHEN '9' THEN '1001' WHEN 'A' THEN '1010' WHEN 'B' THEN '1011' WHEN 'C' THEN '1100' WHEN 'D' THEN '1101' WHEN 'E' THEN '1110' WHEN 'F' THEN '1111' END) data1 FROM dual CONNECT BY rownum <= length(p_str)); RETURN v_return; EXCEPTION WHEN OTHERS THEN RETURN NULL; END f_hex_to_bin; / SQL> select f_hex_to_bin('0A') from dual;F_HEX_TO_BIN('0A') -------------------------------------------------------------------------------- 1010SQL> select f_hex_to_bin('A0') from dual;F_HEX_TO_BIN('A0') -------------------------------------------------------------------------------- 10100000SQL> select lpad(f_hex_to_bin('0A'),length('0A')*4,'0') from dual;LPAD(F_HEX_TO_BIN('0A'),LENGTH('0A')*4,'0') ---------------------------------------------------------------- 00001010 -- 加个左填充函数,就ok啦!
--------------------------------------------------------------------------------
10101110SQL>
RESULT1 VARCHAR2(1000);
TYPE t_date IS record(A VARCHAR2(4));
TYPE t_resorce IS TABLE OF t_date;
v_record t_resorce :=t_resorce();
i INTEGER;
v_counter INTEGER;
BEGIN
--FOR rec IN (
SELECT (CASE UPPER(SUBSTR(MIDID, ROWNUM, 1))
WHEN '0' THEN
'0000'
WHEN '1' THEN
'0001'
WHEN '2' THEN
'0010'
WHEN '3' THEN
'0011'
WHEN '4' THEN
'0100'
WHEN '5' THEN
'0101'
WHEN '6' THEN
'0110'
WHEN '7' THEN
'0111'
WHEN '8' THEN
'1000'
WHEN '9' THEN
'1001'
WHEN 'A' THEN
'1010'
WHEN 'B' THEN
'1011'
WHEN 'C' THEN
'1100'
WHEN 'D' THEN
'1101'
WHEN 'E' THEN
'1110'
WHEN 'F' THEN
'1111'
END) bulk collect INTO v_record
FROM DUAL
CONNECT BY ROWNUM <= LENGTH(MIDID);
FOR i IN 1..v_record.count LOOP
RESULT1 := RESULT1||v_record(i).a;
END LOOP;
RETURN(RESULT1);
END H_2_B;
v_data VARCHAR2(2000);
BEGIN
with t as(
SELECT '0' HEX, '0000' BINARY FROM DUAL UNION ALL
SELECT '1' HEX, '0001' BINARY FROM DUAL UNION ALL
SELECT '2' HEX, '0010' BINARY FROM DUAL UNION ALL
SELECT '3' HEX, '0011' BINARY FROM DUAL UNION ALL
SELECT '4' HEX, '0100' BINARY FROM DUAL UNION ALL
SELECT '5' HEX, '0101' BINARY FROM DUAL UNION ALL
SELECT '6' HEX, '0110' BINARY FROM DUAL UNION ALL
SELECT '7' HEX, '0111' BINARY FROM DUAL UNION ALL
SELECT '8' HEX, '1000' BINARY FROM DUAL UNION ALL
SELECT '9' HEX, '1001' BINARY FROM DUAL UNION ALL
SELECT 'A' HEX, '1010' BINARY FROM DUAL UNION ALL
SELECT 'B' HEX, '1011' BINARY FROM DUAL UNION ALL
SELECT 'C' HEX, '1100' BINARY FROM DUAL UNION ALL
SELECT 'D' HEX, '1101' BINARY FROM DUAL UNION ALL
SELECT 'E' HEX, '1110' BINARY FROM DUAL UNION ALL
SELECT 'F' HEX, '1111' BINARY FROM DUAL
)
SELECT REPLACE(MAX(sys_connect_by_path(binary, ',')), ',') INTO v_data
FROM t,
(SELECT str, LEVEL ln, substr(str, LEVEL, 1) digit
FROM (SELECT i_data str FROM dual)
CONNECT BY LEVEL <= length(str)) a
WHERE t.hex = a.digit
START WITH a.ln = 1
CONNECT BY PRIOR ln = ln - 1;
RETURN v_data;
END;
高手真多,看到qin_phoenix的简洁与直观,tangren的复杂与精妙 太佩服了!!!
(
total VARCHAR2(4000), STATIC FUNCTION odciaggregateinitialize(sctx IN OUT type_str_agg)
RETURN NUMBER, MEMBER FUNCTION odciaggregateiterate
(
SELF IN OUT type_str_agg,
VALUE IN VARCHAR2
) RETURN NUMBER, MEMBER FUNCTION odciaggregateterminate
(
SELF IN type_str_agg,
returnvalue OUT VARCHAR2,
flags IN NUMBER
) RETURN NUMBER, MEMBER FUNCTION odciaggregatemerge
(
SELF IN OUT type_str_agg,
ctx2 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 odciconst.success;
END; MEMBER FUNCTION odciaggregateiterate
(
SELF IN OUT type_str_agg,
VALUE IN VARCHAR2
) RETURN NUMBER IS
BEGIN
SELF.total := SELF.total || VALUE;
RETURN odciconst.success;
END; MEMBER FUNCTION odciaggregateterminate
(
SELF IN type_str_agg,
returnvalue OUT VARCHAR2,
flags IN NUMBER
) RETURN NUMBER IS
BEGIN
returnvalue := SELF.total;
RETURN odciconst.success;
END; MEMBER FUNCTION odciaggregatemerge
(
SELF IN OUT type_str_agg,
ctx2 IN type_str_agg
) RETURN NUMBER IS
BEGIN
SELF.total := SELF.total || ctx2.total;
RETURN odciconst.success;
END;END;
/CREATE OR REPLACE FUNCTION f_stragg(p_input VARCHAR2) RETURN VARCHAR2
PARALLEL_ENABLE
AGGREGATE USING type_str_agg;
/CREATE OR REPLACE FUNCTION f_hex_to_bin(p_str IN VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 对象名称: f_dec_to_oct
-- 对象描述: 十六进制转换二进制
-- 输入参数: p_str 十六进制字符串
-- 返回结果: 二进制字符串
-- 测试用例: SELECT pkg_number_trans.f_hex_to_oct('78A') FROM dual;
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
BEGIN
SELECT to_char(to_number(f_stragg(data1))) INTO v_return
FROM (SELECT (CASE upper(substr(p_str, rownum, 1))
WHEN '0' THEN '0000'
WHEN '1' THEN '0001'
WHEN '2' THEN '0010'
WHEN '3' THEN '0011'
WHEN '4' THEN '0100'
WHEN '5' THEN '0101'
WHEN '6' THEN '0110'
WHEN '7' THEN '0111'
WHEN '8' THEN '1000'
WHEN '9' THEN '1001'
WHEN 'A' THEN '1010'
WHEN 'B' THEN '1011'
WHEN 'C' THEN '1100'
WHEN 'D' THEN '1101'
WHEN 'E' THEN '1110'
WHEN 'F' THEN '1111'
END) data1
FROM dual
CONNECT BY rownum <= length(p_str));
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_hex_to_bin;
/
SQL> select f_hex_to_bin('0A') from dual;F_HEX_TO_BIN('0A')
--------------------------------------------------------------------------------
1010SQL> select f_hex_to_bin('A0') from dual;F_HEX_TO_BIN('A0')
--------------------------------------------------------------------------------
10100000SQL> select lpad(f_hex_to_bin('0A'),length('0A')*4,'0') from dual;LPAD(F_HEX_TO_BIN('0A'),LENGTH('0A')*4,'0')
----------------------------------------------------------------
00001010
-- 加个左填充函数,就ok啦!