ORACLE使用函数对二进制、十进制、十六进制数互相转换
ORACLE使用函数对二进制、十进制、十六进制数互相转换
1.将十进制的数转换为十六进制的数请使用to_char函数。
数据库中16进制的表达是按照字符串来描述的，所以将十进制的数转换为十六进制的数使用to_char函数
BYS@bys1>select to_char(10,'xxx'), to_char(42,'xxx') from dual;
TO_C TO_C
---- ----
a 2a
2.将十六进制的数转换为十进制的数请使用to_number函数。
如下，16进制A是10.2A是2*16+A=42.注意xxx，如果转换的数比较大，要多写几个，避免位数不足而报错。
BYS@bys1>select to_number('a','xxx'), to_number('2a','xxx') from dual;
TO_NUMBER('A','XXX') TO_NUMBER('2A','XXX')
-------------------- ---------------------
10 42
####################################################
ORACLE未提供二进制与十进制互转的函数，可以自己创建。以下十进制与二进制转换脚本引自惜纷飞博客，感谢！
3.十进制转换二进制--使用自定义函数
CREATE OR REPLACE FUNCTION NUMBER_TO_BIT(V_NUM NUMBER)
RETURN VARCHAR IS V_RTN VARCHAR(8);--注意返回列长度
V_N1 NUMBER;
V_N2 NUMBER;
BEGIN
V_N1 := V_NUM;
LOOP
V_N2 := MOD(V_N1, 2);
V_N1 := ABS(TRUNC(V_N1 / 2));
V_RTN := TO_CHAR(V_N2) || V_RTN;
EXIT WHEN V_N1 = 0;
END LOOP;
--返回二进制长度
SELECT lpad(V_RTN,8,0)
INTO V_RTN
FROM dual;
return V_RTN;
end;
BYS@bys1>col a255 for a20
BYS@bys1>col a1 for a20
BYS@bys1>select number_to_bit(255) as a255,number_to_bit(1) as a1 from dual;
A255 A1
-------------------- --------------------
11111111 00000001
4.二进制转换十进制--使用自定义函数
CREATE OR REPLACE FUNCTION BIT_TO_NUMBER(P_BIN IN VARCHAR2) RETURN NUMBER AS
V_SQL VARCHAR2(30000) := 'SELECT BIN_TO_NUM(';
V_RETURN NUMBER;
BEGIN
IF LENGTH(P_BIN) >= 256 THEN
RAISE_APPLICATION_ERROR(-20001, 'INPUT BIN TOO LONG!');
END IF;
IF LTRIM(P_BIN, '01') IS NOT NULL THEN
RAISE_APPLICATION_ERROR(-20002, 'INPUT STR IS NOT VALID BIN VALUE!');
END IF;
FOR I IN 1 .. LENGTH(P_BIN) LOOP
V_SQL := V_SQL || SUBSTR(P_BIN, I, 1) || ',';
END LOOP;
V_SQL := RTRIM(V_SQL, ',') || ') FROM DUAL';
EXECUTE IMMEDIATE V_SQL
INTO V_RETURN;
RETURN V_RETURN;
END;
BYS@bys1>select bit_to_number('11111111') from dual;
BIT_TO_NUMBER('11111111')
-------------------------
255
5.关于创建公有同义词解决其它用户引用当前用户的定义函数的问题：
当前BYS用户为DBA用户，其它用户默认无法使用当前用户的自定义函数。
可以将自定义函数的执行权限授予PUBLIC用户，并创建公有同义词。这样其它用户就可以通过引用公有同义词来使用新建的自定义函数了。
BYS@bys1>select * from user_objects where object_type='FUNCTION';
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
-------------------- ------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE
------------------- ------------------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
NUMBER_TO_BIT 76930 FUNCTION
2013/11/03 11:57:30 2013/11/03 11:57:30 2013-11-03:11:57:30 VALID N N N 1
BIT_TO_NUMBER 76931 FUNCTION
2013/11/03 12:00:16 2013/11/03 12:00:16 2013-11-03:12:00:16 VALID N N N 1
创建公有同义词--先不将函数执行权限授予PUBLIC，此时从其它用户使用函数报错。
BYS@bys1>create public synonym number_to_bit for number_to_bit;
Synonym created.
BYS@bys1>conn test/test
Connected.
TEST@bys1>select number_to_bit(255) from dual;
select number_to_bit(255) from dual
*
ERROR at line 1:
ORA-00904: : invalid identifier
将函数执行权限授予PUBLIC，此时从其它用户可以正常使用函数。
TEST@bys1>conn bys/bys
Connected.
BYS@bys1>grant execute on number_to_bit to public;
Grant succeeded.
BYS@bys1>conn test/test
Connected.
TEST@bys1>select number_to_bit(255) from dual;
NUMBER_TO_BIT(255)
----------------------------------------------------------------------------------------------------
11111111
#########################
对于bit_to_number函数，先将函数执行权限授予PUBLIC，但是并未创建同义词。此时使用其它用户执行函数报错
BYS@bys1>grant execute on bit_to_number to public;
Grant succeeded.
TEST1@bys1>select bit_to_number(11111111) from dual;
select bit_to_number(11111111) from dual
*
ERROR at line 1:
ORA-00904: "BIT_TO_NUMBER": invalid identifier
TEST1@bys1>conn bys/bys
Connected.
此时对函数创建公有同义词，使用其它用户可以正常执行函数。
BYS@bys1>create public synonym bit_to_number for bit_to_number;
Synonym created.
BYS@bys1>conn test1/test1
Connected.
TEST1@bys1>select bit_to_number(11111111) from dual;
BIT_TO_NUMBER(11111111)
-----------------------
255
###################################################################
6.二进制与十六进制的转换
因为没有直接的函数，但是结合以上的十进制转二进制与十进制转十六进制函数，可以通过函数嵌套来实现二进制与十六进制的转换。
即先使用二进制与十进制的转换，再将转换成的十进制做为参数转换为十六进制。
BYS@bys1>select number_to_bit(17) as a255 from dual;
A255
--------------------
00010001
下面语句得出16进制数11，换算成十进制是17，转换正确完成。
BYS@bys1>select to_char(bit_to_number('00010001'),&
- 更多Oracle疑问解答：
- 运行exp备份oracle数据库提示oracle-12154错误
- 有没有，生产Oracle Rman 备份脚本的工具啊！
- 初学orcle，希望有大大帮忙解说一下详细步骤，从登录oracle到创建表的过程
- oracle语句问题：一张user表，三个字段，id,name,time，插入记录比如：张三2007，李四2008，张三2011
- 如何写一个ORACLE触发器同步两个表中的数据？
- oracle 如何查看一个服务器上有多少个数据库.
- oracle 创建包的时候错误 求解
- oracle 重复列的问题
- oracle 中如何查处2星期前的数据
- 请教oracle数据库安装中的问题
- 请问谁能提供给我标准的oracle ERP的数据库表结构并详细说明各表主要的作用?
- 安装oracle遇到的问题 invalid entry CRC (expected 0x3e12e795 but got 0x9db0e9fd)
- 我的是ORACLE 10G，在RMAN中如何按指定的时间恢复数据文件啊？
- oracle为什么没有自动增长列
- oracle快捷键都有哪些啊？