> 本文主要介绍 Oracle下 批量导入导出图片的方法，存储过程 #### Oracle LOB 类型介绍 ##### BLOB的含义 BLOB (binary large object)，二进制大对象，是一个可以存储二进制文件的容器。 主要图片、声音文件等,保存位图 ##### CLOB的含义 CLOB(Character Large Object),它与LONG数据类型类似，只不过CLOB用于存储数据库中的大型单字节字符数据块，不支持宽度不等的字符集。可存储的最大大小为4G字节 .CLOB使用CHAR来保存数据。 如：保存XML文档。 #### 批量导入导出 ##### 批量导入 ``` sql --创建表，及目录 create table image_lob(t_id varchar2(10) not null,name varchar2(30),t_image blob not null); create or replace directory "images_dump" as '/home/oracle/images_dump/'; --创建存储过程 create or replace procedure img_insert(tid varchar2,vname varchar2,filename varchar2) as f_lob bfile; b_lob blob; begin insert into image_lob(t_id,name,t_image) values(tid,vname,empty_blob()) return t_image into b_lob; f_lob:=bfilename('images_dump',filename); dbms_lob.fileopen(f_lob,dbms_lob.file_readonly); dbms_lob.loadfromfile(b_lob,f_lob,dbms_lob.getlength(f_lob)); dbms_lob.fileclose(f_lob); commit; end; / --执行导入 exec img_insert(89898989,'firsouler','dbwn.png'); exec img_insert(16211779,'abc','adr.png'); ``` ##### 批量导出 ``` sql --创建导出目录,注意权限 create or replace directory BLOBDIR as '/home/oracle/images_exp'; grant read,write on directory BLOBDIR to mytest; --创建过程,本例使用t_id,name列作为照片名字,根据t_id 排序获取rownum CREATE OR REPLACE PROCEDURE GET_PIC_BLOB(i_xh VARCHAR2) IS l_file UTL_FILE.FILE_TYPE; l_buffer RAW(32767); l_amount BINARY_INTEGER := 32767; l_pos INTEGER := 1; l_name VARCHAR2(30); l_id VARCHAR2(30); l_blob BLOB; l_blob_len INTEGER; BEGIN with tmp_image as (select rownum as rn,t_id,name,t_image from image_lob order by t_id) select t_id,name,t_image into l_id,l_name,l_blob from tmp_image where rn = i_xh; l_blob_len := DBMS_LOB.GETLENGTH(l_blob); l_file := UTL_FILE.FOPEN('BLOBDIR', l_id || '_' || l_name || '.jpg', 'WB', 32767); WHILE l_pos < l_blob_len LOOP DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer); UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE); l_pos := l_pos + l_amount; END LOOP; UTL_FILE.FCLOSE(l_file); EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLERRM); IF UTL_FILE.IS_OPEN(l_file) THEN UTL_FILE.FCLOSE(l_file); END IF; RAISE; END GET_PIC_BLOB; / --循环导出200个图片,以列t_id排序获取rownum declare cursor cur is with t as (select rownum as rn from image_lob order by t_id) select rn from t where rownum <= 200; --导出前200张 begin for rec in cur loop GET_PIC_BLOB(rec.rn); end loop; end; / ```