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


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