Control Statements

RETURN Statements

In MogDB, data can be returned in either of the following ways:RETURN, RETURN NEXT, or RETURN QUERY. RETURN NEXT and RETURN QUERY are used only for functions and cannot be used for stored procedures.

RETURN

Syntax

Figure 1 shows the syntax diagram for a return statement.

Figure 1 return_clause::=

The above syntax diagram is explained as follows:

This statement returns control from a stored procedure or function to a caller.

Examples

See Example for call statement examples.

RETURN NEXT and RETURN QUERY

Syntax

When creating a function, specify SETOF datatype for the return values.

return_next_clause::=

return_query_clause::=

The above syntax diagram is explained as follows:

If a function needs to return a result set, use RETURN NEXT or RETURN QUERY to add results to the result set, and then continue to execute the next statement of the function. As the RETURN NEXT or RETURN QUERY statement is executed repeatedly, more and more results will be added to the result set. After the function is executed, all results are returned.

RETURN NEXT can be used for scalar and compound data types.

RETURN QUERY has a variant RETURN QUERY EXECUTE. You can add dynamic queries and add parameters to the queries by USING.

Examples

MogDB=# CREATE TABLE t1(a int);
MogDB=# INSERT INTO t1 VALUES(1),(10);

--RETURN NEXT
MogDB=# CREATE OR REPLACE FUNCTION fun_for_return_next() RETURNS SETOF t1 AS $$
DECLARE
   r t1%ROWTYPE;
BEGIN
   FOR r IN select * from t1
   LOOP
      RETURN NEXT r;
   END LOOP;
   RETURN;
END;
$$ LANGUAGE PLPGSQL;
MogDB=# call fun_for_return_next();
 a
---
 1
 10
(2 rows)

-- RETURN QUERY
MogDB=# CREATE OR REPLACE FUNCTION fun_for_return_query() RETURNS SETOF t1 AS $$
DECLARE
   r t1%ROWTYPE;
BEGIN
   RETURN QUERY select * from t1;
END;
$$
language plpgsql;
MogDB=# call fun_for_return_query();
 a
---
 1
 10
(2 rows)

Conditional Statements

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

MogDB supports five usages of IF:

• IF_THEN

  Figure 2 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:

  MogDB=# IF v_user_id <> 0 THEN
      UPDATE users SET email = v_email WHERE user_id = v_user_id;
  END IF;

• IF_THEN_ELSE

  Figure 3 IF_THEN_ELSE::=

  

  IF-THEN-ELSE statements add ELSE branches and can be executed if the condition is false.

  Example:

  MogDB=# IF parentid IS NULL OR parentid = ''
  THEN
      RETURN;
  ELSE
      hp_true_filename(parentid); -- Call the stored procedure.
  END IF;

• IF_THEN_ELSE IF

  IF statements can be nested in the following way:

  MogDB=# IF sex = 'm' THEN
      pretty_sex := 'man';
  ELSE
      IF sex = 'f' THEN
          pretty_sex := 'woman';
      END IF;
  END IF;

  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 4 IF_THEN_ELSIF_ELSE::=

  

  Example:

  IF number_tmp = 0 THEN
      result := 'zero';
  ELSIF number_tmp > 0 THEN
      result := 'positive';
  ELSIF number_tmp < 0 THEN
      result := 'negative';
  ELSE
      result := 'NULL';
  END IF;

• IF_THEN_ELSEIF_ELSE

  ELSEIF is an alias of ELSIF.

  Example:

  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;
  /
  
  CALL proc_control_structure(3);
  
  -- Delete the stored procedure.
  DROP PROCEDURE proc_control_structure;

Loop Statements

Simple LOOP Statements

The syntax diagram is as follows:

Figure 5 loop::=

Example

CREATE OR REPLACE PROCEDURE proc_loop(i in integer, count out integer)
AS
    BEGIN
        count:=0;
        LOOP
        IF count > i THEN
            raise info 'count is %. ', count;
            EXIT;
        ELSE
            count:=count+1;
        END IF;
        END LOOP;
    END;
/

CALL proc_loop(10,5);

NOTICE: The loop must be exploited together with EXIT; otherwise, a dead loop occurs.

WHILE-LOOP Statements

Syntax diagram

Figure 6 while_loop::=

If the conditional expression is true, a series of statements in the WHILE statement are repeatedly executed and the condition is decided each time the loop body is executed.

Example

CREATE TABLE integertable(c1 integer) ;
CREATE OR REPLACE PROCEDURE proc_while_loop(maxval in integer)
AS
    DECLARE
    i int :=1;
    BEGIN
        WHILE i < maxval LOOP
            INSERT INTO integertable VALUES(i);
            i:=i+1;
        END LOOP;
    END;
/

-- Invoke a function:
CALL proc_while_loop(10);

-- Delete the stored procedure and table.
DROP PROCEDURE proc_while_loop;
DROP TABLE integertable;

FOR_LOOP (Integer variable) Statement

Syntax diagram

Figure 7 for_loop::=

NOTE:

• The variable name is automatically defined as the integer type and exists only in this loop. The variable name falls between lower_bound and upper_bound.
• When the keyword REVERSE is used, the lower bound must be greater than or equal to the upper bound; otherwise, the loop body is not executed.

FOR_LOOP Query Statements

Syntax diagram

Figure 8 for_loop_query::=

NOTE: The variable target is automatically defined, its type is the same as that in the query result, and it is valid only in this loop. The target value is the query result.

FORALL Batch Query Statements

Syntax diagram

Figure 9 forall::=

NOTE:

• The variable index is automatically defined as the integer type and exists only in this loop. The value of index falls between the value of low_bound and the value of upper_bound.
• If SAVE EXCEPTIONS is specified, exceptions occurred during DML execution in the loop body are saved in SQL&BULK_EXCEPTIONS and an exception is thrown after the execution is complete. If there is no abnormal execution result in the loop, the loop will not be rolled back in the current subtransaction.

Example

CREATE TABLE hdfs_t1 (
  title NUMBER(6),
  did VARCHAR2(20),
  data_peroid VARCHAR2(25),
  kind VARCHAR2(25),
  interval VARCHAR2(20),
  time DATE,
  isModified VARCHAR2(10)
);

INSERT INTO hdfs_t1 VALUES( 8, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', to_date('21-06-1999', 'dd-mm-yyyy'), 'SH_CLERK' );

CREATE OR REPLACE PROCEDURE proc_forall()
AS
BEGIN
    FORALL i IN 100..120
        update hdfs_t1 set title = title + 100*i;
END;
/

-- Invoke a function:
CALL proc_forall();

-Query the invocation result of the stored procedure.
SELECT * FROM hdfs_t1 WHERE title BETWEEN 100 AND 120;

-- Delete the stored procedure and table.
DROP PROCEDURE proc_forall;
DROP TABLE hdfs_t1;

LABEL_LOOP Statements

Syntax

[label_begin:] LOOP
    statements
END LOOP [label_end]

NOTE:

The usage of the label is added based on the simple loop statement. The label rules are as follows:

• label_begin can appear independently (without label_end). However, if label_end is used, label_begin must appear.
• The label can be referenced by the CONTINUE or EXIT statement. In the B-compatible database, the ITERATE or LEAVE statement can also be used.

NOTICE: This loop is used only in the B-compatible database. An error is reported in other databases. This loop must be used together with EXIT. (In B-compatible mode, LEAVE has the same effect as EXIT, and ITERATE has the same effect as CONTINUE.) Otherwise, an infinite loop occurs.

Example

CREATE OR REPLACE PROCEDURE label_loop(i in integer, count out integer)
AS
    BEGIN
        count:=0;
        label:
        LOOP
        IF count > i THEN
            raise info 'count is %. ', count;
            LEAVE;
        ELSE
            count:=count+1;
        END IF;
        END LOOP label;
    END;
/

CALL proc_loop(10,5);

Branch Statements

Syntax

Figure 10 shows the syntax diagram for a branch statement.

Figure 10 case_when::=

Figure 11 shows the syntax diagram for when_clause.

Figure 11 when_clause::=

Parameter description:

• case_expression: specifies the variable or expression.
• when_expression: specifies the constant or conditional expression.
• statement: specifies the statement to be executed.

Examples

CREATE OR REPLACE PROCEDURE proc_case_branch(pi_result in integer, pi_return out integer)
AS
    BEGIN
        CASE pi_result
            WHEN 1 THEN
                pi_return := 111;
            WHEN 2 THEN
                pi_return := 222;
            WHEN 3 THEN
                pi_return := 333;
            WHEN 6 THEN
                pi_return := 444;
            WHEN 7 THEN
                pi_return := 555;
            WHEN 8 THEN
                pi_return := 666;
            WHEN 9 THEN
                pi_return := 777;
            WHEN 10 THEN
                pi_return := 888;
            ELSE
                pi_return := 999;
        END CASE;
        raise info 'pi_return : %',pi_return ;
END;
/

CALL proc_case_branch(3,0);

-- Delete the stored procedure.
DROP PROCEDURE proc_case_branch;

NULL Statements

In PL/SQL programs, NULL statements are used to indicate "nothing should be done", equal to placeholders. They grant meanings to some statements and improve program readability.

Syntax

The following shows example use of NULL statements.

DECLARE
    ...
BEGIN
    ...
    IF v_num IS NULL THEN
        NULL; --No data needs to be processed.
    END IF;
END;
/

Error Trapping Statements

By default, any error occurring in a PL/SQL function aborts execution of the function, and indeed of the surrounding transaction as well. You can trap errors and restore from them by using a BEGIN block with an EXCEPTION clause. The syntax is an extension of the normal syntax for a BEGIN block:

[<<label>>]
[DECLARE
    declarations]
BEGIN
    statements
EXCEPTION
    WHEN condition [OR condition ...] THEN
        handler_statements
    [WHEN condition [OR condition ...] THEN
        handler_statements
    ...]
END;

If no error occurs, this form of block simply executes all the statements, and then control passes to the next statement after END. But if an error occurs within the statements, further processing of the statements is abandoned, and control passes to the EXCEPTION list. The list is searched for the first condition matching the error that occurred. If a match is found, the corresponding handler_statements are executed, and then control passes to the next statement after END. If no match is found, the error propagates out as though the EXCEPTION clause were not there at all:

The error can be caught by an enclosing block with EXCEPTION, or if there is none it aborts processing of the function.

The condition names can be any of those shown in SQL standard error codes. The special condition name OTHERS matches every error type except QUERY_CANCELED.

If a new error occurs within the selected handler_statements, it cannot be caught by this EXCEPTION clause, but is propagated out. A surrounding EXCEPTION clause could catch it.

When an error is caught by an EXCEPTION clause, the local variables of the PL/SQL function remain as they were when the error occurred, but all changes to persistent database state within the block are rolled back.

Example:

CREATE TABLE mytab(id INT,firstname VARCHAR(20),lastname VARCHAR(20)) ;

INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');

CREATE FUNCTION fun_exp() RETURNS INT
AS $$
DECLARE
    x INT :=0;
    y INT;
BEGIN
    UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
    x := x + 1;
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'caught division_by_zero';
        RETURN x;
END;$$
LANGUAGE plpgsql;

call fun_exp();
NOTICE:  caught division_by_zero
 fun_exp
---------
       1
(1 row)

select * from mytab;
 id | firstname | lastname
----+-----------+----------
    | Tom       | Jones
(1 row)

DROP FUNCTION fun_exp();
DROP TABLE mytab;

When control reaches the assignment to y, it will fail with a division_by_zero error. This will be caught by the EXCEPTION clause. The value returned in the RETURN statement will be the incremented value of x.

NOTE: A block containing an EXCEPTION clause is more expensive to enter and exit than a block without one. Therefore, do not use EXCEPTION without need. In the following scenario, an exception cannot be caught, and the entire transaction rolls back. The threads of the nodes participating the stored procedure exit abnormally due to node failure and network fault, or the source data is inconsistent with that of the table structure of the target table during the COPY FROM operation.

Example: Exceptions with UPDATE/INSERT

This example uses exception handling to perform either UPDATE or INSERT, as appropriate:

CREATE TABLE db (a INT, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP

-- First try to update the key
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
-- Not there, so try to insert the key. If someone else inserts the same key concurrently, we could get a unique-key failure.
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
        -- Do nothing, and loop to try the UPDATE again.
        END;
     END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');

--Delete FUNCTION and TABLE:
DROP FUNCTION merge_db;
DROP TABLE db;

GOTO Statements

A GOTO statement unconditionally transfers the control from the current statement to a labeled statement. The GOTO statement changes the execution logic. Therefore, use this statement only when necessary. Alternatively, you can use the EXCEPTION statement to handle issues in special scenarios. To run a GOTO statement, the labeled statement must be unique.

Syntax

label declaration ::=

goto statement ::=

Examples

MogDB=# CREATE OR REPLACE PROCEDURE GOTO_test()
AS
DECLARE
    v1  int;
BEGIN
    v1  := 0;
        LOOP
        EXIT WHEN v1 > 100;
                v1 := v1 + 2;
                if v1 > 25 THEN
                        GOTO pos1;
                END IF;
        END LOOP;
<<pos1>>
v1 := v1 + 10;
raise info 'v1 is %. ', v1;
END;
/

call GOTO_test();

Constraints

Using GOTO statements has the following constraints:

• A GOTO statement does not allow multiple labeled statements even if the statements are in different blocks.

  BEGIN
    GOTO pos1;
    <<pos1>>
    SELECT * FROM ...
    <<pos1>>
    UPDATE t1 SET ...
  END;

• A GOTO statement cannot transfer control to the IF, CASE, or LOOP statement.

  BEGIN
     GOTO pos1;
     IF valid THEN
       <<pos1>>
       SELECT * FROM ...
     END IF;
   END;

• A GOTO statement cannot transfer control from one IF clause to another, or from one WHEN clause in the CASE statement to another.

  BEGIN
     IF valid THEN
       GOTO pos1;
       SELECT * FROM ...
     ELSE
       <<pos1>>
       UPDATE t1 SET ...
     END IF;
   END;

• A GOTO statement cannot transfer control from an outer block to an inner BEGIN-END block.

  BEGIN
     GOTO pos1;
     BEGIN
       <<pos1>>
       UPDATE t1 SET ...
     END;
   END;

• A GOTO statement cannot transfer control from an exception handler to the current BEGIN-END block. However, a GOTO statement can transfer control to the upper-layer BEGIN-END block.

  BEGIN
     <<pos1>>
     UPDATE t1 SET ...
     EXCEPTION
       WHEN condition THEN
          GOTO pos1;
   END;

• To branch to a position that does not have an executable statement, add the NULL statement.

  DECLARE
     done  BOOLEAN;
  BEGIN
     FOR i IN 1..50 LOOP
        IF done THEN
           GOTO end_loop;
        END IF;
        <<end_loop>>  -- not allowed unless an executable statement follows
        NULL; -- add NULL statement to avoid error
     END LOOP;  -- raises an error without the previous NULL
  END;
  /