Backend - postgresSQL DB 存储过程（数据库存储过程）

create [or replace] function 函数名(参数名 参数类型, … ) 
returns 返回值类型 as
    $body$
        declare 
            变量名 变量类型;
            变量名 类型 := 值;
            变量名 类型; 变量名 := 值;
        begin 
            函数体; 
        exception when others then 
            raise exception '(%)', sqlerrm;
        end
    $body$
language plpgsql;

2. 举例

（1）例1-循环批量插入数据

① 首先，声明函数

create or replace function test001 (
    num int
) returns void as 
$$
begin 
    while num <10
    loop 
        insert into public.rep_updhist(id, lastupdate, tablename, starttimekey, endtimekey) values (num, '2024-03-27 15:33:14.014 +0800', 'test0521', '20240101073054822149', '20240327153311867313');
        num = num +1;
    end loop;
exception 
    when others then 
        raise exception '(%)', sqlerrm;
end
$$ 
language plpgsql;

② 调用函数

select test001(8);

（2）例2-计算面积

① 首先，声明函数

create or replace function test002 (
    w int, h int
) returns int as 
$calculator_area$
declare 
    res int := 0;
begin
    res := w * h;
    return res;
exception 
    when others then 
        raise exception '(%)', sqlerrm;
end
$calculator_area$ 
language plpgsql;

② 调用函数

select test002(4,5);

（二）存储过程结构

1. SQL代码

create [or replace] procedure 存储过程名(参数名 参数类型, … )
language plpgsql as
$body$
    declare 
        变量名 变量类型;
        变量名 类型 := 值;
        变量名 类型; 变量名 := 值;
    begin 
        SQL 语句; 
    exception when others then 
        rollback;
    end
$body$

2. 举例

（1）首先声明存储过程

create or replace procedure testpro001("w" in integer, "h" in integer, "msg" inout text)
language plpgsql as
$$
declare
    res_area integer := 0;
begin 
    res_area := w * h;
    msg := msg||res_area;
    raise notice '控制台展示面积数据：%', res_area;
end;
$$

（2）调用存储过程

call testpro001(4,6, '面积的计算结果是');

（3）结果是

（三）注意

1. 每句末尾必须带分号隔开。

2. 字串相加的连接符号是 ||，而不是+。

3. format部分中，%是占位符，接收变量的值。例如, aa变量值是23, bb := '结果是%',aa; 则bb的值是'结果是23'。

4. 在函数中，若returns返回值类型是void，则无需return，否则得写上 return 变量名。

5. 报错

SQL 错误 [42725]: ERROR: procedure testpro001(integer, integer, integer) is not unique
Hint: Could not choose a best candidate procedure. You might need to add explicit type casts.
Position: 6

解决：查看存储过程文件夹中，是否有重复的方法名（或存储过程名）。

6. 执行函数时，若函数名已经存在，但参数类型有变，则会生成函数名重复的函数。同理，执行过程也是。

7. 保留大小写，需要加上双引号。

解决：使用“”，或者使用quote_ident('XXX')，给字符串加上双引号

四、异常处理

1. 异常写法

exception when others then 
    rollback;
    raise exception '异常是%', sqlerrm;

2. OTHERS

表示除了声明外的错误。

3. RAISE EXCEPTION

抛出异常。

举例：raise exception '出现了异常，请检查！';

4. SQLERRM

储存当前错误的详细信息。

举例：raise exception '异常是%', sqlerrm;

五、raise level format 语句

（一）作用

显示消息或异常。

（二）异常等级

DEBUG(向服务器日志写信息)、

LOG(向服务器日志写信息，优先级更高)、

INFO、NOTICE和WARNING(把信息写到服务器日志以及转发到客户端应用，优先级逐步升高)、

EXCEPTION抛出一个错误(强制关闭当前事务)

（三）举例

1. 展示正常信息

（1）声明时

create or replace function get_data()
returns void as 
$$
declare strval text;
begin 
    strval := '一个大写字母' || quote_ident('B') || '!';
    raise notice '这是%', strval;
exception when others then
    raise WARNING '出现异常：(%)', sqlerrm;
end;
$$
language plpgsql;

（2）调用时

select get_data();

（3）结果是

2. 显示异常

（1）声明时

create or replace function get_data()
returns void as 
$$
declare
    strval text;
begin 
    raise notice '这是%', xx;
exception when others then
    -- raise EXCEPTION '出现异常：(%)', sqlerrm;
    raise WARNING '出现异常：(%)', sqlerrm;
end;
$$
language plpgsql;

（2）调用时

select get_data();

（3）结果是

六、查询获取数据库的值

1. 声明时

create or replace procedure testpro001()
language plpgsql as
$calculator_area$
declare 
    qry_book text := '';
    qry_author text := '';
begin
    select name into qry_book from public."myApp_book" where bid = 3; -- 第1种
    execute 'select author from public."myApp_book" where bid = 3' into qry_author; -- 第2种
    raise notice '书籍ID为是3的书名是：%，作者是：%', qry_book, qry_author;
end;
$calculator_area$

2. 调用时

call testpro001();

七、查询多笔记录

（一）第一种

在声明函数时，定义输出的值（指定out参数，使用return next）

1. 声明时

create or replace function get_record(out out_bid int, out out_card character varying)
returns setof record as 
$$
declare 
    r record;
begin 
    for i in 1..5 loop
        select * into r from public."myApp_book" where bid=i;
        out_bid := r.bid;
        out_card := r.card;
        return next;
    end loop;
end
$$
language plpgsql;

2. 调用时

select * from get_record();

3. 结果是

（二）第二种

在调用时，定义获取的值（使用return query）

1. 声明时

create or replace function get_record()
returns setof record as 
$$
declare 
    r record;
begin 
    for i in 1..5 loop
        return query(select bid, card from public."myApp_book" where bid=i);
    end loop;
end
$$
language plpgsql;

2. 调用时

select * from get_record() as t(id integer, card character varying);

3. 结果是

七、控制结构

（一）if条件

if … then … elseif … then … else … end if;

其中，end if的后面一定要带上分号；elseif的写法是连接在一起的，中间无空格隔开。

例子：

create or replace procedure testpro001("w" in integer, "h" in integer, "msg" inout text)
language plpgsql as
$$
declare
    res_area integer := 0;
begin 
    res_area := w * h;
    msg := msg||res_area;
    if res_area <= 10 then
        raise notice '面积数据：%，小于10', res_area;
    elseif res_area <= 20 then
        raise notice '面积数据：%，小于20', res_area;
    else
        raise notice '面积数据：%，大于20', res_area;
    end if;
end;
$$
call testpro001(3,6, '求w的值');

（二）循环

1. while … loop

（1）代码

while … loop 
    # 函数体;
end loop;

（2）例子

① 声明时

create or replace procedure testpro001("w" in integer, "h" in integer, "msg" inout text)
language plpgsql as
$$
declare
    res_area integer := 0;
begin 
    while w<=h loop
        w = w+1;
    end loop;
    msg := 'w的值是' || w;
end;
$$

② 调用存储过程时

call testpro001(3,6, '求w的值');

2. for

（1）声明时

create or replace procedure testpro001()
language plpgsql as
$$
declare
    sumval int := 0;
begin 
    for i in 1..6 loop
        sumval := sumval+i;
    end loop;
    raise notice '总和值：%',sumval;
end;
$$