NoteOfMySQL-09-存储过程与函数

  常用的SQL语句在执行时需要先编译，然后执行；而存储过程(Store Procedure)是经编译后存储在数据库中的SQL语句集，在数据库中创建和保存。

一、存储过程与函数的区别

  SQL语句中不能使用存储过程，但可以使用函数。

	存储过程	函数
特点	功能相对负责	功能针对性较强
参数	IN、OUT、INOUT	IN
返回参数	可以返回参数	可以返回值、表对象
返回类型	不需指明返回的类型	需要指明返回的类型，且需包含return语句
函数体	可以使用非确定函数	不能使用非确定函数

二、存储过程与函数

1. delimiter命令

  在MySQL中默认是以分号作为语句的结束标志，但若语句中包含分号，则语句会提前结束，达不到语句应有的功能，因此，通过此命令可修改结束标志的符合。语法如下：

	delimiter $$

2. 存储过程与函数

2.1 创建存储过程

（1）语法格式

create procedure sp_name (proc_parameters[,...]) [characteristic...] routine_body;

（2）参数说明

参数	说明
sp_name	存储过程的名称
routine_body	SQL代码，可以使用begin...end来包含SQL代码
proc_parameters	存储过程的参数，格式为：[IN | OUT | INOUT] param_name type;
characteristic	参数有多种取值，详见下表

characteristic参数	说明
language SQL	说明routine_body部分SQL语句，默认值
[not] deterministic	deterministic说明结构是确定的，即每次执行存储过程，相同的输入总会得到相同的输出；反之（默认值）亦然
{contains SQL | no SQL | reads SQL data | modifies SQL data}	contains SQL表示子程序包含SQL语句，但不包含读或写数据的语句（默认值）；no SQL表示子程序不包含SQL语句；reads SQL data表示子程序中包含读数据的语句；modifies SQL data表示子程序中包含写数据的语句
SQL security {definer | invoker}	definer表示只要定义者才能执行（默认值），invoker表示只有调用者才能执行
comment 'string'	注释信息

（3）例子

2.2 创建函数

create function sp_name ([func_parameter[,...]]) returns type [characteristic...] routine_body;

参数	说明
sp_name	存储过程的名称
routine_body	SQL代码，可以使用begin...end来包含SQL代码
func_parameter	函数参数，格式为：param_name type;characteristic，参数同上

2.3 查看存储过程或函数

show {procedure | function} status [like 'pattern'];

参数中，用来匹配自定义存储过程名称或函数名称，若省略则显示所有的存储过程或函数。

show create {procrdure | function} sp_name;

参数中，sp_name为自定义存储过程名称或函数名称。

select * from information_schema.routines [where routine_name='pattern'];

参数中，pattern为自定义存储过程名称，若省略则显示所有的存储过程。

2.4 修改存储过程或函数

alter procedure sp_name [characteristic...];

参数中，characteristic与创建存储函数时相同。

2.5 删除存储过程或函数

drop {procedure | function} [if exists] sp_name;

2.6 使用存储过程与函数

-- 调用存储过程
call sp_name([param])
-- 调用函数
select sp_name([param])

3. 变量

3.1 定义变量

declare var_name[, ...] type [default_value];

使用declare定义的变量只能在begin…end中有效，且declare语句必须在复合语句的开头。

set @var_name = defalut_value;

使用set定义的变量为用户变量，必须在定义时赋值，可在任意位置定义、不用显式声明类型。

3.2 变量赋值

set var_name = value[, ...];

使用set进行赋值，可同时给多个变量赋值。

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

使用select语句进行赋值，table_expr为select语句中from之后的字句。

3.3 变量使用

@var_name

变量使用直接在其前面添加@符号即可。

3.4 说明

• declare需要在begin…end程序段中才能使用
• 使用set定义的变量可以使用如下语句进行输出

select @var_name [from dual];

3.5 例子

4. 条件与处理

  定义条件和处理主要用于在处理过程中遇到问题时的相应处理步骤。

4.1 定义条件

declare condition_name condition for {SQLstate SQLstate_value | MySQL_error_value};

其中，condition_name是条件名称。

示例代码如下：

-- 捕获SQLstate_value
declare can_not_find condition for SQLstate '13d12';

-- 捕获MySQL_error_code
declare can_not_find condition for MySQ_error_code 1111;

4.2 定义处理程序

declare handler_type handler for condition_value[, ...] sp_statement;

参数	参数值	说明
sp_statement		表示一些存储过程或函数的执行语句
handler_type	continue、exit、undo	continue表示继续执行；exit表示退出；undo表示撤回之前的操作，MySQL暂时不支持这种方式
condition_value	SQLstate SQLstate_value、MySQL_error_code、condition_name、SQLwarning、not found、SQLexception	前两种与条件创建的类似；condition_name表示使用创建的条件名称；SQLwarning表示所有以01开头的SQLstate_value值；not found表示所有以02开头的SQLstate_value值；SQLexception表示其他类型的SQLstate_value值

示例代码如下：

-- 捕获SQLstate_value
declare continue handler for SQLstate '42s02' set @info='can not find';

-- 捕获MySQL_error_code
declare continue handler for 1146 set @info='can not find';

-- 先定义条件，然后调用
declare can_not_find condition for 1146;
declare continue handler for can_not_find set @info='can not find';

-- 使用SQLwarning
declare exit handler for SQLwarning set @info='can not find';

-- 使用not found
declare exit handler for not found set @info='can not find';

-- 使用SQLexception
declare exit handler for SQLexception set @info='can not find';

5. 游标

5.1 声明游标

  游标声明位置必须在处理程序之前，在变量和条件之后。游标是只读的，不能更新的，不能滚动的。游标只能在存储过程或函数中使用。

declare cursor_name cursor for select_statement;

其中，cursor_name为游标名称，select_statement为select子句，且不能带有into子句。

5.2 打开游标

  一个游标可以被打开多次，但每次打开的结果可能会不同。

open cursor_name;

5.3 读取数据

fetch cursor_name into var_name[, ...]

  需要注意，into子句中变量的个数必须与select子句中列的数目相同。

5.4 关闭游标

close cursor_name;

5.5 示例代码：求表中数据的条数，作用与count相同。

6. 流程控制

6.1 if语句

if search_condition then statement_list
[elseif search_condition then statement_list]
...
[else statement_list]
end if;

其中，search_condition为条件判断语句，statement_list为执行语句。

6.2 case语句

	case case_value
	when when_value then statement_list
	[when when_value then statement_list]
	...
	[else statement_list]
	end case;

其中，case_value为条件判断的变量，statement_list为执行语句。

6.3 leave语句

leave label

其中，label为标签，一般结合loop语句使用，表示跳出循环。

6.4 iterate语句

iterate label

其中，label为标签，一般结合loop语句使用，表示跳出本次循环。

6.5 loop语句

[begin_label: ] loop
    statement_list
end loop [end_label]

其中，statement_list为执行语句。

-- loop结合leave、iterate示例代码
add_num: loop
set @count = @count + 1;
if @count = 10 then leave add_num;
elseif mod(@count, 2) = 0 then iterate add_num;
end loop

6.6 repeat语句

[begin_label: ] repeat
    statement_list
    until search_condition
end repeat [end_label]

其中，search_condition为条件判断语句，表示当满足条件时跳出循环语句。

6.7 while语句

[begin_label: ] while search_condition do
    statement_list
end while [end_lable]

其中，search_condition为条件判断语句，表示当满足条件时执行循环语句。

三、系统函数

  常见的系统函数：Mysql常用函数大全(分类汇总讲解)_Mysql_脚本之家 (jb51.net)