Início / dba / Perguntas / 49634
Accepted
jpmc26
Asked: 2013-09-11 18:57:37 +0800 CST

Excluir linhas na tabela pai com base nos critérios da tabela filha - Oracle

  • 772

Eu tenho uma tabela Bcom uma chave estrangeira para tabela A. Eu quero DELETEalgumas linhas na tabela Be também quero DELETEsuas linhas pai na tabela A. No entanto, os critérios de exclusão são baseados na tabela B. As linhas na tabela Anão podem ser excluídas primeiro porque a referência na tabela Brestringe a exclusão, mas também preciso obter as chaves das Alinhas para excluir em B.

Aqui está um SQLFiddle com uma estrutura de tabela de exemplo: http://sqlfiddle.com/#!4/f156c/4/0 .

Minha primeira inclinação foi tentar salvar as chaves SELECTinserindo-as de Bem uma variável e, em seguida, usá -las em DELETEde A.

DECLARE
  A_ID_TO_DELETE DBMS_SQL.NUMBER_TABLE;
BEGIN
  SELECT A_ID BULK COLLECT INTO A_ID_TO_DELETE
  FROM (SELECT A_ID
        FROM B
        WHERE LENGTH(B_DATA) > 4
       );

  DELETE FROM B
  WHERE LENGTH(B_DATA) > 4;

  DELETE FROM A
  WHERE A_ID IN A_ID_TO_DELETE;
END;
/

Mas isso só dá um PLS-00382: expression is of wrong typeerro. O erro em si vem DELETEdo A; Eu sei disso porque se eu comentar, o bloco será executado.

Como posso contornar o expression is of wrong typeerro ou qual é outra maneira de abordar isso?

Versão do Oracle: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

(Sim, estou bem ciente de quantos anos isso tem. A escolha do banco de dados do cliente, não nossa.)

oracle oracle-10g

2 respostas

  1. Best Answer

    Tente assim,

    DECLARE
     A_ID_TO_DELETE DBMS_SQL.NUMBER_TABLE;
BEGIN
     SELECT A_ID BULK COLLECT INTO A_ID_TO_DELETE
     FROM (SELECT A_ID
           FROM   B
           WHERE  LENGTH(B_DATA) > 4
          );

     DELETE FROM B
     WHERE LENGTH(B_DATA) > 4;

     FORALL i IN A_ID_TO_DELETE.first..A_ID_TO_DELETE.last
          DELETE FROM A
          WHERE A_ID = A_ID_TO_DELETE(i);
END;
/
    • 2

  2. As restrições de chave estrangeira no Oracle são definidas (por padrão) como NON DEFERRABLEo que significa que a restrição é verificada sempre no final das instruções. Existem mais duas maneiras de definir uma restrição:

    • DEFERRABLE INITIALLY DEFERRED

    Se você tiver a restrição definida (ou modificá-la agora) como DEFERRABLE INITIALLY DEFERRED, então a verificação da restrição acontecerá no final das transações. Portanto, isso funcionaria para você (mas também modificaria como a restrição é verificada em todas as outras transações).

    • DEFERRABLE INITIALLY IMMEDIATE

    Se você definir como DEFERRABLE(obtém o padrão de INITIALLY IMMEDIATE), ainda será verificado no final das instruções, mas poderá ser adiado. Assim, você pode optar por definir todas as restrições dentro de uma transação com SET CONSTRAINTS ALL DEFERRED;e isso funcionaria bem:

    SET CONSTRAINTS ALL DEFERRED; 

DELETE FROM A
WHERE A_ID IN  
     ( SELECT A_ID
       FROM B
       WHERE LENGTH(B_DATA) > 4
     );

DELETE FROM B
WHERE LENGTH(B_DATA) > 4;

    Teste no SQL-Fiddle

    Mais detalhes sobre restrições em Oracle docs: Constraints and Managing Integrity Constraints

    • 2

relate perguntas