主页 / user-7677

rybosome's questions

Martin Hope
rybosome
Asked: 2012-03-27 11:21:43 +0800 CST
  • 1

我正在从第三方供应商导入数据。我已经将他们的表导入到我现有的数据库中。现在，我只需要遍历他们的每条person记录，稍微调整一下，然后将其插入到我这边的几个表中。不过，我的光标似乎只迭代一次 - 这是代码。

DELIMITER @@

DROP PROCEDURE IF EXISTS import_members@@
CREATE PROCEDURE import_members ()
BEGIN
    -- Declare loop constructs --
    DECLARE done INT DEFAULT FALSE; 

    -- Declare Person variables --
    DECLARE person_id INT;
    DECLARE era_username VARCHAR(100);
    DECLARE last_name VARCHAR(50);
    DECLARE first_name VARCHAR(50);
    DECLARE email VARCHAR(100);
    DECLARE email_primary VARCHAR(50);
    DECLARE degree_id_1 INT;
    DECLARE degree_id_2 INT;
    DECLARE member_status INT;

    DECLARE user_id INT;
    DECLARE user_email VARCHAR(100);

    -- Declare Cursor --
    DECLARE member_cursor CURSOR FOR 
        SELECT person_id, era_username, last_name, first_name, TRIM(email), TRIM(email_primary), degree_id_1, degree_id_2, member_status
        FROM z_data_person
        WHERE member_status IS NOT NULL;

    -- Declare Continue Handler --
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN member_cursor;

    read_loop: LOOP

        -- Fetch data from cursor --
        FETCH member_cursor 
        INTO person_id, era_username, last_name, first_name, email, email_primary, degree_id_1, degree_id_2, member_status;

        -- Exit loop if finished --
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Create User --
        SET user_id = SELECT (MAX(uid) + 1) FROM users;
        IF email_primary IS NOT NULL AND email_primary NOT LIKE '%null%' THEN
            SET user_email = email_primary;
        ELSE
            SET user_email = email;
        END IF;

        INSERT INTO `users` (`uid`, `name`, `pass`, `mail`, `created`)
        VALUES (user_id, era_username, SHA1(RAND()), user_email, UNIX_TIMESTAMP());

        -- Create Member --
        INSERT INTO `members` (`uid`, `first_name`, `last_name`, `phone`, `member_category_id`, `subscription_weekly_email`, `subscription_monthly_newsletter`)
        VALUES (user_id, first_name, last_name, phone_num, member_status, weekly_com, monthly_com);

        -- Add Degrees --
        IF degree_id_1 IS NOT NULL THEN
            INSERT INTO member_degrees_held
            VALUES (user_id, degree_id_1);
        END IF;

        IF degree_id_2 IS NOT NULL THEN
            INSERT INTO member_degrees_held
            VALUES (user_id, degree_id_2);
        END IF;

        -- Add Areas of Expertise --
        INSERT INTO `member_expertises_held` (`uid`, `specialty_id`)
        SELECT (SELECT MAX(uid) FROM users), z.specialty_id
        FROM z_map_person_specialty AS z
        WHERE z.person_id = person_id;

    END LOOP read_loop;

    CLOSE member_cursor;

END; @@

DELIMITER ;
CALL import_members();

就好像SELECT用于填充游标的语句没有返回任何结果。我已经独立测试过了，它确实返回了正确的结果。我试过把 aSELECT person_id直接放在第一个下面FETCH，然后返回NULL。所以......虽然这个查询应该返回结果，但它们并没有将它放入游标的结果集中。我错过了什么吗？

mysql cursors