oracle-向blob字段插入图片,并查看每张图片大小---oracle
--创建表空间
CREATE TABLESPACE test
LOGGING
DATAFILE '/u01/app/oracle/oradata/orcl/test.dbf'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
--创建用户
create user test identified by test;
注:创建表空间和用户后,建议使用客户端(如PLSQL)执行下面的语句
--创建表
CREATE TABLE test.IMAGE_LOB (T_ID int primary key NOT NULL,T_IMAGE BLOB NOT NULL);
--创建图片目录,然后将no1,no2,no3图片上传到这个目录
CREATE OR REPLACE DIRECTORY IMAGES AS '/home/oracle/piture';
--创建过程
CREATE OR REPLACE PROCEDURE IMG_INSERT (TID VARCHAR2,FILENAME VARCHAR2) AS
F_LOB BFILE;
B_LOB BLOB;
BEGIN
INSERT INTO IMAGE_LOB (T_ID, T_IMAGE) VALUES (TID,EMPTY_BLOB ()) RETURN T_IMAGE INTO B_LOB;
F_LOB:= BFILENAME ('IMAGES', 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;
--调用存储过程,注意这里的no1.jpg为图片的名称
call IMG_INSERT('1','no1.jpg');
call IMG_INSERT('2','no2.jpg');
call IMG_INSERT('3','no3.jpg');
--查看blob每行字段大小(每张图片)
select t_id,t_image,dbms_lob.getLength(T_IMAGE) from test.IMAGE_LOB;