01-PostgreSQL 存储过程的基本介绍以及入门(基本结构、声明和赋值、控制结构)(上)+https://developer.aliyun.com/article/1492279
常量
如果在定义变量时指定了 CONSTANT 关键字,意味着定义的是常量。常量的值需要在声明时初始化,并且不能修改。
以下示例通过定义常量 PI 计算圆的面积:
DO $$ DECLARE PI CONSTANT NUMERIC := 3.14159265; radius NUMERIC; BEGIN radius := 1.0; RAISE NOTICE 'The area is %', PI * radius * radius; END $$;
NOTICE: The area is 3.1415926500
常量可以用于避免魔数(magic number),提高代码的可读性;也可以减少代码的维护工作,所有使用常量的代码都会随着常量值的修改而同步,不需要修改多个硬编码的数据值。
控制结构
IF 语句
IF 语句可以基于条件选择性执行操作, PL/pgSQL 提供了三种形式的 IF 语句。
IF … THEN … END IF
IF … THEN … ELSE … END IF
IF … THEN … ELSIF … THEN … ELSE … END IF
首先,最简单的 IF 语句如下:
IF boolean-expression THEN statements END IF;
如果表达式 boolean-expression 的值为真,执行 THEN 之后的语句;否则,忽略这些语句。例如:
DO $$ BEGIN IF 2 > 3 THEN RAISE NOTICE '2 大于 3'; END IF; IF 2 < 3 THEN RAISE NOTICE '2 小于 3'; END IF; END $$; NOTICE: 2 小于 3
第二种 IF 语句的语法如下:
IF boolean-expression THEN statements ELSE other-statements END IF;
如果表达式 boolean-expression 的值为真,执行 THEN 之后的语句;否则,执行 ELSE 之后的语句。例如:
DO $$ BEGIN IF 2 > 3 THEN RAISE NOTICE '2 大于 3'; ELSE RAISE NOTICE '2 小于 3'; END IF; END $$; NOTICE: 2 小于 3
第三种 IF 语句支持多个条件分支:
IF boolean-expression THEN statements [ ELSIF boolean-expression THEN statements ] [ ELSIF boolean-expression THEN statements ] ... [ ELSE statements ] END IF;
依次判断条件中的表达式,如果某个条件为真,执行相应的语句;如果所有条件都为假,执行 ELSE 后面的语句;如果没有 ELSE 就什么都不执行。例如:
DO $$ DECLARE i integer := 3; j integer := 3; BEGIN IF i > j THEN RAISE NOTICE 'i 大于 j'; ELSIF i < j THEN RAISE NOTICE 'i 小于 j'; ELSE RAISE NOTICE 'i 等于 j'; END IF; END $$; NOTICE: i 等于 j DO
CASE 语句
除了 IF 语句之外,PostgreSQL 还提供了 CASE 语句,同样可以根据不同的条件执行不同的分支语句。CASE 语句分为两种:简单 CASE 和搜索 CASE 语句。
⚠️CASE 语句和第 15 篇中介绍的 CASE 表达式不是一个概念,CASE 表达式是一个 SQL 表达式。
简单 CASE 语句的结构如下:
CASE search-expression WHEN expression [, expression [ ... ]] THEN statements [ WHEN expression [, expression [ ... ]] THEN statements ... ] [ ELSE statements ] END CASE;
首先,计算 search-expression 的值;然后依次和 WHEN 中的表达式进行等值比较;如果找到了相等的值,执行相应的 statements;后续的分支不再进行判断;如果没有匹配的值,执行 ELSE 语句;如果此时没有 ELSE,将会抛出 CASE_NOT_FOUND 异常。
例如:
DO $$ DECLARE i integer := 3; BEGIN CASE i WHEN 1, 2 THEN RAISE NOTICE 'one or two'; WHEN 3, 4 THEN RAISE NOTICE 'three or four'; ELSE RAISE NOTICE 'other value'; END CASE; END $$; NOTICE: three or four
简单 CASE 语句只能进行简单的等值比较,搜索 CASE 语句可以实现更复杂的控制逻辑:
CASE WHEN boolean-expression THEN statements [ WHEN boolean-expression THEN statements ... ] [ ELSE statements ] END CASE;
依次判断每个 WHEN 之后的表达式,如果为真则执行相应的语句;后续的分支不再进行判断;如果没有匹配的值,执行 ELSE 语句;如果此时没有 ELSE,将会抛出 CASE_NOT_FOUND 异常。例如:
DO $$ DECLARE i integer := 3; BEGIN CASE WHEN i BETWEEN 0 AND 10 THEN RAISE NOTICE 'value is between zero and ten'; WHEN i BETWEEN 11 AND 20 THEN RAISE NOTICE 'value is between eleven and twenty'; ELSE RAISE NOTICE 'other value'; END CASE; END $$;
搜索 CASE 表达式可以构造任意复杂的判断逻辑,实现 IF 语句的各种功能。
循环语句
PostgreSQL 提供了 4 种循环执行命令的语句:LOOP、WHILE、FOR 和 FOREACH 循环,以及循环控制的 EXIT 和 CONTINUE 语句。
首先,LOOP 用于定义一个无限循环语句:
[ <<label>> ] LOOP statements END LOOP [ label ];
一般需要使用 EXIT 或者 RETURN 语句退出循环,label 可以用于 EXIT 或者 CONTINUE 语句退出或者跳到执行的嵌套循环中。例如:
DO $$ DECLARE i integer := 0; BEGIN LOOP EXIT WHEN i = 5; i := i + 1; RAISE NOTICE 'Loop: %', i; END LOOP; END $$; NOTICE: Loop: 1 NOTICE: Loop: 2 NOTICE: Loop: 3 NOTICE: Loop: 4 NOTICE: Loop: 5
其中,EXIT 语句用于退出循环。完整的 EXIT 语句如下:
EXIT [ label ] [ WHEN boolean-expression ];
另一个控制循环的语句是 CONTINUE:
CONTINUE [ label ] [ WHEN boolean-expression ];
CONTINUE 表示忽略后面的语句,直接进入下一次循环。例如:
DO $$ DECLARE i integer := 0; BEGIN LOOP EXIT WHEN i = 10; i := i + 1; CONTINUE WHEN mod(i, 2) = 1; RAISE NOTICE 'Loop: %', i; END LOOP; END $$; NOTICE: Loop: 2 NOTICE: Loop: 4 NOTICE: Loop: 6 NOTICE: Loop: 8 NOTICE: Loop: 10
当变量 i 为奇数时,直接进入下一次循环,不会打印出变量的值。
WHILE 循环的语法如下:
[ <<label>> ] WHILE boolean-expression LOOP statements END LOOP [ label ];
当表达式 boolean-expression 的值为真时,循环执行其中的语句;然后重新计算表达式的值,当表达式的值假时退出循环。例如:
DO $$ DECLARE i integer := 0; BEGIN WHILE i < 5 LOOP i := i + 1; RAISE NOTICE 'Loop: %', i; END LOOP; END $$; NOTICE: Loop: 1 NOTICE: Loop: 2 NOTICE: Loop: 3 NOTICE: Loop: 4 NOTICE: Loop: 5
FOR 循环可以用于遍历一个整数范围或者查询结果集,遍历整数范围的语法如下:
[ <<label>> ] FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP statements END LOOP [ label ];
FOR 循环默认从小到大进行遍历,REVERSE 表示从大到小遍历;BY 用于指定每次的增量,默认为 1。例如:
DO $$ BEGIN FOR i IN 1..5 BY 2 LOOP RAISE NOTICE 'Loop: %', i; END LOOP; END $$; NOTICE: Loop: 1 NOTICE: Loop: 3 NOTICE: Loop: 5
变量 i 不需要提前定义,可以在 FOR 循环内部使用。
遍历查询结果集的 FOR 循环如下:
[ <<label>> ] FOR target IN query LOOP statements END LOOP [ label ];
其中,target 可以是一个 RECORD 变量、行变量或者逗号分隔的标量列表。在循环中,target 代表了每次遍历的行数据。例如:
DO $$ DECLARE emp record; BEGIN FOR emp IN (SELECT * FROM employees LIMIT 5) LOOP RAISE NOTICE 'Loop: %,%', emp.first_name, emp.last_name; END LOOP; END $$; NOTICE: Loop: Steven,King NOTICE: Loop: Neena,Kochhar NOTICE: Loop: Lex,De Haan NOTICE: Loop: Alexander,Hunold NOTICE: Loop: Bruce,Ernst
FOREACH 循环与 FOR 循环类似,只不过变量的是一个数组:
[ <<label>> ] FOREACH target [ SLICE number ] IN ARRAY expression LOOP statements END LOOP [ label ];
如果没有指定 SLICE 或者指定 SLICE 0,FOREACH 将会变量数组中的每个元素。例如:
DO $$ DECLARE x int; BEGIN FOREACH x IN ARRAY (ARRAY[[1,2,3],[4,5,6]]) LOOP RAISE NOTICE 'x = %', x; END LOOP; END $$; NOTICE: x = 1 NOTICE: x = 2 NOTICE: x = 3 NOTICE: x = 4 NOTICE: x = 5 NOTICE: x = 6
如果指定了一个正整数的 SLICE,FOREACH 将会变量数组的切片;SLICE 不能大于数组的维度。例如:
DO $$ DECLARE x int[]; BEGIN FOREACH x SLICE 1 IN ARRAY (ARRAY[[1,2,3],[4,5,6]]) LOOP RAISE NOTICE 'row = %', x; END LOOP; END $$; NOTICE: row = {1,2,3} NOTICE: row = {4,5,6}
以上示例通过 FOREACH 语句遍历了数组的一维切片。
关注公众号:熊猫Jay字节之旅,了解更多 AI 技巧 ~