mysql 函数中循环表数据

DELIMITER //
CREATE PROCEDURE LoopThroughTable()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT;
    DECLARE v_name VARCHAR(255);
    -- 假设有一个名为 `my_table` 的表，包含 `id` 和 `name` 列
    DECLARE cur CURSOR FOR SELECT id, name FROM my_table;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO v_id, v_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 在这里执行对每一行的操作
        SELECT v_id, v_name;
    END LOOP;

    CLOSE cur;
END //
DELIMITER ;

DELIMITER //
CREATE FUNCTION LoopThroughTableFunc() RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN
    DECLARE v_result VARCHAR(1000) DEFAULT '';
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT;
    DECLARE v_name VARCHAR(255);
    -- 创建一个临时表来存储需要循环的数据
    CREATE TEMPORARY TABLE temp_table AS SELECT id, name FROM my_table;
    DECLARE cur CURSOR FOR SELECT id, name FROM temp_table;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO v_id, v_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 在这里执行对每一行的操作，并将结果拼接起来
        SET v_result = CONCAT(v_result, 'ID: ', v_id, ', Name: ', v_name, '; ');
    END LOOP;

    CLOSE cur;
    -- 删除临时表
    DROP TEMPORARY TABLE IF EXISTS temp_table;

    RETURN v_result;
END //
DELIMITER ;