An example of whether an Oracle judgment table exists

The main is Query All_tables table table_name and owner, if the table exists, execute execute immediate ' drop table
TABLE_NAME ';

--Determine if the table exists, delete if present

Declare
Num number;
Begin
Select COUNT (1) into NUM from all_tables where table_name = ' EMP ' and owner= ' SCOTT ';
If Num=1 Then
Execute immediate ' drop table EMP ';
End If;
End

/
--Create a table

CREATE TABLE EMP
(EMPNO number (4) is not NULL,
Ename VARCHAR2 (10),
JOB VARCHAR2 (9),
MGR Number (4),
HireDate DATE,
SAL Number (7, 2),
COMM Number (7, 2),
DEPTNO number (2));

The above is to create the table below let's see if a table name exists before deleting the table and if so, delete the example.

Deleting a nonexistent table in Oracle, such as drop table notexisttable, prompts:

ORA-00942: Table or view does not exist,

If you use a program to execute the statement, you will report an exception, which requires us to determine whether the table exists before we delete it, or delete it if it exists.

The following is SQL that deletes a table without using a stored procedure implementation:

DECLARE num number;
BEGIN
SELECT COUNT (1) into the NUM from user_tables WHERE table_name = UPPER (' tablename ');
IF num > 0 THEN
EXECUTE IMMEDIATE ' DROP TABLE tablename ';
End IF;
End;