问题描述:

What I want: I want to handle each row of result in separate query.

What I have now: SELECT field1, field2 INTO @field1, @field2 FROM [QUERY].

Problem: If [QUERY] returns more than one result I'll get an exception.

Question: [main]: How can I store the result into table?.

Question: [sub]: how can I execute separate query for each row of result table?

I.E:

SET result_table = SELECT field1, field2 INTO @field1, @field2 FROM [QUERY]

For each row of result_table do QUERY2

网友答案:

Have you considered using a cursor?

DELIMITER $$
CREATE PROCEDURE sample_procedure
BEGIN
    -- We need this variable to identify the end of our procedure
    DECLARE finished_flag INTEGER DEFAULT 0;
    -- These are variables for the fields you have, match the types.
    DECLARE field1 varchar(100);
    DECLARE field2 int;
    DECLARE field3 datetime;

    -- declare cursor for our select
    DECLARE run_foreach_cursor CURSOR FOR 
    SELECT
        field1,
        field2
    FROM [QUERY];

    -- declare NOT FOUND so we know when we've run out of results
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished_flag = 1;

    -- Open the cursor
    OPEN run_foreach_cursor;

    run_foreach_content: LOOP

        -- Put fields list on this line, this is where they go.
        FETCH run_foreach_cursor INTO field1, field2;

        IF finished_flag = 1 THEN 
            LEAVE run_foreach_content;
        END IF;

        -- Do your query here.
        INSERT INTO [tbl2]
        VALUES
           (
              field1,
              field2 + 7
           );

    -- Marks the end of our loop
    END LOOP run_foreach_content;

    -- Close the cursor so MySQL can free up the query attached to it. 
    CLOSE run_foreach_cursor;

END $$

DELIMITER ;

CALL sample_procedure();
相关阅读:
Top