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

🏆 文章目标:帮助那些了解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) 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-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_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
    statements
EXCEPTION
    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 rettype
AS $$
DECLARE
  declarations
BEGIN
  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 numeric
AS $$
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
  declarations
BEGIN
  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 plpgsql
AS $$
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|

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

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

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.mfbz.cn/a/9667.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

sql语法:详解DDL

Mysql版本&#xff1a;8.0.26 可视化客户端&#xff1a;sql yog 目录一、DDL是什么&#xff1f;二、和数据库相关的DDL2.1 创建数据库2.2 删除数据库2.3 查看所有的数据库&#xff0c;当前用户登录后&#xff0c;可以看到哪些数据库2.4 查看某个数据库的详细定义2.5 修改数据库…

在深圳做了5年软件测试,到头来啥也不是~

本科非计算机专业&#xff0c;在深圳做了5年软件测试工作&#xff0c;从一开始一脸懵的点点点&#xff0c;到现在会自动化测试了&#xff0c;浅谈一下从事软件测试的一点点心得体会&#xff0c;仅供参考交流。如果你本科且非计算机专业的话可以试下&#xff08;但就目前环境建议…

谈一谈Java的ThreadLocal

目录 先说原理&#xff1a; 再上代码&#xff1a; 运行结果&#xff1a; 先说原理&#xff1a; ThreadLocal 是一个本地线程副本变量工具类&#xff0c;它可以在每个线程中创建一个副本变量&#xff0c;每个线程可以独立地修改自己的副本变量&#xff0c;而不会影响其他线程…

测试的价值不仅仅是找

测试的价值不仅仅是找Bug 在我测试工作的前5年&#xff0c;一直以为测试的目标和价值就是在黑盒测试活动中找bug&#xff0c;以找到bug越多越自豪。但当我随着商业意识的不断积累&#xff0c;跳出测试的视角&#xff0c;站在公司的角度看测试时&#xff0c;会发现测试的目标是商…

linux驱动学习加强版-5(ioctl的使用)

文章目录一、添加ioctl控制节点二、修改测试APP2.1 测试APP的代码&#xff1a;2.2 驱动代码&#xff1a;ioctl是用户空间和内核空间相互交流时候用的比较多的一种手段。我们也可以在HAL层通过ioctl调到驱动里面。一、添加ioctl控制节点 先看patch 吧 这是在驱动中添加的ioct…

个人-计算机操作系统第五章

第五章 虚拟存储器 一、章节练习 1.系统抖动是指( )。 A. 使用机器时&#xff0c;千万屏幕闪烁的现象 B. 刚被调出的页面又立刻被调入所形成的频繁调入调出现象 C. 系统盘不净&#xff0c;千万系统不稳定的现象 D. 由于内存分配不当&#xff0c;偶然造成内存不够…

02-神经网络基础

一、从机器学习到神经网络 1. 两层神经网络 - 多层感知机 2. 浅层神经网络特点 (1)需要数据量小、训练速度快; (2)对复杂函数的表示能力有限,泛化能力受到制约。 Kurt Hornik 证明了理论上两层神经网络足以拟合任意函数,而且过去没有足够的数据和计算能力,因此之前的…

兰伯特光照模型(Lambert Lighting)和半兰伯特光照模型(Half-Lanbert)

关于漫反射 光打到凹凸不平的平面上&#xff0c;光线会被反射到四面八方&#xff0c;被称为漫反射 关于这种模型&#xff0c;由于光线由于分散&#xff0c;所以进入人眼的光线强度和观察角度没有区别 在A点和B点接收到的光线强度是一样的 在漫反射下&#xff0c;光线强度只和光…

卫龙携手契约锁,推动人事、采购、销售业务电子签

知名辣味休闲食品企业——卫龙&#xff0c;携手契约锁就印章管控及电子签章技术展开合作&#xff0c;打造覆盖总部及20多家子公司的印章管控平台&#xff0c;以现有泛微OA、钉钉APP为基础&#xff0c;推动人事、采购、销售业务电子签&#xff0c;通过签字、盖章无纸化&#xff…

使用Navicat进行ssh通道连接MySQL数据库

1、正常打开连接窗口&#xff0c;选择SSH&#xff1b; 2、填写完SSH对应参数&#xff0c;重新选择常规&#xff1b; SSH参数&#xff1a; 主机 > 服务器IP 端口 > 22 【一般22&#xff0c;SSH监听端口】 用户名 > 登录服务器账户 密码 > 登录服务器密码常规的参数…

mplfinancet绘制K线图

mplfinancet绘制K线图 1、安装 安装速度快 pip install mplfinance -i https://pypi.tuna.tsinghua.edu.cn/simple C:\Users>pip install mplfinance -i https://pypi.tuna.tsinghua.edu.cn/simple Defaulting to user installation because normal site-packages is not …

35岁的测试工程师被公司强行辞退,感叹道:我以前就该好好努力了

曾经的高薪软件测试工程师&#xff0c;今年35岁了&#xff0c;被公司劝退了&#xff0c;外卖跑到凌晨&#xff0c;很累&#xff0c;但还是有一种想诉说的冲动。哪怕让大家觉得已经说得太多了&#xff0c;烦了&#xff0c;都成祥林嫂了&#xff0c;但是&#xff0c;我是真的想说…

js:手写一个promise

背景 promise 作为前端开发中常用的函数&#xff0c;解决了 js 处理异步时回调地狱的问题&#xff0c;大家应该也不陌生了&#xff0c;今天来学习一下 promise 的实现过程&#xff0c;这样可以加&#xff08;面&#xff09;深&#xff08;试&#xff09;理&#xff08;要&…

第八天并发编程篇

一、简述线程、进程、程序的基本概念&#xff1f; 1.进程&#xff1a; 我们把运行中的程序叫做进程,每个进程都会占用内存与CPU资源,进程与进程之间互相独立. 2.线程&#xff1a; 线程就是进程中的一个执行单元&#xff0c;负责当前进程中程序的执行。一个进程可以包含多个线程…

Matlab在线IDE:计算定积分上限

上一篇文章&#xff1a;Matlab在线IDE&#xff1a;MATLAB Online介绍与计算定积分案例 1、案例介绍 % 定义符号变量 x syms x;% 定义函数 f(x) x f x;% 定义定积分的值 I I 2;% 计算函数 f(x) 在 [0, x] 区间的定积分&#xff0c;并求其反函数 F(x) F finverse(int(f, 0, …

【hello Linux】Linux软件管理器yum

目录 1.Linux软件管理器yum 1.1 关于lrzsz 1.2 使用yum时的注意事项 1.3 查看软件包&#xff1a;yum list 1.4 安装软件&#xff1a;yum install 1.5 卸载软件&#xff1a;yum remove 1.6 更新yum源 1.7 实战项目 Linux&#x1f337; 1.Linux软件管理器yum 在windows系统下有应…

ROS学习——艰辛的环境安装之路一Ubuntu

文章目录Ubuntu安装和下载页面设置安装Vmware Tools安装VSCODE用几个常用命令简单熟悉下UbuntuUbuntu 安装和下载 Ubuntu的安装和下载 看这个链接 Ubuntu安装和下载1 或者这个链接 Ubuntu安装和下载2 页面设置 安装Vmware Tools 看这个链接 VMware Tools的介绍和安装 安装…

超详细从入门到精通,pytest自动化测试框架实战-pytest插件的开发(八)

目录&#xff1a;导读前言一、Python编程入门到精通二、接口自动化项目实战三、Web自动化项目实战四、App自动化项目实战五、一线大厂简历六、测试开发DevOps体系七、常用自动化测试工具八、JMeter性能测试九、总结&#xff08;尾部小惊喜&#xff09;前言 pytest框架采用的是…

3年测试经验只会“点点点”,不会自动化即将面临公司淘汰?沉淀100天继续做测试

前段时间一个朋友跟我吐槽&#xff0c;说自己做软件测试工作已经3年了&#xff0c;可这三年自己的能力并没有得到提升&#xff0c;反而随着互联网的发展&#xff0c;自己只会“点点点”的技能即将被淘汰。说自己很苦恼了&#xff0c;想要提升一下自己&#xff0c;可不知道该如何…

简单的做一个学校毕业啊项目

前言&#xff1a;相信看到这篇文章的小伙伴都或多或少有一些编程基础&#xff0c;懂得一些linux的基本命令了吧&#xff0c;本篇文章将带领大家服务器如何部署一个使用django框架开发的一个网站进行云服务器端的部署。 文章使用到的的工具 Python&#xff1a;一种编程语言&…
最新文章