Create condition in instruction

Asked

Viewed 28 times

0

Hello, I have the following select: 

SELECT CONSTRAINT_NAME FROM user_cons_columns WHERE COLUMN_NAME = 'CANCELAMENTO' and table_name = 'NAVIO';

After picking the name the same goes to another query but if it returns null I end up having an error and interrupts my script. How would a condition be to verify whether it has worked or not? If it returns something, it will be executed: 

alter table NAVIO DROP CONSTRAINT || constr_name;
sql oracle

1 answer

0

If you are using PL-SQL in your script, you can solve it this way:

DECLARE
  CURSOR C_CONSTRAINT IS
    SELECT CONSTRAINT_NAME 
      FROM user_cons_columns 
     WHERE COLUMN_NAME = 'CANCELAMENTO' 
       AND table_name = 'NAVIO';       
  V_CONSTRAINT C_CONSTRAINT%ROWTYPE;       
BEGIN    
    OPEN C_CONSTRAINT;
    LOOP
      FETCH C_CONSTRAINT INTO V_CONSTRAINT;
      EXIT WHEN C_CONSTRAINT%NOTFOUND;
      EXECUTE IMMEDIATE 'ALTER TABLE NAVIO DROP CONSTRAINT ' || V_CONSTRAINT.CONSTRAINT_NAME;
    END LOOP;
END;

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.