MySQL 存储过程及游标使用
时间:2019-02-18 13:34 来源:linux.it.net.cn 作者:IT
一、存储过程创建
1.1、语法
[DELIMITER [Math Processing Error]
]−−声明结束符号，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
$$
调用后的结果：
(责任编辑：IT)
一、存储过程创建
1.1、语法
[DELIMITER [Math Processing Error]
]−−声明结束符号，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
$$
调用后的结果：
(责任编辑：IT) |