Oracle's method of judging whether the table, column and primary key exist

When writing a program, the database structure will often change, so it is often necessary to write some database scripts, which need to be sent to the site for execution after the completion of writing. If there are already existing or repeated execution, some scripts will report errors, so it is necessary to judge whether they exist. Now I will share some frequently used judgment methods with you:

One. How to determine whether an Oracle table exists

declare tableExistedCount number;   --Declare whether the variable stores the existence of the table to be queried
begin 
     select count(1) into tableExistedCount  from user_tables t where t.table_name = upper('Test'); --Query whether the current table exists from the system table
     if tableExistedCount  = 0 then --If not, create a new table using the quick execute statement
         execute immediate
         'create table Test --Create test table
         (ID number not null,Name = varchar2(20) not null)';
     end if;
end;

Two. The method of judging whether the column in Oracle table exists

declare columnExistedCount number;   --Declare whether the variable stores the existence of columns in the table to be queried
begin 
        --Query whether the columns in the table exist from the system table
        select count(1) into columnExistedCount from user_tab_columns t where t.table_name = upper('Test')  and t.column_name = upper('Age');     
        --If not, use the quick execute statement to add Age column
        if columnExistedCount = 0 then 
           execute immediate
           'alter table Test add age number not null';
        end if;
end;
DECLARE
num NUMBER;
BEGIN
SELECT COUNT(1)
INTO num
from cols
where table_name = upper('tableName')
and column_name = upper('columnName');
IF num > 0 THEN
execute immediate 'alter table tableName drop column columnName';
END IF;
END;

Three. The method of judging whether there is primary key in Oracle table

declare primaryKeyExistedCount number;   --Declare whether the variable stores the existence of columns in the table to be queried
begin 
        --Query whether there is a primary key in a table from the system table (since a table can only have one primary key, you only need to judge the constraint type)
        select count(1) into primaryKeyExistedCount from user_constraints t where t.table_name = upper('Test') and t.constraint_type = 'P';     
        --If not, add a primary key constraint using the quick execute statement
        if primaryKeyExistedCount  = 0 then 
        execute immediate
        'alter table Test add constraint PK_Test_ID primary key(id)';
        end if;
end;

Four. The method of judging whether there is foreign key in Oracle table

declare foreignKeyExistedCount number;   --Declare whether the variable stores the existence of columns in the table to be queried
begin 
        --Query whether there is a primary key in a table from the system table (since a table can only have one primary key, you only need to judge the constraint type)
        select count(1) into foreignKeyExistedCount from user_constraints t where t.table_name = upper('Test') and t.constraint_type = 'R' and t.constraint_name = 'Foreign key constraint name';     
        --If not, add a primary key constraint using the quick execute statement
        if foreignKeyExistedCount = 0 then 
           execute immediate
           'alter table Test add constraint Foreign key constraint name foreign key references Foreign key reference table(column)';
        end if;
end;

Tags: Oracle Database

Posted on Sun, 05 Apr 2020 12:38:05 -0700 by BrettCarr