postgresql存儲過程執行個體：已稽核證書存入臨時表

存儲過程執行個體： 

需求： 

思路：建立存儲過程 

代碼邏輯： 

1、先清空緩存表t_cert_sample_tmp； 

2、擷取清單， 查詢語句： 

select cert_id 

from t_cert_state where is_export=1 and check_state=1 and ylf_export=0 group by cert_id; 

3、循環計算單個cert_id的方法： 

1）如果證書修改後狀态為'w'：取狀态為g且未進入篩分系統的樣本md5 

sqlsexec='select sample_md5 from t_cert_sample where cert_id=' || cert_id || ' and not exists (select sample_hash from t_sfa_sample where state=-1 

and sample_hash=t_cert_sample.sample_md5) and exists (select sample_hash from t_white where sample_state=''g'' and sample_hash=t_cert_sample.sample_md5) limit 1'; 

2）如果證書修改後狀态不是'w'： 

  步驟1：優先查黑表，如果此證書下有黑樣本，則取該證書下預設第一例黑樣本md5 

sqlsexec='select sample_md5 from t_cert_sample t1,t_black t2 where t1.cert_id=' || cert_id || 'and  t1.sample_md5=t2.sample_hash  limit 1'; 

步驟2：如果黑表沒有樣本，則再查白表，取該證書下預設第一例樣本md5 

sqlsexec='select sample_md5 from t_cert_sample t1,t_white t2 where t1.cert_id=' || cert_id || ' and  t1.sample_md5=t2.sample_hash limit 1'; 

4、将證書id和剛剛擷取到的樣本md5，插入到緩存表t_cert_sample_tmp（對應字段分别為cert_id和sample_md5） 

建表： 

create table t_cert_sample_tmp 

( 

id serial not null primary key, 

cert_id integer not null, 

sample_md5 character varying(41) not null 

) 

with ( 

oids=false 

); 

alter table t_cert_sample_tmp owner to postgres; 

grant all on table t_cert_sample_tmp to postgres; 

grant select, update, insert, delete on table t_cert_sample_tmp to sqluser; 

grant select on table t_cert_sample_tmp to fenxi; 

grant select, update on sequence t_cert_sample_tmp_id_seq to sqluser; 

--描述 

comment on table t_cert_sample_tmp is '存放捕獲判定分析已稽核證書界面md5執行個體資料'; 

comment on column t_cert_sample_tmp .cert_id is '證書id'; 

comment on column t_cert_sample_tmp .sample_md5 is '樣本的md5值'; 

以下幾種其實實作的是不同的結果，可以仔細研究下 

實作： 

-- function: cert_sample_tmp() 

-- drop function cert_sample_tmp(); 

create or replace function cert_sample_tmp() 

  returns text as 

$body$ 

declare 

sqlsexec  character varying :=''; 

        sqlsexec1  character varying :=''; 

        sqlsexec2  character varying :=''; 

        sqlsexec3  character varying :='';       

        insexec character varying :=''; 

        tmp_hash varchar(32); 

r record; 

r1 record; 

r2 record; 

r3 record;

begin 

        ------------------------------------------ 

        ----函數功能：優化捕獲分析判定系統已稽核證書界面md5取值邏輯，并提高頁面加載性能 

sqlsexec = 'truncate table t_cert_sample_tmp;'; 

execute sqlsexec; 

      ----隻取最新修改的一條記錄 

        sqlsexec = 'select a.cert_id,a.later_state from 

                   (select cert_id,later_state,record_time from t_cert_state where is_export=1 and check_state=1 and ylf_export=0 )a, 

                   (select cert_id,max(record_time) as max_record_time from t_cert_state where is_export=1 and check_state=1 and ylf_export=0 group by cert_id)b 

                    where a.record_time=b.max_record_time and a.cert_id=b.cert_id;'; 

        for r in execute sqlsexec 

        loop        

              ---如果證書修改後狀态為'w'：取狀态為g且未進入篩分系統的樣本md5,将證書對應的hash取一條插入t_cert_sample_tmp表中 

              if r.later_state='w' then 

                  sqlsexec1='select sample_md5,cert_id from t_cert_sample where cert_id='''|| r.cert_id ||''' and not exists (select sample_hash from t_sfa_sample where state=-1 

                         and sample_hash=t_cert_sample.sample_md5) and exists (select sample_hash from t_white where sample_state=''g'' and sample_hash=t_cert_sample.sample_md5) limit 1'; 

                  raise notice '%',sqlsexec1; 

                  execute sqlsexec1 into r1; 

                  if r1.sample_md5 is not null then 

                  tmp_hash = r1.sample_md5; 

                  end if; 

              else 

                       ----如果證書修改後狀态不是'w'：1.優先查黑表，如果此證書下有黑樣本，則取該證書下預設第一例黑樣本md5 

                       sqlsexec2='select sample_md5 from t_cert_sample t1,t_black t2 where t1.cert_id='''|| r.cert_id ||''' and  t1.sample_md5=t2.sample_hash  limit 1'; 

                       raise notice '%',sqlsexec2; 

                       execute sqlsexec2 into r2; 

                       if r2.sample_md5 is not null then 

                       tmp_hash = r2.sample_md5; 

                       else 

                           ----2.如果黑表沒有樣本，則再查白表，取該證書下預設第一例樣本md5 

                           sqlsexec3='select sample_md5 from t_cert_sample t1,t_white t2 where t1.cert_id='''||r.cert_id ||''' and  t1.sample_md5=t2.sample_hash limit 1'; 

                           raise notice '%',sqlsexec3; 

                           execute sqlsexec3 into r3; 

                           if r3.sample_md5 is not null then 

                           tmp_hash = r3.sample_md5; 

                           end if; 

                       end if; 

              end if; 

                       insexec = 'insert into t_cert_sample_tmp(cert_id,sample_md5) values ('''||r.cert_id||''','''||tmp_hash||''');'; 

                       raise notice '%',insexec; 

                       execute insexec;        

        end loop;             

        return 'done'; 

exception when others then 

raise exception '(%)', sqlerrm; 

return 'error'; 

end 

  language plpgsql volatile 

  cost 100; 

alter function cert_sample_tmp() 

  owner to postgres; 

grant execute on function cert_sample_tmp() to public; 

grant execute on function cert_sample_tmp() to postgres; 

grant execute on function cert_sample_tmp() to sqluser; 

====================================================================== 

        ----函數功能：優化捕獲分析判定系統已稽核證書界面md5取值邏輯，并提高頁面加載性能（每個證書下面隻顯示一個樣本hash） 

        sqlsexec = 'select distinct cert_id from t_cert_state where is_export=1 and check_state=1 and ylf_export=0'; 

              sqlsexec1='select sample_md5,cert_id from t_cert_sample where cert_id='''|| r.cert_id ||''' and not exists (select sample_hash from t_sfa_sample where state=-1 

              raise notice '%',sqlsexec1; 

              execute sqlsexec1 into r1; 

              ---如果證書修改後狀态為'w'：取狀态為g且未進入篩分系統的樣本md5,将證書對應的所有hash都逐條插入t_cert_sample_tmp表中 

                   if r1.sample_md5 is not null then 

                       tmp_hash = r1.sample_md5;   

                   else 

                  insexec = 'insert into t_cert_sample_tmp(cert_id,sample_md5) values ('''||r.cert_id||''','''||tmp_hash||''');'; 

                       execute insexec;          

==================================================================================== 

        ----函數功能：優化捕獲分析判定系統已稽核證書界面md5取值邏輯，并提高頁面加載性能（每個證書下面滿足條件的hash都顯示出來） 

                         and sample_hash=t_cert_sample.sample_md5) and exists (select sample_hash from t_white where sample_state=''g'' and sample_hash=t_cert_sample.sample_md5)'; 

              for r1 in execute sqlsexec1 

                  loop 

                       tmp_hash = r1.sample_md5; 

                       insexec = 'insert into t_cert_sample_tmp(cert_id,sample_md5) values ('''||r1.cert_id||''','''||tmp_hash||''');'; 

                       execute insexec;    

                       execute insexec;  

                end loop;