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");
以上是 Postgresql存储过程 的全部内容, 来源链接: utcz.com/z/533836.html