CREATE TABLE IMAGE_LOB
(
T_ID VARCHAR2 (5) NOT NULL,
T_IMAGE BLOB NOT NULL
);
--表已创建。
--conn as sys
CREATE OR REPLACE DIRECTORY IMAGES AS 'E:\IMG';
GRANT READ ON DIRECTORY IMAGES TO scott;
--目录已创建。
--conn as scott
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()) RETURNING T_IMAGE INTO B_LOB;
F_LOB:= BFILENAME('IMAGES', FILENAME);
IF DBMS_LOB.FILEISOPEN(F_LOB)=0 THEN
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);
END IF;
COMMIT;
END IMG_INSERT;
/
--过程已创建。
BEGIN
IMG_INSERT('1','啊啊.gif');
END;
/
--PL/SQL 过程已成功完成。
select length(t_image) from image_lob where t_id='1';
LENGTH(T_IMAGE)
---------------
99999999
(
T_ID VARCHAR2 (5) NOT NULL,
T_IMAGE BLOB NOT NULL
);
--表已创建。
--conn as sys
CREATE OR REPLACE DIRECTORY IMAGES AS 'E:\IMG';
GRANT READ ON DIRECTORY IMAGES TO scott;
--目录已创建。
--conn as scott
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()) RETURNING T_IMAGE INTO B_LOB;
F_LOB:= BFILENAME('IMAGES', FILENAME);
IF DBMS_LOB.FILEISOPEN(F_LOB)=0 THEN
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);
END IF;
COMMIT;
END IMG_INSERT;
/
--过程已创建。
BEGIN
IMG_INSERT('1','啊啊.gif');
END;
/
--PL/SQL 过程已成功完成。
select length(t_image) from image_lob where t_id='1';
LENGTH(T_IMAGE)
---------------
99999999