1. 存储过程的创建
create procedure procedure_name([[IN |OUT |INOUT ] param_name param_type...])
comment ''
begin
# 1. 声明变量
declare param_name param_type default 'default_value';
# 2. 变量赋值
set param_name = default_value, param_name2 = default_value2...;
# 3. 循环
while condition do
...
end while;
# 4. 条件判断_if
if condition1 then ...
elseif condition2 then ...
else ...
end if;
# 5. 条件判断_case
case when condition then ...
when condition2 then ...
else ...
case param_name when value1 then ...
when value2 then ...
else ...
end;
参数说明:
参数值 | 说明 |
---|---|
IN、OUT、INOUT | 参数类型 IN:调用者向过程传入值,可为字面量或变量 OUT:过程向调用者返回值,可以返回多个值,且返回值只能是变量 INOUT:既是传入值又是返回值 |
2. 存储过程的删除
drop procedure procedure_name
3. 存储过程的调用
call procedure_name(params)
4. 存储过程遍历结果集
# 声明游标
declare res_list cursor for sql_statement;
# 声明遍历结束标识
declare finished_sign int default 0;
declare continue handler for not found set finished_sign = 1;
# 声明每次遍历的数据字段变量
declare a, b, c varchar(255);
# 打开游标
open res_list;
# 遍历字段赋值
fetch res_list into a,b,c;
# 循环
while finished_sign <> 1 do
...
# 遍历字段赋值
fetch res_list into a,b,c;
end while;
# 关闭游标
close res_list;