IF_EXISTS_SAMPLES.sql

DECLARE
   COUNT_CONSTRAINTS INTEGER;
BEGIN
	SELECT COUNT(CONSTRAINT_NAME) 
	INTO COUNT_CONSTRAINTS
	FROM USER_CONSTRAINTS 
	WHERE CONSTRAINT_NAME = 'PK_TABLE_EXAMPLE';

	IF COUNT_CONSTRAINTS > 0
	THEN
		EXECUTE IMMEDIATE 'ALTER TABLE TABLE_EXAMPLE DROP CONSTRAINT PK_TABLE_EXAMPLE';
	END IF;
END;
/
DECLARE
   COUNT_INDEXES INTEGER;
BEGIN
   SELECT COUNT ( * )
     INTO COUNT_INDEXES
     FROM USER_INDEXES
    WHERE INDEX_NAME = 'PK_TABLE_EXAMPLE';

   IF COUNT_INDEXES > 0
   THEN
	  EXECUTE IMMEDIATE 'DROP INDEX PK_TABLE_EXAMPLE;';
   END IF;
END;
/
ALTER TABLE TABLE_EXAMPLE ADD CONSTRAINT PK_TABLE_EXAMPLE PRIMARY KEY (SEQUENCIA, COD_EMPRESA_FW, COD_CENARIO);

--OR......
DEFINE OWN = &_USER
DECLARE
	V_TESTE				NUMBER(1, 0);
BEGIN  	
	
	SELECT COUNT(CONSTRAINT_TYPE) INTO V_TESTE FROM ALL_CONSTRAINTS WHERE TABLE_NAME = 'TABLE_EXAMPLE' AND CONSTRAINT_TYPE = 'P' AND OWNER = '&OWN';
	IF V_TESTE = 1 THEN --IF EXISTS
		BEGIN
			
			DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------------------------------------------');
			DBMS_OUTPUT.PUT_LINE('ACTION: CREATING PK_TABLE_EXAMPLE ');
			DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------------------------------------------');		
			
			EXECUTE IMMEDIATE('ALTER TABLE TABLE_EXAMPLE DROP CONSTRAINT PK_TABLE_EXAMPLE)'); 
			EXECUTE IMMEDIATE('ALTER TABLE TABLE_EXAMPLE ADD CONSTRAINT PK_TABLE_EXAMPLE PRIMARY KEY (SEQUENCIA, COD_EMPRESA_FW, COD_CENARIO)'); 
			
		EXCEPTION
			WHEN OTHERS THEN
				DBMS_OUTPUT.PUT_LINE('ERROR: PK_TABLE_EXAMPLE nao criada.');	
				DBMS_OUTPUT.PUT_LINE('	MSG:'||SQLCODE||' - '||SQLERRM);	
				ROLLBACK;
		END;
		COMMIT;
	ELSE
		EXECUTE IMMEDIATE('ALTER TABLE TABLE_EXAMPLE ADD CONSTRAINT PK_TABLE_EXAMPLE PRIMARY KEY (SEQUENCIA, COD_EMPRESA_FW, COD_CENARIO)'); 
	END IF;
	
END;

/

DECLARE
	V_TESTE				NUMBER(1, 0);
BEGIN  	
	
	SELECT COUNT(CONSTRAINT_TYPE) INTO V_TESTE FROM ALL_CONSTRAINTS WHERE TABLE_NAME = 'TABLE_TEST' AND CONSTRAINT_TYPE = 'P' AND OWNER = '&OWN';
	IF V_TESTE = 0 THEN --IF NOT EXISTS
		BEGIN		
			 
			EXECUTE IMMEDIATE('ALTER TABLE TABLE_TEST ADD CONSTRAINT PK_TABLE_TEST PRIMARY KEY (COD_EMPRESA_FW, COD_CENARIO, SEQ_PERIODO, IND_TIPO)'); 
			
		EXCEPTION
			WHEN OTHERS THEN
				DBMS_OUTPUT.PUT_LINE('ERROR: PK_TABLE_TEST nao criada.');	
				DBMS_OUTPUT.PUT_LINE('	MSG:'||SQLCODE||' - '||SQLERRM);	
				ROLLBACK;
		END;
	END IF;
END;

/

--If exists column IND_TIPO, drop it
DECLARE
	V_TESTE				NUMBER(1, 0);
BEGIN  	
	
	SELECT COUNT(TABLE_NAME) INTO V_TESTE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'TABLE_TEST' AND COLUMN_NAME = 'IND_TIPO';
	IF V_TESTE = 1 THEN --IF EXISTS
		BEGIN		
			 
			EXECUTE IMMEDIATE('ALTER TABLE TABLE_TEST DROP COLUMN IND_TIPO;'); 
			
		EXCEPTION
			WHEN OTHERS THEN
				DBMS_OUTPUT.PUT_LINE('ERROR: Coluna IND_TIPO nao dropada.');	
				DBMS_OUTPUT.PUT_LINE('	MSG:'||SQLCODE||' - '||SQLERRM);	
				ROLLBACK;
		END;
	END IF;
END;

/