熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

oracle進制之間轉換

2013-11-13 22:22:24  來源: Oracle 

  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;

  /


From:http://tw.wingwit.com/Article/program/Oracle/201311/18912.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.