mysql存储过程学习记要
mysql存储过程学习记录 例1 ：嵌套游标 CREATE PROCEDURE card_rollback()BEGINDECLARE 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
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?1146??SET?@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、调用存储过程
CALL??sp_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;