root/trunk/sql/oracle/shortestPath.sql

CREATE OR REPLACE PROCEDURE shortestPath IS
BEGIN
  DECLARE
    maxLength INT:=1;
    oldSize INT;
    newSize INT:=0;
  BEGIN
    INSERT INTO paths SELECT head AS startNode,tail AS endNode,1 FROM edges;
    
    LOOP
      INSERT INTO paths
        SELECT DISTINCT p.startNode AS startNode,tail AS endNode,maxLength+1 AS length
        FROM paths p
        JOIN edges ON p.endNode=head
        WHERE p.length=maxLength
          AND p.startNode!=tail
          AND NOT EXISTS (
            SELECT * FROM paths q
            WHERE q.startNode=p.startNode AND q.endNode=tail);
      maxLength:=maxLength+1;
      oldSize:=newSize;
      SELECT COUNT(*) INTO newSize FROM paths;
      EXIT WHEN oldSize=newSize;
    END LOOP;
  END;
END;
/

TRUNCATE TABLE paths; -- こうした方が速い

CALL shortestPath();