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;