数据库笔记（SQL之函数与存储过程）

1. 函数与存储过程作用概述

数据库的函数以及存储过程是可执行SQL语句的封装，类似于编程语言中的函数或是方法。

虽然SQL有嵌入式SQL的支持，可以将某些数据库业务逻辑写在客户端中，但在数据库中编写函数或是存储过程有以下优点：
1. 当拥有多种客户端时，若要修改业务逻辑，只需要修改函数或者存储过程而无需将各个客户端的代码逐一修改。
2. 可兼容多种客户端平台，当要添加新的平台的客户端时无需重写业务逻辑。
3. 提高客户端性能，对于安装在机器性能低下的客户端程序，将业务逻辑转移到数据库服务器运行可以提高客户端的性能。

使用数据库的函数以及存储过程还有许多优势，这里不一一列举。

2. 函数与存储过程的声明

2.1 函数的声明

与一般的编程语言相似，函数的声明包括函数名、参数、返回值以及函数体组成。函数声明中，括号中的参数，returns子句表明返回值的类型，begin和end中间的便是函数体。具体示例如下：

create function dept_count(dep_name varchar(20))
    returns integer
    begin
    declare d_count integer;
        select count(*) into d_count
        from instructor
        where instructor.dep_name = dep_name
    return d_count;
    end

除了返回固定的数据类型外，函数还支持返回表。这样的函数被称为表函数（Table Function）。表函数的返回值的声明要包含列属性，其声明示例如下：

create function instructors_of (dep_name varchar(20))
returns table (
    ID varchar (5), 
    given_name varchar (20),
    dep_name varchar (20),
    salary numeric (8,2))
 return table
    (select ID, given_name, dep_name, salary
    from instructor 
    where instructor.dep_name = instructor_of.dep_name);

2.2 存储过程的声明

存储过程的声明与函数相似，但与函数不同，存储过程使用in和out关键词来定义输入输出，不使用return或是returns关键词：

create procedure dep_count_proc(in dep_name varchar(20), out d_count integer)
begin
    select count(*) into d_count
        from instructor
        where instructor.dep_name = dep_count_proc.dep_name
end

存储过程的调用使用关键词call进行，另外在使用带返回值的存储过程，需要有自己定义相关变量：

declare d_count integer;
call dept_count_proc('Physic', d_count);

3. 持续性存储模块(PSM)

在以上的声明示例中，有些语句以一般的SQL语句不同。这些与基本可执行SQL不同的，又与一般的编程语言中过程性语句相似的语句被称为持续性存储模块（Persistent Storage Module），它由ISO所定义，且到本文纂写为止，最新的相关标准为ISO/IEC 9075-4:2016。

由于这些语句仅仅是ISO标准语句，对于各个数据库的自定义函数和存储过程的相关代码存在一定差异，可能无法直接应用在实际数据库上。

3.1 基本语法

单独的属性声明使用declare关键词，若想要使用初值，还可以使用default给予变量初值：

declare n integer default 0;

使用set关键词对属性进行赋值：

set n = n − 1;

使用into关键词将查询结果赋值到属性中：

declare currEnrol int;
select count(*) into currEnrol from takes;

3.2 条件语句

条件语句中必须以if开始，end if结束。else以及elseif是可选项。

if (n = 0)
then 
    n = n + 1;
elseif (n = 1)
    n = n - 1;
else 
    n = n + 2;
end if

3.3 循环语句

在SQL:1999中，支持while和repeat两种循环。

While语句：

while (n>10) 
n = n + 1;
end while

Repeat语句：

repeat
n = n + 1; 
until (n > 10) 
end repeat

同时，SQL还支持使用for语句进行迭代循环：

for r as
    select budget from department
    where dep_name = music
do
    set n = n− r.budget
end for

在循环中，可以使用leave语句来跳出循环。