本文主要是介绍Backend - postgresSQL DB 存储过程（数据库存储过程），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
目录
一、存储过程的特性
（一）作用
（二）特点
（三）编码结构的区别
二、定时执行存储过程
三、2种编码结构
（一）函数结构
1. SQL代码
2. 举例
（1）例1-循环批量插入数据
① 首先，声明函数
② 调用函数
（2）例2-计算面积
① 首先，声明函数
② 调用函数
（二）存储过程结构
1. SQL代码
2. 举例
（1）首先声明存储过程
（2）调用存储过程
（3）结果是
编辑
（三）注意
四、异常处理
1. 异常写法
2. OTHERS
3. RAISE EXCEPTION
4. SQLERRM
五、raise level format 语句
（一）作用
（二）异常等级
（三）举例
1. 展示正常信息
（1）声明时
（2）调用时
（3）结果是
2. 显示异常
（1）声明时
（2）调用时
（3）结果是
六、查询获取数据库的值
1. 声明时
2. 调用时
七、查询多笔记录
（一）第一种
1. 声明时
2. 调用时
3. 结果是
（二）第二种
1. 声明时
2. 调用时
3. 结果是
七、控制结构
（一）if条件
（二）循环
1. while … loop
（1）代码
（2）例子
① 声明时
② 调用存储过程时
2. for
（1）声明时
（2）调用存储过程时
一、存储过程的特性
（一）作用
可以将SQL语句存放在数据库服务器上。
（二）特点
存储于数据库服务器。
一次编译后，可多次调用。
有两种编码结构：函数和存储过程。
（三）编码结构的区别
函数可以在select、update等SQL语句中被调用，而存储过程不能。
存储过程无需return返回值，函数必须有return返回值。
存储过程必须用call调用，函数可用select即可。
二、定时执行存储过程
针对pgadmin软件，使用pg_cron插件或pg_agent插件。但是pg_cron是安装在Unix上，pg_agent
目前也没有实现Windows安装。
所以，现在只能用代码调用存储过程。
三、2种编码结构
（一）函数结构
1. SQL代码
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 <10loop 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;
beginres := 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
$$
declareres_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 thenraise WARNING '出现异常：(%)', sqlerrm;
end;
$$
language plpgsql;（2）调用时
select get_data();（3）结果是
2. 显示异常
（1）声明时
create or replace function get_data()
returns void as
$$
declarestrval 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 := '';
beginselect 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 loopselect * 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 loopreturn 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
$$
declareres_area integer := 0;
begin res_area := w * h;msg := msg||res_area;if res_area <= 10 thenraise notice '面积数据：%，小于10', res_area;elseif res_area <= 20 thenraise notice '面积数据：%，小于20', res_area;elseraise 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
$$
declareres_area integer := 0;
begin while w<=h loopw = w+1;end loop;msg := 'w的值是' || w;
end;
$$② 调用存储过程时
call testpro001(3,6, '求w的值');2. for
（1）声明时
create or replace procedure testpro001()
language plpgsql as
$$
declaresumval int := 0;
begin for i in 1..6 loopsumval := sumval+i;end loop;raise notice '总和值：%',sumval;
end;
$$（2）调用存储过程时
call testpro001();这篇关于Backend - postgresSQL DB 存储过程（数据库存储过程）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
