控制结构

最近更新时间：2022-09-02 15:27:14
我的收藏
本页目录：

判断语句
IF...THEN...END IF
postgres=# CREATE OR REPLACE PROCEDURE p_if() AS
$$    
BEGIN           
    IF random()>0.5 THEN
        RAISE NOTICE '随机数大于0.5';
    END IF;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
postgres=# CALL p_if();
NOTICE:  随机数大于0.5
CALL
postgres=#
IF...THEN...ELSE...END IF
postgres=# CREATE OR REPLACE PROCEDURE p_if_else() AS
$$    
BEGIN           
    IF random()>0.99 THEN
        RAISE NOTICE '随机数大于0.99';
    ELSE
        RAISE NOTICE '随机数小于或等于0.99';
    END IF;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
postgres=# CALL p_if_else();
NOTICE:  随机数小于或等于0.99
CALL
postgres=#
IF...THEN...ELSIF...THEN...ELSE...END IF
postgres=# CREATE OR REPLACE PROCEDURE p_if_elsif()  AS
$$    
DECLARE
    v_float8 float8 := random();
BEGIN           
    IF v_float8>0.99 THEN
        RAISE NOTICE '随机数大于0.99';
    ELSIF v_float8>0.5 THEN
        RAISE NOTICE '随机数大于0.50';   
    ELSIF v_float8>0.25 THEN
        RAISE NOTICE '随机数大于0.25';       
    ELSE
        RAISE NOTICE '随机数小于或等于0.25';
    END IF;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
postgres=# CALL p_if_elsif();
NOTICE:  随机数大于0.50
CALL
CASE 语句
postgres=# CREATE OR REPLACE PROCEDURE p_case()  AS
$$    
DECLARE
    v_float8 float8 := random();
BEGIN           
    CASE 
    WHEN v_float8>0.99 THEN
        RAISE NOTICE '随机数大于0.99';
    WHEN v_float8>0.5 THEN
        RAISE NOTICE '随机数大于0.50';   
    WHEN v_float8>0.25 THEN
        RAISE NOTICE '随机数大于0.25';       
    ELSE
        RAISE NOTICE '随机数小于或等于0.25';
    END CASE;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
postgres=# CALL p_case();
NOTICE:  随机数小于或等于0.25
CALL
postgres=#
循环语句
LOOP 循环
postgres=# 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
﻿
postgres=# CALL p_loop();
NOTICE:  1
NOTICE:  2
NOTICE:  3
CALL
postgres=#
WHILE 循环
postgres=# 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
postgres=# CALL p_while();
NOTICE:  1
CALL
FOR 循环
postgres=# 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
postgres=# CALL p_for();
NOTICE:  i = 1
NOTICE:  i = 2
NOTICE:  i = 3
CALL
﻿
postgres=# 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
postgres=# CALL p_for_reverse();
NOTICE:  i = 3
NOTICE:  i = 2
NOTICE:  i = 1
CALL
使用 REVERSE 递减。
postgres=# 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
postgres=# CALL p_for_by();
NOTICE:  i = 1
NOTICE:  i = 3
NOTICE:  i = 5
NOTICE:  i = 7
CALL
postgres=#
使用 BY 设置步长。
FOR 循环查询结果
postgres=# 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
postgres=# CALL p_for_record();
NOTICE:  (pg_stat_statements,v)
NOTICE:  (pg_proc,v)
CALL
postgres=#
FOREACH 循环一个数组
postgres=# 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
postgres=# CALL p_foreach();
NOTICE:  0.744417542591691
NOTICE:  0.804096563253552
CALL
postgres=#
﻿
postgres=# 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
postgres=# CALL p_foreach_slice();
NOTICE:  0.0220407997258008
NOTICE:  0.898449067492038
NOTICE:  0.190678883343935
NOTICE:  0.103653562255204
CALL
postgres=#
循环会通过计算 expression 得到的数组的个体元素进行迭代。
postgres=# 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
postgres=# CALL p_foreach_slice_1();
NOTICE:  {0.248282201588154,0.757913041394204}
NOTICE:  {0.0194511725567281,0.43799454299733}
CALL
通过一个正 SLICE 值，FOREACH 通过数组的切片而不是单一元素迭代。
其它控制语句
动态执行
postgres=# CREATE OR REPLACE PROCEDURE p_execute() AS
$$    
DECLARE
    v_sql TEXT;
    v_relname TEXT;
BEGIN          
    v_sql := 'SELECT relname FROM pg_class limit 1';
    EXECUTE v_sql INTO v_relname;     
    RAISE NOTICE 'relname = %',v_relname;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
postgres=# CALL p_execute();
NOTICE:  relname = pg_stat_statements
CALL
postgres=#
也可以使用 immediate。
postgres=# CREATE OR REPLACE PROCEDURE p_execute() AS
$$    
DECLARE
    v_sql TEXT;
    v_relname TEXT;
BEGIN          
    v_sql := 'SELECT relname FROM pg_class limit 1';
    EXECUTE immediate v_sql INTO v_relname;     
    RAISE NOTICE 'relname = %',v_relname;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
postgres=# CALL p_execute();
NOTICE:  relname = s1
CALL
postgres=#
动态执行就是拼 SQL 语句，然后使用 EXECUTE 命令执行。
执行一个没有结果的命令
postgres=# CREATE OR REPLACE PROCEDURE p_perform()  AS
$$   
BEGIN          
    perform md5(random()::text);
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
postgres=# call p_perform();
CALL
postgres=#  
获取执行结果
postgres=# CREATE OR REPLACE PROCEDURE p_found()  AS
$$  
DECLARE
    v_relname TEXT; 
BEGIN          
    SELECT relname INTO v_relname FROM pg_class limit 1;
    IF FOUND THEN
        RAISE NOTICE '查询到记录，值为%',v_relname;        
    ELSE
        RAISE NOTICE '查不到记录' ;
    END IF;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
postgres=# CALL p_found();
NOTICE:  查询到记录，值为pg_stat_statements
CALL
获取影响行数
postgres=# CREATE OR REPLACE PROCEDURE p_row_count()  AS
$$  
DECLARE    
    v_row_count BIGINT;
BEGIN          
    delete from t1;
    GET DIAGNOSTICS v_row_count = ROW_COUNT;
    RAISE NOTICE '查询到的记录数为 % ',v_row_count;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
postgres=# call p_row_count();
NOTICE:  查询到的记录数为 3 
CALL
postgres=#
GOTO
postgres=# create or replace procedure p_goto(v_maxnum integer) as
$$
declare
    maxnum integer;
begin
    maxnum := v_maxnum;
for i in 1..maxnum loop
    if i=3 then
        goto label;
    end if;
    raise notice 'i=%',i;
end loop;
<<label>>
    raise notice 'goto end';
end;
$$
language plpgsql;
CREATE PROCEDURE
postgres=# call p_goto(5);
NOTICE:  i=1
NOTICE:  i=2
NOTICE:  goto end
CALL
postgres=#
go 用于跳转到某个标签下。
俘获错误
错误俘获处理
postgres=# CREATE OR REPLACE PROCEDURE p_exception(a_id integer,a_nc text) AS
$$    
BEGIN           
    INSERT INTO t_exception VALUES(a_id,a_nc);
    RETURN ;
    EXCEPTION WHEN OTHERS THEN     
    RAISE NOTICE '执行出错';   
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
postgres=#
﻿
postgres=# CALL p_exception(1,'Tdsql_pg');
CALL
postgres=# CALL p_exception(1,'Tdsql_pg');
NOTICE:  执行出错
CALL
获取错误相关信息
postgres=# CREATE OR REPLACE PROCEDURE p_exception_error(a_id integer,a_nc text) AS
$$    
DECLARE    
    v_sqlstate text;
    v_context text;
    v_message_text text;
BEGIN           
    INSERT INTO t_exception VALUES(a_id,a_nc);
    RETURN ;
    EXCEPTION WHEN OTHERS THEN 
    GET STACKED DIAGNOSTICS v_sqlstate = RETURNED_SQLSTATE,
                                 v_message_text = MESSAGE_TEXT,
                                 v_context = PG_EXCEPTION_CONTEXT;     
    RAISE NOTICE '错误代码 : %',v_sqlstate;
    RAISE NOTICE '出错信息 : %',v_message_text;
    RAISE NOTICE '发生异常语句 : %',v_context; 
    raise notice '错误代码 : % \\n出错信息 : % 发生异常语句 : %',v_sqlstate ,v_message_text,v_context;   
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
postgres=# CALL p_exception_error(2,'Tdsql_pg');
CALL
postgres=# CALL p_exception_error(2,'Tdsql_pg');
NOTICE:  错误代码 : 23505
NOTICE:  出错信息 : node:dn001, backend_pid:16204, nodename:dn001,backend_pid:16204,message:duplicate key value violates unique constraint "t_exception_id_uidx"
NOTICE:  发生异常语句 : SQL statement "INSERT INTO t_exception VALUES(a_id,a_nc)"
PL/pgSQL function p_exception_error(integer,text) line 7 at SQL statement
NOTICE:  错误代码 : 23505 \\n出错信息 : node:dn001, backend_pid:16204, nodename:dn001,backend_pid:16204,message:duplicate key value violates unique constraint "t_exception_id_uidx" 发生异常语句 : SQL statement "INSERT INTO t_exception VALUES(a_id,a_nc)"
PL/pgSQL function p_exception_error(integer,text) line 7 at SQL statement
CALL
postgres=#
﻿
上一篇: 变量使用下一篇: Sysbench 测试说明