Utilización de consultas recursivas

Algunas aplicaciones trabajan con datos que son recursivos por naturaleza. Para consultar este tipo de datos, puede utilizar una consulta jerárquica o una expresión de tabla común recursiva.

Un ejemplo de datos recursivos es una aplicación de lista de materiales (BOM) que trabaja con la expansión de piezas y sus subcomponentes. Por ejemplo, una silla puede estar hecha de una unidad de asiento y un conjunto de pierna. La unidad de asiento puede consistir en un asiento y dos brazos. Cada una de estas partes se puede dividir en sus subpartes hasta que haya una lista de todas las partes necesarias para construir una silla.

Db2® for i proporciona dos formas de definir una consulta recursiva. La primera se denomina consulta jerárquica que utiliza la cláusula CONNECT BY para definir cómo debe asociarse una fila padre con sus filas hijo. El segundo método es utilizar una expresión de tabla común recursiva. Esto utiliza una expresión de tabla común para definir la primera fila, o semilla, y luego utiliza UNION para definir cómo se determinan las filas hijo.

Cada uno de estos métodos para definir una consulta recursiva tiene ventajas y desventajas. La sintaxis de CONNECT BY es mucho más sencilla de entender, pero tiene menos formas de derivar los datos en su consulta. CONNECT BY puede especificarse en cualquier subselección en cualquier lugar de una consulta. Una expresión de tabla común recursiva tiene más opciones sobre cómo se define la unión para generar las filas hijo.

Hay un par de diferencias de comportamiento entre una consulta de conexión por recursiva y una consulta de expresión de tabla común recursiva. En primer lugar, difieren en cómo manejan los datos cíclicos. Esta diferencia se analiza en los ejemplos. En segundo lugar, conectar por permite una clasificación entre hermanos. Esto también se muestra en los ejemplos. Por último, las dos implementaciones difieren en cómo se colocan los datos en una cola que se utiliza para implementar la recurrencia. De forma predeterminada, los datos de una expresión de tabla común recursiva tienden a salir en primer orden de amplitud, primero en entrar primero en salir. Con connect by, el pedido está diseñado para salir primero a profundidad. Esto significa que las filas de un paso recursivo van inmediatamente después de su fila padre. La sintaxis de expresión de tabla común recursiva le ofrece una opción de profundidad o amplitud en primer orden jerárquico añadiendo la cláusula SEARCH. La conexión por sintaxis es siempre la profundidad primero.

En los ejemplos del planificador de viajes para estos métodos recursivos, los vuelos de las aerolíneas y las conexiones de tren se utilizan para encontrar rutas de transporte entre ciudades. En los ejemplos se utilizan las siguientes definiciones de tabla y datos.

CREATE TABLE FLIGHTS (DEPARTURE CHAR(20),
                      ARRIVAL CHAR(20), 
                      CARRIER CHAR(15),
                      FLIGHT_NUMBER CHAR(5), 
                      PRICE INT);


INSERT INTO FLIGHTS VALUES('New York', 'Paris', 'Atlantic', '234', 400);
INSERT INTO FLIGHTS VALUES('Chicago', 'Miami', 'NA Air', '2334', 300);
INSERT INTO FLIGHTS VALUES('New York', 'London', 'Atlantic', '5473', 350);
INSERT INTO FLIGHTS VALUES('London', 'Athens'  , 'Mediterranean', '247', 340);
INSERT INTO FLIGHTS VALUES('Athens', 'Nicosia' , 'Mediterranean', '2356', 280); 
INSERT INTO FLIGHTS VALUES('Paris', 'Madrid' , 'Euro Air',  '3256', 380);
INSERT INTO FLIGHTS VALUES('Paris', 'Cairo' , 'Euro Air', '63', 480);
INSERT INTO FLIGHTS VALUES('Chicago', 'Frankfurt', 'Atlantic', '37', 480);
INSERT INTO FLIGHTS VALUES('Frankfurt', 'Moscow', 'Asia Air', '2337', 580);
INSERT INTO FLIGHTS VALUES('Frankfurt', 'Beijing', 'Asia Air',  '77', 480); 
INSERT INTO FLIGHTS VALUES('Moscow', 'Tokyo', 'Asia Air', '437', 680);
INSERT INTO FLIGHTS VALUES('Frankfurt', 'Vienna', 'Euro Air', '59', 200);
INSERT INTO FLIGHTS VALUES('Paris', 'Rome', 'Euro Air', '534', 340);
INSERT INTO FLIGHTS VALUES('Miami', 'Lima', 'SA Air', '5234', 530);
INSERT INTO FLIGHTS VALUES('New York', 'Los Angeles', 'NA Air', '84', 330);
INSERT INTO FLIGHTS VALUES('Los Angeles', 'Tokyo', 'Pacific Air', '824', 530);
INSERT INTO FLIGHTS VALUES('Tokyo', 'Hawaii', 'Asia Air', '94', 330);
INSERT INTO FLIGHTS VALUES('Washington', 'Toronto', 'NA Air', '104', 250);

CREATE TABLE TRAINS(DEPARTURE CHAR(20), 
                    ARRIVAL CHAR(20),
                    RAILLINE CHAR(15), 
                    TRAIN CHAR(5), 
                    PRICE INT); 

INSERT INTO TRAINS VALUES('Chicago', 'Washington', 'UsTrack', '323', 90;
INSERT INTO TRAINS VALUES('Madrid', 'Barcelona', 'EuroTrack', '5234', 60);
INSERT INTO TRAINS VALUES('Washington' , 'Boston' , 'UsTrack', '232', 50);

CREATE TABLE FLIGHTSTATS(FLIGHT_NO CHAR(5), 
                         ON_TIME_PERCENT DECIMAL(5,2), 
                         CANCEL_PERCENT DECIMAL(5,2)); 

INSERT INTO FLIGHTSTATS VALUES('234', 85.0, 0.20);
INSERT INTO FLIGHTSTATS VALUES('2334',  92.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('5473', 86.2, 0.10);
INSERT INTO FLIGHTSTATS VALUES('247',  91.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('2356', 91.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('3256', 92.0 , 0.10);
INSERT INTO FLIGHTSTATS VALUES('63', 90.5 , 0.10);
INSERT INTO FLIGHTSTATS VALUES('37', 87.0 , 0.20);
INSERT INTO FLIGHTSTATS VALUES('2337', 80.0, 0.20);
INSERT INTO FLIGHTSTATS VALUES('77', 86.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('437', 81.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('59',  85.0, 01.0);
INSERT INTO FLIGHTSTATS VALUES('534', 87.0 , 01.0);
INSERT INTO FLIGHTSTATS VALUES('5234', 88.0, 0.20);
INSERT INTO FLIGHTSTATS VALUES('84', 88.0, 0.1);
INSERT INTO FLIGHTSTATS VALUES('824', 93.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('94', 92.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('104', 93.0, 0.10);