循环语句
更新时间 2025-02-05 09:36:49
最近更新时间: 2025-02-05 09:36:49
本页介绍天翼云TeleDB数据库存储过程开发的循环语句。
LOOP 循环
teledb=# CREATE OR REPLACE PROCEDURE p_loop() AS
$$
DECLARE
v_id INTEGER := 1;
BEGIN
LOOP
RAISE NOTICE '%',v_id;
EXIT WHEN random()>0.8;
v_id := v_id + 1;
END LOOP ;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_loop();
NOTICE: 1
NOTICE: 2
NOTICE: 3
CALL
teledb=#
WHILE 循环
teledb=# CREATE OR REPLACE PROCEDURE p_while() AS
$$
DECLARE
v_id INTEGER := 1;
v_random float8 ;
BEGIN
LOOP
RAISE NOTICE '%',v_id;
v_id := v_id + 1;
v_random := random();
IF v_random > 0.8 THEN
RETURN;
END IF;
END LOOP ;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_while();
NOTICE: 1
CALL
FOR 循环
teledb=# CREATE OR REPLACE PROCEDURE p_for() AS
$$
BEGIN
FOR i IN 1..3 LOOP
RAISE NOTICE 'i = %',i;
END LOOP;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_for();
NOTICE: i = 1
NOTICE: i = 2
NOTICE: i = 3
CALL
teledb=# CREATE OR REPLACE PROCEDURE p_for_reverse() AS
$$
BEGIN
FOR i IN REVERSE 3..1 LOOP
RAISE NOTICE 'i = %',i;
END LOOP;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_for_reverse();
NOTICE: i = 3
NOTICE: i = 2
NOTICE: i = 1
CALL
使用REVERSE 递减。
teledb=# CREATE OR REPLACE PROCEDURE p_for_by() AS
$$
BEGIN
FOR i IN 1..8 BY 2 LOOP
RAISE NOTICE 'i = %',i;
END LOOP;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_for_by();
NOTICE: i = 1
NOTICE: i = 3
NOTICE: i = 5
NOTICE: i = 7
CALL
teledb=#
使用BY 设置步长。
FOR 循环查询结果
teledb=# CREATE OR REPLACE PROCEDURE p_for_record() AS
$$
DECLARE
v_rec RECORD;
BEGIN
FOR v_rec IN SELECT relname,relkind FROM pg_class limit 2 LOOP
RAISE NOTICE '%',v_rec;
END LOOP;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_for_record();
NOTICE: (pg_stat_statements,v)
NOTICE: (pg_proc,v)
CALL
teledb=#
FOREACH 循环一个数组
teledb=# CREATE OR REPLACE PROCEDURE p_foreach() AS
$$
DECLARE
v_random_arr float8[]:=ARRAY[random(),random()];
v_random float8;
BEGIN
FOREACH v_random IN ARRAY v_random_arr LOOP
RAISE NOTICE '%',v_random ;
END LOOP;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_foreach();
NOTICE: 0.744417542591691
NOTICE: 0.804096563253552
CALL
teledb=#
teledb=# CREATE OR REPLACE PROCEDURE p_foreach_slice() AS
$$
DECLARE
v_random_arr float8[][]:=ARRAY[ARRAY[random(),random()],ARRAY[random(),random()]];
v_random float8;
BEGIN
FOREACH v_random SLICE 0 IN ARRAY v_random_arr LOOP
RAISE NOTICE '%',v_random ;
END LOOP;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_foreach_slice();
NOTICE: 0.0220407997258008
NOTICE: 0.898449067492038
NOTICE: 0.190678883343935
NOTICE: 0.103653562255204
CALL
teledb=#
循环会通过计算expression 得到的数组的个体元素进行迭代。
teledb=# CREATE OR REPLACE PROCEDURE p_foreach_slice_1() AS
$$
DECLARE
v_random_arr float8[][]:=ARRAY[ARRAY[random(),random()],ARRAY[random(),random()]];
v_random float8[];
BEGIN
FOREACH v_random SLICE 1 IN ARRAY v_random_arr LOOP
RAISE NOTICE '%',v_random ;
END LOOP;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_foreach_slice_1();
NOTICE: {0.248282201588154,0.757913041394204}
NOTICE: {0.0194511725567281,0.43799454299733}
CALL
通过一个正SLICE 值，FOREACH 通过数组的切片而不是单一元素迭代。
