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));
CREATE OR REPLACE TABLE PUBLIC.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 |
|COL1 |
|-----------------|
|Unexpected input.|
|COL1 |
|-----|
|three|
|COL1 |
|-----------------|
|Unexpected input.|
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 |
|COL1 |
|-----------------|
|Unexpected input.|
|COL1 |
|-----|
|three|
|COL1 |
|-----------------|
|Unexpected input.|
Known issues
No issues were found.
Related EWIS
No related EWIs.
Last updated