We have a database table EXAMPLE, which has data as follows:

Some records have nulls under FK field. We are trying to replace each null with “FK value of the direct parent node”, and write the corresponding PK value in the indent format at output. If the parent node is also node, find the FK value recursively upward. Below is the desired result:

Oracle的SQL：

SELECT

    LPAD(' ',LEVEL) || PK AS PK,

    NVL(FK, REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(FK,'/'),'(\d+)/*$',1,1,'',1)) AS FK,

    PARENT

FROM EXAMPLE

START WITH PARENT='000' CONNECT BY PRIOR PK = PARENT;

A recursive operation is needed here to replace the current FK value with that in the direct parent node. It is not very hard to achieve a recursive query in Oracle. The true difficulty lies in the subsequent computations, where the FK value on the superior node will be referenced. As SQL does not have concepts of explicit records and reference, it uses the regular expression to handle strings generated according to the recursive relationship. That is too hard.

It is easy to code the task in the open-source esProc SPL, without using the difficult regular expression:

SPL supports explicit records to be able to convert the referencing foreign key into a record type field, making it convenient to handle subsequent computations after the recursive operation.