一个存储过程,关于blob

2/9/2008来源:Oracle教程人气:6683


  好坏给点意见,谢了
  
  
  代码:--------------------------------------------------------------------------------
  create or replace PRocedure ps_cmpimage(
   p_srtid in cmpimage.cimg_srtid%type,
   p_id in cmpimage.cimg_id%type,
   p_filename in varchar2,
   p_title in cmpimage.cimg_title%type,
   p_flag in number,
   p_rowid in rowid)
   is
   image BLOB;
   sourcefile BFILE;
   cursor cur_cmpimage is
   select a.cimg_count,a.cimg_idate from cmpimage a
   where a.rowid=p_rowid;
   rowimage cur_cmpimage%rowtype;
  BEGIN
   if p_flag=0 then
   INSERT INTO cmpimage (cimg_srtid,cimg_id,cimg_image,cimg_title)
   VALUES (p_srtid,p_id,EMPTY_BLOB,p_title)
   RETURNING cimg_image INTO image;
   sourcefile := BFILENAME('PHOTO_DIR',p_filename);
   DBMS_LOB.OPEN(sourcefile, DBMS_LOB.LOB_READONLY);
   DBMS_LOB.LOADFROMFILE(image,sourcefile,DBMS_LOB.GETLENGTH(sourcefile));
   else
   if p_filename is null then
   update cmpimage a
   set a.cimg_srtid=p_srtid,a.cimg_id=p_id,a.cimg_title=p_title
   where a.rowid=p_rowid;
   else
   update cmpimage a
   set a.cimg_srtid=p_srtid,a.cimg_id=p_id,a.cimg_title=p_title
   where a.rowid=p_rowid;
   select a.cimg_image into image from cmpimage a
   where a.rowid=p_rowid
   for update;
   sourcefile := BFILENAME('PHOTO_DIR',p_filename);
   DBMS_LOB.OPEN(sourcefile, DBMS_LOB.LOB_READONLY);
   DBMS_LOB.LOADFROMFILE(image,sourcefile,DBMS_LOB.GETLENGTH(sourcefile));
   end if;
   end if;
   commit;
  end ps_cmpimage;--------------------------------------------------------------------------------
  
  
  相关:
  -- Create Directory
  create or replace directory PHOTO_DIR
  as 'C:\WINNT\TEMP\';
  --grant
  grant create any directory to 用户