Postgresql存储过程

database

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");

以上是 Postgresql存储过程 的全部内容, 来源链接: utcz.com/z/533836.html

回到顶部