變量使用實例
更新時間 2025-02-14 10:22:15
最近更新時間: 2025-02-14 10:22:15
分享文章
本頁介紹天翼云TeleDB數據庫存儲過程開發的變量使用實例。
變量聲明語法
name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];如果給定DEFAULT子句，它會指定進入該塊時分配給該變量的初始值。如果沒有給出 DEFAULT子句，則該變量被初始化為SQL空值。
CONSTANT選項阻止該變量在初始化之后被賦值，這樣它的值在塊的持續期內保持不變。
COLLATE選項指定用于該變量的一個排序規則。如果指定了NOT NULL，對該變量賦值為空值會導致一個運行時錯誤。所有被聲明為NOT NULL的變量必須被指定一個非空默認值。等號（=）可以被用來代替 PL/SQL-兼容的:=。
定義一個普通變量
teledb=# CREATE OR REPLACE PROCEDURE ordinary_var() AS
$$
DECLARE
--所有變量的聲明都要放在這里,建議變量以v_開頭,參數以a_開頭
v_int integer := 1;
v_text text;
BEGIN
v_text = 'teledb_pg';
RAISE NOTICE 'v_int = %',v_int;
RAISE NOTICE 'v_text = %',v_text;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL ordinary_var();
NOTICE: v_int = 1
NOTICE: v_text = teledb_pg
CALL
teledb=#定義CONSTANT 變量
teledb=# 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
teledb=# CALL p_constant();
NOTICE: v_int = 1
CALLCONSTANT 不能再次賦值
teledb=# 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;
^
teledb=#定義NOT NULL變量
teledb=# 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
teledb=# 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
teledb=#
定義為NOT NULL變量，則該變量受NOT NULL約束定義COLLATE 變量
按unicode 值對比大小。
teledb=# 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
teledb=# CALL p_collate_unicode();
NOTICE: 豐 > 嚴
CALL
teledb=#
teledb=# select '嚴'::bytea;
bytea
----------
\xe4b8a5
(1 row)
teledb=# select '豐'::bytea;
bytea
----------
\xe4b8b0
(1 row)按漢字的拼音對比大小。
teledb=# 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
teledb=# CALL p_collate_pinyin();
NOTICE: 嚴 -> 豐
CALL
teledb=#變量賦值
teledb=# 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 = 'teledb_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
teledb=# CALL p_setval();
NOTICE: v_relname = pg_toast_17220_index , relpages = 1
CALL
teledb=#BULK COLLECT
BULK COLLECT 將一個查詢結果集保存起來。
示例1
teledb=# create table t5(f1 integer,f2 varchar(10));
CREATE TABLE
teledb=# insert into t5 values(1,'teledb_pg1');
INSERT 0 1
teledb=# insert into t5 values(2,'teledb_pg2');
INSERT 0 1
teledb=# 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
teledb=# CALL p_bulk_collect();
NOTICE: teledb_pg1
NOTICE: teledb_pg2
CALL示例2
teledb=# create table tbl_person(id integer, name text, tdd int);
CREATE TABLE
teledb=# insert into tbl_person values(1,'teledb_pg',1);
insert into tbl_person values(2,'pgxz',1);
INSERT 0 1
teledb=# insert into tbl_person values(2,'pgxz',1);
INSERT 0 1
teledb=# insert into tbl_person values(3,'pg',2);
INSERT 0 1
teledb=# 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
teledb=# CALL p_bulkcollect_select_into(1);
NOTICE: count=2
NOTICE: loop=1
NOTICE: vname=teledb_pg
NOTICE: loop=2
NOTICE: vname=pgxz
NOTICE: vpa.count=2
CALL
teledb=#
