结构控制和循环介绍:
作为编程语言中极为重要的知识,控制和循环可以降低代码量和减少人的工作量。在PL/PGSQL中实现了常用的控制结构和循环方法,灵活使用确实可以用来提高数据库查询的效率。
结构控制:
1. 结构:IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF
CREATE OR REPLACE FUNCTION mydb.mysc.learn_conditional01(IN num integer,OUT result_str varchar(20)) AS
$BODY$
BEGIN
IF num <= 0 THEN
result_str = '小于等于0';
ELSIF num <= 100 THEN
result_str = '(0-100]';
ELSIF num <= 1000 THEN
result_str = '(100-1000]';
ELSE
result_str = '1000以上';
END IF;
END;
$BODY$
LANGUAGE plpgsql;
输出示例:
2. 结构:CASE ... WHEN ... THEN ... ELSE ... END CASE
CREATE OR REPLACE FUNCTION mydb.mysc.learn_conditional02(IN num integer,OUT result_str varchar(20)) AS
$BODY$
BEGIN
CASE num
WHEN 1,2,3,4,5 THEN
result_str = '1-5之间';
WHEN 7,8,9,10,11 THEN
result_str = '7-11之间';
WHEN 12,13,14,15,16 THEN
result_str = '12-16之间';
ELSE
result_str = '其他的';
END CASE;
END;
$BODY$
LANGUAGE plpgsql;
输出示例:
3. 结构:CASE WHEN ... THEN ... ELSE ... END CASE
CREATE OR REPLACE FUNCTION mydb.mysc.learn_conditional03(IN num integer,OUT result_str varchar(20)) AS
$BODY$
BEGIN
CASE
WHEN num <= 0 THEN
result_str = '小于等于0';
WHEN num <= 100 THEN
result_str = '(0-100]';
WHEN num <= 1000 THEN
result_str = '(100-1000]';
ELSE
result_str = '1000以上';
END CASE;
END;
$BODY$
LANGUAGE plpgsql;
输出示例:
循环:
1. LOOP循环
CREATE OR REPLACE FUNCTION mydb.mysc.learn_conditional04(OUT loopcnt integer) AS
$BODY$
DECLARE counter integer = 1;
BEGIN
LOOP
EXIT WHEN counter > 100; -- 条件满足时退出循环
counter := counter+1;
CONTINUE WHEN counter = 50; -- 条件满足时,跳过下边的代码进入下一次循环
RAISE notice '这是第%次循环',counter;
END LOOP;
loopcnt = counter;
END;
$BODY$
LANGUAGE plpgsql;
输出示例:
2. WHILE循环
CREATE OR REPLACE FUNCTION mydb.mysc.learn_conditional05(OUT loopcnt integer) AS
$BODY$
DECLARE counter integer = 1;
BEGIN
WHILE counter<=100 LOOP
RAISE notice '这是第%次循环',counter;
counter:=counter+1;
END LOOP;
loopcnt = counter;
END;
$BODY$
LANGUAGE plpgsql;
输出示例:
3. FOR循环
-- IN可以跟子查询
CREATE OR REPLACE FUNCTION mydb.mysc.learn_conditional06()
RETURNS Void AS
$BODY$
DECLARE counter integer = 1;
BEGIN
RAISE notice '--------------';
FOR i IN 1..10 LOOP
RAISE notice '这是第%次循环',i;
END LOOP;
RAISE notice '--------------';
FOR i IN REVERSE 10..1 LOOP
RAISE notice '这是倒序的第%次循环',i;
END LOOP;
RAISE notice '--------------';
FOR i IN 1..10 BY 3 LOOP --间隔2个数字
RAISE notice '这是间隔2个数的第%次循环',i;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql;
输出示例:
4. FOREACH循环
-- 实现传入数组的累加功能
CREATE OR REPLACE FUNCTION mydb.mysc.learn_conditional07(IN intarray int[],OUT resultnum integer)AS
$BODY$
DECLARE
x int;
tmp integer := 0;
BEGIN
FOREACH x IN ARRAY intarray
LOOP
RAISE notice '这是%',x;
tmp := tmp + x;
END LOOP;
resultnum = tmp;
END;
$BODY$
LANGUAGE plpgsql;
输出示例: