Help Center/ GaussDB/ Centralized_8.x/ Stored Procedure/ Control Statements/ Conditional Statements
Updated on 2024-06-03 GMT+08:00
View PDF

Conditional Statements

Conditional statements are used to decide whether given conditions are met. Operations are executed based on the decisions made.

GaussDB supports five usages of IF:

  • IF_THEN
    Figure 1 IF_THEN::=

    IF_THEN is the simplest form of IF. If the condition is true, statements are executed. If it is false, they are skipped.

    Example:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
    		 
    gaussdb=# DECLARE
    v_user_id integer default 1;
BEGIN
IF v_user_id <> 0 THEN
    raise info 'v_user_id is NOT 0';
    
END IF;
END;
/
INFO:  v_user_id is NOT 0
  • IF_THEN_ELSE
    Figure 2 IF_THEN_ELSE::=

    IF_THEN_ELSE has an ELSE branch and can be executed if the condition is false.

    Example:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
    		 
    gaussdb=# DECLARE
    v_user_id integer default 1;
BEGIN
    IF v_user_id <> 0 THEN
        raise info 'v_user_id is NOT 0';
    ELSE
        raise info 'v_user_id is 0';
    END IF;
END;
/
INFO:  v_user_id is NOT 0
  • IF_THEN_ELSE IF

    IF statements can be nested in the following way:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
    		 
    gaussdb=# DECLARE
    v_user_id integer default 1;
BEGIN
    IF v_user_id = 0 THEN
        raise info 'v_user_id is  0';
    ELSE
        IF v_user_id > 0 THEN
            raise info 'v_user_id > 0';
        END IF;
    END IF;
END;
/
INFO:  v_user_id > 0

    Actually, this is a way of an IF statement nesting in the ELSE part of another IF statement. Therefore, an END IF statement is required for each nesting IF statement and another END IF statement is required to end the parent IF-ELSE statement. To set multiple options, use the following form:

  • IF_THEN_ELSIF_ELSE
    Figure 3 IF_THEN_ELSIF_ELSE::=

    Example:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
    		 
    gaussdb=# DECLARE
    v_user_id integer default NULL;
BEGIN
    IF v_user_id = 0 THEN
        raise info 'v_user_id is  0';
    ELSIF v_user_id > 0 THEN
        raise info 'v_user_id > 0';
    ELSIF v_user_id < 0 THEN
        raise info 'v_user_id < 0';
    ELSE
        raise info 'v_user_id is NULL';
    END IF;
END;
/
INFO:  v_user_id is NULL
  • IF_THEN_ELSEIF_ELSE

    ELSEIF is an alias of ELSIF.

    Example: 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
    		 
    gaussdb=# CREATE OR REPLACE PROCEDURE proc_control_structure(i in integer) 
AS
    BEGIN
        IF i > 0 THEN
            raise info 'i:% is greater than 0. ',i; 
        ELSIF i < 0 THEN
            raise info 'i:% is smaller than 0. ',i; 
        ELSE
            raise info 'i:% is equal to 0. ',i; 
        END IF;
        RETURN;
    END;
/
CREATE PROCEDURE

gaussdb=# CALL proc_control_structure(3);
INFO:  i:3 is greater than 0. 
 proc_control_structure 
------------------------

(1 row)

-- Delete the stored procedure.
gaussdb=# DROP PROCEDURE proc_control_structure;
DROP PROCEDURE
Parent topic: Control Statements