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]