TDSQL PostgreSQL版
有奖捉虫：行业应用 & 管理与支持文档专题 HOT
文档中心 > TDSQL PostgreSQL版 > 开发指南 > 存储过程开发 > 变量使用

变量使用

最近更新时间：2022-09-02 15:26:50

我的收藏

本页目录：

变量使用介绍

在一个块中使用的所有变量必须在该块的声明小节中事先进行声明，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;    
BEGIN
    v_text = 'tdsql_pg';
    RAISE NOTICE 'v_int = %',v_int;  
    RAISE NOTICE 'v_text = %',v_text;  
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
postgres=# CALL ordinary_var();                         
NOTICE:  v_int = 1
NOTICE:  v_text = tdsql_pg
CALL
postgres=#

定义 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 PROCEDURE
postgres=# CALL p_constant();
NOTICE:  v_int = 1
CALL

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 CONSTANT
LINE 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 PROCEDURE
postgres=# CALL p_not_null_var();
NOTICE:  v_int = 1
ERROR:  null value cannot be assigned to variable "v_int" declared NOT NULL
CONTEXT:  PL/pgSQL function p_not_null_var() line 6 at SQL statement
postgres=#  

定义为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" := '丰';     
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 PROCEDURE
postgres=# CALL p_collate_unicode();
NOTICE:   丰 > 严 
CALL
postgres=#

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 PROCEDURE
postgres=# CALL p_collate_pinyin();                         
NOTICE:   严 -> 丰 
CALL
postgres=#

变量赋值

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 PROCEDURE
postgres=# CALL p_setval();
NOTICE:  v_relname = pg_toast_17220_index , relpages = 1
CALL
postgres=#

BULK COLLECT

BULK COLLECT 将一个查询结果集保存起来。 示例1
postgres=# create table t5(f1 integer,f2 varchar(10));
CREATE TABLE
postgres=# insert into t5 values(1,'tdsql_pg1'); 
INSERT 0 1
postgres=# insert into t5 values(2,'tdsql_pg2'); 
INSERT 0 1

postgres=# 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 varying
CREATE PROCEDURE
postgres=# CALL p_bulk_collect();
NOTICE:  tdsql_pg1
NOTICE:  tdsql_pg2
CALL
示例2
postgres=# create table tbl_person(id integer, name text, tdd int);
CREATE TABLE
postgres=# insert into tbl_person values(1,'tdsql_pg',1);
insert into tbl_person values(2,'pgxz',1);
INSERT 0 1
postgres=# insert into tbl_person values(2,'pgxz',1);
INSERT 0 1
postgres=# insert into tbl_person values(3,'pg',2);
INSERT 0 1

postgres=# 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 PROCEDURE
postgres=# CALL p_bulkcollect_select_into(1);
NOTICE:  count=2
NOTICE:  loop=1
NOTICE:  vname=tdsql_pg
NOTICE:  loop=2
NOTICE:  vname=pgxz
NOTICE:  vpa.count=2
CALL
postgres=#

中国站