1、 在${path}/创建img文件夹,存放图片路径:${path}/图片文件夹
注意:文件目录一定要和数据库在同一台机器上
2、 在文件夹中放入图片
3、 用pl/sql登录oracle
4、在oracle中创建BLOBDIR目录,并与img文件夹关联
5、创建表
(1)
create table TBL_MONITOR_CAR_INFO
(
CLXXBH VARCHAR2(38) not null,
KKBH VARCHAR2(18),
JGSJ VARCHAR2(14),
XSFX VARCHAR2(2),
CDBH VARCHAR2(2),
HPHM VARCHAR2(15),
HPYS VARCHAR2(1),
ZPSL NUMBER(2),
CLSD NUMBER(5),
XSZT VARCHAR2(4),
CLPP1 VARCHAR2(50),
CLPP2 VARCHAR2(50),
CWKC NUMBER(4),
CSYS VARCHAR2(5),
YSSQ VARCHAR2(1),
CLLX VARCHAR2(4),
HPZL VARCHAR2(2)
)
(2)
create table TBL_MONITOR_PIC_INFO
(
CLXXBH VARCHAR2(38) not null,
CTHPTZZP BLOB,
CWHPTZZP BLOB,
QJZP BLOB,
CTZP BLOB,
CWZP BLOB,
YLZP1 BLOB,
YLZP2 BLOB
)
6、编写存储过程
CREATE OR REPLACE PROCEDURE car_init_data IS
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
id varchar2(40);
BEGIN
dbms_output.put_line('starttime:'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
for i in 39438..40438 loop
SELECT SEQ_CAR_MONITOR_ID.NEXTVAL INTO id FROM DUAL;
src_file := bfilename('BLOBDIR', mod(i,16)+1||'.jpg');
--src_file := bfilename('BLOBDIR',i||'.jpg');
INSERT INTO TBL_MONITOR_CAR_INFO (CLXXBH,KKBH,JGSJ)VALUES(id,100000000000000001,sysdate);
INSERT INTO TBL_MONITOR_PIC_INFO (CLXXBH,CTHPTZZP)VALUES (id, EMPTY_BLOB()) RETURNING CTHPTZZP INTO dst_file;
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
DBMS_LOB.close(src_file);
commit;
end loop;
dbms_output.put_line('endtime:'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
END car_init_data;
7、调用存储过程,测试程序
Begin
car_init_data;
End;