5.2 函数和过程

发表于 更新于 分类于 ， ， ， Waline： 本文字数： 3.1k 阅读时长 ≈ 3 分钟

5.2 函数和过程

我们已经介绍了SQL语言的几个内建函数。在本节中,我们将演示开发者如何来编写他们自己的函数和过程,把它们存储在数据库里并在SQL语句中调用。
函数对于特定的数据类型比如图像和几何对象来说特别有用。例如,用在地图数据库中的一个线段数据类型可能有一个相关函数用于判断两个线段是否交叠,一个图像数据类型可能有一个相关函数用于比较两幅图的相似性。

使用函数和过程的好处

函数和过程允许”业务逻辑”作为存储过程记录在数据库中,并在数据库内执行。
例如,大学里通常有许多规章制度,规定在一个学期里每个学生能选多少课,在一年里一个全职的教师至少要上多少节课,一个学生最多可以在多少个专业中注册,等等。尽管这样的业务逻辑能够被写成程序设计语言过程并完全存储在数据库以外,但把它们定义成数据库中的存储过程有几个优点。例如,它允许多个应用访问这些过程,允许当业务规则发生变化时进行单个点的改变,而不必改变应用系统的其他部分。
应用代码可以调用存储过程,而不是直接更新数据库关系

如何定义函数 过程 和方法

SQL允许定义函数、过程和方法。可以通过SQL的有关过程的组件来定义,也可以通过外部的程序设计语言来定义,例如Java、C或C++。我们首先查看SQL中的定义,然后在5.2.3节了解如何使用外部语言中的定义。

不同的数据库实现的SQL语法可能不同

我们在这里介绍的是SQL标准所定义的语法,然而大多数数据库都实现了它们自己的非标准版本的语法。例如Oracle(PL/SQL)、 Microsoft SQL Sever(TransactSQL)和PostgreSQL(PL/ pgSQL)所支持的过程语言都与我们在这里描述的标准语法有所差别。我们将在后面用Oracle来举例说明某些不同之处更进一步的详细信息可参见各自的系统手册。尽管我们介绍的部分语法在这些系统上并不支持,但是所阐述的概念在不同的实现上都是适用的,只是语法上有所区别。

5.2.1 声明和调用SQL函数和过程

假定我们想要这样一个函数:给定一个系的名字,返回该系的教师数目。我们可以如下所示定义函数:

1
2
3
4
5
6
7
8
9
create function dept_count(dept_name varchar(20))
returns integer
begin
    declare d_count integer;
    select count(*) into d_count
        from instructor
        where instructor.dept_name = dept_name;
    return d_count;
end;

这个函数可以用在返回教师数大于12的所有系的名称和预算的查询中:

1
2
3
select dept_name,budget
    from department
    where dept_count(dept_name)>12;
1
2
3
4
mysql> select dept_name,budget
    from department
    where dept_count(dept_name)>12;
Empty set

表函数

SQL标准支持返回关系作为结果的函数;这种函数称为表函数(table functions)。考虑图5-6中定义的函数:

1
2
3
4
5
6
7
8
9
10
11
create function instructor_of(dept_name varchar(20))
    returns table(
        ID varchar(5),
        name varchar(20),
        dept_name varchar(20),
        salary numeric(8,2))
return table(
    select ID,name,dept_name,salary
        from instructor
        where instructor.dept_name=instructor_of.dept name;
);

该函数返回一个包含某特定系的所有教师的表。注意,使用函数的参数时需要加上函数名作为前缀( instructor_of.dept_name)。
这种函数可以如下在一个查询中使用:

1
2
select *
from table(instructor_of('Finance'));
1
经过的测试MySQL的函数好像不能返回表,也就是MySQL不支持**返回关系作为结果的函数**

带参数的视图

这个査询返回’金融‘系的所有教师。在上面的简单情况下直接写这个查询而不用以表为值的函数也是很直观的。
但通常以表为值的函数可以被看作带参数的视图( parameterized view),它通过允许参数把视图的概念更加一般化

过程

SQL也支持过程。 dept_count函数也可以写成一个过程:

1
2
3
4
5
6
create procedure
    dept_count_proc(in dept_name varchar(20), out d_count integer)
begin
    select count(*) into d_count
    where instructor.dept_name=dept_count_proc.dept_name;
end;
1
2
3
4
5
6
7
mysql> create procedure
    dept_count_proc( in dept_name varchar(20), out d_count integer)
begin
    select count(*) into d_count
    where instructor.dept_name=dept_count_proc.dept_name;
end;
Query OK, 0 rows affected
  • 关键字in表示待赋值的参数
  • 关键字out为返回结果

调用存储过程

可以从一个SQL过程中或者从嵌入式SQL中使用call语句调用过程:

1
2
declare d_count integer;
call dept_count_proc('Physics', d_count);

过程和函数可以通过动态SQL触发,如5.1.1.4节中JDBC语法所示.

存储过程和函数可”重载”

  • SQL允许多个过程同名,只要同名过程的参数个数不同。名称和参数个数用于标识一个过程。
  • SQL也允许多个函数同名,只要这些同名的不同函数的参数个数不同,或者对于那些有相同参数个数的函数,至少有一个参数的类型不同。