数据库之函数、存储过程

函数、存储过程

1.函数

函数，常用于对一个或多个输入参数进行操作，主要目的是返回一个结果值，就是一种方法，在postgre里存放的位置叫function，比如创建一个计算长方面积的函数。

举例：建立一个计算长方形面积的函数，代码如下

create or replace function area_of_rectangle(lenth integer,height integer)--声明函数名及需要的参数，(变量名1 变量类型 , 变量名2 变量类型,…)
returns integer as  --returns,注意有s奥
$$
declare                  --表示声明变量area integer := 0;   --函数赋值初始化，用：=
beginarea := lenth * height;return area;           --函数要写返回值
end
$$
language 'plpgsql';   --固定

调用函数：

select area_of_rectangle(5,9);

结果截图：

2.存储结构

存储过程通常包含一系列的SQL语句或其他代码块，主要目的是执行一系列的操作，跟函数差不多，但是它不能用在select后边，不能用于sql语句，不需要返回值，一定要有的话需要用OUT关键字，具体功能我也不太清楚，我觉得用函数更方便。

举例代码如下：

一个求长方体体积的存储过程。

CREATE PROCEDURE volume_of_cuboid(lenth integer,width integer,height integer, OUT volume integer)
LANGUAGE plpgsql AS--跟函数的区别，他的这个LANGUAGE写上边了
$$
DECLAREarea integer := 0;
BEGINarea := lenth * width;volume := area * height;
END;
$$

调用存储过程

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

存储过程的变量类型：

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

3.基本流程语句

3.1 if语句：

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

3.2 case语句：

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

这个例子用select之后会报错，我明天问问我🐕头师父看看怎么事再做修改

2024年5月28日整明白了

举例代码如下：

CREATE OR REPLACE FUNCTION qufen(name VARCHAR)
returns VARCHAR as
$$
DECLARE
notice VARCHAR ;
BEGIN CASE name WHEN '张静','晓静' THENnotice := '张静和晓静都是我的名称';ELSEnotice :=  '你叫错名字了';END case;RETURN notice;END 
$$
LANGUAGE 'plpgsql';

首先最上边理论部分只是简单说了一下形式，实际上当我真正敲代码的时候发现这个代码我写完之后，也套在函数的形式里边了，然后输出的时候，无论我输出哪个人名都是返回的是0，并且前面messa中写 ‘你叫错名字了’。我就很疑惑，然后今天讲解之后发现，首先不能用RAISE NOTICE，用notice才对，通过不同的输入情况给notice赋值，然后我缺少返回值，应该最后返回notice，就能达成根据输入的不同形成输出 ‘张静和晓静都是我的名称’ '你叫错名字了’这两句话，这回调整之后就对了

3.3 2.循环

（1）简单循环 中括号为可选项

[ <<label>> ]
LOOP循环体语句;EXIT [ label ] [ WHEN 判断条件表达式 ];
END LOOP [ label ];--例-计算1到100的和：
sum := 0;
i := 0;
loopi := i + 1;sum := sum + i;exit when i = 100 ;
end loop;
RAISE NOTOCE '1到100的和为：%',sum;

2024年5月27日这个也有问题，我怎么才能不设置参数直接运行呢？

2024年5月28日这个今天也整明白了，见我下边代码

CREATE OR REPLACE FUNCTION yidaoyibaidehe()
returns INTEGER as
$$
DECLARE
sum INTEGER;
i INTEGER;
BEGIN
sum := 0;
i := 0;
loopi := i + 1;sum := sum + i;exit when i = 100 ;
end loop;
RAISE notice '1到100的和为：%',sum;
return sum;
END
$$
LANGUAGE 'plpgsql';

首先，我的函数名后边留个空括号，就是代表不用入参的意思，然后方法体里边因为有sum和i，这两个比不能直接用，得先在declare后边声明一下才能用，所以才会有我昨天的报错“sum和i未声明或者未定义”，就差declare后边声明这一步了，敲里哇，雾我大半宿

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