一道数据库开发面试题

已知表route,字段和内容如下:

起始节点终止节点距离ab100ac150ad200be300bf800eg100

 

要求写sql或者一段代码,找出从节点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','b',100);

 

2.创建字符串数组类型

CREATE OR REPLACE TYPE t_string_array IS TABLE OF VARCHAR2(10);

 

3.创建函数,在字符串数组中查找指定的字符串

CREATE OR REPLACE FUNCTION FindInStringArray(src t_string_array, dest VARCHAR2)RETURN BOOLEANISi NUMBER(4);BEGIN  FOR i in 1..src.COUNT LOOP    IF src(i)=dest THEN      RETURN TRUE;    END IF;  END LOOP;  RETURN FALSE;END;

 

4.PL/SQL

DECLAREv_begin_node VARCHAR2(3);v_end_node VARCHAR2(3);CURSOR c_route IS SELECT end_node FROM route WHERE begin_node=v_begin_node;--已经搜索到的途经的节点集合searchedNodes t_string_array := t_string_array('a');--节点集合中将要处理的位置index0 NUMBER(5) := 1;BEGIN  --遍历途经的每一个点  WHILE index0 <= searchedNodes.COUNT LOOP    --当前要处理的节点    v_begin_node := searchedNodes(index0);        --当前节点可直接通往的节点    OPEN c_route;      LOOP      FETCH c_route INTO v_end_node;      EXIT WHEN c_route%NOTFOUND;          IF NOT FindInStringArray(searchedNodes, v_end_node) THEN        --这是一个未在集合中出现过的新节点,增加到集合        searchedNodes.EXTEND(1);        searchedNodes(searchedNodes.COUNT) := v_end_node;        --输出        DBMS_OUTPUT.PUT_LINE(v_end_node);      END IF;    END LOOP;        CLOSE c_route;    --集合中的下一元素    index0:=index0 + 1;  END LOOP;END;

 

5.如果数据间引用不存在环路,还可以用递归查询来完成

SELECT end_node FROM routeCONNECT BY begin_node=PRIOR end_nodeSTART WITH begin_node='a'

6.如果要打印出从a开始的遍历路径,该怎么办呢?

如果要将前篇文章的route问题的完整路径求解出来,该如何解决呢?
已知表route,字段和内容如下:
起始节点终止节点距离ab100ac150ad200be300bf800eg100eh300
 
要求找出从节点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)ASnextNode 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]