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

The table to insert the picture is not fixed, and a lot of pictures are inserted in batches, and it is also considered that because of the wrong file name of the picture, it should be inserted again after modification. At this time, repeated operations that have already been inserted should be avoided and a waste of time.

So choose to use a temporary table to temporarily save the photos read from the file system, using the DBMS_LOB method to deal with.

Step 1: create a directory that the database can access (note: this directory is on the database server, not on the client)

-- Create directory create or replace directory picture directory as'e:\ photo'

Step 2: put the picture file under the directory you just built. Don't create a new folder, just put it in this root directory.

The third step: according to their own specific needs, write stored procedures, before doing, I also found a lot on the Internet, but basically just about said, did not find a more complete, here to post their own project source code, for everyone to learn and communicate.

CREATE OR REPLACE PROCEDURE PRO_ insert picture (V _ table name IN VARCHAR2) IS P_FILENAME VARCHAR2 (50);-- photo name, dynamic stitching to get P _ document number VARCHAR2 (50); P _ name VARCHAR2 (50);-- this photo name is obtained by splicing the name + document number. Because the basic test data do not provide the real document number, choose to use the mobile phone number instead of P _ query SQL VARCHAR2 (500). PUPUPDAT SQL VARCHAR2 (5000); P_LOB BLOB; P_FILE BFILE; TYPE P_REF_CURSOR IS REF CURSOR;-defines the dynamic cursor variable type P_CURSOR cursor ref;-defines the dynamic cursor variable, because you want to insert photos of the whole table at once, so you choose to use cursors to process TYPE P_ROW_RECORD IS RECORD (document number VARCHAR2 (50), name VARCHAR2 (50)); C_ROW P_ROW_RECORD V_ERR VARCHAR2; BEGIN P_ update SQL: = 'update' | | V_ table name | | 'set ID number = Mobile number WHERE ID number IS NULL';-- uses the mobile number instead of the empty data EXECUTE IMMEDIATE P_ update SQL; COMMIT; P_ query SQL: =' SELECT ID number, name FROM'| | V_ form name | | 'WHERE ID number, IS NOT NULL and photo IS NULL order by ID' | OPEN P_CURSOR FOR P _ query SQL; LOOP begin FETCH P_CURSOR INTO paid row; EXIT WHEN P_CURSOR%NOTFOUND

-- obtain the document number and name, first exclude dirty data such as spaces, and then splice it into a file name

P _ document number: = C_ROW. Certificate number; P _ name: = C_ROW. Name; SELECT REPLACE (P _ document number,'',') INTO P _ document number FROM DUAL; SELECT substr (P _ document number, 1, 11) INTO P _ document number FROM DUAL; SELECT REPLACE (P _ name,'','') INTO P _ name FROM DUAL; P_FILENAME: = P _ document number | P _ name | | '.jpg'; SELECT REPLACE (P_FILENAME,',') INTO P_FILENAME FROM DUAL -- the following is the core code for inserting the picture: INSERT INTO TA_ photo summary table _ TEMP (document number, name, photo) VALUES (P _ document number, P _ name, EMPTY_BLOB ()) RETURN photo INTO displayed lob;-- get the files in the specified directory P_FILE: = BFILENAME ('picture directory', P_FILENAME) -- Open the file DBMS_LOB.FILEOPEN (P_FILE, DBMS_LOB.FILE_READONLY) read-only;-- pass the object DBMS_LOB.LOADFROMFILE (P_LOB, P_FILE, DBMS_LOB.GETLENGTH (P_FILE));-- close the original file DBMS_LOB.FILECLOSE (P_FILE); COMMIT

Insert a picture into the target table through an update statement

SQL: = 'UPDATE' | | V_ table name | |'A SET a. Photo = (SELECT photo FROM TA_ photo summary table _ TEMP b WHERE A. Document number = B. Document number and a. Name = b. Name AND ROWNUM=1) WHERE EXISTS (SELECT 1 FROM TA_ photo summary table _ TEMP B WHERE A. Document number = B. Document number and a. Name = b. Name)'; EXECUTE IMMEDIATE P_ update SQL; COMMIT; EXCEPTION

Handle the exception situation, which can skip the exception and continue to run when an exception occurs. Normal data can still be inserted and abnormal information can be recorded to facilitate exception handling. This is because the process of writing for the first time was broken as soon as the error was reported, and the picture that could have been inserted could not continue, and then this optimization was made.

WHEN OTHERS THEN rollback; V_ERR: = SUBSTR (SQLERRM, 1150) | | 'Photo name:' | | P_FILENAME

It is a very good habit to define a record table of abnormal information.

The INSERT INTO TA_ program runs the exception record VALUES (SQ_ exception sequence .NEXTVAL, 'PRO_ insert Picture', V_ERR, SYSDATE); COMMIT; end; END LOOP; CLOSE matching current; COMMIT; DELETE TA_ Photo General Table _ TEMP; COMMIT;END PRO_ insert Picture

Summary

Then test, run, basically no problem, but the size of the picture, very affect the actual insertion time, this time optimization there is no good countermeasure.

The above is the whole content of this article on ORACLE batch import pictures to BLOB field code example, I hope it will be helpful to everyone. Interested friends can refer to: oracle database startup phase analysis, ORACLE SQL sentence optimization technical points analysis, oracle length, lengthb, substr, substrb function usage introduction, etc., if you have any questions, you can leave a message at any time, the editor will reply to you in time. I also hope that friends will support the website!

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.