开发者社区> 转身泪倾城> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
RDS 实例更换 VPC 网络和交换机工具
RDS 实例更换 VPC 网络和交换机工具
2527 0
开放下载!《RDS数据库入门一本通》
RDS从入门到精通,云运维工程师不可错过的匠心之作
48125 0
小微企业阿里云最佳实践系列(一):ECS 服务器与 RDS 数据库
本博文主要写给创业团队、技术团队人数 < 5 人、没有专业运维等小微企业作为参考,需要掌握基础的服务器管理、软件开发等经验。 博文主要内容 本博文主要使用传统服务器架构与云服务架构进行横向对比,解决企业在搭建软件系统中所遇到等问题和痛点,以及为小微企业降低成本的同时尽可能提高软件系统的高可通、低延迟、高规范、低人力投入。
1259 0
【阿里云新品发布·周刊】第11期:云数据库 MySQL 8.0 重磅发布,更适合企业使用场景的RDS数据库
云数据库MySQL 8.0 升级发布会2019年5月29日15时,阿里云云数据库 MySQL 8.0 重磅发布,2倍以上性能提升,SQL窗口函数、JSON扩展语法等企业级新功能震撼上市!主要从技术层面介绍MySQL 8.0的优势和与过去版本对比。
704 0
ecs与数据库(rds,redis,mongodb,memcached)连通性判断流程图
由于文档https://yq.aliyun.com/articles/164796内容较多,不方便查看,可以参考流程图来初步判断连通性是否正常
171 0
【云栖号案例 | 新零售】数据库RDS为跨境电商管理平台支撑亿级流水
智赢科技每天面对用户修改价格库存对更新即时性要求高,索引和表结构变更不易。RDS支持数组和分区,降低中间表的数量又可以自动分区,加快开发速度。
1778 0
【云栖号案例 | 物联网&人工智能】RDS为慧联无限数据库运维减负
业务持续增长没有专业运维人员,导致 MySQL 不堪重负。上云后RDS数据库实现了纵向弹性扩缩容,提供自助服务能力,短期内不需要专业运维人员,控制了成本。
1641 0
你知道数据库RDS手动续费的方法有几种么?
包年包月实例有到期时间,如果到期未续费,会导致业务中断甚至数据丢失,建议您及时手动续费。
703 0
文章
问答
文章排行榜
最热
最新
相关电子书
更多
为什么PostgreSQL是最适合去O的数据库
立即下载
PolarDB for PostgreSQL三节点功能介绍
立即下载
阿里云PostgreSQL、PPAS、HDB for PG生
立即下载