PostgreSQL存储过程源码分析

这篇文章主要介绍了postgresql存储过程源码分析的相关知识，内容详细易懂，操作简单快捷，具有一定借鉴价值，相信大家阅读完这篇Postgresql存储过程源码分析文章都会有所收获，下面我们一起来看看吧。

游标

PL/pgSQL 游标允许我们封装一个查询，然后每次处理结果集中的一条记录。游标可以将大结果集拆分成许多小的记录，避免内存溢出；另外，我们可以定义一个返回游标引用的函数，然后调用程序可以基于这个引用处理返回的结果集。

使用游标的步骤大体如下：

• 声明游标变量；

• 打开游标；

• 从游标中获取结果；

• 判断是否存在更多结果。如果存在，执行第 3 步；否则，执行第 5 步；

• 关闭游标。

我们直接通过一个示例演示使用游标的过程：

DO $$DECLARE   rec_emp RECORD;  cur_emp CURSOR(p_deptid INTEGER) FOR    SELECT first_name, last_name, hire_date     FROM employees    WHERE department_id = p_deptid;BEGIN  -- 打开游标  OPEN cur_emp(60);  LOOP    -- 获取游标中的记录    FETCH cur_emp INTO rec_emp;    -- 没有找到更多数据时退出循环    EXIT WHEN NOT FOUND;    RaiSE NOTICE '%,% hired at:%' , rec_emp.first_name, rec_emp.last_name, rec_emp.hire_date;  END LOOP;  -- Close the cursor  CLOSE cur_emp;END $$;NOTICE:  Alexander,Hunold hired at:2006-01-03NOTICE:  Bruce,Ernst hired at:2007-05-21NOTICE:  David,Austin hired at:2005-06-25NOTICE:  Valli,Pataballa hired at:2006-02-05NOTICE:  Diana,Lorentz hired at:2007-02-07

首先，声明了一个游标 cur_emp，并且绑定了一个查询语句，通过一个参数 p_deptid 获取指定部门的员工；然后使用 OPEN 打开游标；接着在循环中使用 FETCH 语句获取游标中的记录，如果没有找到更多数据退出循环语句；变量 rec_emp 用于存储游标中的记录；最后使用 CLOSE 语句关闭游标，释放资源。

游标是 PL/pgSQL 中的一个强大的数据处理功能，更多的使用方法可以参考官方文档。

错误处理

报告错误和信息

PL/pgSQL 提供了 RAISE 语句，用于打印消息或者抛出错误：

RAISE level fORMat;

不同的 level 代表了错误的不同严重级别，包括：

DEBUG
LOG
NOTICE
INFO
WARNING
EXCEPTioN

在上文示例中，我们经常使用 NOTICE 输出一些信息。如果不指定 level，默认为 EXCEPTION，将会抛出异常并且终止代码运行。

format 是一个用于提供信息内容的字符串，可以使用百分号（%）占位符接收参数的值， 两个连写的百分号（%%）表示输出百分号自身。

以下是一些 RAISE 示例：

DO $$ BEGIN   RAISE DEBUG 'This is a debug text.';  RAISE INFO 'This is an information.';  RAISE LOG 'This is a log.';  RAISE WARNING 'This is a warning at %', now();  RAISE NOTICE 'This is a notice %%';END $$;INFO:  This is an information.WARNING:  This is a warning at 2020-05-16 11:27:06.138569+08NOTICE:  This is a notice %

从结果可以看出，并非所有的消息都会打印到客户端和服务器日志中。这个可以通过配置参数 client_min_messages 和 log_min_messages 进行设置。

对于 EXCEPTION 级别的错误，可以支持额外的选项：

RAISE [ EXCEPTION ] format USING option = expression [, ... ];RAISE [ EXCEPTION ] condition_name USING option = expression [, ... ];RAISE [ EXCEPTION ] SQLSTATE 'sqlstate' USING option = expression [, ... ];RAISE [ EXCEPTION ] USING option = expression [, ... ];

其中，option 可以是以下选项：

MESSAGE，设置错误消息。如果 RAISE 语句中已经包含了 format 字符串，不能再使用该选项。
DETAIL，指定错误详细信息。
HINT，设置一个提示信息。
ERRCODE，指定一个错误码（SQLSTATE）。可以是文档中的条件名称或者五个字符组成的 SQLSTATE 代码。
COLUMN、CONSTRAINT、DATATYPE、TABLE、SCHEMA，返回相关对象的名称。
以下是一些示例：

RAISE EXCEPTION 'Nonexistent ID --> %', user_id      USING HINT = 'Please check your user ID';RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';RAISE division_by_zero;RAISE SQLSTATE '22012';

检查断言

PL/pgSQL 提供了 ASSERT 语句，用于调试存储过程和函数：

ASSERT condition [ , message ];

其中，condition 是一个布尔表达式；如果它的结果为真，ASSERT 通过；如果结果为假或者 NULL，将会抛出 ASSERT_FAILURE 异常。message 用于提供额外的错误信息，默认为“assertion failed”。例如：

DO $$ DECLARE   i integer := 1;BEGIN   ASSERT i = 0, 'i 的初始值应该为 0！';END $$;ERROR:  i 的初始值应该为 0！CONTEXT:  PL/pgSQL function inline_code_block line 5 at ASSERT

⚠️注意，ASSERT 只适用于代码调试；输出错误信息使用 RAISE 语句。

捕获异常

默认情况下，PL/pgSQL 遇到错误时会终止代码执行，同时撤销事务。我们也可以在代码块中使用 EXCEPTION 捕获错误并继续事务：

[ <<label>> ][ DECLARE    declarations ]BEGIN    statementsEXCEPTION    WHEN condition [ OR condition ... ] THEN        handler_statements    [ WHEN condition [ OR condition ... ] THEN          handler_statements      ... ]END;

如果代码执行出错，程序将会进入 EXCEPTION 模块；依次匹配 condition，找到第一个匹配的分支并执行相应的 handler_statements；如果没有找到任何匹配的分支，继续抛出错误。

以下是一个除零错误的示例：

DO $$ DECLARE   i integer := 1;BEGIN   i := i / 0;EXCEPTION  WHEN division_by_zero THEN    RAISE NOTICE '除零错误！';  WHEN OTHERS THEN    RAISE NOTICE '其他错误！';END $$;NOTICE:  除零错误！OTHERS 用于捕获未指定的错误类型。

PL/pgSQL 还提供了捕获详细错误信息的 GET STACKED DIAGNOSTICS 语句，具体可以参考官方文档。

自定义函数

要创建一个自定义的 PL/pgSQL 函数，可以使用 CREATE FUNCTION 语句：

CREATE [ OR REPLACE ] FUNCTION  name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )  RETURNS rettypeAS $$DECLARE  declarationsBEGIN  statements;  ...END; $$LANGUAGE plpgsql;

CREATE 表示创建函数，OR REPLACE 表示替换函数定义；name 是函数名；括号内是参数，多个参数使用逗号分隔；argmode 可以是 IN（输入）、OUT（输出）、INOUT（输入输出）或者 VARIADIC（数量可变），默认为 IN；argname 是参数名称；argtype 是参数的类型；default_expr 是参数的默认值；rettype 是返回数据的类型；AS 后面是函数的定义，和上文中的匿名块相同；最后，LANGUAGE 指定函数实现的语言，也可以是其他过程语言。

以下示例创建一个函数 get_emp_count，用于返回指定部门中的员工数量：

CREATE OR REPLACE FUNCTION get_emp_count(p_deptid integer) RETURNS integer AS $$DECLARE  ln_count integer;BEGIN  select count(*) into ln_count  from employees  where department_id = p_deptid;  return ln_count;END; $$LANGUAGE plpgsql;

创建该函数之后，可以像内置函数一样在 SQL 语句中进行调用：

select department_id,department_name,get_emp_count(department_id)from departments d;department_id|department_name     |get_emp_count|-------------|--------------------|-------------|           10|Administration      |            1|           20|Marketing           |            2|           30|Purchasing          |            6|...

PL/pgSQL 函数支持重载（Overloading），也就是相同的函数名具有不同的函数参数。例如，以下语句创建一个重载的函数 get_emp_count，返回指定部门指定日期之后入职的员工数量：

CREATE OR REPLACE FUNCTION get_emp_count(p_deptid integer, p_hiredate date) RETURNS integer AS $$DECLARE  ln_count integer;BEGIN  select count(*) into ln_count  from employees  where department_id = p_deptid and hire_date >= p_hiredate;  return ln_count;END; $$LANGUAGE plpgsql;

查询每个部门 2005 年之后入职的员工数量：

select department_id,department_name,get_emp_count(department_id),get_emp_count(department_id, '2005-01-01')from departments d;department_id|department_name     |get_emp_count|get_emp_count|-------------|--------------------|-------------|-------------|           10|Administration      |            1|            0|           20|Marketing           |            2|            1|           30|Purchasing          |            6|            4|...

我们再来看一个 VARIADIC 参数的示例：

CREATE OR REPLACE FUNCTION sum_num(  VARIADIC nums numeric[])  RETURNS numericAS $$DECLARE ln_total numeric;BEGIN  SELECT SUM(nums[i]) INTO ln_total  FROM generate_subscripts(nums, 1) t(i);  RETURN ln_total;END; $$LANGUAGE plpgsql;

参数 nums 是一个数组，可以传入任意多个参数；然后计算它们的和值。例如：

SELECT sum_num(1,2), sum_num(1,2,3);sum_num|sum_num|-------|-------|      3|      6|

如果函数不需要返回结果，可以返回 void 类型；或者直接使用存储过程。

存储过程

PostgreSQL 11 增加了存储过程，使用 CREATE PROCEDURE 语句创建：

CREATE [ OR REPLACE ] PROCEDURE  name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )AS $$DECLARE  declarationsBEGIN  statements;  ...END; $$LANGUAGE plpgsql;

存储过程的定义和函数主要的区别在于没有返回值，其他内容都类似。以下示例创建了一个存储过程 update_emp，用于修改员工的信息：

CREATE OR REPLACE PROCEDURE update_emp(  p_empid in integer,  p_salary in numeric,  p_phone in varchar)AS $$BEGIN  update employees   set salary = p_salary,      phone_number = p_phone  where employee_id = p_empid;END; $$LANGUAGE plpgsql;

调用存储过程使用 CALL 语句：

call update_emp(100, 25000, '515.123.4560');

事务管理

在存储过程内部，可以使用 COMMIT 或者 ROLLBACK 语句提交或者回滚事务。例如：

create table test(a int);CREATE PROCEDURE transaction_test()LANGUAGE plpgsqlAS $$BEGIN    FOR i IN 0..9 LOOP        INSERT INTO test (a) VALUES (i);        IF i % 2 = 0 THEN            COMMIT;        ELSE            ROLLBACK;        END IF;    END LOOP;END$$;CALL transaction_test();select * from test;a|-|0|2|4|6|8|

只有偶数才会被最终提交。

关于“PostgreSQL存储过程源码分析”这篇文章的内容就介绍到这里，感谢各位的阅读！相信大家对“PostgreSQL存储过程源码分析”知识都有一定的了解，大家如果还想学习更多知识，欢迎关注编程网精选频道。