PGSQL存储过程学习
⼀、存储过程定义：
存储过程（Stored Procedure）是在⼤型中，⼀组为了完成特定功能的SQL 语句集，它存储在数据库中，⼀次后永久有效，⽤户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执⾏它。存储过程是数据库中的⼀个重要对象。在数据量特别庞⼤的情况下利⽤存储过程能达到倍速的效率提升。
⼆、存储过程的结构
  案例：　创建⼀个求长⽅形⾯积的存储过程。
create or replace function area_of_rectangle(lenth integer,height integer)  --存储过程名称与参数【参数格式：(变量名1 变量类型 , 变量名2 变量类型,…)
returns integer as  --有“s”
$$
declare                      --declare表⽰声明变量，可以声明多个变量
area integer := 0;  --定义⾯积变量数据类型
begin
area := lenth * height;  --主逻辑与返回值
return area;              --返回值
end $$ language 'plpgsql';
注意：两个 $$ 符中间可以填⼊符合命名规则的任意字符，如$body$、$aaaa$。但是下⽅的美元符必须与这⾥的保持⼀致。mysql语句的执行顺序
调⽤存储过程：
⼆、变量使⽤
1.变量类型：存储过程中，对变量赋值需要两个值类型⼀致；
注意：record类型变量是“记录类型”的变量，⽤于存储多⾏多列的值。
  按官⽅⽂档的说明，record类型的变量并不是真正的变量，该类型变量在第⼀次赋值前，它有多少列、每⼀列是什么类型都是不确定的。在第⼀次赋值后，该变量就根据值⾃动确定列的数量和各列的类型。
三、赋值
  3.1、静态赋值：
  student_name := '张静';
  3.2、动态赋值：
    select name into student_name from class where stu_No = 1;
    --或者
    execute 'select name from class where stu_No = 1' into student_name;
四、基本流程语句：存储过程中，使⽤RAISE NOTICE可以在运⾏时将变量输出显⽰
4.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 '我不是张静，也不姓李';
4.2、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;
4.3、循环
[ <<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;
[ <<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;
[ <<label>> ]
FOR 循环控制变量 IN [ REVERSE ] 循环范围 [ BY expression ] LOOP
循环体语句;
END LOOP [ label ];
--计算1到100的和：
-
-例1 - 循环执⾏过程类似于：for(i=1;i<=100;i++){}
scalar固态硬盘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;
[ <<label>> ]
FOR 变量 IN 查询语句 LOOP
循环体语句;
END LOOP [ label ];
--例 - 遍历班级中每个⼈的名字：
for student_name in select name from class loop
RAISE NOTICE '姓名：%',student_name;
end loop;
四、查询并返回多条记录
案例1：
排名函数excel公式怎么用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));
CREATE OR REPLACE st()
RETURNS SETOF record
如何开发app软件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 st()
OWNER TO postgres;
-- 调⽤⽰例：
SELECT * FROM test() as temp1(num_1 integer,num_2 integer,num_3 integer);
实战案例：获取数据表中ID最⼤值：
CREATE  TABLE department(
ID INT PRIMARY KEY                        NOT NULL,
d_code                                    VARCHAR(50),
d_name                                    VARCHAR(50)    NOT NULL,
d_parentID                                INT            NOT NULL  DEFAULT 0
);
insert into department values(1,'001','office');
insert into department values(2,'002','office',1);
create or replace function f_getNewID(myTableName text,myFeildName text) returns integer as $$
declare
mysql text;
myID integer;
begin
mysql:='select max('
|| quote_ident(myFeildName)
||') from '
|| quote_ident(myTableName);
execute mysql into myID;
--using myTableName,myFeildName;
if myID is null or myID=0 then return 1;
else return myID+1;
end if;
end;
$$ language plpgsql;
调⽤：
select f_getnewid('department','id');
总结⼀下：
1、存储过程（FUNCITON）变量可以直接⽤  || 拼接。上⾯没有列出，下⾯给个栗⼦；
create or replace function f_getNewID(myTableName text,myFeildName text) returns integer as $$
declare
mysql text;
myID integer;
begin
mysql:='select max('|| $2 || ' ) from '||$1;商家小程序怎么申请
execute mysql into myID using myFeildName,myTableName;
if myID is null or myID=0 then return 1;
else return myID+1;
end if;
end;
$$ language plpgsql;
react框架是什么语言2、存储过程的对象不可以直接⽤变量，要⽤ quote_ident(objVar)
3、$1  $2是 FUNCTION 参数的顺序，如1中的 $1 $2交换，USING 后⾯的不换结果：select max(myTableName) from myFeildname
4、注意：SQL语句中的⼤写全部会变成⼩写，要想⼤写存⼤，必须要⽤双引号。
总结：
1. 运⾏速度：对于很简单的 sql ，存储过程没有什么优势。对于复杂的业务逻辑，因为在存储过程创建的时候，数据库已经对其进⾏了⼀次解析和优化。存储过程⼀旦执⾏，在内存中就会保留⼀份这个存储过程，这样下次再执⾏同样的存储过程时，可以从内存中直接调⽤，所以执⾏速度会⽐普通 s ql 快。
2. 减少⽹络传输：存储过程直接就在数据库服务器上跑，所有的数据访问都在数据库服务器内部进⾏，不需要传输数据到其它服务器，所以会减少⼀定的⽹络传输。但是在存储过程中没有多次数据交互，那么实际上⽹络传输量和直接 sql 是⼀样的。⽽且我们的应⽤服务器通常与数据库是在同⼀内⽹，⼤数据的访问的瓶颈会是硬盘的速度，⽽不是⽹速。
3. 可维护性：的存储过程有些时候⽐程序更容易维护，这是因为可以实时更新 DB 端的存储过程。有些 bug ，直接改存储过程⾥的业务逻辑，就搞定了。
4. 增强安全性：提⾼代码安全，防⽌ SQL 注⼊。这⼀点 sql 语句也可以做到。
5. 可扩展性：应⽤程序和数据库操作分开，独⽴进⾏，⽽不是相互在⼀起。⽅便以后的扩展和 DBA 维护优化。这⾥转载于：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。