DROP PROCEDURE IF EXISTS Proc_Cursor;
DELIMITER ;;
CREATE PROCEDURE Proc_Cursor( IN iPhone VARCHAR(11) )
BEGIN
DECLARE Done INT DEFAULT FALSE; -- 遍历数据结束标志定义
DECLARE strName VARCHAR(128) CHARACTER SET utf8;
DECLARE strAddress VARCHAR(128) CHARACTER SET utf8;
DECLARE rs CURSOR FOR SELECT username, address FROM dbname.tablename WHERE mobile = iPhone; -- 定义游标
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = TRUE; -- 游标结束/异常处理
OPEN rs; -- 结束游标
FETCH NEXT FROM rs INTO strName, strAddress; -- 遍历游标取值
REPEAT
IF NOT Done THEN
-- FETCH NEXT FROM rs INTO strName, strAddress; -- 遍历游标取值放在此处则后面不需要再放
START TRANSACTION; -- 开始事务
INSERT INTO dbname.tablename2( username, phone, address ) VALUES( strName, iPhone, strAddress );
SELECT * FROM dbname.tablename2;
COMMIT; -- 提交事务
END IF;
FETCH NEXT FROM rs INTO strName, strAddress; -- 循环遍历处理
UNTIL Done END REPEAT; -- 结束遍历
CLOSE rs; -- 关闭游标
END;;
DELIMITER ;
MySQL 存储过程中的游标使用
最新推荐文章于 2024-07-28 21:37:11 发布