分享一个oracle写的存储过程用于从签名库中取签名图片插入到EAS中原创
10人赞赏了该文章
165次浏览
编辑于2023年06月29日 15:39:37
背景
客户期望使用统一签名库中的图片作为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;