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;