IF

Translation reference for IF structure

Description

The IF statement either runs or skips a sequence of one or more statements, depending on the value of a BOOLEAN expression. For more information regarding Oracle IF, check here.

IF boolean_expression THEN 
    statement 
    [ statement ]...
[ 
ELSIF boolean_expression THEN 
    statement 
    [ statement ]... ]...
   [ 
ELSE 
statement [ statement ]... ] END IF ;

IF ( <condition> ) THEN
    <statement>;
    [ <statement>; ... ]
[
ELSEIF ( <condition> ) THEN
    <statement>;
    [ <statement>; ... ]
]
[
ELSE
    <statement>;
    [ <statement>; ... ]
]
END IF;

Sample Source Patterns

Sample auxiliar table

CREATE TABLE if_table(col1 varchar(30));

Possible IF variations

Oracle

CREATE OR REPLACE PROCEDURE ifExample1 ( flag NUMBER )
IS
BEGIN
   IF flag = 1 THEN
      INSERT INTO if_table(col1) VALUES ('one');
   END IF;
END;

CALL ifExample1(1);
SELECT * FROM if_table;

CREATE OR REPLACE PROCEDURE ifExample2 ( flag NUMBER )
IS
BEGIN
   IF flag = 1 THEN
      INSERT INTO if_table(col1) VALUES ('one');
   ELSE
      INSERT INTO if_table(col1) VALUES ('Unexpected input.');
   END IF;
END;

CALL ifExample2(2);
SELECT * FROM if_table;

CREATE OR REPLACE PROCEDURE ifExample3 ( flag NUMBER )
IS
BEGIN
   IF flag = 1 THEN
      INSERT INTO if_table(col1) VALUES ('one');
   ELSIF flag = 2 THEN
      INSERT INTO if_table(col1) VALUES ('two');
   ELSIF flag = 3 THEN
      INSERT INTO if_table(col1) VALUES ('three');
   END IF;
END;

CALL ifExample3(3);
SELECT * FROM if_table;

CREATE OR REPLACE PROCEDURE ifExample4 ( flag NUMBER )
IS
BEGIN
   IF flag = 1 THEN
      INSERT INTO if_table(col1) VALUES ('one');
   ELSIF flag = 2 THEN
      INSERT INTO if_table(col1) VALUES ('two');
   ELSIF flag = 3 THEN
      INSERT INTO if_table(col1) VALUES ('three');
   ELSE
      INSERT INTO if_table(col1) VALUES ('Unexpected input.');  
   END IF;
END;

CALL ifExample4(4);
SELECT * FROM if_table;

|COL1|
|----|
|one |

Snowflake Scripting

CREATE OR REPLACE PROCEDURE PUBLIC.ifExample1 (flag FLOAT )
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      IF ( flag = 1) THEN
         INSERT INTO PUBLIC.if_table(col1) VALUES ('one');
      END IF;
   END;
$$;

CALL PUBLIC.ifExample1(1);

SELECT * FROM PUBLIC.if_table;

CREATE OR REPLACE PROCEDURE PUBLIC.ifExample2 (flag FLOAT )
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      IF ( flag = 1) THEN
         INSERT INTO PUBLIC.if_table(col1) VALUES ('one');
      ELSE
         INSERT INTO PUBLIC.if_table(col1) VALUES ('Unexpected input.');
      END IF;
   END;
$$;

CALL PUBLIC.ifExample2(2);

SELECT * FROM PUBLIC.if_table;

CREATE OR REPLACE PROCEDURE PUBLIC.ifExample3 (flag FLOAT )
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      IF ( flag = 1) THEN
         INSERT INTO PUBLIC.if_table(col1) VALUES ('one');
      ELSEIF ( flag = 2) THEN
         INSERT INTO PUBLIC.if_table(col1) VALUES ('two');
      ELSEIF ( flag = 3) THEN
         INSERT INTO PUBLIC.if_table(col1) VALUES ('three');
      END IF;
   END;
$$;

CALL PUBLIC.ifExample3(3);

SELECT * FROM PUBLIC.if_table;

CREATE OR REPLACE PROCEDURE PUBLIC.ifExample4 (flag FLOAT )
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      IF ( flag = 1) THEN
         INSERT INTO PUBLIC.if_table(col1) VALUES ('one');
      ELSEIF ( flag = 2) THEN
         INSERT INTO PUBLIC.if_table(col1) VALUES ('two');
      ELSEIF ( flag = 3) THEN
         INSERT INTO PUBLIC.if_table(col1) VALUES ('three');
      ELSE
         INSERT INTO PUBLIC.if_table(col1) VALUES ('Unexpected input.');
      END IF;
   END;
$$;

CALL PUBLIC.ifExample4(4);

SELECT * FROM PUBLIC.if_table;

|COL1|
|----|
|one |

Known issues

No issues were found.

No related EWIs.

PreviousBulk Cursor Helpers NextIS EMPTY

Last updated 3 years ago

Was this helpful?