PostgreSql Plsql 演算Bill of material
使用 PostgreSql 几天，我被其新颖的功能以及效率深深吸引，我非常喜欢她！然而 PostgreSql 较稳定的版本在8.0以前通常在Unix以及Linux系统下运行，让一些普通开发人员入门很难！而同时我发觉国内开发人员不怎么愿意与别人分享成功的经验，看来传统文化“绝技不外传”的影响力依然不可小看。BOM之算法虽然非什么绝技但确实难到一些开发人员，本人一直研究MRP，在国内外BBS以及网上还没有看见谁将MRP最核心的算法公布出来，而MRP核心的核心BOM之算法也不多见。而以PostgreSql 的扩展语言plpgsql撰写BOM算法我还没有在国内外的网站上看见过，现在我将完整的BOM算法公布出来，无阶层限制。当然更好的BOM算法还有，如最终报告实现树形结构的SQL算法。此算法待我有空再发布出来。
第一步，建立两个表。第一个表存放BOM结构数据，而另外一个表存放你查询一个产品BOM报告。
--BOM 数据存放表
CREATE TABLE bomib
(
ib010 char(12), --父阶
ib020 char(12), --子阶
ib030 int4 --用量
)WITH OIDS;
--测试数据
INSERT INTO BOMIB
VALUES('01OS620000GS',
'1040-1212212,
1);
INSERT INTO BOMIB
VALUES('01OS620000GS',
'1041-1212212,
1);
INSERT INTO BOMIB
VALUES('01OS620000GS',
'13OS-6200010,
1);
INSERT INTO BOMIB
VALUES('01OS620000GS',
'13OS-6200020,
1);
INSERT INTO BOMIB
VALUES('1040-1212212',
'2411-0602700,
1);
INSERT INTO BOMIB
VALUES('13OS-6200010,
'2012-1212212,
1);
--BOM 报表存放地
CREATE TABLE bomzz
(
zz010 char(12), --父阶
zz020 char(12), --子阶
zz030 int4, --用量
zz040 int8 --阶层码
)WITH OIDS;
--检查零时表是否存在的函数
CREATE OR REPLACE FUNCTION ps_temp_table("varchar")
RETURNS bool AS
$BODY$
DECLARE
BEGIN
perform n.nspname ,c.relname
FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname like 'pg_temp_%'
AND pg_catalog.pg_table_is_visible(c.oid)
AND Upper(relname) = Upper($1);
IF FOUND THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--BOM 展开函数
CREATE OR REPLACE FUNCTION bomia_part(bpchar)
RETURNS SETOF bomzz AS
$BODY$
DECLARE
ENDTREE INT8;
NLVL INT8;
REC INT8;
RESULT RECORD;
BEGIN
ENDTREE:=0;
NLVL:=1;
--Clear bill of material report data
DELETE FROM bomzz;
--Check temp table "STACK" exists
IF ps_temp_table('STACK') THEN
RAISE NOTICE 'TEMP TABLE "STACK" EXISTS!';
ELSE
CREATE TEMP TABLE STACK
(
PARENT CHAR(12),
PARTNO CHAR(12),
USAGE INT8,
LVLS INT8
) WITHOUT OIDS ON COMMIT DELETE ROWS;
END IF;
INSERT INTO STACK
SELECT IB010,IB020,IB030,1
FROM BOMIB
WHERE IB010=$1;
WHILE (ENDTREE=0) LOOP
NLVL := NLVL + 1;
INSERT INTO STACK(LVLS,PARENT,PARTNO,USAGE)
SELECT NLVL,
A.IB010,
A.IB020,
A.IB030
FROM BOMIB A, STACK B
WHERE A.IB010 =B.PARTNO
AND B.LVLS= NLVL -1;
IF NOT EXISTS(SELECT PARTNO FROM STACK WHERE LVLS =NLVL) THEN
ENDTREE := 1;
END IF;
END LOOP;
INSERT INTO BOMZZ(ZZ010,ZZ020,ZZ030,zz040)
SELECT PARENT,PARTNO,USAGE,LVLS
FROM STACK;
FOR RESULT IN SELECT * FROM BOMZZ LOOP
RETURN NEXT RESULT;
END LOOP;
RETURN;
END$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--使用这个函数
SELECT * FROM bomia_part('xxx'); --xxx = 父阶料号