Firebird - рекурсия в процедурах

Ниже приведены различные варианты работы с "рекурсивными" таблицами через хранимые процедуры.

На входе: таблица PARTS (PART_ID, PART_PID...)

/* Получить все субэлементы */
CREATE PROCEDURE PARTS_INSIDE (
    A_PART_ID INTEGER NOT NULL
) RETURNS (
    B_PART_ID INTEGER
) AS
DECLARE VARIABLE X INTEGER;
BEGIN
    SELECT PART_ID FROM PARTS WHERE PART_ID=:A_PART_ID INTO :B_PART_ID;
    IF (NOT B_PART_ID IS NULL) THEN SUSPEND;
	    ELSE EXCEPTION E_INVALID_VALUE 'Part not found: '||A_PART_ID;

    FOR SELECT PART_ID FROM PARTS
        WHERE PART_PID=:A_PART_ID INTO :X DO
    BEGIN
        FOR SELECT B_PART_ID FROM PARTS_INSIDE(:X) INTO :B_PART_ID DO SUSPEND;
    END
END

На входе: таблица TOPICS (TOPIC_ID, TOPIC_PID, TOPIC_CTYPE...)

/* Получить корневой элемент для узла дерева */
CREATE PROCEDURE PART_ROOT (
    TOPIC_ID INTEGER NOT NULL
) RETURNS (
    CTYPE INTEGER
) AS
DECLARE VARIABLE PID INTEGER;
BEGIN
    CTYPE=0;
	SELECT TOPIC_PID, TOPIC_CTYPE FROM TOPICS WHERE TOPIC_ID=:TOPIC_ID INTO :PID, :CTYPE;
	IF (NOT PID IS NULL) THEN
		EXECUTE PROCEDURE TOPIC_ROOTTYPE(:PID) RETURNING_VALUES :CTYPE;
    SUSPEND;
END


На входе: таблица USAGES (USAGE_ID, USAGE_PID, USAGE_NAME...)

/* Получить полный путь для заданного ID */
CREATE PROCEDURE USG_PATH (
    USAGE_ID INTEGER
) RETURNS (
    STRPATH VARCHAR(1024)
) AS
DECLARE VARIABLE USAGE_NAME VARCHAR(250);
DECLARE VARIABLE USAGE_PID INTEGER;
BEGIN
    IF (NOT USAGE_ID IS NULL) THEN
    BEGIN
        SELECT USAGE_PID, USAGE_NAME FROM USAGES WHERE USAGE_ID=:USAGE_ID INTO :USAGE_PID, :USAGE_NAME;
        SELECT STRPATH || '/' || :USAGE_NAME FROM USG_PATH(:USAGE_PID) INTO :STRPATH;
        SUSPEND;
    END
    ELSE
    BEGIN
        STRPATH='';
        SUSPEND;
    END
END

/* Получить все пути единым списком */
CREATE PROCEDURE USAGE_LIST
RETURNS (
    USAGE_ID INTEGER,
    USAGE_PID INTEGER,
    USAGE_RW XBOOL,
    USAGE_CODE XCODE,
    USAGE_PATH VARCHAR(1024)
) AS
BEGIN
    FOR SELECT USAGE_ID, USAGE_PID, USAGE_RW, USAGE_CODE FROM USAGES
		INTO :USAGE_ID, :USAGE_PID, :USAGE_RW, :USAGE_CODE DO
    BEGIN
        SELECT STRPATH FROM USG_PATH(:USAGE_ID) INTO :USAGE_PATH;
        SUSPEND;
    END
END

/* Получить все конечные точки группы */
CREATE PROCEDURE USG_INSIDE (
    A_USAGE_ID INTEGER NOT NULL
) RETURNS (
    B_USAGE_ID INTEGER
) AS
DECLARE VARIABLE X_USAGE_ID INTEGER;
BEGIN
    SELECT USAGE_ID FROM USAGES WHERE USAGE_ID=:A_USAGE_ID INTO :B_USAGE_ID;
    IF (NOT B_USAGE_ID IS NULL) THEN SUSPEND;
		ELSE EXCEPTION E_INVALID_VALUE 'Usage not found: '||A_USAGE_ID;

    FOR SELECT USAGE_ID FROM USAGES
        WHERE USAGE_PID=:A_USAGE_ID INTO :X_USAGE_ID DO
    BEGIN
        FOR SELECT B_USAGE_ID FROM USG_INSIDE(:X_USAGE_ID) INTO :B_USAGE_ID DO SUSPEND;
    END
END

/* Получить все родительские группы для точки  */
CREATE PROCEDURE USG_OUTSIDE (
    A_USAGE_ID INTEGER NOT NULL
) RETURNS (
    B_USAGE_ID INTEGER
) AS
DECLARE VARIABLE X_USAGE_ID INTEGER;
BEGIN
    SELECT USAGE_ID FROM USAGES WHERE USAGE_ID=:A_USAGE_ID INTO :B_USAGE_ID;
    IF (NOT B_USAGE_ID IS NULL) THEN SUSPEND;
		ELSE EXCEPTION E_INVALID_VALUE 'Usage not found: '||A_USAGE_ID;

    FOR SELECT USAGE_PID FROM USAGES
        WHERE USAGE_ID=:A_USAGE_ID AND USAGE_PID IS NOT NULL INTO :X_USAGE_ID DO
    BEGIN
        FOR SELECT B_USAGE_ID FROM USG_OUTSIDE(:X_USAGE_ID) INTO :B_USAGE_ID DO SUSPEND;
    END
END

/* Получить все точки в цепочке (можно сделать одним запросом WITH) */
CREATE PROCEDURE USG_FULLSIDE ( /* B_USAGE_ID:A_USAGE_ID */
    A_USAGE_ID INTEGER NOT NULL
) RETURNS (
    B_USAGE_ID INTEGER
) AS
BEGIN
    FOR SELECT B_USAGE_ID FROM USG_INSIDE(:A_USAGE_ID)
      UNION SELECT B_USAGE_ID FROM USG_OUTSIDE(:A_USAGE_ID)
        INTO :B_USAGE_ID DO SUSPEND;
END

/* Получить разницу между двуми деревьями */
CREATE PROCEDURE USG_DIFF (
    USAGE_ID1 INTEGER NOT NULL,
    USAGE_ID2 INTEGER NOT NULL
) RETURNS (
    USAGE_ID INTEGER
) AS
DECLARE VARIABLE Q INTEGER;
BEGIN
    FOR SELECT X.B_USAGE_ID, 1 AS K FROM USG_INSIDE(:USAGE_ID1) X
        LEFT JOIN (SELECT B_USAGE_ID, 2 AS K FROM USG_INSIDE(:USAGE_ID2)) Y
            ON X.B_USAGE_ID=Y.B_USAGE_ID WHERE K IS NULL INTO :USAGE_ID, :Q DO
    SUSPEND;
END