02-PostgreSQL 存储过程的进阶介绍(含游标、错误处理、自定义函数、事务)

news/2024/11/30 8:32:51/

🏆 文章目标:帮助那些了解Oracle PL/SQL,但是不熟悉PL/pgSQL 的人,用于快速整改或者上手。
🍀 02-PostgreSQL 存储过程的进阶介绍(含游标、错误处理、自定义函数、事务)
✅ 创作者:Jay…
🎉 个人主页:Jay的个人主页
🍁 展望:若本篇喜欢人数较多,将讲解Oracle PL/SQL 的使用方式,常见问题解答,以及一些编写规范。

介绍

上一篇我们讲解了PostgreSQL 存储过程的基本入门,满足一些最简单的使用,本章介绍相对复杂的使用方式。

游标

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

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

  • 声明游标变量;
  • 打开游标;
  • 从游标中获取结果;
  • 判断是否存在更多结果。如果存在,执行第 3 步;否则,执行第 5 步;
  • 关闭游标。

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

DO $$
DECLARE rec_emp RECORD;cur_emp CURSOR(p_deptid INTEGER) FORSELECT first_name, last_name, hire_date FROM employeesWHERE 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 cursorCLOSE cur_emp;
END $$;NOTICE:  Alexander,Hunold hired at:2006-01-03
NOTICE:  Bruce,Ernst hired at:2007-05-21
NOTICE:  David,Austin hired at:2005-06-25
NOTICE:  Valli,Pataballa hired at:2006-02-05
NOTICE:  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+08
NOTICE:  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_idUSING 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>> ]
[ DECLAREdeclarations ]
BEGINstatements
EXCEPTIONWHEN condition [ OR condition ... ] THENhandler_statements[ WHEN condition [ OR condition ... ] THENhandler_statements... ]
END;

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

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

DO $$ 
DECLARE i integer := 1;
BEGIN i := i / 0;
EXCEPTIONWHEN division_by_zero THENRAISE NOTICE '除零错误!';WHEN OTHERS THENRAISE NOTICE '其他错误!';
END $$;NOTICE:  除零错误!
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 指定函数实现的语言,也可以是其他过程语言。

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

CREATE OR REPLACE FUNCTION get_emp_count(p_deptid integer)RETURNS integer 
AS $$
DECLAREln_count integer;
BEGINselect count(*) into ln_countfrom employeeswhere 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 $$
DECLAREln_count integer;
BEGINselect count(*) into ln_countfrom employeeswhere 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 numeric
AS $$
DECLARE ln_total numeric;
BEGINSELECT SUM(nums[i]) INTO ln_totalFROM 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 ] PROCEDUREname ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
AS $$
DECLAREdeclarations
BEGINstatements;...
END; $$
LANGUAGE plpgsql;

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

CREATE OR REPLACE PROCEDURE update_emp(p_empid in integer,p_salary in numeric,p_phone in varchar)
AS $$
BEGINupdate employees set salary = p_salary,phone_number = p_phonewhere 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 plpgsql
AS $$
BEGINFOR i IN 0..9 LOOPINSERT INTO test (a) VALUES (i);IF i % 2 = 0 THENCOMMIT;ELSEROLLBACK;END IF;END LOOP;
END
$$;CALL transaction_test();
select * from test;
a|
-|
0|
2|
4|
6|
8|

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

欢迎大家评论和点赞,本篇大多内容来自官网文档的理解,以及本人的经验。若大家喜欢,将讲解Oracle 存储过程的内容,谢谢关注!


http://www.ppmy.cn/news/40570.html

相关文章

小游戏如何带动直播平台发展?

据艾媒咨询数据显示&#xff0c;截至2021年6月&#xff0c;我国网络直播用户规模达6.38亿&#xff0c;与2020年同期相比增长47.2%&#xff0c;占网民整体的63.1%。而随着互联网技术的高速发展&#xff0c;网络直播生态链备受用户关注&#xff0c;网络直播用户规模持续上升&…

使用pycharm远程连接服务器,可进行远程调试

利用Pycharm连接服务器 前言 当下&#xff0c;很多深度学习的模型需要高配置的设备来跑&#xff0c;本地的pc可能无法满足要求。所以就需要利用服务器来训练&#xff0c;但是在服务器上操作代码不是很方便。利用Pycharm可以在本地编写/修改代码&#xff0c;能够同步到服务器上&…

02 | 论文中的「题目、目录、摘要和结论」应该怎么写?

前言 写好外审的重点部分&#xff1a;题目、目录、摘要和结论。 文章目录前言一、专家&#xff08;教授&#xff09;一般审稿顺序二、题目1. 包含的要素2. 注意点3. 达到的效果三、研究背景及意义1. 要点2. 撰写思路四、目录1. 作用2. 逻辑关系的体现五、摘要1. 包含要素2. 关键…

12款 Macmini A1347 跑 Stable Diffusion,20多分钟一张图

设备 2012款 Macmini A1347 12款 mini A1347 跑 Stable Diffusion 要20多分钟一张图 来欣赏一下20分钟画出来的图片 a black and white cat 环境&#xff1a;

坦克大战第一阶段代码

package tanke.game;import javax.swing.*; import java.awt.*; import java.awt.event.KeyEvent; import java.awt.event.KeyListener; import java.util.Vector;//为了监听键盘事件&#xff0c;实现keylistener public class mypanel extends JPanel implements KeyListener …

Hadoop 配置历史服务器及日志的聚集 (六)

配置历史服务器 为了查看程序的历史运行情况&#xff0c;需要配置一下历史服务器。具体配置步骤如下&#xff1a; 1 &#xff09; 配置 mapred-site.xml 只需在mapred-site.xml配置文件加两个参数&#xff1a; [leokadiahadoop102 hadoop]$ vim mapred-site.xml 在该文件里面…

数据库并发控制基本概念和基本技术

并发控制与基本技术一、并发控制1. 概述2. 并发访问可能出现的问题二、并发控制的主要技术1、基本技术2、封锁及锁的类型2.1、什么是封锁2.2、基本封锁类型2.2.1、排它锁&#xff08;Exclusive Locks&#xff0c;简记为 X 锁&#xff09;2.2.2、共享锁&#xff08;Share Locks&…

pip3 升级软件包时遇到超时错误解决方法

如果你在使用 pip3 升级软件包时遇到超时错误&#xff0c;可能是因为下载速度缓慢或网络不稳定。以下是解决方法&#xff1a; 更改 pip3 源&#xff1a;你可以切换到其他 pip3 源&#xff0c;例如清华、阿里等等&#xff0c;以提高下载速度。 pip3 install -i https://pypi.tun…