oracle二进制数据类型 [英] oracle binary data types

问题描述

是否存在一种类型或方式以二进制级别在oracle中存储数据. 我对表中的dml和pl/sql的操作都感兴趣.

is there a type or way how to store data in oracle in binary level. I'm interested both in operations for dml at table and pl/sql.

当前所有二进制元素都以varchar2(1000)='11111 ... 0000.1111'的形式存储，但是操作和数据存储量很大，因此需要一些用于优化的解决方案.如果此数据可以二进制格式存储，则将需要1000/8字节 (有超过7亿条记录)

Currently all binary elements are stored as varchar2(1000)='11111...0000.1111' however operations and data storage size is rather big, therefore need some solutions for optimization. If this data could be stored in binary format, it would require 1000/8 bytes (have >700mln records)

也许一个解决方案是对这些操作使用某种java + oracle组合.

Maybe a solution would be using some kind of java+oracle combination for these operations.

欢迎提出想法和建议.

推荐答案

我不确定是否有帮助，但是您可以使用NUMBER数据类型和

I'm not sure if this helps, but you can usea NUMBER datatype and bin_to_num function:

create table test_bin(num_val number, var_val varchar2(1000));

insert into test_bin values(bin_to_num(1,0,1,0,1,0,1,0), '10101010');

select dump(num_val, 17) n, dump(var_val, 17) v from test_bin;

n                     |   v
---------------------------------------------------
Typ=2 Len=3 c2,^B,G   |   Typ=1 Len=8 1,0,1,0,1,0,1,0

如您所见，它是一个排序器-而不是8个字节，您只有3个字节，但仍然不是1个字节

As you can see, it's sorter - instead of 8 bytes you have only 3, but it's still not one byte

更新 找到了一种将其保存在一个字节中的方法: 您可以使用chr函数

UPDATE Found a way to have it in one byte: You can use the chr function

select dump(chr(bin_to_num(1,0,1,0,1,0,1,0))) c from dual;

n                   
--------------------
Typ=1 Len=1 170

因此，您可以继续使用varchar2，但只需将每8位转换为一个字符

So, you can keep using varchar2 but just convert every 8 bits to a char

这篇关于oracle二进制数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！