10.pgsql函数和存储过程

最新推荐文章于 2024-07-31 16:35:24 发布

Lei_Da_Gou

最新推荐文章于 2024-07-31 16:35:24 发布

阅读量7.3k

点赞数 2

分类专栏： pgsql 文章标签： postgresql

本文链接：https://blog.csdn.net/Lei_Da_Gou/article/details/104364887

版权

pgsql 专栏收录该内容

22 篇文章 2 订阅

订阅专栏

1.说明

pgsql并没有将函数和存储过程做区分，通俗一点说，就是pgsql的函数也就是存储过程，存储过程就是函数。psql官方网站统称为 PL/pgSQL - SQL 过程语言。

2.函数的语法

CREATE [OR REPLACE] FUNCTION function_name (arguments)  
    RETURNS return_datatype AS $variable_name$  
-- 1.变量声明部分
  DECLARE  
    -- 变量声明;  
-- 2.函数体执行部分
  BEGIN  
    -- 执行语句
    RETURN { variable_name | value }  
-- 3.异常处理部分
 EXCEPTION
 END;
$variable_name$  LANGUAGE plpgsql;

function_name：函数名称。
[OR REPLACE]：是可选的，它允许您修改/替换现有函数。
RETURNS：函数返回的数据类型。它可以是基础，复合或域类型，或者也可以引用表列的类型。
LANGUAGE ：它指定实现该函数的语言的名称。

3.举例说明

表结构如下：

建表语句：

create table t_user (
    id serial,
    user_name varchar(255),
    pass_word varchar(255),
    create_time date,
    dr char(1),
    primary key(id)
);

存储过程sql语句：

create or replace function batch_insert_proc(num int) returns void as $$
declare
	randNum int;
begin
	while num > 0 loop
		randNum := round(random()*num);
		insert into t_user(user_name,pass_word,create_time,dr) values('user_name'||randNum,'pass_word'||randNum,now(),'0');
		num = num - 1;
	end loop;
exception
	when others then
	raise exception'(%)', 'unkown err';
end;
$$ language plpgsql;

4.函数（存储过程）调用结果

5.EXCEPTION异常说明并测试举例说明

   

-- 1.创建测试表
create table mytab(
    first_name varchar(255),
    last_name varchar(255)
)

-- 2.插入测试数据
INSERT INTO mytab(first_name, last_name) VALUES('Lei', 'DaGou');

-- 3.查看原表数据
select * from mytab;
    
-- 4.创建存储过程测试EXCEPTION
create or replace function exception_proc() returns int as $$
declare
    x int default 0;
    y int default 0;
BEGIN
    UPDATE mytab SET first_name = 'Lei' WHERE last_name = 'ErGOU';
    x := x + 1;
    y := x / 0;
    return x;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'caught division_by_zero(%)',x;
        RETURN x;
END;
$$ language plpgsql;

-- 5.执行存储过程查看结果
select exception_proc();

测试结果如下

原表：

过程调用成功，但是遇见异常并捕获:

调用后表：

EXCEPTION测试结论：

如果一个错误被 EXCEPTION 捕获，PL/pgSQL 函数的局部变量保持错误发生时的原值，但是所有该块中想固化在数据库中的状态都回滚。

    当控制到达给 y 赋值的地方时，它会带着一个 division_by_zero 错误失败。这个错误将被 EXCEPTION 子句捕获。而在 RETURN 语句里返回的数值将是 x 的增量值。但是，在该块之前的 UPDATE 和 INSERT 将不会回滚，因此最终的结果是数据库包含 Lei DaGou而不是 Lei ErGou。

6.写pgsql函数（存储过程）总结及注意点

1）、returns不要写成return；

2）、双美元符可以改成单引号，因为pgsql是把函数体作为文本处理的，之所以用$$是因为字符类型也会涉及到单引号，如果用单引号的话，函数体中如果用到自字符类型是，单引号需要包裹多重；

3）、双美元符中可以输入任意字符，前提是$$首尾内容保持一致，我把里面的内容当注释来看待，一般使用$body$居多；

4）、LANGUAGE指定内部语法使用的是pgsql的plsql语法，也可以指定为language sql等；

5）、函数参数也可以使用$1、$2的形式分别代表函数第一二个参数，可读性差，不建议使用；

6）、缺省时，一个在 PL/pgSQL 函数里发生的错误退出函数的执行，并且实际上其周围的事务也会退出。你可以使用一个带有 EXCEPTION 子句的 BEGIN 块捕获错误并且从中恢复。其语法是正常的 BEGIN 块语法的一个扩展；

7）、进入和退出一个包含 EXCEPTION 子句的块要比不包含的块开销大的多。因此，不必要的时候不要使用 EXCEPTION ；

8）、过程中如果需要返回集合类型使用setof类型，调用时返回的可能就是一个完整的表使用SELECT * FROM some_func()格式进行调用，目前的 PL/pgSQL 的 RETURN NEXT 实现在从函数返回之前把整个结果集都保存起来，就像上面描述的那样。这意味着如果一个 PL/pgSQL 函数生成一个非常大的结果集，性能可能会很差：数据将被写到磁盘上以避免内存耗尽，但是函数在完成整个结果集的生成之前不会退出。将来的 PL/pgSQL 版本可能会允许用户定义没有这样限制的返回集合的函数。目前，数据开始向磁盘里写的时刻是由配置变量 work_mem 控制的。拥有足够内存的管理员如果想在内存里存储更大的结果集，则可以考虑把这个参数增大一些；

9）、该存储过程使用了一些内部函数、循环语句和其它sql大同小异，这里不再赘述，pgsql中的函数中还有一些内容，如条件语句、循环语句、out in参数修饰符等，限于篇幅这里不再赘述。