亚洲精品蜜桃久久久久久,亚洲日韩精品射精日,亚洲国产精品无码久久一区二区

Oracle中的進(jìn)制轉(zhuǎn)換

??? 本文通過創(chuàng)建一個Oracle的各種進(jìn)制之間的轉(zhuǎn)換函數(shù)包，來了解一下Oracle的Package、Procdure、Function、Type等等元素的基本特性，以及這些元素之間的相互關(guān)系。不過最值得借鑒的還是這個包的作者的編碼習(xí)慣，這的是非常賞心悅目的一段代碼啊，包括注釋結(jié)構(gòu)也很美。注：實例來源于網(wǎng)絡(luò)，我能夠找到的網(wǎng)址是：http://www.laozizhu.com/view-Oracle%E9%87%8C%E4%BA%8C%E8%BF%9B%E5%88%B6%E3%80%81%E5%85%AB%E8%BF%9B%E5%88%B6%E3%80%81%E5%8D%81%E8%BF%9B%E5%88%B6%E3%80%81%E5%8D%81%E5%85%AD%E8%BF%9B%E5%88%B6%E4%B9%8B%E9%97%B4%E7%9A%84%E4%BA%92%E7%9B%B8%E8%BD%AC%E6%8D%A2-18961.htm (汗，好長的漢字網(wǎng)址……)

Oracle里二進(jìn)制、八進(jìn)制、十進(jìn)制、十六進(jìn)制之間的互相轉(zhuǎn)換

--各進(jìn)制之間轉(zhuǎn)換

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 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
-- 對象描述: 二進(jìn)制轉(zhuǎn)換八進(jìn)制
-- 輸入?yún)?shù): p_str 二進(jìn)制字符串
-- 返回結(jié)果: 八進(jìn)制字符串
-- 測試用例: SELECT pkg_number_trans.f_bin_to_oct('11110001010') FROM dual;
-- 備注: 需要定義f_stragg函數(shù)和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
-- 對象描述: 二進(jìn)制轉(zhuǎn)換十進(jìn)制
-- 輸入?yún)?shù): p_str 二進(jìn)制字符串
-- 返回結(jié)果: 十進(jìn)制字符串
-- 測試用例: 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
-- 對象描述: 二進(jìn)制轉(zhuǎn)換十六進(jìn)制
-- 輸入?yún)?shù): p_str 二進(jìn)制字符串
-- 返回結(jié)果: 十六進(jìn)制字符串
-- 測試用例: SELECT pkg_number_trans.f_bin_to_oct('11110001010') FROM dual;
-- 備注: 需要定義f_stragg函數(shù)和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
-- 對象描述: 八進(jìn)制轉(zhuǎn)換二進(jìn)制
-- 輸入?yún)?shù): p_str 八進(jìn)制字符串
-- 返回結(jié)果: 二進(jìn)制字符串
-- 測試用例: SELECT pkg_number_trans.f_oct_to_bin('3612') FROM dual;
-- 備注: 需要定義f_stragg函數(shù)和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
-- 對象描述: 八進(jìn)制轉(zhuǎn)換十進(jìn)制
-- 輸入?yún)?shù): p_str 八進(jìn)制字符串
-- 返回結(jié)果: 十進(jìn)制字符串
-- 測試用例: 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
-- 對象描述: 八進(jìn)制轉(zhuǎn)換十六進(jìn)制
-- 輸入?yún)?shù): p_str 八進(jìn)制字符串
-- 返回結(jié)果: 十六進(jìn)制字符串
-- 測試用例: 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
-- 對象描述: 十進(jìn)制轉(zhuǎn)換二進(jìn)制
-- 輸入?yún)?shù): p_str 十進(jìn)制字符串
-- 返回結(jié)果: 二進(jìn)制字符串
-- 測試用例: 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
-- 對象描述: 十進(jìn)制轉(zhuǎn)換八進(jìn)制
-- 輸入?yún)?shù): p_str 十進(jìn)制字符串
-- 返回結(jié)果: 八進(jìn)制字符串
-- 測試用例: 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
-- 對象描述: 十進(jìn)制轉(zhuǎn)換十六進(jìn)制
-- 輸入?yún)?shù): p_str 十進(jìn)制字符串
-- 返回結(jié)果: 十六進(jìn)制字符串
-- 測試用例: 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
-- 對象描述: 十六進(jìn)制轉(zhuǎn)換二進(jìn)制
-- 輸入?yún)?shù): p_str 十六進(jìn)制字符串
-- 返回結(jié)果: 二進(jìn)制字符串
-- 測試用例: 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
-- 對象描述: 十六進(jìn)制轉(zhuǎn)換八進(jìn)制
-- 輸入?yún)?shù): p_str 十六進(jìn)制字符串
-- 返回結(jié)果: 八進(jìn)制字符串
-- 測試用例: 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
-- 對象描述: 十六進(jìn)制轉(zhuǎn)換十進(jìn)制
-- 輸入?yún)?shù): p_str 十六進(jìn)制字符串
-- 返回結(jié)果: 十進(jìn)制字符串
-- 測試用例: 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;
/

另外再轉(zhuǎn)一篇比較簡單的做法：

---------------------------------------------------------------------------------

http://www.wangchao.net.cn/bbsdetail_1775191.html

進(jìn)制轉(zhuǎn)換是開發(fā)中經(jīng)常需要用到的，本文簡單介紹幾種常用的進(jìn)制轉(zhuǎn)化方法.

一 16進(jìn)制轉(zhuǎn)換為10進(jìn)制

　　可以通過to_number函數(shù)實現(xiàn)

　　SQL> select to_number(’19f’,’xxx’) from dual;

　　TO_NUMBER(’19F’,’XXX’)
　　----------------------
　　415

　　SQL> select to_number(’f’,’xx’) from dual;

　　TO_NUMBER(’F’,’XX’)
　　-------------------
　　15

二 10進(jìn)制轉(zhuǎn)換為16進(jìn)制

　　可以通過to_char函數(shù)轉(zhuǎn)換

　　SQL> select to_char(123,’xxx’) from dual;

　　TO_C
　　----
　　7b

　　SQL> select to_char(4567,’xxxx’) from dual;

　　TO_CH
　　-----
　　11d7

三 2進(jìn)制轉(zhuǎn)換為10進(jìn)制

　　從Oracle9i開始,提供函數(shù)bin_to_num進(jìn)行2進(jìn)制到10進(jìn)制的轉(zhuǎn)換

　　SQL> select bin_to_num(1,1,0,1) a,bin_to_num(1,0) b from dual;

　　A ??? B
　　----- ----------
　　13 ?? 2

　　SQL> select bin_to_num(1,1,1,0,1) from dual;

　　BIN_TO_NUM(1,1,1,0,1)
　　---------------------
　　29

四通過自定義函數(shù)實現(xiàn)進(jìn)制轉(zhuǎn)換

　　以下函數(shù)來自AskTom網(wǎng)站,是Tom給出的例子,供參考:

　　create or replace function to_base( p_dec in number, p_base in number )
　　return varchar2
　　is
　　l_str varchar2(255) default NULL;
　　l_num number default p_dec;
　　l_hex varchar2(16) default ’0123456789ABCDEF’;
　　begin
　　if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
　　raise PROGRAM_ERROR;
　　end if;
　　loop
　　l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) l_str;
　　l_num := trunc( l_num/p_base );
　　exit when ( l_num = 0 );
　　end loop;
　　return l_str;
　　end to_base;
　　/

　　create or replace function to_dec
　　( p_str in varchar2,
　　p_from_base in number default 16 ) return number
　　is
　　l_num number default 0;
　　l_hex varchar2(16) default ’0123456789ABCDEF’;
　　begin
　　for i in 1 .. length(p_str) loop
　　l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1;
　　end loop;
　　return l_num;
　　end to_dec;
　　/
　　show errors

　　create or replace function to_hex( p_dec in number ) return varchar2
　　is
　　begin
　　return to_base( p_dec, 16 );
　　end to_hex;
　　/

　　create or replace function to_bin( p_dec in number ) return varchar2
　　is
　　begin
　　return to_base( p_dec, 2 );
　　end to_bin;
　　/

　　create or replace function to_oct( p_dec in number ) return varchar2
　　is
　　begin
　　return to_base( p_dec, 8 );
　　end to_oct;
　　/

posted on 2009-07-02 22:15 decode360 閱讀(1438) 評論(0) 編輯收藏所屬分類: 07.Oracle

新用戶注冊刷新評論列表


只有注冊用戶登錄后才能發(fā)表評論。




網(wǎng)站導(dǎo)航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關(guān)文章: 關(guān)于MV刷新的問題 Oracle XE安裝詳解 Oracle10g中OEM相關(guān)問題 Oracle約束的啟用和停用 Oracle修改TableSpace的Name Oracle的DataFile大小問題 Streams技術(shù)學(xué)習(xí) Oracle ASM介紹 Oracle高水位線(HWM)及性能優(yōu)化[轉(zhuǎn)] Oracle中的move命令

Decode360's Blog

公告

常用鏈接

留言簿(13)

隨筆分類(397)

隨筆檔案(397)

文章分類(33)

新聞分類(15)

收藏夾(74)

Blog_List

IT_Web

My_Link

最新隨筆

最新評論