Comparando esquemas Crear DAD
Mar 06

Borrado en cascada

Scripts Add comments

Hace poco necesité un script que me eliminase una tabla, y, antes de ella,todas las tablas de las que aquella dependiesen y antes que estas las que dependan de ellas, y así hasta el infinito.
El siguiente problema es el orden de eliminación, pero si utilizamos la opcion CASCADE CONSTRAINTS en el drop, el problema desaparece.


El script busca las dependencias recursivamente, evitando, en cualquier caso,
entrar en bucles infinitos.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121

SET SERVEROUTPUT ON
SET VERIFY OFF
ACCEPT tabla PROMPT "Nombre de la tabla: "
DECLARE
  ------------
  -- Tipos de Variable
  ------------
  TYPE T_tablas IS TABLE OF USER_TABLES.TABLE_NAME%TYPE INDEX BY BINARY_INTEGER;
  -------------
  -- Constantes
  -------------
  VTAB CONSTANT USER_TABLES.TABLE_NAME%TYPE := UPPER( '&&tabla' );
  ------------
  -- Variables
  ------------
  t_tab T_tablas;
 
  ------------------------------
  -- Procedimientos y funciones
  ------------------------------
  -- LoTengo -----------------------------------------------------------------
  FUNCTION LoTengo( vtabla IN USER_TABLES.TABLE_NAME%TYPE,
                    ttab   IN T_tablas )
RETURN BOOLEAN IS
    -- Función que determina si la tabla que le pasan como parámetro
    -- está contenida en la varible t_tab
    ------------
    -- Variables
    ------------
    blotengo BOOLEAN := FALSE;
  BEGIN
    IF ttab.LAST IS NOT NULL THEN
 
      <<BUSQUEDA>>
      FOR i IN ttab.FIRST..ttab.LAST LOOP
        IF ttab(i) = vtabla THEN
          blotengo := TRUE;
          EXIT BUSQUEDA;
        END IF;
      END LOOP BUSQUEDA;
    END IF;
 
    RETURN blotengo;
  END LoTengo;
 
  -- Agrega ------------------------------------------------------------------
  PROCEDURE Agrega( vtabla IN USER_TABLES.TABLE_NAME%TYPE,
                    ttab   IN OUT T_tablas )
  IS
    -- Procedimiento que agrega el nombre de tabla que se le pasa como
    -- parámetro al final de la variable tipo T_tablas que se le pasa
    -- como parámetro
    ------------
    -- Variables
    ------------
    nultimo BINARY_INTEGER;
  BEGIN
    nultimo := NVL( ttab.LAST + 1, 1 );
    IF NOT LoTengo( vtabla, ttab ) THEN
      ttab( nultimo ) := vtabla;
    END IF;
  END Agrega;
 
  -- BuscaDep ----------------------------------------------------------------
  PROCEDURE BuscaDep( vtabla IN USER_TABLES.TABLE_NAME%TYPE,
                      ttab   IN OUT T_tablas )
  IS
    -- Procedimiento recursivo que busca dependencias de la tabla que le pasan
    -- como parámetro y las agrega a la lista si no está ya
    -----------
    -- Cursores
    -----------
    CURSOR CTablas( vnombre_tabla USER_CONSTRAINTS.TABLE_NAME%TYPE ) IS
      SELECT B.TABLE_NAME VTABLA
      FROM   ( SELECT CONSTRAINT_NAME, OWNER
               FROM   USER_CONSTRAINTS
               WHERE  TABLE_NAME = vnombre_tabla
                 AND  CONSTRAINT_TYPE IN(  'P', 'U' )
             )A,
             USER_CONSTRAINTS B
      WHERE  A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
        AND  A.OWNER = B.R_OWNER
        AND  B.TABLE_NAME != vnombre_tabla;
 
  BEGIN
    -- En primer luger agrego la tabla que me pasan a la lista
      FOR rtab IN CTablas( vtabla ) LOOP
        IF NOT LoTengo( rtab.VTABLA, ttab )  THEN
          -- En primer lugar agrego la tabla
          Agrega( rtab.VTABLA, ttab );
 
          -- Ahora busco dependecias de la tabla que estoy recorriendo
          BuscaDep( rtab.VTABLA, ttab );
        END IF;
      END LOOP;
 
  END BuscaDep;
BEGIN
  DBMS_OUTPUT.DISABLE;
  DBMS_OUTPUT.ENABLE(10000000);
 
  -- Pongo el primer elemento de la lista de tablas
  --Agrega( VTAB,  t_tab );
 
  -- Busco las dependencias
  BuscaDep( VTAB, t_tab);
 
  -- Finalmente las muestro en pantalla
  IF t_tab.LAST IS NOT NULL THEN
    FOR i IN t_tab.FIRST..t_tab.LAST LOOP
      DBMS_OUTPUT.PUT_LINE( 'DROP TABLE ' || t_tab(i) ||
                            ' CASCADE CONSTRAINTS' );
      DBMS_OUTPUT.PUT_LINE( '/' );
    END LOOP;
  ELSE
    DBMS_OUTPUT.PUT_LINE( 'No se encontraron referencias para a la tabla ' ||
                          VTAB );
  END IF;
END;
/
UNDEF TABLA
This work, unless otherwise expressly stated, is licensed under a Creative Commons Attribution 3.0 License.

Comments are closed.