是否有任何方法可以存储SELECT结果并同时输出结果集?

Question

在下一个示例过程中，我是否可以将查询表中的行作为过程结果集输出，同时在变量中存储单个字段或某些字段(类似于使用SELECT INTO时)?我能想到的唯一方法就是重复这个查询。

CREATE PROCEDURE `Panel_TerminalesForm`(IN idTerminal INT)
  BEGIN
    declare somefield INT:
    -- this select statement returns the found row as a resultset
    SELECT terminales.*
    FROM terminales
    WHERE id_terminal = idTerminal;
    -- but I also want to have here a table field inside the variable somefield
    -- do some manipulation with somefield...
END;

Answer 1

如果我理解正确的话，你需要创建一个临时表，然后你可以按你想要的方式操作这个临时表。

MySQL:

    CREATE PROCEDURE `Panel_TerminalesForm`(IN idTerminal INT)
        BEGIN
          declare somefield INT
          CREATE TEMPORARY TABLE SampleTempTable
          SELECT * INTO SampleTempTable FROM terminales WHERE id_terminal = idTerminal;
          -- output the resultset
          SELECT * FROM SampleTempTable   
          -- read the variables you want
          SELECT field INTO somefield FROM SampleTempTable   
          -- Drop the temp table
          DROP TEMPORARY TABLE SampleTempTable
    END;

http://www.mysqltutorial.org/mysql-temporary-table/

SQL服务器:

    CREATE PROCEDURE `Panel_TerminalesForm`(IN idTerminal INT)
        BEGIN
          declare somefield INT
          SELECT terminales.* 
          INTO #tempSample FROM terminales
          WHERE id_terminal = idTerminal;
          SELECT * FROM #tempSample
          -- Drop the temp table
          DROP TABLE #tempSample
    END;