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.
For conditional control structures, IF and CASE are supported. IF statements can be written in the following three ways:
if ... then ... end if if ... then ... else ... end if if ... then ... elsif ... then ... else ... end if
And CASE can be written in two ways:
case ... when ... then ... else ... end case case when ... then ... else ... end case
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 THEN update t_order set order_code = '123' where id = 1; END IF;
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 THEN select account into r_account from t_user t where t.name = i_name ; ELSE raise notice 'User name is empty'; END IF;
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 THEN raise notice 'primary school'; ELSIF i_n >= 13 and i_n <16 THEN raise notice 'junior high school'; ELSE raise notice 'finished'; END IF;
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 CASE WHEN 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 account WHEN 'zhangsan','lisi' THEN msg := 'Account number is zhangsan or lisi' ELSE msg := 'Account not found' END CASE; or CASE WHEN account in ('zhangsan','lisi') THEN msg := 'Account number is zhangsan or lisi' ELSE msg := 'Account not found' END CASE;
The function of CASE statement is completely consistent with that of IF-THEN-ELSIF.
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.
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.
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..10 for i in 1..10 loop raise notice '%',i; end loop; --From 10..1 Output, you need to add reverse for i in reverse 10..1 loop raise notice '%',i; end loop; --From 1..10，Every two outputs for i in 1..10 by 2 loop raise 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 $$ declare t_o record; begin for 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 $$ declare i int := 10; begin while i != 0 loop raise notice '%',i; i := i-1; end loop; end $$;
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 exception when 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 $$ declare x int:=6; begin x := x/0; exception when division_by_zero then raise notice 'Divisor cannot be 0'; end $$;