循環語句
更新(xin)時間 2025-02-05 09:36:49
最近更新時間: 2025-02-05 09:36:49
分(fen)享文章
本頁介(jie)紹天翼(yi)云TeleDB數據庫(ku)存(cun)儲過(guo)程開發的循環語句。
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 設置步長(chang)。
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=#
循環會(hui)通過計算expression 得到的數組(zu)的個體元素進(jin)行迭代。
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 通過數(shu)組的切片而不是單一元素迭代。
