PostgreSQL的存储过程及基本使用
一、存储过程的结构
一个求长方形面积的存储过程。（当然，这个存储过程在数据库中并没有什么实用意义，这里只是为了说明存储过程的基本结构）
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
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
说明：
- 第一行 用于声明函数名及需要的参数。需要注意参数格式：(变量名1 变量类型 , 变量名2 变量类型,…)
- 第二行 声明返回值的类型。注意这里是returns，有“s”！！！
- 第三行 两个美元符中间可以填入符合命名规则的任意字符，如$body$、$aaaa$。但是下方的美元符必须与这里的保持一致。
- 第四行 declare表示声明变量，可以声明多个变量。
- 第五行 声明了一个integer类型的变量并赋了初始值。
- 第六行 begin表示语句开始
- 第七行 计算长方形的面积
- 第八行 将值返回
- 第九行 end表示语句结束
- 第十一行 声明该存储过程使用的语言。有时遇到编译报错可能是这里写成了’sql’。
调用存储过程：
select area_of_rectangle(5,9);
- 1
结果截图：
二、变量使用
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 := '张静';
- 1
动态赋值：
select name into student_name from class where stu_No = 1;
--或者
execute 'select name from class where stu_No = 1' into student_name;
- 1
- 2
- 3
注意 “:=” 和 “into” 是关键。
三、基本流程语句
参考来源：
https://www.cnblogs.com/lottu/p/7405829.html
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 '我不是张静，也不姓李';
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
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;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
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;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
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;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
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;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
for遍历查询的结果集
[ <<label>> ]
FOR 变量 IN 查询语句 LOOP
循环体语句;
END LOOP [ label ];
–例 - 遍历班级中每个人的名字：
for student_name in select name from class loop
RAISE NOTICE ‘姓名：%’,student_name;
end loop;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
四、查询并返回多条记录
---- 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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
五、其它
1、存储过程中，使用RAISE NOTICE可以在运行时将变量输出显示，可用于查看循环执行情况等。如下语句中，单引号内为输出内容，%表示变量值，loop_count为要输出显示的变量。这种输出格式与C语言中的printf()有点像。
RAISE NOTICE '---- 已执行 % 个账号',loop_count;
- 1
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’
<span class="token comment">--这三句话我也还没搞懂其含义，但加上不会报错</span>
COST <span class="token number">100</span>
VOLATILE
<span class="token keyword">ROWS</span> <span class="token number">1000</span>
AS
B
O
D
Y
BODY
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;
<span class="token comment">--给第二个结果集赋值</span>
<span class="token keyword">for</span> rec <span class="token operator">in</span> <span class="token keyword">execute</span> <span class="token string">'SELECT 4,5,6'</span> <span class="token keyword">loop</span>
<span class="token keyword">return</span> <span class="token keyword">next</span> rec<span class="token punctuation">;</span>
RAISE NOTICE <span class="token string">'rec:%'</span><span class="token punctuation">,</span>rec<span class="token punctuation">;</span>
<span class="token keyword">end</span> <span class="token keyword">loop</span><span class="token punctuation">;</span>
<span class="token comment">--给第二个结果集追加值</span>
rec :<span class="token operator">=</span> <span class="token punctuation">(</span><span class="token number">9</span><span class="token punctuation">,</span><span class="token number">8</span><span class="token punctuation">,</span><span class="token number">7</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">return</span> <span class="token keyword">next</span> rec<span class="token punctuation">;</span>
<span class="token comment">--将两个结果集一起返回</span>
<span class="token keyword">return</span> <span class="token punctuation">;</span>
END
B
O
D
Y
BODY
BODY;
ALTER FUNCTION public.test()
OWNER TO postgres;
调用示例：
SELECT * FROM test() as temp1(num_1 integer,num_2 integer,num_3 integer);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
2469
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
