【SQL】过程化SQL

课程基于 高等教育出版社《数据库系统概论（第6版）》王珊、杜小勇、陈红

过程化SQL

相关内容在书本的 P250

过程化SQL是对基本SQL的扩展，在其基础上添加了一些控制过程的语句。

过程化SQL的基本结构是块，每个块可以包含定义部分和执行部分，块与块之间可以相互嵌套；

-- 定义部分
DECLARE
变量、常量、游标、异常等
-- 执行部分
BEGIN
	-- SQL语句、过程化SQL控制语句
	EXCEPTION -- 遇到不能继续执行情况称为异常
	-- 异常处理部分
END;

变量定义

-- 变量定义
变量名 数据类型 [[NOT NULL]:=初值表达式]
变量名 数据类型 [[NOT NULL] 初值表达式]
-- 变量赋值
变量名:=表达式
-- 常量定义
常量名 数据类型 CONSTANT:=常量表达式 -- 常量必须要在定义的时候赋值，且后续不可以修改

流程控制

条件判断

IF语句可以嵌套

IF 条件 THEN
	-- 条件为真时执行的操作
END IF;

IF 条件 THEN
	-- 条件为真时执行
ELSE
	-- 条件为假时执行
END IF;

循环控制语句

死循环

1
2
3

LOOP
	-- 循环体
END LOOP;

这个循环体没有类似while(条件)一样的循环终止，而是一个死循环，一般的关系型数据库都会提供EXIT\BREAK\LEAVE等语句来退出这个循环

WHILE循环

1
2
3

WHILE 条件 LOOP
	-- 循环体
END LOOP;

这个就是我们编程语言中常用的WHILE循环了，没啥好说的。而且同样支持提前退出的语句。

FOR-LOOP

1
2
3

FOR count IN [REVERSE] bound1 .. bound2 LOOP
	-- 循环体
END LOOP;

这个循环体的基本步骤：将count变量设置为循环下界bound1，检查他是否小于上界bound2，每次循环count+1；

当指定REVERSE的时候，将count设置为bound2，判断它是否大于下界bound1（反向迭代），每次循环count-1；

下面是一个示例，遍历PC表中的price字段，并将其speed大于pspeed部分求和

FOR getprice IN (select price from PC where speed>pspeed) LOOP
   mCount:= mCount +1;
   total:= total+ getprice; 
END LOOP;

游标的概念和使用过程

游标的概念、使用过程 p253

在过程化SQL中，如果SELECT语句只返回一条记录，可以将该结果存放到变量中，当查询返回多条记录的时候，就要使用用表对结果集进行处理。

一个游标和一个SQL语句相关联。

使用游标的步骤

1.声明/定义游标

1
2
3

DECLARE 游标名 [(参数1 数据类型,参数2 数据类型,...)]
CURSOR FOR 
SELECT 语句;

定义游标仅仅是一条说明性语句，这里的SELECT语句并没有被立刻执行

2.打开游标

1	OPEN 游标名 [(参数1 数据类型,参数2 数据类型,...)];

打开游标的时候会执行对应的SELECT语句，并把查询结果取到缓冲区中。此时游标处于活动状态，指针指向查询结果集中的第一条记录。

3.使用游标

推进游标并读取当前记录

1	FETCH 游标名 INTO 变量1[,变量2,....];

其中变量必须和SELECT语句中的目标列表达式具有一一对应关系

用FETCH语句将游标指针向前推进一条记录，同时将缓冲区中的记录取出送至变量供过程化SQL进行进一步处理。通过内环指向FETCH语句逐条取走结果集中的行进行处理。

4.关闭游标

释放结果集占用的缓冲区和其他资源

1	CLOSE 游标名;

被关闭的游标可以被再次打开，此时会重新再次执行一次SELECT语句，两次的结果并不一定相同。

存储过程（自定义函数）

创建存储过程

CREATE OR REPLACE PROCEDURE 过程名([
	[IN|OUT|INOUT] 参数1 数据类型,
	[IN|OUT|INOUT] 参数2 数据类型,
	...
])
AS 
<过程块SQL>; -- 这里的过程块SQL就是上面提到的过程化SQL的一个块

存储过程提供了IN,OUT,INOUT三种参数模式，分别是入参、输出参数、输入输出参数。不声明参数类型的时候默认是IN类型。

输入参数需要手动传入
输出参数不需要手动传入，而是以返回值返回
输入输出参数调用的时候需要传入初值，还会返回一个新的值

如果是输出参数，则需要一个变量来存放输出后的值。

示例-实验2中的题目

在PC表中，将某一速度（整型）作为输入参数，求大于该速度的PC机的平均价格。

-- real是浮点型
CREATE OR REPLACE FUNCTION Meanprice (in pspeed int, out mean real) 
AS $$
DECLARE getprice INT; mCount INT; total INT;
BEGIN
	mean:=0; mCount:=0;total:=0; 
	FOR getprice IN select price from PC 
	where speed>pspeed
	LOOP
	   mCount:= mCount +1;
	   total:= total+ getprice; 
	END LOOP;
	mean:= total/ mCount; 
END;
$$ LANGUAGE plpgsql;