MySQL 存储过程 echo "rows affected"而不是所需的结果？

Question

谁能帮我调试这个存储过程吗？它成功完成了创建，但是当我在 NaviCat 中执行它时，使用命令:

CALL getJoinTables('data2013q3', 2013, 2);

虽然它确实生成了我在过程中创建的 View ...

schema      table       alias
data2013q3  b2013q2a    a
data2013q3  b2013q2b    b
master  b2013q2t1   t1
master  b2013q2t2   t2
master  b2013q2t3   t3

NaviCat 返回一个消息框，声明:

affected rows: -6

当查询应返回包含所有上述表的 JOIN 语句时。这是我的程序代码:

CREATE PROCEDURE `common`.`getJoinTables`(IN strSchema varchar(35), IN iYr INT, IN iQtr INT)
BEGIN
DECLARE cursor_end CONDITION FOR SQLSTATE '02000';
DECLARE strPd, strTblPrefix, strBSearch, strMSearch VARCHAR(35);
DECLARE strSqlJoinTables VARCHAR(500);
DECLARE strTableSchema, strTable, strAlias VARCHAR(150);
DECLARE done, i INT DEFAULT 0;
DECLARE cur_tables CURSOR FOR SELECT strTableSchema, table_name, alias FROM common.vw_join_tables;
DECLARE CONTINUE HANDLER FOR cursor_end SET done = 1;

SET strPd = CONCAT(iYr, 'q', iQtr);
SET strTblPrefix = CONCAT('b', strPd);
SET strBSearch = CONCAT(strTblPrefix, '_');
SET strMSearch = CONCAT(strTblPrefix, 't_');
SET @strOut = '';
SET i=0;

SET @strSqlJoinTables = CONCAT('CREATE VIEW common.vw_join_tables AS SELECT table_schema, table_name, REPLACE(table_name, \'', strTblPrefix, '\', \'\') alias
FROM information_schema.TABLES
WHERE (table_schema = \'master\' AND table_name LIKE \'', strMSearch, '\') OR (table_schema = \'', strSchema, '\' AND table_name LIKE \'', strBSearch, '\')
GROUP BY table_schema, table_name
ORDER BY table_schema, table_name' );

DROP VIEW IF EXISTS common.vw_join_tables;

SELECT @strSqlJoinTables;
PREPARE stmt from @strSqlJoinTables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

OPEN cur_tables;
FETCH cur_tables INTO strTableSchema, strTable, strAlias;
read_loop: LOOP
    IF done THEN
        LEAVE read_loop;
    END IF;

    IF i = 0 THEN
        SET @strOut = CONCAT(strTableSchema, '.', strTable, ' ', strAlias, ' ');
    ELSE
        SET @strOut = CONCAT(@strOut, ' JOIN ', strTableSchema, '.', strTable, ' ', strAlias, ' ON a.id=', strAlias, '.id ');
    END IF;

    SET i = i+1;
    FETCH cur_tables INTO strTableSchema, strTable, strAlias;
END LOOP;

CLOSE cur_tables;

SELECT @strOut;
END

Answer 1

我想你想创建一个 stored function而不是存储过程。存储的函数可以定义为返回一个字符串。

例如，您可以像这样更改代码(我将省略大部分不会更改的详细信息):

CREATE FUNCTION `common`.`getJoinTables`(IN strSchema varchar(35), IN iYr INT, IN iQtr INT) 
RETURNS LONGTEXT
BEGIN
DECLARE v_strOut LONGTEXT DEFAULT '';
...
RETURN v_strOut;
END

然后你可以这样调用它:

SELECT getJoinTables('data2013q3', 2013, 2);