Oracle (PL/SQL) Table operation: Check whether the table exists in the database during table creation. If the table exists, delete it and create it again.

Technorati label: before creating a table in PL/SQL, determine whether the table exists.
Delete the execution statement of the table if any.

Since PL/SQL is used for Oracle client operations, since SQL 2005 was used to execute "before creating a table, determine whether the table exists. If yes, delete the table, the "create again" Operation statement is very simple:

1: If exists (
2: Select * From sysobjects
3: Where id = object_id ('Students] ') and objectproperty (ID, 'isusertable') = 1)
4: Drop table [Students]

However, I am very depressed that the same method cannot be run in PL/SQL, and a compilation error is prompted.

After I checked the relevant posts on the internet, I wrote the following method for implementation:

1: declare
2: CNT number;
3: Begin
4: --- query whether the table to be created exists
5: Select count (*) into CNT from user_tables where table_name = 'students ';
6:
7: --- Delete the table if it exists
8: If CNT> 0 then
9: execute immediate 'drop table students ';
10: dbms_output.put_line (the 'table exists. The table is deleted successfully! ');
11: end if;
12: --- create the table after deletion
13: execute immediate 'create table students
14 :(
15: SnO numeric (6, 0) not null,
16: sname char (8) Not null,
17: Age numeric (3, 0 ),
18: sex char (2 ),
19: bplace char (20 ),
20: Primary Key (SNO)
21 :)
22: tablespace Users ';
23: end;

After comparison, it is not difficult to find that PL/SQL is a package of SQL statements to avoid compilation check.