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;