大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说Postgresql 存储过程,希望您对编程的造诣更进一步.
1、没有定义 plpsql
createlang -d tms plpgsql
代码100分
2、找出不连续的SN
代码100分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盘，导致烧录日志无法上报系统，而出现烧录成功但后台没有记录。
解决：
代码100分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");
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/8249.html
