【聚杰网PL/SQL】Oracle进阶 LOB字段学习

--插入bfile

create or replace procedure insert_book(filename varchar2) as
 book_file  bfile := NULL;
 bookExists boolean := false;
begin
 book_file  := bfilename('BOOK_TEXT', filename);
 bookExists  := dbms_lob.fileexists(book_file) = 1;
 
 if bookExists then
  insert into my_book_files values ((select count(*) from my_book_files) + 1 , book_file);
  dbms_output.put_line('Insert sucess! file : ' || filename);
 else
  dbms_output.put_line('Not exists! file : ' || filename);
   end if;
exception
  when dbms_lob.noexist_directory then
       dbms_output.put_line('Error: ' || sqlerrm); 
  when dbms_lob.invalid_directory then
       dbms_output.put_line('Error : ' || sqlerrm);
  when others then
       dbms_output.put_line('Unkown Error : ' || sqlerrm);      
end insert_book;
/
 

create or replace procedure insertPDF(fileName varchar2) is
        fileLoc bfile;
        nID number;
        nPDFSize integer;
        bFileExists boolean := false;
begin
     fileLoc := bfilename('PDFDIR',filename);
     bFileExists := dbms_lob.fileexists(fileLoc) = 1;
     if bFileExists = false then
        dbms_output.put_line(fileName || ' not exists');
        return;
     end if;
    
     nPDFSize := dbms_lob.getlength(fileLoc);
     dbms_output.put_line('the length of ' || fileName || ' is ' || nPDFSize);
     select count(*) + 1 into nID from PDFTable;
     insert into PDFTable(ID,Pdffile)
            values (nID, fileLoc);
exception
  when dbms_lob.noexist_directory then
       dbms_output.put_line('Error: ' || sqlerrm); 
  when dbms_lob.invalid_directory then
       dbms_output.put_line('Error : ' || sqlerrm);
  when others then
       dbms_output.put_line('Unkown Error : ' || sqlerrm);
end;      
/

--插入 blob

CREATE OR REPLACE procedure insertImg(imgName varchar2) is
        v_file_loc bfile;
        v_image blob;
        nID number;
        nImgSize integer;
 bFileExists boolean := false;
begin
      v_file_loc := bfilename('IMAGEDIR', imgName);
      bFileExists := dbms_lob.fileExists(v_file_loc) = 1;
      if bFileExists = false then
       dbms_output.put_line(imgName || ' not exists');
       return;
      end if;

      nImgSize := dbms_lob.getlength(v_file_loc);
      dbms_output.put_line(imgName ||' size is ' || nImgSize);
      dbms_output.put_line('Now Inserting empty image row');

      select count(*) + 1 into nID from imagetable;
      insert into imagetable(ID, image)
             values (nID, empty_blob)
             returning image into v_image;

      DBMS_LOB.FILEOPEN (v_file_loc);
      dbms_output.put_line('Open file');
      dbms_lob.loadfromfile(v_image, v_file_loc, nImgSize);
      DBMS_LOB.FILECLOSE(v_file_loc);
      commit;
exception
          when others then
               dbms_output.put_line('Error happen! ' || sqlerrm);
        DBMS_LOB.FILECLOSE(v_file_loc);
end insertImg;
/

--=================================================

SQL> create table view_sites_info (
  2   site_id  number(3),
  3   audio  blob default empty_blob(),
  4   document clob default empty_clob(),
  5   video_file  bfile default null
  6  );

表已创建。

SQL> commit;

提交完成。

SQL> @e:/writelob

PL/SQL 过程已成功完成。

SQL> desc view_sites_info;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 SITE_ID                                            NUMBER(3)
 AUDIO                                              BLOB
 DOCUMENT                                           CLOB
 VIDEO_FILE                                         BINARY FILE LOB

SQL> select document from view_sites_info where site_id = 100;

DOCUMENT                                                                       
--------------------------------------------------------------------------------
This is a writing example                                                      
SQL> desc view_sites_info
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 SITE_ID                                            NUMBER(3)
 AUDIO                                              BLOB
 DOCUMENT                                           CLOB
 VIDEO_FILE                                         BINARY FILE LOB

SQL> insert into blobtest values (1, bfilename('tempdir', 'C:/Documents and Settings/Administrator/My Documents/My Pictures/tu1.jpg'));
insert into blobtest values (1, bfilename('tempdir', 'C:/Documents and Settings/Administrator/My Documents/My Pictures/tu1.jpg'))
                                *
ERROR 位于第 1 行:
ORA-00932: 数据类型不一致

SQL> desc BFILETEST
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(3)
 FNAME                                              BINARY FILE LOB

SQL> insert into BFILETEST values (1, bfilename('tempdir', 'C:/Documents and Settings/Administrator/My Documents/My Pictures/tu1.jpg'));

已创建 1 行。

SQL> get E:/insertimg
  1  create or replace procedure img_insert (
  2     tid   varchar2,
  3     filename  varchar2) as
  4     F_LOB   BFILE;
  5     B_LOB  BLOB;
  6  begin
  7   insert into image_lob (t_id, t_image) values (tid, empty_blob()) return t_image into B_LOB;
  8   F_LOB := bfilename('images', filename);
  9   dbms_lob.fileopen(F_LOB, dbms_lob.file_readonly);
 10   dbms_lob.loadfromfile(B_LOB, F_LOB, dbms_lob.getlength(F_LOB));
 11   dbms_lob.fileclose(F_LOB);
 12   commit;
 13* end;
SQL> r
  1  create or replace procedure img_insert (
  2     tid   varchar2,
  3     filename  varchar2) as
  4     F_LOB   BFILE;
  5     B_LOB  BLOB;
  6  begin
  7   insert into image_lob (t_id, t_image) values (tid, empty_blob()) return t_image into B_LOB;
  8   F_LOB := bfilename('images', filename);
  9   dbms_lob.fileopen(F_LOB, dbms_lob.file_readonly);
 10   dbms_lob.loadfromfile(B_LOB, F_LOB, dbms_lob.getlength(F_LOB));
 11   dbms_lob.fileclose(F_LOB);
 12   commit;
 13* end;

1 2 下一页