【轉帖】Oracle中的二進位制、八進位制、十進位制、十六進位制相互轉換函式
作者：hotiice
原文出處：http://www.itpub.net/thread-991291-1-1.html
Oracle中的二進位制、八進位制、十進位制、十六進位制相互轉換函式
大家經常遇到進位制轉換的問題，網上搜到的轉換隻是部分十進位制與其它進位制的轉換，所以我把自己寫的轉換函式提供給大家，4種進位制共12個轉換函式，雖然有的轉換直接使用to_char()就可以實現，但我還是把它們整理到一起，使用和查詢都方便。
部分函式需要先建立type_str_agg型別和f_stragg函式才能使用，這兩個物件的程式碼也附在之後。
這些函式並非都是我原創，部分是參照別人的程式碼整理而來：type_str_agg、f_stragg、f_hex_to_dec、f_oct_to_dec、f_bin_to_dec。
CREATE OR REPLACE PACKAGE pkg_number_trans IS
FUNCTION f_bin_to_oct(p_str IN VARCHAR2) RETURN VARCHAR2;
FUNCTION f_bin_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2;
FUNCTION f_bin_to_hex(p_str IN VARCHAR2) RETURN VARCHAR2;
FUNCTION f_oct_to_bin(p_str IN VARCHAR2) RETURN VARCHAR2;
FUNCTION f_oct_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2;
FUNCTION f_oct_to_hex(p_str IN VARCHAR2) RETURN VARCHAR2;
FUNCTION f_hex_to_bin(p_str IN VARCHAR2) RETURN VARCHAR2;
FUNCTION f_hex_to_oct(p_str IN VARCHAR2) RETURN VARCHAR2;
FUNCTION f_hex_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2;
FUNCTION f_dec_to_bin(p_int IN VARCHAR2) RETURN VARCHAR2;
FUNCTION f_dec_to_oct(p_int IN VARCHAR2) RETURN VARCHAR2;
FUNCTION f_dec_to_hex(p_int IN VARCHAR2) RETURN VARCHAR2;
END pkg_number_trans;
/
CREATE OR REPLACE PACKAGE BODY pkg_number_trans IS
FUNCTION f_bin_to_oct(p_str IN VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 物件名稱: f_bin_to_oct
-- 物件描述: 二進位制轉換八進位制
-- 輸入引數: p_str 二進位制字串
-- 返回結果: 八進位制字串
-- 測試用例: SELECT pkg_number_trans.f_bin_to_oct('11110001010') FROM dual;
-- 備 注: 需要定義f_stragg函式和type_str_agg型別
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
v_bin VARCHAR2(4000);
BEGIN
v_bin := substr('00' || p_str, -3 * ceil(length(p_str) / 3));
SELECT f_stragg(data1) INTO v_return
FROM (SELECT (CASE upper(substr(v_bin, (rownum - 1) * 3 + 1, 3))
WHEN '000' THEN '0'
WHEN '001' THEN '1'
WHEN '010' THEN '2'
WHEN '011' THEN '3'
WHEN '100' THEN '4'
WHEN '101' THEN '5'
WHEN '110' THEN '6'
WHEN '111' THEN '7'
END) data1
FROM dual
CONNECT BY rownum <= length(v_bin) / 3);
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_bin_to_oct;
FUNCTION f_bin_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 物件名稱: f_bin_to_dec
-- 物件描述: 二進位制轉換十進位制
-- 輸入引數: p_str 二進位制字串
-- 返回結果: 十進位制字串
-- 測試用例: SELECT pkg_number_trans.f_bin_to_dec('11110001010') FROM dual;
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
BEGIN
SELECT SUM(data1) INTO v_return
FROM (SELECT substr(p_str, rownum, 1) * power(2, length(p_str) - rownum) data1
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 VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 物件名稱: f_bin_to_hex
-- 物件描述: 二進位制轉換十六進位制
-- 輸入引數: p_str 二進位制字串
-- 返回結果: 十六進位制字串
-- 測試用例: SELECT pkg_number_trans.f_bin_to_oct('11110001010') FROM dual;
-- 備 注: 需要定義f_stragg函式和type_str_agg型別
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
v_bin VARCHAR2(4000);
BEGIN
v_bin := substr('000' || p_str, -4 * ceil(length(p_str) / 4));
SELECT f_stragg(data1) INTO v_return
FROM (SELECT (CASE upper(substr(v_bin, (rownum - 1) * 4 + 1, 4))
WHEN '0000' THEN '0'
WHEN '0001' THEN '1'
WHEN '0010' THEN '2'
WHEN '0011' THEN '3'
WHEN '0100' THEN '4'
WHEN '0101' THEN '5'
WHEN '0110' THEN '6'
WHEN '0111' THEN '7'
WHEN '1000' THEN '8'
WHEN '1001' THEN '9'
WHEN '1010' THEN 'A'
WHEN '1011' THEN 'B'
WHEN '1100' THEN 'C'
WHEN '1101' THEN 'D'
WHEN '1110' THEN 'E'
WHEN '1111' THEN 'F'
END) data1
FROM dual
CONNECT BY rownum <= length(v_bin) / 4);
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_bin_to_hex;
FUNCTION f_oct_to_bin(p_str IN VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 物件名稱: f_oct_to_bin
-- 物件描述: 八進位制轉換二進位制
-- 輸入引數: p_str 八進位制字串
-- 返回結果: 二進位制字串
-- 測試用例: SELECT pkg_number_trans.f_oct_to_bin('3612') FROM dual;
-- 備 注: 需要定義f_stragg函式和type_str_agg型別
----------------------------------------------------------------------------------------------------------------------
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 '000'
WHEN '1' THEN '001'
WHEN '2' THEN '010'
WHEN '3' THEN '011'
WHEN '4' THEN '100'
WHEN '5' THEN '101'
WHEN '6' THEN '110'
WHEN '7' THEN '111'
END) data1
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 VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 物件名稱: f_oct_to_dec
-- 物件描述: 八進位制轉換十進位制
-- 輸入引數: p_str 八進位制字串
-- 返回結果: 十進位制字串
-- 測試用例: SELECT pkg_number_trans.f_oct_to_dec('3612') FROM dual;
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
BEGIN
SELECT SUM(data1) INTO v_return
FROM (SELECT substr(p_str, rownum, 1) * power(8, length(p_str) - rownum) data1
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 VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 物件名稱: f_oct_to_bin
-- 物件描述: 八進位制轉換十六進位制
-- 輸入引數: p_str 八進位制字串
-- 返回結果: 十六進位制字串
-- 測試用例: SELECT pkg_number_trans.f_oct_to_hex('3612') FROM dual;
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
v_bin VARCHAR2(4000);
BEGIN
SELECT pkg_number_trans.f_oct_to_bin(p_str) INTO v_bin FROM dual;
SELECT pkg_number_trans.f_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 VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 物件名稱: f_dec_to_bin
-- 物件描述: 十進位制轉換二進位制
-- 輸入引數: p_str 十進位制字串
-- 返回結果: 二進位制字串
-- 測試用例: SELECT pkg_number_trans.f_dec_to_bin('1930') FROM dual;
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
v_hex VARCHAR2(4000);
BEGIN
SELECT pkg_number_trans.f_dec_to_hex(p_int) INTO v_hex FROM dual;
SELECT pkg_number_trans.f_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 VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 物件名稱: f_dec_to_oct
-- 物件描述: 十進位制轉換八進位制
-- 輸入引數: p_str 十進位制字串
-- 返回結果: 八進位制字串
-- 測試用例: SELECT pkg_number_trans.f_dec_to_oct('1930') FROM dual;
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
v_bin VARCHAR2(4000);
BEGIN
SELECT pkg_number_trans.f_dec_to_bin(p_int) INTO v_bin FROM dual;
v_bin := substr('00' || v_bin, -3 * ceil(length(v_bin) / 3));
SELECT f_stragg(data1) INTO v_return
FROM (SELECT (CASE upper(substr(v_bin, (rownum - 1) * 3 + 1, 3))
WHEN '000' THEN '0'
WHEN '001' THEN '1'
WHEN '010' THEN '2'
WHEN '011' THEN '3'
WHEN '100' THEN '4'
WHEN '101' THEN '5'
WHEN '110' THEN '6'
WHEN '111' THEN '7'
END) data1
FROM dual
CONNECT BY rownum <= length(v_bin) / 3);
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END f_dec_to_oct;
FUNCTION f_dec_to_hex(p_int IN VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 物件名稱: f_dec_to_oct
-- 物件描述: 十進位制轉換十六進位制
-- 輸入引數: p_str 十進位制字串
-- 返回結果: 十六進位制字串
-- 測試用例: SELECT pkg_number_trans.f_dec_to_hex('1930') FROM dual;
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
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 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;
FUNCTION f_hex_to_oct(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);
v_bin VARCHAR2(4000);
BEGIN
SELECT pkg_number_trans.f_hex_to_bin(p_str) INTO v_bin FROM dual;
SELECT pkg_number_trans.f_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 VARCHAR2) RETURN VARCHAR2 IS
----------------------------------------------------------------------------------------------------------------------
-- 物件名稱: f_hex_to_dec
-- 物件描述: 十六進位制轉換十進位制
-- 輸入引數: p_str 十六進位制字串
-- 返回結果: 十進位制字串
-- 測試用例: SELECT pkg_number_trans.f_hex_to_dec('78A') FROM dual;
----------------------------------------------------------------------------------------------------------------------
v_return VARCHAR2(4000);
BEGIN
SELECT SUM(data1) INTO v_return
FROM (SELECT (CASE upper(substr(p_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(p_str) - rownum) data1
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;
/
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;
/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/498744/viewspace-281099/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 二進位制，八進位制，十進位制，十六進位制的相互轉換
- JAVA 二進位制，八進位制，十六進位制，十進位制間進行相互轉換Java
- java中二進位制、八進位制、十進位制、十六進位制的轉換Java
- 二進位制，八進位制，十進位制，十六進位制之間的轉換
- 二進位制、十進位制與十六進位制相互轉化
- 【進位制轉換】二進位制、十六進位制、十進位制、八進位制對應關係
- 計算機基礎進位制轉換（二進位制、八進位制、十進位制、十六進位制）計算機
- Qt進位制轉換(十進位制轉十六進位制)QT
- 進位制之間的轉換之“十六進位制 轉 十進位制 轉 二進位制 方案”
- 遞迴函式實現十進位制正整數轉換為二進位制，八進位制，十六進位制遞迴函式
- 一看就懂二進位制、八進位制、十六進位制數轉換十進位制
- [計算機基礎] 計算機進位制轉換：二進位制、八進位制、十進位制、十六進位制計算機
- 十進位制轉十六進位制
- 十進位制——二 (八、十六 )進位制
- 整數轉化成八進位制、十六進位制、二進位制，以及轉回
- 進位制詳解：二進位制、八進位制和十六進位制
- JavaScript 二進位制、八進位制與十六進位制JavaScript
- JavaScript十進位制轉換為二進位制JavaScript
- Go語言實現十進位制轉換成二、八、十六進位制Go
- n進位制轉十進位制
- 二進位制轉十進位制快速方法
- 進位制數轉換方法（八/十六/十）
- 十進位制轉二進位制推導（草稿）
- Python 十進位制轉換為二進位制 高位補零Python
- 進位制與二進位制及相關轉換
- 什麼是二進位制？二進位制如何轉換？
- [20190716]十進位制轉換其他進位制指令碼.txt指令碼
- 十進位制轉換任意進位制--鏈棧實現
- JavaScript十六進位制和八進位制字面量JavaScript
- 八進位制，十六進位制和浮點數
- 3416：【例72.1】 二進位制轉化為十進位制
- 藍橋杯 基礎練習 十六進位制轉八進位制
- 教你如何進行數倉字串、二進位制、十六進位制互轉字串
- JavaScript中的多種進位制與進位制轉換JavaScript
- 十進位制轉十六進位制(藍橋杯之前每日一題)每日一題
- 進位制轉換
- oracle_16進位制與10進位制轉換小示例Oracle
- 口算 16 進位制轉換 10 進位制，但只適合兩位的 16 進位制
- 你知道二進位制、八進位制、十進位制、十六進位制的區別嗎？它們分別有什麼應用場景？