??常用的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)