欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql存储过程学习记录 博客分类: database、mysql mysql、存储过程、游标 

程序员文章站 2024-03-22 16:31:52
...

例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_boydSQL组成

         [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    表示调用者可以执行

         COMMENTstring’  注释信息

 

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、流程控制的使用

1IF语句

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

 

2CASE语句

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;

 

3LOOP语句

[begin_label:]LOOP

         statement_list

ENDLOOP[end_label]

 

add_num:LOOP

         SET @count=@count+1;

END LOOPadd_num;

 

4LEAVE语句

跳出循环控制

LEAVE label

 

add_num:LOOP

         SET @count=@count+1;

         LEAVE add_num;

END LOOPadd_num;

 

5ITERATE语句

跳出本次循环,执行下一次循环

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;

 

6REPEAT语句

有条件循环,满足条件退出循环

[begin_label:]REPEAT

         statement_list

         UNTIL search_condition

ENDREPEAT[end_label]

 

REPEAT

         SET @count=@count+1;

         UNTIL @count=100;

ENDREPEAT;

 

7WHILE语句

[begin_label:]WHILEsearch_condition DO

         statement_list

ENDREPEAT[end_label]

 

WHILE@count<100 DO

         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;