參數數據類型
更新時間 2025-02-14 10:22:07
最近更新時間: 2025-02-14 10:22:07
分享文章
本頁介紹天翼云TeleDB數據庫存儲過程開發的參數數據類型。
數據類型(可以有模式修飾)，可以是基本類型，復合類型、域類型、游標、或者可以引用一個現有表類型、字段類型(建立時轉換為對應的類型)、還可以是多態類型 anyelement、anyarray，也可以是各種數據類型的數組形式。
基本類型
teledb=# CREATE OR REPLACE PROCEDURE p_base_para (a_int integer,a_str text) AS
$$
BEGIN
RAISE NOTICE 'a_int = % ; a_str = %',a_int,a_str;
END;
$$
LANGUAGE PLPGSQL;
CREATE PROCEDURE
teledb=#
CALL p_base_para(1,'teledb_pg');
NOTICE: a_int = 1 ; a_str = teledb_pg
CALL
teledb=#
teledb=# CREATE OR REPLACE PROCEDURE p_base_array (a_int integer[],a_str text[]) AS
$$
BEGIN
RAISE NOTICE 'a_int = % ; a_str = %',a_int,a_str;
END;
$$
LANGUAGE PLPGSQL;
CREATE PROCEDURE
teledb=# CALL p_base_array(ARRAY[1,2,3],ARRAY['teledb_pg','pgxz']);
NOTICE: a_int = {1,2,3} ; a_str = {teledb_pg,pgxz}
CALL
teledb=#復合類型
teledb=# CREATE TYPE public.t_per AS
(
id integer,
mc text
);
CREATE TYPE
teledb=# CREATE OR REPLACE PROCEDURE p_type (a_row public.t_per) AS $$
BEGIN
RAISE NOTICE 'id = % ; mc = %',a_row.id,a_row.mc;
END;
$$
LANGUAGE PLPGSQL;
CREATE PROCEDURE
teledb=# CALL p_type(ROW(1,'teledb_pg')::public.t_per);
NOTICE: id = 1 ; mc = teledb_pg
CALL
teledb=#復合數組
teledb=# CREATE OR REPLACE PROCEDURE p_type_array (a_rec public.t_per[]) AS
$$
BEGIN
RAISE NOTICE 'a_rec = %',a_rec;
RAISE NOTICE 'a_rec[1].id = %',a_rec[1].id;
END;
$$
LANGUAGE PLPGSQL;
CREATE PROCEDURE
teledb=# CALL p_type_array (ARRAY[ROW(1,'teledb_pg'),ROW(1,'pgxz')]::public.t_per[]);
NOTICE: a_rec = {"(1,teledb_pg)","(1,pgxz)"}
NOTICE: a_rec[1].id = 1
CALL
teledb=#行類型
teledb=# create table public.t(id int,mc text);
CREATE TABLE
teledb=#
teledb=# CREATE OR REPLACE PROCEDURE p_row (a_row public.t) AS
$$
BEGIN
RAISE NOTICE 'id = % ; mc = %',a_row.id,a_row.mc;
END;
$$
LANGUAGE PLPGSQL;
CREATE PROCEDURE
teledb=#CALL p_row(ROW(1,'teledb_pg'));
NOTICE: id = 1 ; mc = teledb_pg
CALL
teledb=#行數組
teledb=# CREATE OR REPLACE PROCEDURE p_row_array (a_rec public.t[]) AS
$$
BEGIN
RAISE NOTICE 'a_rec = %',a_rec;
RAISE NOTICE 'a_rec[1].id = %',a_rec[1].id;
END;
$$
LANGUAGE PLPGSQL;
CREATE PROCEDURE
teledb=# CALL p_row_array(array[row(1,'teledb_pg'),row(1,'pgxz')]::public.t[]);
NOTICE: a_rec = {"(1,teledb_pg)","(1,pgxz)"}
NOTICE: a_rec[1].id = 1
CALL
teledb=#游標類型
teledb=# CREATE OR REPLACE PROCEDURE p_refcursor (a_ref refcursor) AS
$$
DECLARE v_rec record;
BEGIN
OPEN a_ref FOR SELECT * FROM t LIMIT 1;
FETCH a_ref INTO v_rec;
RAISE NOTICE 'v_rec = % ',v_rec;
END;
$$
LANGUAGE PLPGSQL;
CREATE PROCEDURE
teledb=# CALL p_refcursor('a');
NOTICE: v_rec = (1,teledb_pg)
CALL
teledb=#
teledb=# CREATE OR REPLACE PROCEDURE f_any(a_arg anyelement) AS
$$
BEGIN
RAISE NOTICE '%',a_arg;
END;
$$
LANGUAGE PLPGSQL;
CREATE PROCEDURE
teledb=# CALL f_any(1);
NOTICE: 1
CALL
teledb=# CALL f_any('teledb_pg'::varchar);
NOTICE: teledb_pg
CALL
teledb=#
teledb=# CALL f_any('teledb_pg'::TEXT);
NOTICE: teledb_pg
f_any
-------
(1 行記錄)
teledb=# CALL f_any(ROW(1,'teledb_pg')::public.t);
NOTICE: (1,teledb_pg)
CALL
teledb=#
teledb=# CALL f_any(ARRAY[1,2]::INTEGER[]);
NOTICE: {1,2}
CALL
teledb=#
teledb=# CALL f_any(ARRAY[[1,2],[3,4],[5,6]]::INTEGER[][][]);
NOTICE: {{1,2},{3,4},{5,6}}
CALL
teledb=#
注意多態類型參數調用時最好直接聲明參數類型，否則有可能出錯。
teledb=# CREATE OR REPLACE PROCEDURE f_any(a_arg anyarray) AS
$$
BEGIN
RAISE NOTICE '%',a_arg;
END;
$$
LANGUAGE PLPGSQL;
CREATE PROCEDURE
teledb=# call f_any(ARRAY['teledb_pg','pgxz']::TEXT[]);
ERROR: procedure f_any(text[]) is not unique
LINE 1: call f_any(ARRAY['teledb_pg','pgxz']::TEXT[]);
^
HINT: Could not choose a best candidate procedure. You might need to add explicit type casts.
teledb=#teledb=# CREATE OR REPLACE PROCEDURE f_any(a_arg anyarray) AS
$$
BEGIN
RAISE NOTICE '%',a_arg;
END;
$$
LANGUAGE PLPGSQL;
CREATE PROCEDURE
teledb=# call f_any(ARRAY['teledb_pg','pgxz']::TEXT[]);
ERROR: procedure f_any(text[]) is not unique
LINE 1: call f_any(ARRAY['teledb_pg','pgxz']::TEXT[]);
^
HINT: Could not choose a best candidate procedure. You might need to add explicit type casts.
teledb=#注意
Anyelement 參數如果寫成數組，其意義就跟 anyarray 參數一致，所以 f_any(a_arg anyelement) 與 f_any(a_arg anyarray) 在調用 f_any(ARRAY[1,2]) 時就會出現函數不是唯一化的錯誤 (ERROR: function f_any(…) is not unique) 提示。
參數默認值
teledb=# CREATE OR REPLACE PROCEDURE p_default_value (a_int INTEGER DEFAULT 1) AS
$$
BEGIN
RAISE NOTICE 'a_int = %',a_int;
END;
$$
LANGUAGE PLPGSQL;
CREATE PROCEDURE
teledb=# CALL p_default_value(2);
NOTICE: a_int = 2
CALL
teledb=# CALL p_default_value();
NOTICE: a_int = 1
CALL
teledb=#如果原來存在一個p_default_value () 這樣的存儲過程，則上面的執行就會出錯，因為系統無法清楚到底要執行哪個函數，如下所示。
teledb=# CREATE OR REPLACE PROCEDURE p_default_value() AS
$$
BEGIN
RAISE NOTICE '無參數';
END;
$$
LANGUAGE plpgsql ;
CREATE PROCEDURE
teledb=# CALL p_default_value();
ERROR: procedure p_default_value() is not unique
LINE 1: CALL p_default_value();
^
HINT: Could not choose a best candidate procedure. You might need to add explicit type casts.
teledb=#出錯提示，p_default_value () 存儲過程不是唯一的，這是使用上一個需要特別注意的地方。
