SQL 存储过程游标应用

1. 存储过程与游标循环

DROP PROCEDURE IF EXISTS LES_PARENT_DATA_MIGRATE;
DELIMITER ;;
CREATE PROCEDURE LES_PARENT_DATA_MIGRATE()
BEGIN

    DECLARE done INT DEFAULT 0;
    DECLARE l_id bigint(20) DEFAULT 0;
    DECLARE t_id bigint(20) DEFAULT 0;
    DECLARE p_id bigint(20) DEFAULT 0;

    DECLARE rs CURSOR FOR
    SELECT a.ID,a.TEACHER_ID,b.PARENT_ID FROM sys_les a,sys_person b
    WHERE a.TEACHER_ID = b.ID AND a.PARENT_ID IS NULL;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN rs;
        -- FETCH rs INTO l_id,t_id,p_id;
        while done = 0 do
            FETCH rs INTO l_id,t_id,p_id;
            IF done<>1 THEN 
               select l_id,t_id,p_id;
            END IF;
        end while;
    CLOSE rs;

END;;
DELIMITER ;
CALL LES_PARENT_DATA_MIGRATE();
DROP PROCEDURE IF EXISTS LES_PARENT_DATA_MIGRATE;