存储过程与存储函数

存储过程与存储函数

存储过程和存储函数是MySQL自5.0版本之后开始支持的过程式数据库对象。他们作为数据库存储的重要功能,可以有效提高数据库的处理速度,同时也可以提高数据库编程的灵活性。

存储过程

存储过程是一组为了完成某特定功能的SQL语句集,其实质上就是一段存放在数据库中的代码,它可由声明式的SQL语句(如create、update和select等语句)和过程式SQL语句(如if-then-else控制结构语句)组成。这组语句集经过编译后会存储在数据库中,用户只需要通过指定存储过程的名字并给定参数(如果该存储过程带有参数),即可随时调用并执行它,而不必重新编译,因此这种通过定义一段程序存放在数据库中的方式,可加大数据库操作语句的执行效率。而前面介绍的各条MySQL数据库操作语句在其执行过程中,需要首先编译,然后执行。尽管这个过程由DBMS自动完成,而且对SQL语句的使用者透明,但这种每次执行之前都需要预先编译,就成了数据库操作语句执行效率的一个瓶颈问题。

一个存储过程是一个可编程的函数,同时可看作是在数据库编程中面向对象方法的模拟。它允许控制数据的访问方式,因为,当希望在不同的应用程序或平台上执行相同的特定功能时,存储过程尤为合适,其通常具有这样一些优点:

  1. 可增强SQL语言的功能和灵活性。存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。
  2. 良好的封装性。存储过程被创建之后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句,并且数据库专业人员可以随时对存储过程进行修改,而不会影响到调用它的应用程序源代码。
  3. 高性能。存储过程执行一次后,其执行规划就驻留在高速缓冲存储器中,在以后的操作中,只需从高速缓冲存储器中调用已编译好的二进制代码执行即可,从而提高了系统性能。
  4. 可减少网络流量。由于存储过程是在服务器端运行,且执行速度快,那么当客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而可降低网络负载。
  5. 存储过程可作为一种安全机制来确保数据库的安全性和数据的完整性。使用存储过程可以完成所有数据库操作,并可以通过编程方式控制这些数据库对数据库信息访问的权限。

创建存储过程

其语法格式如下:

1
2
create procedure sp_name([proc_parameter[, ... ]])
[characteristic ... ] routine_body

其中,proc_parameter的格式为:

1
[in | out | inout] param_name type

type的格式为:

1
any valid mysql data type

characteristic的格式为:

1
2
3
4
5
comment 'string'
| language sql
| [not] deterministic
| {contains sql | no sql | reads sql data | modifies sql data}
| sql security {definer | invoker}

routine_body的格式为:

1
valid sql routine statement

主要语法说明如下:

  1. sp_name:存储过程的名称,默认在当前数据库中创建。需要注意避免与MySQL的内置函数重名。
  2. proc_parameter:存储过程的参数列表。其中,param_name为参数名,type为参数的类型(可以为任何有效的MySQL数据类型)。当有多个参数时,参数列表中彼此之间用逗号隔开。存储过程可以没有参数,此时存储过程的名称的后面也需要加一对括号,也可以有一个或多个参数。
    MySQL存储过程支持三种类型的参数,即输入参数、输出参数和输入输出参数,分别用in、out和inout三个关键字标识。其中,输入参数是使数据可以传递给一个存储过程;输出参数用于存储过程需要返回一个操作结果的情形;而输入输出参数既可以充当输入参数也可以充当输出参数。需要注意的是,参数的取名不要与数据表的列名相同,否则尽管不会返回错误信息,但是存储过程中的SQL语句会把这些参数名看成列名,从而引发不可预知的结果。
  3. characteristic:存储过程的某些特征设定,下面分别介绍:
    • comment ‘string’:用于对存储过程的描述,其中string为描述内容,comment为关键字,这个描述信息可以使用show create procedure语句来显示。
    • language sql:指名编写这个存储过程的语言是SQL语言。目前位置MySQL存储过程还不能用外部编程语言来编写,也就是说,这个选项可以不指定。今后MySQL将会对其进行扩展,最有可能第一个被支持的语言是PHP。
    • deterministic:若设置为deterministic,表示存储过程对同样的输入参数产生相同的结果;若设置为not deterministic,则会产生不确定的结果。默认为not deterministic。
    • contains sql | no sql | reads sql data | modifies sql data:contains sql 表示存储过程包含读或写数据的语句;no sql表示存储过程不包含sql语句;reads sql data表示存储过程包含读数据的语句,但是不包含写数据的语句;modifies sql data表示存储过程包含写数据的语句。若没有明确给定,则默认contains sql。
    • sql security:这个特征用来指定存储过程使用创建该存储过程的用户(definer)的许可来执行,还是使用调用者(invoker)的许可来执行。没有明确指定时,默认为definer。
  4. routine_body:存储过程的主体部分,也称为存储过程体,其包含了在过程调用的时候必须执行的SQL语句。这个部分是以关键字begin开始,并以关键字end结束。若存储过程中只有一条SQL语句时,可以省略begin-end标识。另外,在存储过程体中,begin-end复合语句还可以嵌套使用。

在存储过程的创建过程中,经常会用到一个非常终于的MySQL命令,即delimiter命令。特别是对于通过命令行的方式操作MySQL数据库的使用者,更是要学会使用该命令。

在MySQL中,服务器处理SQL语句默认是以分号作为语句结束的标志。然而,在创建存储过程时,存储过程体中可能包含多条SQL语句,这些SQL语句如果仍以分号作为语句结束符,那么MySQL服务器在处理时会以遇到的第一条SQL语句结尾处的分号作为整个程序的结束符,而不再去处理存储过程体后面的SQL语句,这样显然是不行的。为解决这个问题,通常可以使用delimeter命令,将MySQL语句的结束标志临时修改为其他符号,从而使得MySQL服务器可以完整地处理存储过程体中所有的SQL语句。

delimeter命令的语法格式是:

1
delimeter $$

语法说明如下:

  1. $$是用户定义的结束符号。通常这个符号可以是一些特殊的符号,例如##¥¥等。
  2. 当使用delimeter命令时,应该避免使用反斜杠,因为它是MySQL的转义字符。

例如,将MySQL结束符号修改为两个感叹号:

1
2
# 将MySQL结束符号修改为两个感叹号(这个需要在终端运行)
delimiter !!

在MySQL中创建一个存储过程,用于实现给定表tb_student中一个学生的学号即可修改表tb_student中该学生的性别为另一个指定性别。

1
2
3
4
5
6
use db_school;
delimiter $$
create procedure sp_update_sex(in sno char(20), in ssex char(2))
begin
update tb_student set sex = ssex where studentNo = sno;
end $$

使用说明,在MySQL5.5中创建存储过程,必须具有create routine权限。若要查看数据库中有哪些存储过程,可以使用show procedure status命令;若要查看某个存储过程的具体信息,则可以使用show create procedure sp_name命令,其中sp_name用于指定该存储过程的名称。

存储过程体

在存储过程中可以使用各种SQL语句与过程式语句的组合,来封装数据库应用中复杂的业务逻辑和处理规则,以实现数据库应用的灵活编程。这里,主要介绍几个用于构造存储过程体的常用语法元素。

局部变量

在存储过程体中可以声明局部变量,用来存储存储过程体的临时结果。在MySQL5.5中,可以使用declare语句来声明局部变量,并且同时还可以对该局部变量赋予一个初始值。其语法格式是:

1
declare var_name [, ...] type [default value]

语法说明如下:

  1. var_name:用于指定局部变量的名称;
  2. type:用于声明局部变量的数据类型;
  3. default子句:用于为局部变量指定一个默认值。若没有指定,则默认为null;

例如,声明一个整型局部变量sno。
在存储过程中可以使用如下语句来实现:

1
declare sno char(10);

使用说明:

  • 局部变量只能在存储过程体的begin…end语句块中声明;
  • 局部变量必须在存储过程体的开头处声明;
  • 局部变量的作用范围仅限于声明它的语句块中;
  • 局部变量不同于用户变量,两者的区别是:局部变量声明时在其前面没有使用@符号,并且它只能在声明它的语句块中被使用;而用户变量存在整个会话之中。

set语句

在MySQL5.5中,可以使用set语句为局部变量赋值,其语法格式是:

1
set var_name = expr[, var_name = expr]...

例如:

1
set sno = '2013110101'

select … into语句

在MySQL5.5中,可以使用select…into语句把选定列的值直接存储到局部变量中,其语法格式是:

1
select col_name [, ... ] into var_name [, ... ] table_expr

  • table_expr:表示select语句中的from子句及后面的语法部分。
  • 存储过程体的select…into语句返回的结果集只能有一行数据。

流程控制语句

在MySQL5.5中,可以在存储过程体中使用以下两类用于控制语句流程的过程式SQL语句:

条件判断语句

常用的条件判判断语句有if-then-else语句和case语句。其中if-then-else语句的语法格式如下:

1
2
3
4
if search_condition then statement_list
[elseif search_condition then statement_list] ...
[else statement_list]
end if

  • if-then-else语句不同于系统内置函数if().

case语句在存储过程中使用具有两种语法格式,分别是:

1
2
3
4
5
case case_value
when when_value then statement_list
[when when_value then statement_list] ...
[else statement_list]
end case

或者

1
2
3
4
5
case
when search_condition then statement_list
[when search_condition then statement_list] ...
[else statement_list]
end case
循环语句
  1. while语句:

    1
    2
    3
    4
    5
    [begin_label:]
    while search_condition
    do statement_list
    end while
    [end_label]
  2. repeat语句

    1
    2
    3
    4
    5
    [begin_label:] repeat
    statement_list
    until search_condition
    end repeat
    [end_label]
  3. loop语句

    1
    2
    3
    4
    [begin_label:] loop
    statement_list
    end loop
    [end_label]

此时,在循环体中的语句会被一直重复执行,直至循环使用leave 语句退出,leave语句的语法格式为:leave label,这里的label是loop语句中所标注的自定义名字。

另外,循环语句中还可以使用iterate语句,但它只能出现在循环语句的loop、repeat和while子句中,用于表示退出当前循环,且重新开始一个循环。其语法格式为:iterate label,这里的label同样是循环语句中自定义的标注名字,iterate语句与leave语句的区别在于,leave语句是结束整个循环,而iterate语句只是退出当前循环,然后开始下一轮循环。

游标

在MySQL中,一条select…into语句成功执行后,会返回带有值的一行数据,这行数据可以被读取到存储过程中进行处理。然后使用select语句进行数据检索时,若该语句被成功执行,则会返回一组被称为结果集的数据行,该数据集中可能拥有多行数据,这些数据无法直接被一行一行的处理,此时就需要使用游标。游标是一个被select语句检索出来的结果集。在存储了游标之后,应用程序或用户就可以根据需要滚动或浏览其中的数据。

在目前版本的MySQL中,若要使用游标,需要注意以下几点:

  1. MySQL对游标的支持是从MySQL 5.0开始的,之前的版本不支持游标;
  2. 游标只能用于存储过程或存储函数中,不能单独在查询操作中使用。
  3. 在存储过程或存储函数中可以定义多个游标,但是在一个begin…end语句块中每一个游标的名字必须是唯一的。
  4. 游标不是一条select语句,而是被select语句检索出来的结果集。

在MySQL 5.5 中,使用游标的具体步骤如下:

  1. 声明游标
    在使用游标之前,必须先声明它。这个过程实际上没有检索数据,只是定义使用的select语句。在MySQL5.5中,可以使用declare cursor语句来创建游标,语法格式如下:
    1
    declare cursor_name cursor for select_statement;

语法说明如下:

  • cursor_name:指定要创建的游标的名称,其命名规则与表名相同;
  • sleect_statement:指定一个select语句,其会返回一行或多行数据。注意:这里的select语句中不能有into子句;
  1. 打开游标
    在定义游标之后,必须打开游标才能使用。这个过程实际上是将游标连接到select语句返回的结果集中。在MySQL5.5中,可以使用open语句打开游标,语法格式如下:
    1
    open cursor_name;

在实际应用中,一个游标可以被多次打开,由于其他用户或应用程序可能随时更新来数据表,因此每次打开游标的结果集可能会不同。

  1. 读取数据
    对于填有数据的游标,可以根据需要取出数据。在MySQL5.5中,可以使用fetch…into语句从从读取数据,语法格式为:
    1
    fetch cursor_name into var_name [, varname]...;

fetch…into语句与slect…into语句具有相同的意义,fetch语句是将游标指向的一行数据赋给一些变量,这些变量的数据必须声明游标时select子句中选择列的数目。游标相当于一个指针,它指向当前的一行数据。

  1. 关闭游标
    在结束游标使用时,必须关闭游标。语法格式为:
    1
    close cursor_name;

每个游标不再需要使用的时候都应该被关闭,使用close语句将会释放游标所使用的全部资源。在一个游标被关闭后,如果没有重新被打开,则不能被使用。对于声明过的游标,则不需要再次声明,只需要直接使用open打开即可。另外,如果没有明确关闭游标,MySQL会在到达end语句时自动关闭它。

例如,在数据库db_school中创建一个存储过程,用于计算表tb_student中数据行的行数。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
use db_school;
delimiter $$
drop procedure if exists sp_sumofrow;
create procedure sp_sumofrow (out myrows int)
begin
declare sno char(10);
declare found boolean default true;
declare cur cursor for select studentNo from db_school.tb_student;
declare continue handler for not found set found = false;
set myrows = 0;
open cur;
fetch cur into sno;
while found do
set myrows = myrows + 1;
fetch cur into sno;
end while ;
close cur;
end $$

call sp_sumofrow(@myrows);

select @myrows;

这里我遇到了非常难以解决的错误。最后我幸运的发现,把书上的rows换成myrows并且为sno的类型声明为char(10)就不会出错,猜想这大概是因为rows不能作为参数名吧。

上例中定义例一个continue handler句柄,它是在条件出现时被执行的代码,用于控制循环语句,以实现游标的下移。
declare语句的使用存在特定的次序。用declare语句定义的局部变量必须在定义任意游标会句柄之前定义,而句柄必须在游标之后定义。否则系统会出现错误信息。

调用存储过程

语法格式为:

1
2
call sp_name([parameter[, ... ]]);
call sp_name[()];

语法说明如下:

  • sp_name:指定被调用的存储过程的名称。如果调用某个特定数据库的存储过程,则需要在前面加上该数据库的名称;
  • parameter:指定调用存储过程所要使用的参数。调用语句中参数的个数必须等于存储过程的参数个数;
  • 当调用没有参数的存储过程时,使用call sp_name()语句与使用call sp_name语句是相同的。

例如,调用数据库db_school中的存储过程sp_update_sex,将学号为“2013110201”的学生性别修改为“男”:

1
2
call sp_update_sex('2013110201', '男');
select * from tb_student where studentNo = '2013110201';

删除存储过程

上面的代码中已经展示过。

存储函数

在MySQL中,存在一种与存储过程非常相似的过程式数据库对象——存储函数。它与存储过程一样,都是有SQL语句和过程式语句所组成的代码片段,并且可以被应用程序和其它SQL语句调用。但是存储函数和存储过程仍然存在以下几点区别:

  • 存储函数不能拥有输出参数。这是因为存储函数自身就是输出参数;而存储过程可以拥有输入参数和输出参数以及输入输出参数;
  • 可以直接对存储函数进行调用,且不需要使用call语句;而对存储过程的调用需要使用call语句;
  • 存储函数必须包含一条return语句,而这条特殊的return语句不允许包含于存储过程中。

创建存储函数

语法格式为:

1
2
3
create function sp_name ([func_parameter[, ...]])
returns type
routine_body

其中,func_parameter的格式为:

1
param_name type

type 的格式为:

1
Any valid MySQL data type

routine_body的格式为:

1
Valid SQL routine statement

由于create function语句与前面的create procedure语句的语法大致相同,故这里仅对两者有区别的地方进行说明:

  • sp_name:用于指定存储函数的名称。注意,存储函数不能与存储过程同名。
  • func_parameter:用于声明存储函数的参数。这里的参数只有名称和类型,不能指定关键字in/out/inout。
  • returns子句:用于声明存储函数返回值的数据类型。
  • routine_body:存储函数的主体部分,也称为存储函数体。所有在存储过程中使用的SQL语句在存储函数中同样适用,包括前面介绍的局部变量、set语句、流程控制语句、游标等。但是,存储函数体还必须包含一个return value语句,其中的value用于指定存储函数的返回值。

例如,在数据库db_school中创建一个存储函数,要求该函数能够根据给定的学号返回学生的性别,如果数据库中没有给定的学号,就返回没有该学生。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
use db_school;
delimiter $$
drop function if exists db_school.fn_search;
create function fn_search(sno char(10))
returns char(10)
deterministic
begin
declare ssex char(2);
select sex into ssex from tb_student
where studentNo = sno;
if ssex is null then
return (select '没有该学生');
else if ssex = '女' then
return (select '女');
else return (select '男');
end if ;
end if ;
end $$

select fn_search('2013110201');
select fn_search('2014110201');

# MySQL

评论

程振兴

程振兴 @czxa.top
截止今天,我已经在本博客上写了659.4k个字了!

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×