MySQL 存储过程及游标使用

一、存储过程创建

1.1、语法

[DELIMITER $$ ]  --声明结束符号，mysql默认；为语句结束符，这里声明后当存储过程遇到$$才结束。

CREATE PROCEDURE pro_name ([[ IN |OUT | INOUT ] 参数名 数据类形...])  --pro_name过程名，in、out、inout为过程参数后面例子介绍

BEGIN

  --过程体

END

[$$]

1.2、创建user表数据如下

1.3、参数

1.3.1、IN参数  输入参数（可做查询条件）:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

设置参数调用：

结果及解释：

a、定义userName='lisi'调用过程查出name为lisi的记录。

b、过程中通过set改变userName参数的值，查询为改变后的值。

c、过程解释再查询userName，结果为'lisi'，及修改后变量值不会被返回。

1.3.2、OUT参数   输出参数，用于接受返回结果（无法在调用时指定，其值可在存储过程中改变，并将改变后的值返回。）

调用：

结果及解释：

a、定义值无法传入，第一条查询结果为null。

b、在过程中设值后，开始有值，且值可返回。

1.3.3、 INOUT参数  输入输出参数：调用时指定，并且可被改变和返回。（可做查询条件也可做结果返回）

调用：

结果及解释：

a、传入可直接接受

b、作为其他语句的条件直接使用

c、在过程中改变值，并返回到过程外

1.4 存储过程删除

语法: DROP PROCEDURE IF EXISTS pro_name;

二、存储过程使用游标

2.1 简单游标使用

例：定义如下存储过程

调用： CALL test_cursor();

name为lisi的记录已改为lisi1.

2.2 多游标及游标嵌套使用

例：定义的过程如下

DELIMITER $$
CREATE PROCEDURE test_cursor()
BEGIN 
	/*定义变量*/
	DECLARE userId Long;
	DECLARE userName VARCHAR(255);
	DECLARE userAge INT;
	-- 定义循环标识，默认值为 FALSE
	DECLARE done INT DEFAULT FALSE; 
	-- 定义游标
	DECLARE My_Cursor CURSOR FOR (SELECT id,`name`,age FROM user);
	DECLARE My_Cursor2 CURSOR FOR (SELECT id,`name`,age FROM user WHERE id in(3,4));
	DECLARE My_Cursor3 CURSOR FOR (SELECT id,`name`,age FROM user WHERE id=userId);
	-- 将结束标志绑定到游标，若没有数据返回,程序继续,并将变量done设为TRUE 
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	-- 打开游标
	OPEN My_Cursor;
		myLoop : LOOP
			-- 循环游标中的数据，并赋值到变量中
			FETCH My_Cursor INTO userId,userName,userAge;
			IF done THEN
				LEAVE myLoop;
			ELSE
				IF userName='lisi' THEN
					UPDATE user SET `name`='lisi1' WHERE id=userId;
				END IF;
			END IF;
			COMMIT;
		END LOOP myLoop;
	CLOSE My_Cursor;

	-- 开始第二个游标时先将 done 置为 FALSE
	SET done = FALSE;
	
	-- 打开游标
	OPEN My_Cursor2;
		myLoop2 : LOOP
			-- 循环游标中的数据，并赋值到变量中
			FETCH My_Cursor2 INTO userId,userName,userAge;
			IF done THEN
				LEAVE myLoop2;
			ELSE
				OPEN My_Cursor3;
					myLoop3 : LOOP
						FETCH My_Cursor3 INTO userId,userName,userAge;
						IF done THEN
							LEAVE myLoop3;
						ELSE
							UPDATE user SET `name`=CONCAT(userName,'1','2') WHERE id=userId;
						END IF;
						COMMIT;
					END LOOP myLoop3;
				CLOSE My_Cursor3;
				-- 嵌套使用是内部游标结束后给done置 FALSE
				SET done=FALSE;
			END IF;
		END LOOP myLoop2;
	CLOSE My_Cursor2;
END
$$

调用后的结果：