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)例子

NoteOfMySQL-09-存储过程与函数

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,参数同上

NoteOfMySQL-09-存储过程与函数

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 例子

NoteOfMySQL-09-存储过程与函数

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相同。

NoteOfMySQL-09-存储过程与函数

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)

NoteOfMySQL-09-存储过程与函数

上一篇:Oracle中Basic与TNS的区别及与Oracle三种连接方式的关系


下一篇:Linux 常用命令记录