Shulou(Shulou.com)06/01 Report--

Insert exampl

1. Insert data by using stored procedures

-- create a table to store pictures

CREATE TABLE IMAGE_LOB (T_ID VARCHAR2 (5) NOT NULL, T_IMAGE BLOB NOT NULL)

-- create a directory to store pictures. I am on the local C disk.

CREATE OR REPLACE DIRECTORY IMAGES AS'C:\'

-- insert through stored procedures

CREATE OR REPLACE PROCEDURE IMG_INSERT (TID VARCHAR2, FILENAME VARCHAR2) AS

F_LOB BFILE;-- File type

B_LOB BLOB

BEGIN

-- insert empty blob: EMPTY_BLOB ()

INSERT INTO IMAGE_LOB (T_ID, T_IMAGE)

VALUES (TID, EMPTY_BLOB ()) RETURN T_IMAGE INTO B_LOB

-- get the files in the specified directory

Favored lobs = BFILENAME ('IMAGES', FILENAME)

-- Open the file as read-only

DBMS_LOB. FILEOPEN (F_LOB, DBMS_LOB. FILE_READONLY)

-- passing object

DBMS_LOB. LOADFROMFILE (B_LOB, F_LOB, DBMS_LOB. GETLENGTH (F_LOB))

-- close the original file

DBMS_LOB. FILECLOSE (F_LOB)

COMMIT

END

/

two。 Call a stored procedure to insert a picture

Call IMG_INSERT ('1', '1.jpg')

3. Verification result

Select * from IMAGE_LOB

Modify exampl

1. Modify through the process

Declare

L_bfile bfile

L_blob blob

Begin

Update IMAGE_LOB set T_IMAGE=empty_blob () where T_ID=1 return T_IMAGE into l_blob

-- the image path is still on disk C, which can be queried through the system table SELECT * FROM dba_directories;

L_bfile:=bfilename ('IMAGES',' 2.jpg')

-- Open the file as read-only

Dbms_lob. Open (l_bfile, dbms_lob. File_readonly)

-- passing object

Dbms_lob. Loadfromfile (l_blob, l_bfile, dbms_lob. Getlength (l_bfile))

-- close the original file

Dbms_lob. Close (l_bfile)

Commit

End

/

two。 Verification result

Select * from IMAGE_LOB

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.