PostgreSQL下迭代实现数据库递归查询

前言：

考虑这样一个问题，A机构的上级机构是B，B机构的上级机构是C，C机构的上级机构是D，而D机构的上级机构是E。现在需要找出一个给定机构（例如E）的所有上级机构。

这种问题的典型表结构如下

?

今天我们就是要解决诸如此类的问题。

下面的函数返回以in_ins_id为参数，计算该机构的所有直接或者上级机构并返回他们组成的集合

?

CREATE FUNCTION getAllPreIns(VARCHAR)	--接收一个VARCHAR参数
 RETURNS TABLE(ins_id VARCHAR)	--返回一个table
 LANGUAGE plpgsql
AS $function$	--function标识
DECLARE in_ins_id ALIAS FOR $1;    --把接收的的参数放在in_ins_id变量中

BEGIN
	CREATE TEMPORARY TABLE ins(ins_id VARCHAR(8));	--该临时表将返回最终的数据	
	CREATE TEMPORARY TABLE new_ins(ins_id VARCHAR(8)); --存储前一次迭代中找到的数据
	CREATE TEMPORARY TABLE temp(ins_id VARCHAR(8));	--用于存放对集合操作时的中间数据
	--先把in_ins_id的上级机构插入new_ins表中
	INSERT INTO new_ins
		SELECT pre_ins_id
		FROM ins_table
		WHERE ins_table.ins_id = in_ins_id;
	LOOP
		--将new_ins中的数据插入到ins中
		INSERT INTO ins
			SELECT new_ins.ins_id
			FROM new_ins;
		--将new_ins中ins_id的上级机构插入到temp中
		INSERT INTO temp
			(SELECT ins_table.pre_ins_id
				FROM new_ins, ins_table
				WHERE new_ins.ins_id = ins_table.ins_id)
			EXCEPT	--防止出现机构环如A的上级机构是B，B的上级机构是C，C的上级机构是A
			(SELECT ins.ins_id
				FROM ins);
		DELETE FROM new_ins;	--清空new_ins
		INSERT INTO new_ins		--保存temp
			SELECT temp.ins_id
			FROM temp;
		DELETE FROM temp;
		EXIT WHEN NOT EXISTS (SELECT new_ins.ins_id FROM new_ins);	--知道没有上级机构市循环终止
	END LOOP;
	RETURN QUERY SELECT ins.ins_id FROM ins;	--返回
END;

$function$	--function标识

?

?

假设我们传入的参数是A，在此function中，我们先找到A的上级机构B并存放在new_ins中，如此循环，直到某次循环中没有新的机构加进来才停止。

?

此过程所用到的4个表

• ins：存储要返回的元素集合
• new_ins：存储每一次迭代中找到的上级机构，并在下一次迭代中插入到表ins中
• temp：临时存储每一次迭代中找到的上级机构
• Ins_table：我们的原始机构表（如下图）

?

SELECT ins_id, pre_ins_id FROM ins_table; 

?

?

在LOOP循环之前我们先把in_ins_id的所有直接上级机构插入到表new_ins中。LOOP循环开始首先将new_ins中的所有机构插入到ins中。然后为new_ins中的所有机构计算上级机构，并去掉此前已经计算出的in_ins_id的上级机构，把余下的机构插入到temp中。最后用temp的数据替换new_ins。当new_ins为空时，LOOP终止。

?

最后我们在postgresql中调用该函数

?

SELECT * FROM getAllPreIns(‘A‘);

?结果如下图

?

?

结束语：以上的SQL语句全部是在PostgreSQL中运行通过，由于各种数据库的语法的差别，在其他数据库中并不能保证不会报错。由于本人水平所限，如有错误，欢迎请批评指正。

?

参考文献：

1. 《数据库系统概念》第六版第五章：高级SQL

2.?http://postgres.cz/wiki/PL/pgSQL_(en)

?

?

版权声明：本文为博主原创文章，未经博主允许不得转载。

PostgreSQL下迭代实现数据库递归查询

原文：http://tangl163.iteye.com/blog/2253492