分享一个oracle写的存储过程用于从签名库中取签名图片插入到EAS中

背景

客户期望使用统一签名库中的图片作为EAS、s-HR 审批过程中的签名。

数据库是oracle，图片以url形式提供。

开发人员没空，于是用存储过程做了一个，设置成定时任务执行，当前依然在生产环境中使用。

create or replace procedure sp_hgy_insertEsignature
as begin
declare
    l_url varchar2(4000);
    s_id varchar2(44);
    a_id varchar2(44);
    as_id varchar2(44);
    temp number;
    l_http_request   UTL_HTTP.req;
    l_http_response  UTL_HTTP.resp;
    l_raw RAW(2000);
    l_blob BLOB;
    s_blob BLOB;
    cursor c_user
    is
    select fid,fnumber,fname_l2,fpersonid from t_pm_user where fisdelete=0;
    c_row c_user%rowtype;
begin
   -- Important: setup ACL access list first!
    for c_row in c_user loop
      select count(*) into temp from (select * from  t_wfr_esignature where fpersonid =c_row.fpersonid);
      
        s_id:=newbosid('C321B39A');
        a_id:=newbosid('F4AF4F03');
        as_id:=newbosid('172F3A47');
    l_url := 'http://host/api_userseal.jsp?sid=S10&&ucode=' || c_row.fnumber;
    DBMS_LOB.createtemporary(l_blob, FALSE);
    DBMS_Lob.createtemporary(s_blob,false,DBMS_LOB.call);
    l_http_request  := UTL_HTTP.begin_request(l_url);
    l_http_response := UTL_HTTP.get_response(l_http_request);

  -- Copy the response into the BLOB.
  BEGIN
    LOOP
      UTL_HTTP.read_raw(l_http_response, l_raw, 2000);
      DBMS_LOB.writeappend (l_blob, UTL_RAW.length(l_raw), l_raw);
    END LOOP;
  EXCEPTION
    WHEN UTL_HTTP.end_of_body THEN
      UTL_HTTP.end_response(l_http_response);
  END;
  if length(l_blob)<50 then
    continue;
    end if;
    
  ordsys.ordimage.processcopy(l_blob,'fixedscale=100 50',s_blob);
  --if exists then update else insert
  if temp >0 then
      update t_bas_attachment set ffile=s_blob where fid in (select fattachmentid from t_wfr_esignature where fpersonid =c_row.fpersonid);
      commit;
        continue;
    end if;
    
  insert into t_bas_attachment(fid,fcreatorid,fcreatetime,flastupdateuserid,flastupdatetime,fcontrolunitid,fname_l1,fname_l2,fname_l3,fsimplename,ftype,ffile,fisshared,fshareddesc_l1,fshareddesc_l2,fshareddesc_l3,fsize,fsizeinbyte,fstoragetype,fisonlysamebillshare,fattachtypeenum)
  values(a_id,'256c221a-0106-1000-e000-10d7c0a813f413B7DE7F',sysdate,'256c221a-0106-1000-e000-10d7c0a813f413B7DE7F',sysdate,'00000000-0000-0000-0000-000000000000CCE7AED4',c_row.fname_l2,c_row.fname_l2,c_row.fname_l2,'gif','GIF 图形文件',s_blob,0,'否','否','否','1 KB',1299,0,0,0);
  insert into t_wfr_esignature(fid,fpersonid,fpersonname,fattachmentid,fenable) values(s_id,c_row.fpersonid,c_row.fname_l2,a_id,0);
  insert into t_bas_boattchasso(fid,fboid,fassotype_l2,fattachmentid,fassobusobjtype,fseq)
  values(as_id,c_row.fid,'新加附件',a_id,'13B7DE7F',-1);
  insert into WWW_DATA (num, dat) values (102, s_blob);
  commit;

  DBMS_LOB.freetemporary(l_blob);
  DBMS_LOB.freetemporary(s_blob);
  end loop;
end;
end sp_hgy_insertEsignature;