oracle二进制如何 oracle转二进制函数
如何将二进制文件存入Oracle数据库中
先把文件读取到内存，再以二进制格式保持到数据库中的大字段中（clob或clob）。
杂多网站制作公司哪家好，找创新互联建站！从网页设计、网站建设、微信开发、APP开发、成都响应式网站建设等网站项目制作，到程序开发，运营维护。创新互联建站从2013年开始到现在10年的时间，我们拥有了丰富的建站经验和运维经验，来保证我们的工作的顺利进行。专注于网站建设就选创新互联建站。
写大对象。
Java code
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
OutputStream os = null;
FileInputStream fis = null;
int bs = 0;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:oraDB","bigfou","---");
conn.setAutoCommit(false);
stat = conn.createStatement();
stat.executeUpdate("insert into t_video(id,video) values(1,empty_blob())");
rs = stat.executeQuery("select video from t_video where id = 1");
rs.next();
oracle.sql.BLOB blo = (oracle.sql.BLOB)rs.getBlob(1);
os = blo.getBinaryOutputStream();
bs = blo.getBufferSize();
fis = new FileInputStream("D:\\Temp\\MPlayer-CVS-20040808-KK\\mplayer.exe");
byte[] buf = new byte[bs];
int length = 0;
while(true)
{
length = fis.read(buf);
if(length == -1) break;
os.write(buf,0,length);
}
os.close();
os = null;
fis.close();
fis = null;
conn.commit();
conn.setAutoCommit(true);
conn.close();
} catch(Exception ex) {
ex.printStackTrace();
}
}
读大对象
Java code
InputStream is = null;
FileOutputStream fos = null;
byte[] buf = null;
int bs = 0;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:oraDB","bigfou","-");
conn.setAutoCommit(false);
stat = conn.createStatement();
rs = stat.executeQuery("select video from t_video where id = 1");
rs.next();
oracle.sql.BLOB blo = (oracle.sql.BLOB)rs.getBlob(1);
bs = blo.getBufferSize();
buf = new byte[bs];
int length = 0;
is = blo.getBinaryStream();
fos = new FileOutputStream("d:\\test.exe");
while(true) {
length = is.read(buf);
if(length == -1) break;
fos.write(buf,0,length);
}
fos.close();
fos = null;
is.close();
is = null;
conn.commit();
conn.setAutoCommit(true);
conn.close();
...
Oracle中进制表示法
用to_char就可以把十进制转成十六进制：
select to_char(31, '0x') from dual;
结果：1f
1.将十进制的数转换为十六进制的数请使用to_char函数。
数据库中16进制的表达是按照字符串来描述的，所以将十进制的数转换为十六进制的数使用to_char函数
BYS@bys1select 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@bys1select 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@bys1col a255 for a20
BYS@bys1col a1 for a20
BYS@bys1select 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@bys1select bit_to_number('11111111') from dual;
BIT_TO_NUMBER('11111111')
-------------------------
255
怎么将oracle数据库存的二进制转换十进制
--十进制转其他进制函数
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;
文章名称：oracle二进制如何 oracle转二进制函数
标题路径：http://scyanting.com/article/hipcje.html
