MySQL的使用学习之存储过程和函数

存储过程和函数

存储过程和函数可以避免开发人员重复编写相同的sql语句，并且存储过程和函数是在mysql服务器中执行的，可以减少客户端和服务器端的数据传输。

9.1 创建存储过程和函数

创建存储过程和函数是指将经常使用的一组sql语句组合在一起，并将这些sql语句当作一个整体存储在mysql服务器中。

创建存储过程的基本语法如下：

create procedure sp_name([proc_parameter[,…]])

[characteristic…] routine body

其中，sp_name参数是存储过程的名称；proc_parameter表示存储过程的参数列表；characterristic参数指定存储过程的特性；routine_body参数是sql代码的内容，可用begin……end来标志sql代码的开始和结束。

proc_parameter中的每个参数由三部分组成，这三部分分别是输入输出类型、参数名称和参数类型。其形式如下：

[in|out|inout]param_name type

其中，in表示输入参数；out表示输出参数；inout表示既可以是输入也可以是输出；param_name参数是存储过程的参数名称；type参数指定存储过程的参数类型，该类型可以是mysql的任意数据类型。

characteristic参数有多个取值。其取值说明如下：

language sql：说明routine_body部分是由sql语言的语句组成，这也是数据库默认的语言；

[not] deterministic：指明存储过程的执行结果是否是确定的。deterministic表示结果是确定的。每次执行存储过程的时候，相同的输入会得到相同的输出。not deterministic表示结果是非确定的，相同的输入可能得到不同的输出。

{contains sql|no sql|reads sql data|modifies sql data}:指明子程序使用sql语句的限制；contains sql 表示子程序包含sql语句，但不包括读或写数据的语句；no sql表示子程序不包含sql语句；reads sql data表示子程序中包含读数据的语句；modifies sql data表示子程序中包含写数据的语句。默认情况下，系统会指定contains sql。

sqlsecurity {definer|invoker}：指明谁有权限来执行。definer表示只有定义者自己才能执行；invoker表示调用者可以执行；默认情况下，系统指定的权限是definer。

comment ‘string’：注释信息

tips:创建存储过程时，系统默认指定contains sql，表示存储过程中使用了sql语句。但是，如果存储过程中没有使用sql语句，最好设置为no sql。而且，存储过程中最好在comment部分对存储过程进行简单的注释。

下面是一个存储过程的实例：

create procedure num_from_employee(in emp_id int,out count_num int)

reads sql data

begin

select count(*) intocount_numfrom employeewher d_id=emp_id;

end

上述代码中，存储过程名称为num_from_employee；输入变量为emp_id；输出变量为count_num；select语句从employee表中查询d_id等于emp_id的记录，并用count(*)计算d_id值相同的记录的条数。

9.2 创建存储函数

mysql中，创建存储函数的基本形式如下：

create function sp_name([func_parameter[,……]])

returns type

[characteristic……] routine_body

其中，sp_name是存储函数的名称，func_parameter表示存储函数的参数列表；return types指定返回值的类型；characteristic参数指定存储函数的特性，改参数的取值与存储过程中的取值是一样的。routine_body参数是sql代码的内容，可以用begin……end来标志sql代码的开始和结束。

func_parameter可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式如下：

param_name type

其中，param_name参数是存储函数的参数名称；type参数指定存储函数的参数类型，该类型可以使mysql数据库的任意数据类型。

以下是一个名为name_from_employee的存储函数的实例

create function name_from_employee(emp_id int)

returns varchar(20)

begin

return (select name from employee where num_emp_id);

end

上述代码中，name_from_employee是存储函数的名称；该函数的参数为emp_id；返回值是varchar类型；select语句从employee表查询num值等于emp_id的记录，并将该记录的name字段的值返回。

9.3 变量的使用

在存储过程和函数中，可以定义和使用变量。用户可以使用declare关键字来定义变量。然后可以为变量赋值。这些变量的作用范围是begin…end程序段中。

1 定义变量

mysql中使用declare关键字来定义变量。定义变量的基本语法如下：

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

其中，declare关键字是用来声明变量的；var_name变量是参数的名称，这里可以同时定义多个变量，type参数用来指定变量的类型；default value子句将变量默认值设置为value，没有使用default子句时，默认值为null。

2 为变量赋值

mysql中可以使用set关键字来为变量赋值，语法如下：

set var_name = expr[,var_name=expr]…

其中，set关键字是用来为变量赋值的；var_name参数是变量的名称；expr参数是赋值表达式。一个set语句可以同时为多个变量赋值，各个变量的赋值语句之间通过逗号分隔。

mysql中还可以使用select…into语句为变量赋值。基本语法如下：

select col_name[,…] into var_name[,…]

from table_name where condition

其中，col_name参数表示查询的字段名称；var_name参数是变量的名称；table_name参数指表的名称；condition参数指查询条件。

3 定义条件和处理程序

定义条件和处理程序是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决办法，增强程序处理问题的能力，避免程序异常。mysql通过declare关键字来定义条件和处理程序。

定义条件

mysql中使用declare关键字来定义条件，语法如下：

declare condition_name condition for condition_value

condition_value:

sqlstate[value] sqlstate_value | mysql_error_code

其中，condition_name参数表示条件的名称；condition_value参数表示条件的类型；sqlstate_value参数和mysql_error_code参数都可以表示mysql的错误。

定义处理程序

mysql中可以使用declare关键字来定义处理程序，语法如下：

declare handler_type handler for condition_value[,…] sp_statement

handler_type:

contineu|exit|undo

condition_value:

sqlstate[value] sqlstate_value | condition_name | sqlwarning | not found | sqlexception | mysql_error_code

其中，handler_type参数指明错误的处理方式，改参数有3个取值。这三个取值分别是contineu、exit和undo。continue表示遇到错误不进行处理，继续向下执行；exit表示遇到错误马上退出；undo表示遇到错误之后撤回之前的操作，mysql中暂时还不支持这种处理方式。continue_value参数指明错误的类型，该参数有6个取值。sqlstate_value和mysql_error_code与条件定义中的是同一意思。condition_name是declare定义的条件名称。sqlwarning 表示所有已01开头的sqlstate_value的值。not found表示所有以02开头的sqlstate_value值。sqlexception表示所有没有被sqlwarning或not fount捕获的sqlstate_value值。sp_statement表示一些存储过程或函数的执行语句。

tips:通常情况下，执行过程中遇到错误应该立刻停止执行下面的语句并且撤回前面的操作。但是，mysql中现在还不支持undo操作，因此遇到错误时最好执行exit操作。如果事先能够预测错误的类型，并且进行相应的处理，那么可以执行continue操作。

9.4 流程控制的使用

存储过程和函数中中可以使用流程控制来控制语句的执行。mysql中可以使用if语句、case语句、loop语句、leave语句、iterate语句、repeat语句和while语句来进行流程控制。

1 if语句

if语句用来进行条件判断。根据是否满足条件将执行不同的语句。语法如下：

if search_condition then statement_list

[elseif search_condition then statement_list]…

[else statement_list]

endif

其中，search_condition参数表示条件判断语句；statement_list参数表示不同条件的执行语句。

2 case语句

case语句也可用来进行条件判断，其可以实现比if语句更复杂的条件判断。case语句的基本形式如下：

case case_value

then when_value then statement_list

[when when_value then statement_list]…

[else statement_list]

end case

其中，case_value参数表示条件判断的变量；when_value参数表示变量的取值；statement_list参数表示不同when_value值的执行语句。

case语句还有另外一种形式，如下：

case

when search_condition then statement_list

[when search_condition then statement_list]…

[else statement_list]

end case

其中，search_condition参数表示条件判断语句。statement_list参数表示不同条件的执行语句。

3 loop语句

loop语句可以使某些特定的语句重复执行，实现一个简单的循环。但是loop语句本身没有停止循环的语句，必须是遇到leave语句等才能停止循环。loop语句的语法的基本形式如下：

[begin_label:]loop

statement_lise

end loop[end_laben]

其中，begin_label参数和end_label参数分别表示循环开始和结束的标志，这两个标志必须相同，而且都可以省略；statement_list参数表示需要循环执行的语句。

4 leave语句

leave语句主要用于跳出循环的控制。其语法如下：

leave lebel

其中，label参数表示循环的标志。

5 iterate语句

iterate语句也是用来跳出循环的语句。但是iterate语句是跳出本次循环，然后直接进入下一次循环。基本语法如下：

iterate label

其中，label参数表示一个循环的标志。

6 repeat语句

repeat语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。repeat语句的基本语法是：

[begin_label:]repeat

statement_list

until search_condition

end repeat[end_label]

其中，statement_list参数表示循环的执行语句；search_condition参数表示结束循环的条件，满足该条件时循环结束。

7 while语句

while语句也是有条件控制的循环语句。但while语句和repeat语句是不一样的。while语句是当满足条件时，执行循环内的语句。while语句的基本语法如下：

[begin_label:]while search_condition do

statement_list

end while[end_label]

其中，search_condition参数表示循环执行的条件，满足该条件时循环执行；statement_list参数表示循环的执行语句。

9.5 调用存储过程和函数

执行存储过程和存储函数需要有execute权限。mysql中使用call语句调用存储过程和存储函数。调用存储过程或函数后，数据库系统将执行存储过程中的语句。然后将结果返回给输出值。call语句的基本语法如下：

call sp_name([parameter[,…]])

其中，sp_name是存储过程的名称；parameter是指存储过程的函数。

9.6 删除存储过程和函数

mysql中使用drop procedure语句来删除存储过程；通过drop function语句来删除存储函数。其基本形式如下：

drop {procedure | function} sp_name;

其中，sp_name参数表示存储过程或函数的名称。