Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Idempotent Oracle SQL migrations
--
-- create new table
--
declare
table_exists number;
begin
select count(1) into table_exists from USER_TABLES where table_name = 'PRODUCTS';
if (table_exists = 0) then
execute immediate 'create table PRODUCTS (ID number(19) not null ...';
end if;
end;
--
-- create new index
--
declare
index_exists number;
begin
select count(1) into index_exists from USER_INDEXES where index_name = 'PRODUCTS_BK';
if (index_exists = 0) then
execute immediate 'create unique index PRODUCTS_BK ...';
end if;
end;
--
-- create new constraint
--
declare
constraint_exists number;
begin
select count(1) into constraint_exists from USER_CONSTRAINTS where table_name = 'PRODUCTS' and constraint_name = 'PRODUCTS_FK_COMPANIES';
if (constraint_exists = 0) then
execute immediate 'alter table PRODUCTS add constraint PRODUCTS_FK_COMPANIES foreign key (COMPANY_ID) references COMPANIES (ID)';
end if;
end;
--
-- add column to existing table
--
declare
col_exists number;
begin
select count(1) into col_exists from USER_TAB_COLS where table_name = 'PRODUCTS' and column_name = 'SURCHARGE';
if (col_exists = 0) then
execute immediate 'alter table PRODUCTS add (SURCHARGE number(12, 3))';
end if;
end;
--
-- drop column from existing table
--
declare
col_exists number;
begin
select count(1) into col_exists from USER_TAB_COLS where table_name = 'PRODUCTS' and column_name = 'SURCHARGE';
if (col_exists > 0) then
execute immediate 'alter table PRODUCTS drop column SURCHARGE';
end if;
end;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.