PL/SQL的递归调用

已知表route,字段和内容如下:
起始节点 终止节点 距离
a b 100
a c 150
a d 200
b e 300
b f 800
e g 100
e h 300

要求找出从节点a开始能到达的所有路径

1.创建表route,插入数据

CREATE TABLE route (
begin_node VARCHAR2(3),
end_node VARCHAR2(3),
distance NUMBER(4));

INSERT INTO route VALUES('a','b',100);
INSERT INTO route VALUES('a','c',150);
INSERT INTO route VALUES('a','d',200);
INSERT INTO route VALUES('b','e',300);
INSERT INTO route VALUES('b','f',800);
INSERT INTO route VALUES('e','g',300);
INSERT INTO route VALUES('e','h',300);

2.创建t_node类型

CREATE OR REPLACE TYPE t_node AS OBJECT (name VARCHAR2(3), distance NUMBER(5));

3.创建t_node_array类型,是t_node类型数组

CREATE OR REPLACE TYPE t_node_array IS TABLE OF t_node;

4.创建isloopnode(node t_node, nodes t_node_array, nodes_depth NUMBER)函数,判断node是否在nodes中出现过

CREATE OR REPLACE FUNCTION isloopnode(node t_node, nodes t_node_array, nodes_depth NUMBER)
RETURN BOOLEAN
IS
i NUMBER;
BEGIN
FOR i IN 1..nodes_depth LOOP
IF nodes(i).name = node.name THEN
RETURN TRUE;
END IF;
END LOOP;
RETURN FALSE;
END;

5.创建过程printpath来打印路径

CREATE OR REPLACE PROCEDURE printpath(nodes t_node_array, nodes_depth number)
AS
i NUMBER(4);
BEGIN
FOR i IN 1..nodes_depth LOOP
IF i<>1 THEN
DBMS_OUTPUT.PUT('-->');
END IF;
DBMS_OUTPUT.PUT(nodes(i).NAME||'[');
DBMS_OUTPUT.PUT(nodes(i).DISTANCE||']');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
END;

6.遍历过程iterate

CREATE OR REPLACE PROCEDURE iterate(node IN t_node, nodesStack IN OUT t_node_array, stackDepth IN OUT NUMBER)
AS

nextNode t_node;
nextNodes t_node_array := t_node_array();
CURSOR c_route IS SELECT end_node,distance FROM route WHERE begin_node=node.name;
tempStr VARCHAR2(3);
tempInt number(4);
i number(4);
BEGIN
--将当前节点存入路径栈中
IF stackDepth = nodesStack.COUNT THEN
--需要扩展栈
nodesStack.EXTEND(1);
END IF;
stackDepth := stackDepth + 1;
nodesStack(stackDepth):= node;

--找开游标,查找后续节点
OPEN c_route;
FETCH c_route INTO tempStr, tempInt;

--没有后续节点
IF c_route%NOTFOUND THEN
--打印出本条线路
printpath(nodesStack, stackDepth);
CLOSE c_route;
--回归到上一节点
stackDepth := stackDepth - 1;
RETURN;
END IF;

--依次处理后续节点
--先将节点存到临时数组nextNodes,以期尽快关闭游标
WHILE c_route%FOUND LOOP
--路程要累积起来
nextNode := t_node(tempStr, nodesStack(stackDepth).distance + tempInt);
--存入临时数组
nextNodes.EXTEND(1);
nextNodes(nextNodes.COUNT) := nextNode;
FETCH c_route INTO tempStr, tempInt;
END LOOP;
CLOSE c_route;

FOR i IN 1..nextNodes.COUNT LOOP
nextNode := nextNodes(i);
--判断是否与路径上的先前节点重复
IF isloopnode(nextNode, nodesStack, stackDepth) THEN
--打印出本条线路
printpath(nodesStack, stackDepth);
--回归到上一节点
stackDepth := stackDepth - 1;
RETURN;
END IF;

--非重复节点
iterate(nextNode, nodesStack, stackDepth);
END LOOP;

--处理完毕本节点,回归到上一节点
stackDepth := stackDepth - 1;
END;


7.PL/SQL调用iterate

DECLARE
node t_node;
nodesstack t_node_array:=t_node_array();
stackdepth NUMBER(4);
BEGIN
node:=t_node('A', 0);
stackdepth:=0;
iterate(node,nodesstack,stackdepth);
END;

8.执行结果

a[0]-->b[100]-->e[400]-->g[700]
a[0]-->b[100]-->e[400]-->h[700]
a[0]-->b[100]-->f[900]
a[0]-->c[150]
a[0]-->d[200]