The control structure of PL/pgSQL is the most important and useful part. In the actual working scenario, it is inseparable from the business processing logic. When writing PL/pgSQL, the control structure is used to operate data. The control structure supported by PL/pgSQL is almost the same as that of other languages, such as condition, loop, exception, etc. the following describes the use of control structure respectively.

1. Conditions

For conditional control structures, IF and CASE are supported. IF statements can be written in the following three ways:

if ... then ... end ifif ... then ... else ... end if if ... then ... elsif ... then ... else ... end if

And CASE can be written in two ways:

case ... when ... then ... else ... end casecase when ... then ... else ... end case

1.1,IF-THEN

IF Conditional expression THEN Main part END IF

IF-THEN is the simplest form of IF structure. IF the condition is true, the script of the main part will be executed, otherwise it will not be executed. For example:

IF id = 1 THENupdate t_order set order_code = '123' where id = 1;END IF;

1.2,IF-THEN-ELSE

IF Conditional expression THEN Main part ELSE Main part END IF;

IF-THEN-ELSE statementwhen the conditional expression is false, the script of ELSE part is executed. For example:

IF i_name is not null THENselect account into r_account from t_user t where t.name = i_name ;ELSEraise notice 'User name is empty';END IF;

1.3,IF-THEN-ELSIF

IF Conditional expression THEN Main part ELSIF Conditional expression THEN Main part ELSE Main part END IF;

IF-THEN-ELSIF supports multiple conditions. For example, if the first condition is not satisfied, enter the second condition. Otherwise, execute the ELSE part. Of course, the ELSE part can not be written.

IF i_n < 13 THENraise notice 'primary school';ELSIF i_n >= 13 and i_n <16 THENraise notice 'junior high school';ELSE raise notice 'finished';END IF;

1.4,CASE

The CASE of the control structure is written in the same way as the CASE in the SQL statement, for example:

CASE Search parameters WHEN value THEN Main part ELSE Main part END CASE;perhaps CA Guangdong party and government training www.henanganxun.cn SEWHEN Conditional expression THEN Main part ELSE Main part END CASE;

In the first way, if the search parameter is in the given value, execute the statement after THEN. Otherwise, execute the ELSE part, and the ELSE part can be omitted.
The second chapter is the writing method. The qualified expressions execute the statements after THEN, otherwise execute the ELSE part. For example:

CASE accountWHEN 'zhangsan','lisi' THENmsg := 'Account number is zhangsan or lisi'ELSEmsg := 'Account not found'END CASE;or CASE WHEN account in ('zhangsan','lisi') THENmsg := 'Account number is zhangsan or lisi'ELSEmsg := 'Account not found'END CASE;

The function of CASE statement is completely consistent with that of IF-THEN-ELSIF.

2. Circulation

In PL/pgSQL, LOOP, FOR and WHILE can be used to realize the LOOP, and EXIT and CONTINUE control the LOOP.

2.1 simple cycle

[<<label>>]LOOP Main part END LOOP [label];

Using LOOP directly to define a LOOP is an unconditional LOOP. If so defined, executing this statement will enter an infinite LOOP. The LOOP can be terminated only if EXIT and RETURN are written in the body. In actual scenarios, this unconditional LOOP is rarely used. The label here is optional. If a label is added, it can be used by EXIT and CONTINUE statements, such as nested loops. If the innermost LOOP wants to jump directly to the label defined on the outermost layer, it can use EXIT label to jump to the specified label position.

2.1.1 ,EXIT

EXIT [label] [WHEN Conditional expression]

There are two optional parts after EXIT. If the label part is given, EXIT to the specified label. If it is not specified, EXIT the current cycle. If the WHEN part is qualified, EXIT.

2.1.2,CONTINUE

CONTINUE [label] [WHEN Conditional expression]

The syntax of CONTINUE is similar to EXIT, and the functions of the following two parts are the same. The only difference is that EXIT exits the cycle, and CONTINUE exits this cycle and enters the next cycle.

2.2 FOR cycle

2.2.1 simple cycle

[<<label>>]FOR name IN [REVERSE] expression..expression [BY expression] LOOP Main part END LOOP [label];

This form of FOR iterates over a range of integers. The variable name is automatically defined as BY type and only exists in the loop (any existing definition of the variable name will be ignored in this loop). The two expressions that give the upper and lower bounds of the range are calculated once when entering the loop. The BY clause specifies the iteration step size (the default is 1), but if REVERSE is declared, the step size will become the corresponding negative value.

--Cycle output 1..10for i in 1..10 loopraise notice '%',i;end loop;--From 10..1 Output, you need to add reversefor i in reverse 10..1 loopraise notice '%',i;end loop;--From 1..10，Every two outputs for i in 1..10 by 2 loopraise notice '%',i;end loop;

2.2.2. Circular query results

Circular query results are used more in actual scenarios. The syntax is as follows:

[<<label>>]for target in Query statement loop Main part end loop [label];

target is a record variable, which must be declared in the declare part first, such as user information: us_info record, the complete case is as follows:

do $$declaret_o record;beginfor t_o in (select * from t_order) loop raise notice 'ID:%,customer:%',t_o.id,t_o.cus_name;end loop;end $$;

2.3 WHILE cycle

[<<label>>]while Conditional expression loop Main part end loop [label]

while loop: as long as the conditional expression is met, the loop will continue. For example:

do $$declarei int := 10;beginwhile i != 0 loopraise notice '%',i;i := i-1;end loop;end $$;

3. Abnormal

No program can fail. However, sometimes we need to catch exceptions to continue processing or better prompt the user with exception information. The PL/pgSQL exception structure is as follows:

[<<label>>][declare Declaration part]begin Main part exceptionwhen Exception name then exception handling end [label];

As long as there is an exception, it will enter the exception section and be recognized in the when for, such as:

do $$declarex int:=6;beginx := x/0;exception when division_by_zero  thenraise notice 'Divisor cannot be 0';end $$;