变量使用介绍
在一个块中使用的所有变量必须在该块的声明小节中事先进行声明,PL/pgSQL 变量可以是任意 SQL 数据类型,可以是一个简单数据类型、复合类型、RECORD、已经存在的表行类型、表字段类型、游标。
变量使用实例
变量声明语法
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;BEGINv_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$$DECLAREv_int CONSTANT integer := 1;BEGINRAISE 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$$DECLAREv_int CONSTANT integer := 1;BEGINRAISE 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$$DECLAREv_int integer NOT NULL := 1;BEGINRAISE 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$$DECLAREv_txt1 TEXT COLLATE "C" := '严';v_txt2 TEXT COLLATE "C" := '丰';BEGINIF v_txt1 > v_txt2 THENRAISE NOTICE ' % > % ',v_txt1,v_txt2;ELSERAISE 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$$DECLAREv_txt1 TEXT COLLATE "zh_CN.utf8" := '严';v_txt2 TEXT COLLATE "zh_CN.utf8" := '丰';BEGINIF v_txt1 > v_txt2 THENRAISE NOTICE ' % -> % ',v_txt1,v_txt2;ELSERAISE 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;--使用 :=兼容于plsqlv_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$$declareTYPE t5list IS TABLE OF t5.f2%TYPE;t5s t5list;BEGINSELECT f2 BULK COLLECT INTO t5s FROM t5;FOR i IN t5s.FIRST .. t5s.LASTLOOPraise 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$$declaretype TAPersonlist is table of tbl_person%rowtype;vpa TAPersonlist;rp tbl_person%rowtype;beginselect * bulk collect into vpa from tbl_person where tdd = a_tdd;raise notice 'count=%', vpa.count;for i in vpa.first..vpa.last looprp = 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=#