亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

Oracle中的二進制、八進制、十進制、十六進制相

系統 1780 0

Oracle中的二進制、八進制、十進制、十六進制相互轉換函數

?

今天在網上看到一篇關于在oracle中對各種進制數進行轉換的帖子,覺得不錯,也比較全面,幾乎涵蓋了經常用到的所有轉換。轉過來學習一下,也方便以后查詢。

==================================================================================

大家經常遇到進制轉換的問題,網上搜到的轉換只是部分十進制與其它進制的轉換,所以我把自己寫的轉換函數提供給大家,4種進制共12個轉換函數,雖然有的轉換直接使用to_char()和to_number()就可以實現,但我還是把它們整理到一起,使用和查找都方便。

部分函數需要先創建type_str_agg類型和f_stragg函數才能使用,這兩個對象的代碼也附在之后。

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;
/

Oracle中的二進制、八進制、十進制、十六進制相互轉換函數


更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯系: 360901061

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦?。。?/p>

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 国内视频一区二区 | 特黄特黄aaaa级毛片免费看 | 日本a∨在线观看 | 综合久久久久综合体桃花网 | 毛色毛片免费看 | 日韩成人国产精品视频 | 一级毛片牲交大片 | 亚洲精品久久久久久久久久久网站 | 久色99| 香蕉久久精品 | 国产农村精品一级毛片视频 | 成人精品视频在线观看播放 | 一区二区三区在线免费观看视频 | 天天插天天操天天干 | 四虎国产精品永久地址51 | 好吊妞视频一区二区 | 成人永久免费视频网站在线观看 | se94se亚洲欧美在线 | 国产成人精品日本亚洲网站 | 奇米网狠狠 | 老子影院午夜伦手机不卡无 | 亚洲视频免费一区 | 久久久久毛片免费观看 | 色综合色综合色综合色综合 | 青青国产成人精品视频 | 亚洲一级在线观看 | 天天操 夜夜操 | 人体做爰aaaa免费 | 国内精品久久久久久久久蜜桃 | 中文字幕在线看视频一区二区三区 | 日韩二区 | 日日摸夜夜摸无需播放器 | 中文字幕欧美一区 | 日日舔夜夜操 | 中文字幕欧美日韩va免费视频 | 伊人久热这里只有精品视频99 | 羞羞视频在线看 | 欧美视频在线观看一区二区 | 久久99中文字幕伊人 | 日日噜噜夜夜狠狠久久aⅴ 日日噜噜夜夜狠狠久久丁香 | 欧美一级暴毛片 |