PostgreSQL的函数及存储过程的基本使用

  新人刚接触PostgreSQL数据库，在这里总结了一下存储过程中我用到了的基础知识，常常温习。

postgre数据库安装、卸载、重新安装以及pgAdmin的基本使用详见 上一篇

---

零、存储过程与函数的异同

• 函数通常用于对一个或多个输入参数进行操作，主要目的是返回一个结果值；而存储过程通常包含一系列的SQL语句或其他代码块，主要目的是执行一系列的操作。
• 函数可以被用于查询中的SELECT语句中的列表达式的一部分；而存储过程通常在PL/pgSQL代码块中运行，不能作为查询的一部分。
• 函数可以在SELECT、UPDATE、DELETE等SQL语句中被使用；而存储过程不能直接被用于SQL语句中。
• 函数可以有返回值；而存储过程通常没有明确的返回值，若一定需要返回值则需要使用OUT关键字。
• 分支选择、循环等流程语句的使用方法是一致的。

---

一、编码结构

1.函数的结构

例： 一个求长方形面积的函数

create or replace function area_of_rectangle(lenth integer,height integer)
returns integer as
$$
declare
	area integer := 0;
begin
	area := lenth * height;
	return area;
end
$$
language 'plpgsql';

说明：

• 第一行 用于声明函数名及需要的参数。需要注意参数格式：(变量名1 变量类型 , 变量名2 变量类型,…)
• 第二行 声明返回值的类型。注意这里是returns，有“s”！！！
• 第三行 两个美元符中间可以填入符合命名规则的任意字符，如$body$、$aaaa$。但是下方的美元符必须与这里的保持一致。
• 第四行 declare表示声明变量，可以声明多个变量。
• 第五行 声明了一个integer类型的变量并赋了初始值。
• 第六行 begin表示语句开始
• 第七行 计算长方形的面积
• 第八行 将值返回
• 第九行 end表示语句结束
• 第十一行 声明该存储过程使用的语言。有时遇到编译报错可能是这里写成了’sql’。

调用存储过程：

select area_of_rectangle(5,9);

结果截图：

2.存储过程的结构

例： 一个求长方体体积的存储过程。（这个存储过程的内容毫无意义，它更适合定义为函数。这里只是用它来说明存储过程的结构，并且可以自行对比与函数的异同）

CREATE PROCEDURE volume_of_cuboid(lenth integer,width integer,height integer, OUT volume integer)
LANGUAGE plpgsql AS
$$
DECLARE
	area integer := 0;
BEGIN
    area := lenth * width;
    volume := area * height;
END;
$$

说明：

• 第一行 用于声明存储过程的名称及需要的入参、出参。需要注意参数格式：(变量名1 变量类型 , 变量名2 变量类型,…)。并且需要用OUT关键字来标记出参，如果自定义的存储过程不需要出参则不定义即可。
• 第二行 声明该存储过程使用的语言
• 第三行 两个美元符中间可以填入符合命名规则的任意字符，如$body$、$aaaa$。但是下方的美元符必须与这里的保持一致。
• 第四行 declare表示声明变量，可以声明多个变量。
• 第五行 声明了一个integer类型的变量“面积”并赋了初始值0。
• 第六行 begin表示语句开始
• 第七行 计算长方体底面的面积
• 第八行 计算长方体的体积
• 第九行 end表示语句结束

调用存储过程：

CALL volume_of_cuboid(10, 20, 30 total);
-- 或
SELECT * FROM volume_of_cuboid(10, 20, 30);

---

二、变量使用

1.变量类型

存储过程中，对变量赋值需要两个值类型一致，如经常碰到bigint类型值赋值到integer变量中，导致运行报错。我用过的类型：

类型	说明	备注
character varying(n)	变长的字符串类型	在存储过程中不常用
text	不限长度的字符串类型	字符串类型用这个简单方便快捷
integer	4字节整型
bigint	8字节整形	count(*)函数的查询值属于此类型
numeric(m,n)	精确的小数类型
date	日期类型
time	时间类型
record	记录类型	若存储过程需要返回多行多列记录，可以用这个

更多详细变量类型可参考菜鸟教程学习：
https://www.runoob.com/postgresql/postgresql-data-type.html

2.record变量

  record类型变量是“记录类型”的变量，用于存储多行多列的值。
  按官方文档的说明，record类型的变量并不是真正的变量，该类型变量在第一次赋值前，它有多少列、每一列是什么类型都是不确定的。在第一次赋值后，该变量就根据值自动确定列的数量和各列的类型。

3.赋值

静态赋值：

student_name := '张静';

动态赋值：

select name into student_name from class where stu_No = 1;
--或者
execute 'select name from class where stu_No = 1' into student_name;

注意 “:=” 和 “into” 是关键。

---

三、基本流程语句

1.分支选择

if语句：

IF ... THEN ... END IF;
IF ... THEN ... ELSE ... END IF;
IF ... THEN ... ELSE ... THEN ... ELSE ... END IF;
--例：
if student_name = '张静' then
	RAISE NOTICE '我是张静';
else if student_name like '%李%' then
	RAISE NOTICE '我姓李';
else
	RAISE NOTICE '我不是张静，也不姓李';
end if;

case语句：

CASE ... WHEN ... THEN ... ELSE ... END CASE;
CASE WHEN ... THEN ... ELSE ... END CASE;
--例：
case student_name when '张静','晓静' then
	RAISE NOTICE '张静和晓静都是我的名称';
else 
	RAISE NOTICE '你叫错名字了';
end case;
--例：
case when student_name = '张静' or student_name = '晓静' then
	RAISE NOTICE '张静和晓静都是我的名称';
else
	RAISE NOTICE '你叫错名字了';
end case;

2.循环

简单循环 中括号为可选项

[ <<label>> ]
LOOP
    循环体语句;
    EXIT [ label ] [ WHEN 判断条件表达式 ];
END LOOP [ label ];

--例-计算1到100的和：
sum := 0;
i := 0;
loop
	i := i + 1;
	sum := sum + i;
	exit when i = 100 ;
end loop;
RAISE NOTOCE '1到100的和为：%',sum;

while循环

[ <<label>> ]
WHILE 判断条件表达式 LOOP
    循环体语句;
END LOOP [ label ];

--例 - 计算1到100的和：
sum := 0;
i := 1;
while i<=100 loop
	sum := sum + i;
	i := i + 1;
end loop
RAISE NOTOCE '1到100的和为：%',sum;

for循环

[ <<label>> ]
FOR 循环控制变量 IN [ REVERSE ] 循环范围 [ BY expression ] LOOP
    循环体语句;
END LOOP [ label ];

--计算1到100的和：
--例1 - 循环执行过程类似于：for(i=1;i<=100;i++){}
sum := 0;
for i in 1..100 loop
	sum := sum + i;
end loop;
RAISE NOTOCE '1到100的和为：%',sum;

--例2 - 循环执行过程类似于：for(i=100;i>=1;i--){}
sum := 0;
for i in REVERSE 100..1 loop
	sum := sum + i;
end loop;
RAISE NOTOCE '1到100的和为：%',sum;

--计算1到100之间所有奇数的和
--例3 - 循环执行过程类似于：for(i=1;i<=100;i=i+2){}
sum := 0;
for i in 1..100 by 2 loop
	sum := sum + i;
end loop;
RAISE NOTOCE '1到100的和为：%',sum;

for遍历查询的结果集

[ <<label>> ]
FOR 变量 IN 查询语句 LOOP
    循环体语句;
END LOOP [ label ];

--例 - 遍历班级中每个人的名字：
for student_name in select name from class loop
	RAISE NOTICE '姓名：%',student_name;
end loop;

---

四、查询并返回多条记录

---- postgreSQL使用存储过程查询并显示多行多列数据 ----
---- 参考网址：https://blog.csdn.net/wuyilun2013/article/details/42779345

create or replace function f_get_member_info(id integer)
returns setof record as --setof是关键字，暂时不清楚其作用；record是返回的数据类型，即“记录类型”数据；
$$--两个美元符必须存在，中间可以填入符合命名规则的字符（如$body$，$abc$），但必须与下方的两个美元符相统一
declare
	rec record;--定义记录类型的变量，用于存储查询的结果
begin
	--开始for循环，执行SELECT语句。注意，第一个loop后没有分号！
	for rec in EXECUTE 'SELECT id,real_name FROM a_account_all' loop
		return next rec;--将查询结果依次追加到rec变量中
	end loop;--for循环结束
	return;
end
$$
language 'plpgsql';

--调用存储过程f_get_member_info示例
-- a_account_all 为存储过程中被查询的表，id和real_name是表中的字段，也是在存储过程中被查询的字段
SELECT * FROM f_get_member_info(1568) as a_account_all(id integer,real_name character varying(50));

---

五、其它

1、存储过程中，使用RAISE NOTICE可以在运行时将变量输出显示，可用于查看循环执行情况等。如下语句中，单引号内为输出内容，%表示变量值，loop_count为要输出显示的变量。这种输出格式与C语言中的printf()有点像。

	RAISE NOTICE '---- 已执行 % 个账号',loop_count;

2、如果存储过程编译时，一直只提示有语法错误，没有说明咋样的错误，那么先检查语言是否为plpgsql。若语言没有错误，则仔细检查begin与end之间的语句是否正确，如嵌套选择和嵌套循环是否都有对应的END。

3、record变量使用示例：

-- FUNCTION: public.test()

-- DROP FUNCTION public.test();

CREATE OR REPLACE FUNCTION public.test()
    RETURNS SETOF record 
    LANGUAGE 'plpgsql'
	
	--这三句话我也还没搞懂其含义，但加上不会报错
    COST 100
    VOLATILE 
    ROWS 1000
    
AS $BODY$
DECLARE
	temp_rec record ;--第一个记录集
	rec record ;--第二个记录集
BEGIN
	--给第一个结果集赋值
	for temp_rec in execute 'SELECT 1,2,3' loop
		return next temp_rec;
		RAISE NOTICE 'temp_rec:%',temp_rec;
	end loop;
	
	--给第二个结果集赋值
	for rec in execute 'SELECT 4,5,6' loop
		return next rec;
		RAISE NOTICE 'rec:%',rec;
	end loop;
	
	--给第二个结果集追加值
	rec := (9,8,7);
	return next rec;
	
	--将两个结果集一起返回
	return ;
END
$BODY$;

ALTER FUNCTION public.test()
    OWNER TO postgres;

调用示例：
SELECT * FROM test() as temp1(num_1 integer,num_2 integer,num_3 integer);