在MySQL存储过程中,我如何为通过select语句创建的临时表声明一个游标。
谢谢。DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_foo`$$
CREATE PROCEDURE sp_foo()
BEGIN
DROP TEMPORARY TABLE IF EXISTS `TmpMyTable`;
CREATE TEMPORARY TABLE `TmpMyTable` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`person_id` INT(11) NOT NULL,
`first_name` VARCHAR(60) NOT NULL,
`last_name` VARCHAR(60) NOT NULL,
PRIMARY KEY (`id`),
INDEX `idx_first_name` (`person_id`),
INDEX `idx_first_name` (`first_name`)
) ENGINE=MYISAM
SELECT
id AS `person_id`,
first_name,
last_name
FROM person
;
DECLARE _person_id INT(11);
DECLARE personCursor CURSOR
FOR
SELECT * FROM TmpMyTable;
OPEN personCursor;
LOOP
FETCH personCursor INTO _person_id;
...
END LOOP;
END$$
DELIMITER ;