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;
/
原文地址:http://blog.csdn.net/heyixiang/archive/2008/01/02/2008512.aspx
分享到:
相关推荐
二进制 八进制 十进制 十六进制 相互转换 jar 含源代码 运行前安装JRE先,下载地址: http://download.oracle.com/otn-pub/java/jdk/6u29-b11/jre-6u29-windows-i586.exe 本程序用Java编写,适合java爱好者使用
一个SQL脚本函数,10进制转换为34进制。34进制指以0、1、2、3、4、5、6、7、8、9、A、B、C、D、E、F、G、H、J、K、L、M、N、P、Q、R、S、T、U、V、W、X、Y、Z为基数做累加。
实现10进制和34进制之间的转换,资源包含三个Oracle自定义函数,分别为: 34进制转换为10进制; 10进制转换为34进制; 生成34进制的最大编码; 欢迎提宝贵意见! 799192594@qq.com
图片以二进制文件存入oracle,图片以二进制文件存入oracle
一个SQL脚本函数,34进制转换为10进制。36进制指以0、1、2、3、4、5、6、7、8、9、A、B、C、D、E、F、G、H、J、K、L、M、N、P、Q、R、S、T、U、V、W、X、Y、Z为基数做累加。
正在看的ORACLE教程是:Oracle建立二进制文件索引的方法。Oracle text是Oracle的全文检索技术,是9i版本标准版和企业版的一部分。Oracle text使用标准的sql语言索引、查找、分析存储在oracle数据库、文件或者网络里...
springMvc+Oracle的图片转二进制流,导入Eclipse中,sql文件在WebRoot中。添加数据表后,更改hibernate.properties配置文件。然后再运行。
43.CONVERT(c,dset,sset) 将源字符串 sset从一个语言...44.HEXTORAW 将一个十六进制构成的字符串转换为二进制 45.RAWTOHEXT 将一个二进制构成的字符串转换为十六进制 46.ROWIDTOCHAR 将ROWID数据类型转换为字符类型
Oracle,SQL2000,MYSQL存取图片或二进制数据的功能文件,请配合相关的驱动包使用。
本文举例介绍了ORACLE中用一条SQL实现其它进制到十进制的转换。
连接SqlServer2008与Oracle数据库,读取与保存图片的二进制流,其中不附带数据库,方法简单明了
本文介绍了在Oracle中用SQL语句实现进制间互相转换的方法。
oracle函数介绍 1 著名函数之单值函数 pdf oracle函数介绍 2 非著名函数之单值函数 pdf oracle函数介绍 3 著名函数之聚合函数 pdf oracle函数介绍 4 非著名函数之聚合函数 pdf oracle函数介绍 5 分析函数简述 ...
oracle 函数大全oracle 函数大全oracle 函数大全oracle 函数大全oracle 函数大全oracle 函数大全oracle 函数大全
oracle 的一些常用类型转换函数讲解。
ORACLE常用数值函数、转换函数,用例简单,非常实用。
oracle函数大全 oracle函数大全 oracle函数大全
oracle函数大全 tan tanh trunc 字符类型转换 十六精致构成转化二进制
oracle数据库,使用自定义函数,实现了金额的大写转换。sql语句中,直接调用此函数即可得到金额大写。
NULL 博文链接:https://chaozhichen.iteye.com/blog/945579