【Postgres】11、PROCEDURE 存储过程、FUNCTION 函数、使用方式和区别 |
您所在的位置：网站首页 › pgsql创建存储过程 › 【Postgres】11、PROCEDURE 存储过程、FUNCTION 函数、使用方式和区别 |
【Postgres】11、PROCEDURE 存储过程、FUNCTION 函数、使用方式和区别
|
在PostgreSQL中，存储过程（Procedure）和函数（Function）是两种可执行的数据库对象，它们之间有一些区别。以下是它们的主要区别： 返回值：函数具有返回值，而存储过程可以没有返回值或返回多个结果集。调用方式：函数可以像普通的SQL表达式一样调用，可以在查询中直接使用函数的返回值。而存储过程需要使用CALL语句来调用，并且无法将其嵌入到查询中。事务控制：函数在调用时会自动启动一个事务，并且可以使用COMMIT或ROLLBACK语句来控制事务的提交或回滚。存储过程可以在其中包含多个SQL语句，但不会自动启动事务，需要手动控制事务的开始和结束。参数传递：函数和存储过程都可以接收参数，但在函数中，参数可以通过IN、OUT和INOUT模式来指定，而存储过程中的参数只能通过IN模式传递。使用场景：函数通常用于执行一系列计算或操作，并返回结果。存储过程通常用于执行一系列的数据库操作，例如数据加载、数据清理、数据迁移等。存储过程通常用于封装复杂的业务逻辑，并且可以被其他应用程序或过程调用。虽然存储过程和函数在某些方面有区别，但它们也有一些共同点，例如都可以使用PL/pgSQL等编程语言编写，都可以访问数据库对象和执行SQL语句。选择使用存储过程还是函数取决于具体的需求和使用场景。 一、PROCEDUREhttps://www.postgresql.org/docs/current/sql-createprocedure.html 1.1 语法 CREATE [ OR REPLACE ] PROCEDURE name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] ) { LANGUAGE lang_name | TRANSFORM { FOR TYPE type_name } [, ... ] | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | SET configuration_parameter { TO value | = value | FROM CURRENT } | AS 'definition' | AS 'obj_file', 'link_symbol' | sql_body } ... 1.2 描述可以指定在 某 schema 下，创建 procedure，否则在当前 schema 下创建。 procedure 和 function，如果有不同的参数，是可以重名的（即 overloading）。 用 CREATE OR REPLACE PROCEDURE 可以更改 body。但正因为 overloading，所以不能通过 CREATE OR REPLACE PROCEDURE 来更改 name 或 arguments。（如果尝试的话，会 create 一个新的，不同的 procedure）。 必须有 USAGE 权限的用户才能创建 PROCEDURE。 使用 CREATE OR REPLACE PROCEDURE 时，ownership 和 permission 不会变化。 1.3 参数name：名称 argmode：IN、OUT、INOUT、VARIADIC，默认是 IN argname：参数名称 argtype：是存储过程参数的数据类型。可以是基本类型、复合类型、域类型，或者引用表列的类型。参数类型可以选择模式限定，如果需要的话。根据实现的编程语言，也可以指定“伪类型”，例如cstring。伪类型表示实际参数类型不完全指定，或者不属于普通SQL数据类型集合之内。 通过写入table_name.column_name%TYPE，可以引用列的类型。使用这个特性有时可以帮助使存储过程独立于对表定义的更改。 default_expr: 如果未指定参数，则用作默认值的表达式。该表达式必须对参数的实参类型是强制的。带有缺省值的参数后面的所有输入参数也必须具有缺省值。 lang_name: 实现过程所使用的语言的名称。它可以是SQL、C、INTERNAL或用户定义的过程语言的名称，例如plpgsql。如果指定了SQL_BODY，则默认为SQL。不建议使用单引号将名称括起来，并且需要大小写匹配。 TRANSFORM { FOR TYPE type_name } [, … ] } 列出应应用哪些转换对过程的调用。转换在SQL类型和语言特定的数据类型之间进行转换；请参阅创建转换。过程语言实现通常具有内置类型的硬编码知识，因此不需要在这里列出这些知识。如果过程语言实现不知道如何处理类型，并且没有提供转换，则它将退回到转换数据类型的默认行为，但这取决于实现。 1.4 示例 CREATE PROCEDURE insert_data(a integer, b integer) LANGUAGE SQL AS $$ INSERT INTO tbl VALUES (a); INSERT INTO tbl VALUES (b); $$; CREATE PROCEDURE insert_data(a integer, b integer) LANGUAGE SQL BEGIN ATOMIC INSERT INTO tbl VALUES (a); INSERT INTO tbl VALUES (b); END;调用方式为 CALL insert_data(1, 2); 二、FUNCTIONhttp://postgres.cn/docs/14/sql-createfunction.html 2.1 语法 CREATE [ OR REPLACE ] FUNCTION name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] ) [ RETURNS rettype | RETURNS TABLE ( column_name column_type [, ...] ) ] { LANGUAGE lang_name | TRANSFORM { FOR TYPE type_name } [, ... ] | WINDOW | { IMMUTABLE | STABLE | VOLATILE } | [ NOT ] LEAKPROOF | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT } | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER } | PARALLEL { UNSAFE | RESTRICTED | SAFE } | COST execution_cost | ROWS result_rows | SUPPORT support_function | SET configuration_parameter { TO value | = value | FROM CURRENT } | AS 'definition' | AS 'obj_file', 'link_symbol' | sql_body } ...CREATE FUNCTION定义一个新函数。CREATE OR REPLACE FUNCTION将创建一个新函数或者替换一个现有的函数。要定义一个函数，用户必须具有该语言上的USAGE特权。 如果包括了一个模式名，那么该函数会被创建在指定的模式中。否则，它会被创建在当前模式中。新函数的名称不能匹配同一个模式中具有相同输入参数类型的任何现有函数或过程。不过，不同参数类型的函数和过程能够共享一个名字（这被称作重载）。 要替换一个现有函数的当前定义，可以使用CREATE OR REPLACE FUNCTION。但不能用这种方式更改函数的名称或者参数类型（如果尝试这样做，实际上就会创建一个新的不同的函数）。还有，CREATE OR REPLACE FUNCTION将不会让你更改一个现有函数的返回类型。要这样做，你必须先删除再重建该函数（在使用OUT参数时，这意味着除了删除函数之外无法更改任何OUT参数的类型）。 当CREATE OR REPLACE FUNCTION被用来替换一个现有的函数，该函数的拥有权和权限不会改变。所有其他的函数属性会按照该命令中所指定的或者隐含的来赋值。必须拥有（包括成为拥有角色的成员）该函数才能替换它。 如果你删除并且重建一个函数，新函数将和旧的不一样，你将必须删掉引用旧函数的现有规则、视图、触发器等。使用CREATE OR REPLACE FUNCTION更改一个函数定义不会破坏引用该函数的对象。还有，ALTER FUNCTION可以被用来更改一个现有函数的大部分辅助属性。 创建该函数的用户将成为该函数的拥有者。 要创建一个函数，你必须拥有参数类型和返回类型上的USAGE特权。 2.2 重载PostgreSQL允许函数重载，也就是说同一个名称可以被用于多个不同的函数，只要它们具有可区分的输入参数类型。不管是否使用它，在有些用户不信任另一些用户的数据库中调用函数时，这种兼容性需要安全性的预防措施，请参考第 10.3 节。 如果两个函数具有相同的名称和***输入***参数类型，它们被认为相同（不考虑任何OUT参数）。因此这些声明会冲突： CREATE FUNCTION foo(int) ... CREATE FUNCTION foo(int, out text) ...具有不同参数类型列表的函数在创建时将不会被认为是冲突的，但是如果默认值被提供，在使用时它们有可能会冲突。例如，考虑 CREATE FUNCTION foo(int) ... CREATE FUNCTION foo(int, int default 42) ...调用foo(10)将会失败，因为在要决定应该调用哪个函数时会有歧义。 2.3 示例使用SQL函数对两个整数相加： CREATE FUNCTION add(integer, integer) RETURNS integer AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT;同一个函数以更符合SQL习惯的样式编写，使用参数名称和未加引号的函数体，如下： CREATE FUNCTION add(a integer, b integer) RETURNS integer LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT RETURN a + b;在PL/pgSQL中，使用一个参数名称增加一个整数： CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$ BEGIN RETURN i + 1; END; $$ LANGUAGE plpgsql;返回一个包含多个输出参数的记录： CREATE FUNCTION dup(in int, out f1 int, out f2 text) AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$ LANGUAGE SQL; SELECT * FROM dup(42); -- code result: postgres=# SELECT * FROM dup(42); (42,"42 is text")你可以用更复杂的方式（用一个显式命名的组合类型）来做同样的事情： CREATE TYPE dup_result AS (f1 int, f2 text); CREATE FUNCTION dup(int) RETURNS dup_result AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$ LANGUAGE SQL; SELECT * FROM dup(42); -- code result: postgres=# SELECT * FROM dup(42); f1 | f2 ----+------------ 42 | 42 is text另一种返回多列的方法是使用一个TABLE函数： CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text) AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$ LANGUAGE SQL; SELECT * FROM dup(42); -- code result: postgres=# SELECT * FROM dup(42); f1 | f2 ----+------------ 42 | 42 is text不过，TABLE 函数与之前的例子不同，因为它实际返回了一个记录集合而不只是一个记录。 2.4 兼容性SQL标准中定义了CREATE FUNCTION命令。 PostgreSQL实现可以以兼容的方式使用，但有许多扩展。 相反，SQL标准指定了许多未在PostgreSQL中实现的可选功能。 以下是重要的兼容性问题： OR REPLACE是PostgreSQL的扩展。为了与其他一些数据库系统兼容，argmode 可以在*argname*之前或之后写入。 但只有第一种方式符合标准。对于参数默认值，SQL标准仅指定带有DEFAULT关键字的语法。 =的语法是在T-SQL和Firebird中被使用的。SETOF修饰符是PostgreSQL的扩展。只有SQL是被标准化的一个语言。除了 CALLED ON NULL INPUT和 RETURNS NULL ON NULL INPUT以外的所有其他属性都没有标准化。对于LANGUAGE SQL函数的主体，SQL标准只指定了 *SQL_body*形式。简单的LANGUAGE SQL函数可以以既符合标准又可移植到其他实现的方式编写。 使用高级特性、优化属性或其他语言的更复杂的函数必然在很大程度上特定于PostgreSQL。 2.5 示例 2.5.1 declare variable 定义变量 DECLARE variable_name [ CONSTANT ] data_type [ { DEFAULT | := } initial_value ];其中： DECLARE：用于声明一个变量。 variable_name：变量的名称。 [ CONSTANT ]：可选项，用于指定变量是否为常量。 data_type：变量的数据类型，可以是基本数据类型、复合类型或自定义类型。 [ DEFAULT | := ]：可选项，用于指定变量的初始值。DEFAULT关键字和:=赋值符号都可以用于指定初始值。 initial_value：变量的初始值。 以下是一个示例，演示在PostgreSQL函数中声明和使用变量的过程： CREATE OR REPLACE FUNCTION calculate_sum(a INT, b INT) RETURNS INT AS $$ DECLARE result INT; BEGIN result := a + b; RETURN result; END; $$ LANGUAGE plpgsql;在上述示例中，函数calculate_sum声明了一个名为result的整数变量，并将a和b的和赋值给该变量。最后，函数返回了变量result的值。 2.5.2 declare、ARRAY、ANY -- 建表 CREATE TABLE a(id TEXT, ts BIGINT); INSERT INTO a VALUES ('a', 1), ('b', 2), ('c', 3), ('d', 4), ('e', 5); postgres=# SELECT * FROM a; id | ts ----+---- a | 1 b | 2 c | 3 d | 4 e | 5 (5 rows) -- 建FUNCTION CREATE OR REPLACE FUNCTION f(id_arr TEXT[], src_start_ts BIGINT, src_end_ts BIGINT, tgt_start_ts BIGINT) RETURNS VOID AS $$ DECLARE offset_ms BIGINT; BEGIN offset_ms := tgt_start_ts - src_start_ts; -- 偏移多少毫秒 DROP TABLE IF EXISTS t; -- 临时表 CREATE TABLE t AS SELECT * FROM a WHERE id = ANY(id_arr) AND ts >= src_start_ts AND ts |
【本文地址】
今日新闻 |
推荐新闻 |