how to write a loop query with oracle

Question

I have this table

id	i1	i2
Y001	x1	a1
Y001	x1	a2
Y001	a1	a5
Y001	a2	a3
Y001	a3	a4

The relationship between i1 and i2 is i2 contains i1,so a1 contains x1, a5 contains a1, I want to get the final relationship for all items.

The expected result is

id	i	i_final
Y001	x1	a5
Y001	x1	a4
Y001	a1	a5
Y001	a2	a4
Y001	a3	a4

Answer 1

You do not want a "loop". You want to use a hierarchical query and find the leaves of the hierarchy tree for each root:

SELECT id,
       CONNECT_BY_ROOT i1 AS i1,
       i2
FROM   table_name
WHERE  CONNECT_BY_ISLEAF = 1
CONNECT BY
       PRIOR i2 = i1
AND    PRIOR id = id -- Not sure if this is needed as you only have one id value

Which, for the sample data:

CREATE TABLE table_name (id, i1, i2) AS
SELECT 'Y001', 'x1', 'a1' FROM DUAL UNION ALL
SELECT 'Y001', 'x1', 'a2' FROM DUAL UNION ALL
SELECT 'Y001', 'a1', 'a5' FROM DUAL UNION ALL
SELECT 'Y001', 'a2', 'a3' FROM DUAL UNION ALL
SELECT 'Y001', 'a3', 'a4' FROM DUAL;

Outputs:

ID	I1	I2
Y001	a1	a5
Y001	a2	a4
Y001	a3	a4
Y001	x1	a5
Y001	x1	a4

fiddle