一、变量声明语法
name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];
如果给定 DEFAULT 子句，它会指定进入该块时分配给该变量的初始值。如果没有给出 DEFAULT子句， 则该变量被初始化为 SQL 空值。
CONSTANT 选项阻止该变量在初始化之后被赋值，这样它的值在块的持续期内保持不变。
COLLATE 选项指定用于该变量的一个排序规则。如果指定了 NOT NULL，对该变量赋值为空值会导致一个运行时错误。所有被声明为 NOT NULL 的变量必须被指定一个非空默认值。 等号（=）可以被用来代替 PL/SQL-兼容的 :=。
二、定义一个普通变量
postgres=# CREATE OR REPLACE PROCEDURE ordinary_var() AS$$DECLARE –所有变量的声明都要放在这里,建议变量以v_开头,参数以a_开头 v_int integer := 1; v_text text; BEGIN v_text = 'tdsql_pg'; RAISE NOTICE 'v_int = %',v_int; RAISE NOTICE 'v_text = %',v_text; END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL ordinary_var(); NOTICE: v_int = 1NOTICE: v_text = tdsql_pgCALLpostgres=#
三、定义 CONSTANT 变量
postgres=# CREATE OR REPLACE PROCEDURE p_constant() AS$$DECLARE v_int CONSTANT integer := 1; BEGIN RAISE NOTICE 'v_int = %',v_int; END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL p_constant();NOTICE: v_int = 1CALL
四、CONSTANT 不能再次赋值
postgres=# CREATE OR REPLACE PROCEDURE p_constant() AS$$DECLARE v_int CONSTANT integer := 1; BEGIN RAISE NOTICE 'v_int = %',v_int; v_int = 10;END;$$LANGUAGE plpgsql;ERROR: "v_int" is declared CONSTANTLINE 7: v_int = 10; ^postgres=#
五、定义 NOT NULL 变量
postgres=# CREATE OR REPLACE PROCEDURE p_not_null_var() AS$$DECLARE v_int integer NOT NULL := 1; BEGIN RAISE NOTICE 'v_int = %',v_int; SELECT NULL INTO v_int; RAISE NOTICE 'v_int = %',v_int; END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL p_not_null_var();NOTICE: v_int = 1ERROR: null value cannot be assigned to variable "v_int" declared NOT NULLCONTEXT: PL/pgSQL function p_not_null_var() line 6 at SQL statementpostgres=# 定义为NOT NULL变量，则该变量受NOT NULL约束
六、定义 COLLATE 变量
按 unicode 值对比大小。
postgres=# CREATE OR REPLACE PROCEDURE p_collate_unicode() AS$$DECLARE v_txt1 TEXT COLLATE "C" := '严'; v_txt2 TEXT COLLATE "C" := '丰'; BEGINIF v_txt1 > v_txt2 THEN RAISE NOTICE ' % > % ',v_txt1,v_txt2;ELSE RAISE NOTICE ' % > % ',v_txt2,v_txt1;END IF; END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL p_collate_unicode();NOTICE: 丰 > 严 CALLpostgres=#postgres=# select '严'::bytea; bytea ———- \\xe4b8a5(1 row)postgres=# select '丰'::bytea; bytea ———- \\xe4b8b0(1 row)
按汉字的拼音对比大小。
postgres=# CREATE OR REPLACE PROCEDURE p_collate_pinyin() AS$$DECLARE v_txt1 TEXT COLLATE "zh_CN.utf8" := '严'; v_txt2 TEXT COLLATE "zh_CN.utf8" := '丰'; BEGIN IF v_txt1 > v_txt2 THEN RAISE NOTICE ' % -> % ',v_txt1,v_txt2; ELSE RAISE NOTICE ' % -> % ',v_txt2,v_txt1; END IF; END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL p_collate_pinyin(); NOTICE: 严 -> 丰 CALLpostgres=#
七、变量赋值
postgres=# CREATE OR REPLACE PROCEDURE p_setval() AS$$DECLARE –定义时赋值 v_int1 integer = 1; –使用 :=兼容于plsql v_int2 integer := 1; v_txt1 text; v_float float8; –使用查询赋值 v_relname text = (select relname FROM pg_class LIMIT 1); v_relpages integer; v_rec RECORD;BEGIN –在函数体中赋值 v_txt1 = 'tdsql_pg'; v_float = random(); –使用查询赋值的另一种方式 SELECT relname,relpages INTO v_relname,v_relpages FROM pg_class ORDER BY random() LIMIT 1; RAISE NOTICE 'v_relname = % , relpages = %',v_relname,v_relpages;END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL p_setval();NOTICE: v_relname = pg_toast_17220_index , relpages = 1CALLpostgres=#
八、BULK COLLECT
BULK COLLECT 将一个查询结果集保存起来。
示例1
postgres=# create table t5(f1 integer,f2 varchar(10));CREATE TABLEpostgres=# insert into t5 values(1,'tdsql_pg1'); INSERT 0 1postgres=# insert into t5 values(2,'tdsql_pg2'); INSERT 0 1postgres=# create or replace procedure p_bulk_collect()AS $$declare TYPE t5list IS TABLE OF t5.f2%TYPE; t5s t5list;BEGIN SELECT f2 BULK COLLECT INTO t5s FROM t5; FOR i IN t5s.FIRST .. t5s.LAST LOOP raise notice '%',t5s[i]; END LOOP; END$$language plpgsql;NOTICE: type reference t5.f2%TYPE converted to character varyingCREATE PROCEDUREpostgres=# CALL p_bulk_collect();NOTICE: tdsql_pg1NOTICE: tdsql_pg2CALL
示例2
postgres=# create table tbl_person(id integer, name text, tdd int);CREATE TABLEpostgres=# insert into tbl_person values(1,'tdsql_pg',1);insert into tbl_person values(2,'pgxz',1);INSERT 0 1postgres=# insert into tbl_person values(2,'pgxz',1);INSERT 0 1postgres=# insert into tbl_person values(3,'pg',2);INSERT 0 1postgres=# create or replace procedure p_bulkcollect_select_into(a_tdd integer) AS $$declare type TAPersonlist is table of tbl_person%rowtype; vpa TAPersonlist; rp tbl_person%rowtype;begin select * bulk collect into vpa from tbl_person where tdd = a_tdd; raise notice 'count=%', vpa.count; for i in vpa.first..vpa.last loop rp = vpa[i]; raise notice 'loop=%', i; raise notice 'vname=%', rp.name; end loop; raise notice 'vpa.count=%',vpa.count;end;$$language plpgsql;CREATE PROCEDUREpostgres=# CALL p_bulkcollect_select_into(1);NOTICE: count=2NOTICE: loop=1NOTICE: vname=tdsql_pgNOTICE: loop=2NOTICE: vname=pgxzNOTICE: vpa.count=2CALLpostgres=#