Loop Infinito

[rmanParticipante]

@C-S-R

Montei um cenário:


CREATE TABLE PESSOAS(
ID NUMBER
,NOME VARCHAR2(64)
,RESPONSAVEL_ID NUMBER
)
TABLESPACE TS_DELPHI_DATA;

ALTER TABLE PESSOAS ADD CONSTRAINT PK_PESSOAS PRIMARY KEY(ID) USING INDEX TABLESPACE TS_DELPHI_INDEX;

ALTER TABLE PESSOAS ADD CONSTRAINT FK_PESSOAS_PESSOAS FOREIGN KEY(RESPONSAVEL_ID) REFERENCES PESSOAS(ID);

CREATE TABLE CARROS(
ID NUMBER
,PESSOA_ID NUMBER
,PLACA VARCHAR2(16)
)
TABLESPACE TS_DELPHI_DATA;

ALTER TABLE CARROS ADD CONSTRAINT PK_CARROS PRIMARY KEY(ID) USING INDEX TABLESPACE TS_DELPHI_INDEX;

ALTER TABLE CARROS ADD CONSTRAINT FK_CARROS_PESSOAS FOREIGN KEY(PESSOA_ID) REFERENCES PESSOAS(ID);

CREATE TABLE REVISOES(
ID NUMBER
,CARRO_ID NUMBER
,QUILOMETRAGEM NUMBER(15,2)
,DATAREVISAO TIMESTAMP WITH TIME ZONE
)
TABLESPACE TS_DELPHI_DATA;

ALTER TABLE REVISOES ADD CONSTRAINT PK_REVISOES PRIMARY KEY(ID) USING INDEX TABLESPACE TS_DELPHI_INDEX;

ALTER TABLE REVISOES ADD CONSTRAINT FK_REVISOES_CARROS FOREIGN KEY(CARRO_ID) REFERENCES CARROS(ID);


Consegui resolver o problema do auto relacionamento, teria que trabalhar mais caso os relacionamentos fechem o ciclo entre as entidades.


CREATE OR REPLACE PACKAGE PKG_SQL IS

FUNCTION TABLE_DEPENDENCIES(PTABLE IN VARCHAR2
                                    ,PLEVEL IN NUMBER DEFAULT 0) RETURN VARCHAR2;

END PKG_SQL;
/

CREATE OR REPLACE PACKAGE BODY PKG_SQL IS

FUNCTION TABLE_DEPENDENCIES(PTABLE IN VARCHAR2
                                    ,PLEVEL IN NUMBER DEFAULT 0) RETURN VARCHAR2 IS
    VCHIELD_TABLE VARCHAR2(32);
    VDEPENDENCIES NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO VDEPENDENCIES
    FROM USER_CONSTRAINTS P
    INNER JOIN USER_CONSTRAINTS R ON R.R_OWNER = P.OWNER
                                      AND R.R_CONSTRAINT_NAME = P.CONSTRAINT_NAME
                                      AND R.CONSTRAINT_TYPE = 'R'
    WHERE P.CONSTRAINT_TYPE = 'P'
    AND P.TABLE_NAME = PTABLE;

    IF VDEPENDENCIES > 0
    THEN
        FOR VCURRENT_TABLE IN (SELECT R.TABLE_NAME
                                      FROM USER_CONSTRAINTS P
                                      INNER JOIN USER_CONSTRAINTS R ON R.R_OWNER = P.OWNER
                                                                         AND R.R_CONSTRAINT_NAME = P.CONSTRAINT_NAME
                                                                         AND R.CONSTRAINT_TYPE = 'R'
                                      WHERE P.CONSTRAINT_TYPE = 'P'
                                      AND P.TABLE_NAME = PTABLE)
        LOOP
            IF PTABLE = VCURRENT_TABLE.TABLE_NAME
            THEN
                DBMS_OUTPUT.PUT_LINE('AUTO RELATIONSHIP: ' || PTABLE);
            ELSE
                VCHIELD_TABLE := TABLE_DEPENDENCIES(PTABLE => VCURRENT_TABLE.TABLE_NAME
                                                              ,PLEVEL => PLEVEL + 1);
            END IF;
        END LOOP;
    ELSE
        IF PLEVEL = 0
        THEN
            DBMS_OUTPUT.PUT_LINE('NO DEPENDENCIES FOR: ' || PTABLE);
        ELSE
            DBMS_OUTPUT.PUT_LINE('LEAF: ' || PTABLE);
        END IF;

        RETURN PTABLE;
    END IF;

    IF PLEVEL = 0
    THEN
        DBMS_OUTPUT.PUT_LINE('ROOT: ' || PTABLE);
    ELSE
        DBMS_OUTPUT.PUT_LINE('LEVEL ' || PLEVEL || ': ' || PTABLE);
    END IF;

    RETURN VCHIELD_TABLE;
END;

END PKG_SQL;
/


Testando:


DECLARE
VLEAF_TABLE VARCHAR2(32);
BEGIN
VLEAF_TABLE := PKG_SQL.TABLE_DEPENDENCIES(PTABLE => 'PESSOAS');
END;
/


Output:


AUTO RELATIONSHIP: PESSOAS
LEAF: REVISOES
LEVEL 1: CARROS
ROOT: PESSOAS


Na definição da FK existe 3 tratamento para o DELETE:

ON DELETE NO ACTION: Apresenta um erro ORA caso o registro possua dependências.
ON DELETE CASCADE: Remove o registro e suas dependências.
ON DELETE SET NULL: Remove o registro e atualiza as dependências para NULL. Essa é a forma que pode acontecer o registro órfão.

Verifique qual é a melhor opção para você. Eu costumo trabalhar com ON DELETE NO ACTION.

Se você optar por utilizar o ON DELETE CASCADE, analise muito bem, por que ele remove todas as dependências. Bom mas me parece que é isso mesmo que você quer, desta forma você não precisa implementar essa rotina que faria isso. 🙂