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 
$BODY$ 
  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; 


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

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取值逻辑，并提高页面加载性能（每个证书下面只显示一个样本hash） 
        ------------------------------------------ 
sqlsexec = 'truncate table t_cert_sample_tmp;'; 
execute sqlsexec; 

        sqlsexec = 'SELECT distinct cert_id FROM t_cert_state where is_export=1 and check_state=1 and ylf_export=0'; 
        for r in execute sqlsexec 
        loop        
              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; 
             
              ---如果证书修改后状态为'W'：取状态为G且未进入筛分系统的样本MD5,将证书对应的所有hash都逐条插入t_cert_sample_tmp表中 
                   if r1.sample_md5 is not null then 
                       tmp_hash = r1.sample_md5;   
                   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 


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

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取值逻辑，并提高页面加载性能（每个证书下面满足条件的hash都显示出来） 
        ------------------------------------------ 
sqlsexec = 'truncate table t_cert_sample_tmp;'; 
execute sqlsexec; 

        sqlsexec = 'SELECT distinct cert_id FROM t_cert_state where is_export=1 and check_state=1 and ylf_export=0'; 
        for r in execute sqlsexec 
        loop        
              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)'; 
              raise notice '%',sqlsexec1; 
              for r1 in execute sqlsexec1 
                  loop 
             
              ---如果证书修改后状态为'W'：取状态为G且未进入筛分系统的样本MD5,将证书对应的所有hash都逐条插入t_cert_sample_tmp表中 
                   if r1.sample_md5 is not null then 
                       tmp_hash = r1.sample_md5; 
                       insexec = 'insert into t_cert_sample_tmp(cert_id,sample_md5) values ('''||r1.cert_id||''','''||tmp_hash||''');'; 
                       raise notice '%',insexec; 
                       execute insexec;    
                   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; 
                       insexec = 'insert into t_cert_sample_tmp(cert_id,sample_md5) values ('''||r.cert_id||''','''||tmp_hash||''');'; 
                       raise notice '%',insexec; 
                       execute insexec;  
                  end if; 
                end loop;       
        end loop;             
     
        RETURN 'DONE'; 
exception when others then 
RAISE EXCEPTION '(%)', SQLERRM; 
return 'error'; 
END