Oracle PL/SQL/Table/Drop Table

Delete a table if it exists

   
SQL> BEGIN
  2    FOR i IN (SELECT null FROM user_tables WHERE table_name = "INDIVIDUALS") LOOP
  3      EXECUTE IMMEDIATE "DROP TABLE individuals CASCADE CONSTRAINTS";
  4    END LOOP;
  5  END;
  6  /
PL/SQL procedure successfully completed.

Drop a table demo

  
SQL>
SQL> create table drop_me(
  2       a   int,
  3       b   int
  4  );
Table created.
SQL>
SQL> insert into drop_me values ( 1, 1 );
1 row created.
SQL>
SQL> insert into drop_me values ( 1, 2 );
1 row created.
SQL>
SQL> insert into drop_me values ( 2, 1 );
1 row created.
SQL>
SQL> drop table drop_me;
Table dropped.
SQL>
SQL>

Drop only if table exists.

   
SQL> BEGIN
  2
  3    FOR i IN (SELECT table_name FROM user_tables WHERE table_name = "SHARED_ALL") LOOP
  4      EXECUTE IMMEDIATE "DROP TABLE shared_all";
  5    END LOOP;
  6
  7  END;
  8  /
PL/SQL procedure successfully completed.
SQL>

Drop table with CASCADE CONSTRAINTS

   
SQL>
SQL> create table gender_tab (
  2    gender_id char(1),
  3    gender_nm varchar2(6),
  4    constraint gender_pk primary key (gender_id),
  5    constraint gender_id_ck check (gender_id in ("M", "F"))
  6  );
Table created.
SQL>
SQL> insert into gender_tab values ("F", "Female");
1 row created.
SQL> insert into gender_tab values ("M", "Male");
1 row created.
SQL>
SQL> create table people (
  2    first_name        varchar2(20),
  3    last_name         varchar2(25),
  4    gender         char(1)
  5  );
Table created.
SQL>
SQL> alter table people
  2  add constraint people_gender_fk
  3  foreign key (gender)
  4  references gender_tab;
Table altered.
SQL>
SQL> insert into people values ("S", "Dillon", "M");
1 row created.
SQL> insert into people values ("C", "Beck", "M");
1 row created.
SQL> insert into people values ("N", "Ellis", "F");
1 row created.
SQL>
SQL> drop table gender_tab;
drop table gender_tab
           *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SQL>
SQL> drop table gender_tab cascade constraints;
Table dropped.
SQL>
SQL>