一道数据库开发面试题

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

起始节点	终止节点	距离
a	b	100
a	c	150
a	d	200
b	e	300
b	f	800
e	g	100

 

要求写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 BOOLEAN
IS
i 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

DECLARE

v_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 route
CONNECT BY begin_node=PRIOR end_node
START WITH begin_node='a'

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

如果要将前篇文章的route问题的完整路径求解出来,该如何解决呢?
已知表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]