mysql存储过程学习记录
例1 ：嵌套游标
create procedure card_rollback()begin declare done int default 0; -- 游标结束标志 declare value_ int ; declare cur cursor for select id from test ;-- table or view declare continue handler for not found set done = 1 ; -- 读取完是标志结束 open cur; set @@autocommit=0; -- 手动提交 repeat fetch cur into value_ ; -- 重游标中取值 if not done then select '1' ; -- do something begin declare cur_0 cursor for select id from test ;-- 嵌套游标 end ; end if; until done end repeat; close cur; end ;
?
例2：异常处理
delimiter // -- 重新定义换行符drop procedure if exists t_insert_table//create procedure t_insert_table()begin /** 标记是否出错 */ declare t_error int default 0; declare t_warn int default 0; /** 如果出现sql异常，则将t_error设置为1后退出操作 */ declare continue handler for sqlwarning set t_warn = 1; -- 出错处理 declare exit handler for sqlexception set t_error = 1 ; /** 显式的开启事务，它开启后，事务会暂时停止自动提交*/ -- start transaction; /** 关闭事务的自动提交 */ set autocommit = 0; insert into t_bom_test(parent_id,child_id) values('d','abc'); insert into t_trigger_test(name,age) values('zhangsan',null); /** 标记被改变,表示事务应该回滚 */ if t_error=1 then select 'ee' ; rollback; -- 事务回滚 else commit; -- 事务提交 end if; -- rollback; -- commit;end//delimiter ;
?
?
语法定义：
14.1、创建存储过程和函数
14.1.1、创建存储过程
create?proceduresp_name?([proc_parameter[,...]])
[characteristic...]?routine_body
?
procedure?发音?[pr?'si:d]
?
proc_parameter?in|out|inout?param_name?type
characteristic?n.?特征；特性；特色
?languagesql?默认，routine_boyd由sql组成
?[not]deterministic指明存储过程的执行结果是否是确定的，默认不确定
?constainssql?|?no?sql?|?reads?sql?data?|?modifies?sql?data指定程序使用sql语句的限制
constains?sql?子程序包含sql，但不包含读写数据的语句，默认
no?sql子程序中不包含sql语句
reads?sql?data子程序中包含读数据的语句
modifies?sql?data子程序中包含了写数据的语句
?sqlsecurity?{definer|invoker}，指明谁有权限执行。
definer，只有定义者自己才能够执行，默认
invoker表示调用者可以执行
?comment‘string’注释信息
?
createprocedure?num_from_employee?(in?emp_id,?int,?out?count_num?int)
?reads?sql?data
?begin
selectcount(*)intocount_num
fromemployee
whered_id=emp_id;
?end
14.1.2、创建存储函数
create?functionsp_name?([func_parameter[,...]])
returns?type
[characteristic...]?routine_body
createfunction?name_from_employee(emp_id?int)
?returnsvarchar(20)
?begin
return?(select?name?from?employee?wherenum=emp_id);
?end
14.1.3、变量的使用
1．定义变量
declare?var_name[,…]type?[default?value]
?
declaremy_sql?int?default?10;
?
2．为变量赋值
setvar_name=expr[,var_name=expr]…
?
select?col_name[,…]into?var_name[,…]?from?table_name?where?condition
?
14.1.4、定义条件和处理程序
1．定义条件
declare?condition_namecondition?for?condition_value
condition?value:
?sqlstate[value]?sqlstate_value?|?mysql_error_code
?
对于error?1146(42s02)
sqlstate_value:?42s02
mysql_error_code:1146
//方法一
declare?can_not_find?condition?for?sqlstate?‘42s02’
//方法二
declare?can_not_find?condition?for?1146
?
2．定义处理程序
declarehander_type?handler?for?condition_value[,…]?sp_statement
?
handler_type:
?continue|exit|undo
condition_value:
sqlstate[value]?sqlstate_value?|?condition_name?|sqlwarning|notfound|sqlexception|mysql_error_code
?
undo目前mysql不支持
?
1、捕获sqlstate_value
declare?continue?handler?for?sqlstate?‘42s02’?set?@info=’cannot?find’;
2、捕获mysql_error_code
declare?continue?handler?for?1146set?@info=’can?not?find’;
3、先定义条件，然后调用
declare?can_not_find?condition?for?1146;
declare?continue?handler?for?can_not_find?set?@info=’cannot?find’;
4、使用sqlwarning
declare?exithandler?for?sqlwarning?set?@info=’cannot?find’;
5、使用not?found
declare?exit?handler?for?not?found?set?@info=’cannot?find’;
6、使用sqlexception
declare?exit?handler?for?sqlexception?set?@info=’cannot?find’;
?
14.1.5、光标的使用
存储过程中对多条记录处理，使用光标
1．声明光标
declarecousor_name?coursor?for?select?statement;
?
declarecur_employee?cursor?for?select?name,?age?from?employee;
?
2．打开光标
opencursor_name;
?
opencur_employee;
?
3．使用光标
fetchcur_employee?into?var_name[,var_name…];
?
fetch?cur_employeeinto?emp_name,?emp_age;
?
4．关闭光标
closecursor_name
?
close?cur_employee
14.1.6、流程控制的使用
1．if语句
ifsearch_condition?then?statement_list
?[elseif?search_condition?thenstatement_list]…
?[else?statement_list]
end?if
?
if?age>20then?set?@count1=@count1+1;
?elseif?age=20?then?@count2=@count2+1;
?else?@count3=@count3+1;
end
?
2．case语句
case?case_value
?when?when_value?then?statement_list
?[when?when_value?then?statement_list]…
?[else?statement_list]
end?case
?
case
?when?search_condition?thenstatement_list
?[when?search_condition?thenstatement_list]…
?[else?statement_list]
end?case
?
case?age
?when?20?then?set?@count1=@count1+1;
?else?set?@count2=@count2+1;
end?case;
?
case
?where?age=20?then?set@count1=@count1+1;
?else?set?@count2=@count2+1;
end?case;
?
3．loop语句
[begin_label:]loop
?statement_list
endloop[end_label]
?
add_num:loop
?set?@count=@count+1;
end?loopadd_num;
?
4．leave语句
跳出循环控制
leave?label
?
add_num:loop
?set?@count=@count+1;
?leave?add_num;
end?loopadd_num;
?
5．iterate语句
跳出本次循环，执行下一次循环
iterate?label
?
add_num:loop
?set?@count=@count+1;
?if?@count=100?then?leave?add_num;
?elseif?mod(@count,3)=0?then?iterateadd_num;
?select?*?from?employee;
end?loopadd_num;
?
6．repeat语句
有条件循环，满足条件退出循环
[begin_label:]repeat
?statement_list
?until?search_condition
endrepeat[end_label]
?
repeat
?set?@count=@count+1;
?until?@count=100;
endrepeat;
?
7．while语句
[begin_label:]whilesearch_condition?do
?statement_list
endrepeat[end_label]
?
while@count
?set?@count=@count+1;
endwhile;
14.2、调用存储过程和函数
存储过程是通过call语句来调用的。而存储函数的使用方法与mysql内部函数的使用方法是一样的。执行存储过程和存储函数需要拥有execute权限。execute权限的信息存储在information_schema数据库下面的user_privileges表中
14.2.1、调用存储过程
callsp_name([parameter[,…]])?;
?
14.2.2、调用存储函数
存储函数的使用方法与mysql内部函数的使用方法是一样的
?
14.3、查看存储过程和函数
show?{?procedure|?function?}?status?[?like'?pattern?'?];
show?create?{procedure?|?function?}?sp_name?;
select?*?frominformation_schema.routines?where?routine_name='?sp_name?'?;
?
14.4、修改存储过程和函数
alter?{procedure|?function}?sp_name?[characteristic?...]
characteristic:
{?contains?sql?|no?sql?|?reads?sql?data?|?modifies?sql?data?}
|?sql?security?{definer?|?invoker?}
|?comment'string'
?
14.5、删除存储过程和函数
drop?{procedure|?function?}?sp_name;