postgresql-存储过程

简述

除了标准 SQL 语句之外，PostgreSQL 还支持使用各种过程语言（例如 PL/pgSQL、C、PL/Tcl、
PL/Python、PL/Perl、PL/Java 等 ）创建复杂的过程和函数，称为存储过程（Stored Procedure）
和自定义函数（User-Defined Function）。存储过程支持许多过程元素，例如控制结构、循环和
复杂的计算。

使用存储过程带来的好处包括：

• 减少应用和数据库之间的网络传输。所有的 SQL 语句都存储在数据库服务器中，应用程
  序只需要发送函数调用并获取除了结果，避免了发送多个 SQL 语句并等待结果
• 提高应用的性能。因为自定义函数和存储过程进行了预编译并存储在数据库服务器中。
• 可重用性。存储过程和函数的功能可以被多个应用同时使用。
  当然，使用存储过程也可能带来一些问题：
• 导致软件开发缓慢。因为存储过程需要单独学习，而且很多开发人员并不具备这种技能。
• 不易进行版本管理和代码调试。
• 不同数据库管理系统之间无法移植，语法存在较大的差异。
  本文主要介绍 PL/pgSQL 存储过程，它和 Oracle PL/SQL 非常类似，是 PostgreSQL 默认支
  持的存储过程。使用 PL/pgSQL 的原因包括：
• PL/pgSQL 简单易学，无论是否具有编程基础都能够很快学会。
• PL/pgSQL 是 PostgreSQL 默认支持的过程语言，PL/pgSQL 开发的自定义函数可以和内置
  函数一样使用。
• PL/pgSQL 提高了许多强大的功能，例如游标，可以实现复杂的函数。

PL/pgSQL 代码块结构

/** label 是一个可选的代码块标签，可以用于 EXIT 语句退出指定的代码块，或者限定变量的名称* DECLARE 是一个可选的声明部分，用于定义变量* BEGIN 和 END 之间是代码主体，也就是主要的功能代码；所有的语句都使用分号（;）结束* END 之后的分号表示代码块结束。* */
[ <<label>> ]
[ DECLAREdeclarations ]
BEGINstatements;...
END [ label ];

示例

/** 个匿名块，与此相对的是命名块（也就是存储过程和函数）。其中，DO 语句用于* 执行匿名块；我们定义了一个字符串变量 name，然后给它赋值并输出一个信息；RAISE NOTICE用于输出通知消息。* $$用于替换单引号（'）* */
do $$
declare name text;
beginname :='PL/pgSQL';raise notice 'Hello %!',name; end $$;

/** $$用于替换单引号（'），因为 PL/pgSQL 代码主体必须是字符串文本，* 意味着代码中所有的单引号都必须转义（重复写两次）
*/
DO
'DECLAREname text;
BEGINname := ''PL/pgSQL'';RAISE NOTICE ''Hello %!'', name;
END ';

嵌套子块

PL/pgSQL 支持代码块的嵌套，也就是将一个代码块嵌入其他代码块的主体中。被嵌套的代
码块被称为子块（subblock），包含子块的代码块被称为外部块（subblock）。子块可以将代码
进行逻辑上的拆分，子块中可以定义与外部块重名的变量，而且在子块内拥有更高的优先级

DO $$
<<outer_block>>
declarename text;
BEGINname := 'outer_block';RAISE NOTICE 'This is %', name;DECLAREname text := 'sub_block';BEGINRAISE NOTICE 'This is %', name;RAISE NOTICE 'The name FROM the outer block is %', outer_block.name;END;RAISE NOTICE 'This is %', name;END outer_block $$;

声明与赋值

/*variable_name 是变量的名称，通常需要指定一个有意义的名称；data_type 是变量的
类型，可以是任何 SQL 数据类型；如果指定了 NOT NULL，必须使用后面的表达式为变量指定
初始值。赋值使用:=*/
variable_name data_type [ NOT NULL ] [ { DEFAULT | := | = } expression ];

DO $$
declare 
id integer;
-- price默认值是0
price numeric(5,2) default 0.0;
name text;
url varchar :='http://mysite.com';
--行类型的变量，可以存储查询语句返回的数据行（数据行的结构要和 employees相同）
myrow employees%rowtype;
-- myfield 的数据类型取决于 empoyees.first_name 字段的定义
myfield employees.first_name%type;
-- myprice 和 price 的类型一致。
myprice price%type;
-- 记录类型变量
-- 记录类型的变量没有预定义的结构，只有当变量被赋值时才确定，而且可以在运行时被改变。
-- 记录类型的变量可以用于任意查询语句或者 FOR 循环变量
arow RECORD;
-- 使用 ALIAS 定义一个变量别名
myprice1 ALIAS FOR myprice;
-- 在定义变量时指定了 CONSTANT 关键字，意味着定义的是常量。常量的值需要在声明时初始化，并且不能修改
PI CONSTANT NUMERIC := 3.14159265;
beginid := 1;name :='tony';raise notice 'id = %',id;raise notice 'price = %',price;raise notice 'name = %',name;raise notice 'url =%',url;raise notice '常量PI=%',PI;
end $$;

控制结构

IF 语句

• IF … THEN … END IF
• IF … THEN … ELSE … END IF
• IF … THEN … ELSIF … THEN … ELSE … END IF

-- 简单的if语句
DO $$
beginif 2>1 thenraise notice '2大于1';end if;
end $$;

-- if ... else
DO $$
beginif 2>3 thenraise notice '2大于3';elseraise notice '2不大于3';end if;
end $$;

-- if ... else 多个条件分支
DO $$
beginif 2>3 thenraise notice '2大于3';elseif 2=3 thenraise notice '2等于3';elseraise notice '2不大于3';end if;
end $$;

CASE 语句

CASE 语句，同样可以根据不同的条件执行不同的分支语句。CASE 语句分为两种：简单 CASE 语句和搜索 CASE 语句。

简单case语句

-- 语法
CASE search-expressionWHEN expression [, expression [ ... ]] THENstatements[ WHEN expression [, expression [ ... ]] THENstatements... ][ ELSEstatements ]
END CASE;

-- case 简单语句
DO $$
declare i integer := 1;
begincase iwhen 1,2 thenraise notice '1或者2';when 3 thenraise notice '3';elseraise notice '其他值';end case;
end $$;

搜索 CASE 语句

-- 语法
CASEWHEN boolean-expression THENstatements[ WHEN boolean-expression THENstatements... ][ ELSEstatements ]
END CASE;

-- 搜索case 简单语句
DO $$
declare i integer := 25;
begincase when i between 1 and 10 thenraise notice '[1-10]';when i between 11 and 20 thenraise notice '[11-20]';elseraise notice '其他值';end case;
end $$;

循环语句

PostgreSQL 提供 4 种循环执行命令的语句：LOOP、WHILE、FOR 和 FOREACH 循环，以
及循环控制的 EXIT 和 CONTINUE 语句。

-- LOOP 用于定义一个无限循环语句：
-- 一般需要使用 EXIT 或者 RETURN 语句退出循环，label 可以用于 EXIT 或者 CONTINUE 语
-- 句退出或者跳到执行的嵌套循环中
[ <<label>> ]
LOOPstatements
END LOOP [ label ];

-- loop循环
DO $$
declare i integer := 1;
beginloop-- exit退出循环exit when i  = 5;raise notice '第%次执行!',i;i :=i+1;end loop;
end $$;

continue

-- loop循环
DO $$
declare i integer := 0;
beginloop-- exit退出循环exit when i = 10;i := i+1;-- continue 忽略后面的语句，直接进入下一次循环-- mod(i,2) =0;偶数跳过执行continue when mod(i,2) =0;raise notice '第%次执行!',i;end loop;
end $$;

while

-- 语法
-- 当表达式 boolean-expression 的值为真时，循环执行其中的语句；然后重新计算表达式的值，
-- 当表达式的值假时退出循环
[ <<label>> ]
WHILE boolean-expression LOOPstatements
END LOOP [ label ];

-- while语句
DO $$
declare i integer := 0;
beginwhile i <10 loopi := i+1;raise notice '第%次执行',i;end loop;
end $$;

for语句

-- 语法
-- FOR 循环默认从小到大进行遍历，REVERSE 表示从大到小遍历；BY 用于指定每次的增量，
-- 默认为 1
[ <<label>> ]
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOPstatements
END LOOP [ label ];

-- for语句
DO $$
begin-- 变量 i 不需要提前定义，可以在 FOR 循环内部使用。-- by 2每次增量为2for i  in  reverse  20..10 by 2 loopraise notice '第%次循环!',i;end loop;
end $$;

-- for语句
DO $$
begin-- 变量 i 不需要提前定义，可以在 FOR 循环内部使用。-- by 2每次增量为2for i in  1..10 by 2 loopraise notice '第%次循环!',i;end loop;
end $$;

遍历查询结果

-- 语法
-- target 可以是一个 RECORD 变量、行变量或者逗号分隔的标量列表。在循环中，target
-- 代表了每次遍历的行数据。
[ <<label>> ]
FOR target IN query LOOPstatements
END LOOP [ label ];

-- for语句遍历查询结果
DO $$
declare 
emp record;
beginfor emp in (select * from employees limit 5) loopraise notice '员工信息: %, %, %',emp.first_name,emp.last_name,emp.salary;end loop;
end $$;

foreach

-- 语法
[ <<label>> ]
FOREACH target [ SLICE number ] IN ARRAY expression LOOPstatements
END LOOP [ label ];

如果没有指定 SLICE 或者指定 SLICE 0，FOREACH 将会变量数组中的每个元素

do $$
declare
x int;
beginforeach x in array (array[[1,2,3],[4,5,6]]) loopraise notice 'x = %', x;end loop;
end $$;

do $$
declare
x int[];
begin
-- 如果指定了一个正整数的 SLICE，FOREACH 将会变量数组的切片；SLICE 不能大于数组的维度。foreach x slice 1 in array (array[[1,2,3],[4,5,6]]) loopraise notice 'x = %', x;end loop;
end $$;

游标

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

1. 声明游标变量
2. 打开游标
3. 从游标中获取结果
4. 判断是否存在更多结果。如果存在，执行第 3 步；否则，执行第 5 步；
5. 关闭游标

do $$
declare
-- 声明变量类型为recordemp record;emp_cur cursor for select * from employees limit 10;
begin
-- 打开游标open emp_cur;loop-- 获取 fetchfetch emp_cur into emp;-- 退出循环条件 when not found 没有找到数据exit when not found ;raise notice '员工信息:% ,%',emp.first_name,emp.last_name;end loop;-- 关闭游标close emp_cur;
end $$;

游标传参

do $$
declare
-- 声明变量类型为recordemp record;emp_cur cursor(dept_id integer) for select * from employees where department_id = dept_id limit 10;
begin
-- 打开游标open emp_cur(90);loop-- 获取 fetchfetch emp_cur into emp;-- 退出循环条件 when not found 没有找到数据exit when not found ;raise notice '员工信息:% ,%',emp.first_name,emp.last_name;end loop;-- 关闭游标close emp_cur;
end $$;

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

官网介绍

错误处理

报告错误和信息

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

RAISE level format;

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

• DEBUG
• LOG
• NOTICE
• INFO
• WARNING
• EXCEPTION
  ，我们经常使用 NOTICE 输出一些信息。如果不指定 level，默认为 EXCEPTION，
  将会抛出异常并且终止代码运行

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

DO $$
BEGINRAISE 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 $$;

从结果可以看出，并非所有的消息都会打印到客户端和服务器日志中。这个可以通过配置参
数 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，返回相关对象的名称。

do $$
beginraise info 'This s an info.';raise debug 'This s an debug.';raise warning 'This s an warning.';
end $$;

检查断言

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

ASSERT condition [ , message ];

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

DO $$
DECLAREi integer := 1;
BEGINASSERT i = 0, 'i 的初始值应该为 0！';
END $$;

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

捕获异常

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

[ <<label>> ]
[ DECLAREdeclarations ]
BEGINstatements
EXCEPTIONWHEN condition [ OR condition ... ] THENhandler_statements[ WHEN condition [ OR condition ... ] THENhandler_statements... ]
END;

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

DO $$
DECLAREi integer := 1;
BEGINi := i / 0;
EXCEPTIONWHEN division_by_zero THENRAISE NOTICE '除零错误！';WHEN OTHERS THENRAISE NOTICE '其他错误！';
END $$;

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

自定义函数

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

CREATE [ OR REPLACE ] FUNCTIONname ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ]
[, ...] ] )RETURNS rettype
AS $$
DECLAREdeclarations
BEGINstatements;...
END; $$
LANGUAGE plpgsql;

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

-- 函数创建
create or replace function get_emp_count(p_deptid integer)
returns integer
as $$
declare ln_count integer;
begin if p_deptid <= 0 thenraise exception '部门编号不能小于等于0!p_deptid:%',p_deptid;end if;select count(*) into ln_countfrom employeeswhere department_id  = p_deptid;	return ln_count;
end $$
language plpgsql;

-- 函数使用
select get_emp_count(90) as total;

select get_emp_count(-1) as total;

重载

PL/pgSQL 函数支持重载（Overloading），也就是相同的函数名具有不同的函数参数

CREATE OR REPLACE FUNCTION public.get_emp_count(p_deptid integer,p_hire_date varchar)RETURNS integerLANGUAGE plpgsql
AS $function$
declare ln_count integer;
begin if p_deptid <= 0 thenraise exception '部门编号不能小于等于0!p_deptid:%',p_deptid;end if;select count(*) into ln_countfrom employeeswhere department_id  = p_deptidand hire_date > p_hire_date;return ln_count;
end $function$
;

VARIADIC

VARIADIC：参数的数量是多个，可变的

-- 数组 nums的下标索引的产生，1维数组
-- generate_subscripts(nums,1)
create or replace function sum_num(variadic nums numeric[])
returns numeric
as $$
declare total numeric;
begin select sum(nums[i]) into total-- t(i)返回的下标索引变量为ifrom generate_subscripts(nums,1) t(i);return total;
end $$
language plpgsql;

-- 数组内元素为1和2的和
-- 参数 nums 是一个数组，可以传入任意多个参数；然后计算它们的和值
select sum_num(1,2);

存储过程

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

CREATE [ OR REPLACE ] PROCEDUREname ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ]
[, ...] ] )
AS $$
DECLAREdeclarations
BEGINstatements;...
END; $$
LANGUAGE plpgsql;

-- 调用存储过程
call 过程名(参数1，参数2...);

示例

create or replace procedure transfer(p_from integer,p_to integer,p_amount numeric)
as $$
declare l_count integer;
begin select count(*) into l_countfrom accountswhere id = p_from;if l_count = 0 thenraise exception '原账户不存在：%',p_from;end if;select count(*) into l_countfrom accountswhere id = p_to;if l_count = 0 thenraise exception '目标账户不存在：%',p_to;end if;if p_amount < 0 thenraise exception '转账金额错误：%',p_amount;end if;-- 1.扣款update accounts set balance = balance - p_amountwhere id = p_from;-- 2.存款update accounts set balance = balance + p_amountwhere id = p_to;-- 3.记录流水
end $$
language plpgsql;

-- 调用存储过程
call public.transfer(1,2,3000);select * from public.accounts a ;

事务管理

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

create table test2(col integer);

-- 创建存储过程
create or replace procedure transaction_test()
as $$ 
begin for i in 0..10 loopinsert into test2 values(i);--mod 求余-- 偶数提交if mod(i,2) = 0 thencommit;else-- 奇数回滚rollback;end if;end loop;end $$
language plpgsql;

-- 调用存储过程
call transaction_test();

select * from test2;