Postgresql 存储过程

1、没有定义 plpsql

createlang -d tms plpgsql

2、找出不连续的SN

DROP FUNCTION IF EXISTS sn_miss(IN order_name TEXT, IN pcline TEXT);
CREATE OR REPLACE FUNCTION sn_miss(IN order_name TEXT, IN pcline TEXT) 
RETURNS TABLE(sn_series TEXT)
AS $$
DECLARE
    r RECORD;
    sn_min INTEGER := 0;
    sn_max INTEGER := 0;
    sql varchar; 
    sn_base varchar;
BEGIN
    EXECUTE 
        "select 
        min(sn) as min_sn, max(sn) as max_sn 
        from 
        burn_log 
        where 
        order_number = $1 and pc_no = $2"
        USING order_name, pcline
        INTO r;
        
    sn_base := substr(r.min_sn, 1, 12);
    sn_min := to_number(substr(r.min_sn, 13, 8), "00000000");
    sn_max := to_number(substr(r.max_sn, 13, 8), "00000000");

    return QUERY EXECUTE 
            "select 
            * 
            from 
            (select $1 || sn_series from generate_series($2, $3, 1) as t(sn_series)) as A(sn_series) 
            where 
            A.sn_series 
            not in 
            (select sn from burn_log where order_number = $4 and pc_no = $5);"
        USING  sn_base, sn_min, sn_max, order_name, pcline;
END;
$$
LANGUAGE PLPGSQL VOLATILE;

3、修复产线中不连续的SN

DROP FUNCTION IF EXISTS tms_pdline_fix(IN order_name TEXT, IN pcline TEXT);
CREATE OR REPLACE FUNCTION tms_pdline_fix(IN order_name TEXT, IN pcline TEXT) 
RETURNS TABLE(sn TEXT)
AS $$
DECLARE
    r RECORD;
    sn_min INTEGER := 0;
    sn_max INTEGER := 0;
    sql varchar; 
    sn_base varchar;
BEGIN
    EXECUTE 
        "select min(sn) as min_sn, max(sn) as max_sn from burn_log where order_number = $1 and pc_no = $2;"
        USING order_name, pcline
        INTO r;
        
    sn_base := substr(r.min_sn, 1, 12);
    sn_min := to_number(substr(r.min_sn, 13, 8), "00000000");
    sn_max := to_number(substr(r.max_sn, 13, 8), "00000000");

    EXECUTE 
        "create temp table 
        pl_sn_temp 
        as
        select 
        * 
        from 
        (select $1 || sn from generate_series($2, $3, 1) as t(sn)) as A(sn) 
        where 
        A.sn 
        not in 
        (select sn from burn_log where order_number = $4 and pc_no = $5);"
    USING  sn_base, sn_min, sn_max, order_name, pcline;
    
    for r in EXECUTE "select * from pl_sn_temp" loop
        EXECUTE
            "insert into
            burn_log 
            (order_number, pc_no, sn, times_success, dt_success) 
            values 
            ($1, $2, $3, 1, $4);"
        USING order_name, pcline, r.sn, to_timestamp(19700101);
    end loop;
    
    EXECUTE
        "UPDATE 
        pcline 
        SET 
        success_number = (
            SELECT 
            COUNT(*) 
            FROM 
            burn_log 
            WHERE 
            order_number = $1 and pc_no = $2
            )
        WHERE
        order_number = $1 and pc_no = $2"
        USING order_name, pcline;
    
    return QUERY EXECUTE "select * from pl_sn_temp";
    EXECUTE
        "DROP TABLE IF EXISTS pl_sn_temp";
END;
$$
LANGUAGE PLPGSQL VOLATILE;

4、实例

时间：2018-8-15
订单：DD07ME180709014-6
产线：2
原因：系统没有D盘，导致烧录日志无法上报系统，而出现烧录成功但后台没有记录。
解决：

insert into 
burn_log
(order_number, pc_no, sn, times_success, dt_success)
values
("DD07ME180709014-6", "2", "98.00-07.24-10163912", 1, now());

select tms_pdline_fix("DD07ME180709014-6", "2");