RODBC调用MySQL嵌套存储过程无法获取预期结果的问题

1. 修改sp_main代码屏蔽sp_child的输出 #

由于sp_child通过SELECT返回结果集，要阻止该结果集向上传递给调用者，可通过以下两种方式实现：

方法1：用临时表接收sp_child的输出 #

在sp_main中创建临时表，将sp_child的结果插入其中，不对外返回该临时表数据：

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_main`(in param1 int, in param2 int)
BEGIN
declare refid int;
# do some stuff here
select refid;

-- 创建临时表接收sp_child的结果，避免结果集向外传递
CREATE TEMPORARY TABLE IF NOT EXISTS temp_child_output (refid2 int);
INSERT INTO temp_child_output
CALL sp_child(param1, param2, refid);

-- 无需返回临时表内容，因此不执行SELECT
END

方法2：用游标消耗sp_child的结果集 #

声明游标读取sp_child的结果，消耗掉结果集使其不被返回：

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_main`(in param1 int, in param2 int)
BEGIN
declare refid int;
declare temp_refid2 int;
-- 声明游标绑定sp_child的结果集
declare child_cursor cursor for CALL sp_child(param1, param2, refid);

# do some stuff here
select refid;

-- 打开游标并读取结果，消耗结果集
open child_cursor;
fetch child_cursor into temp_refid2;
close child_cursor;
END

2. 在R中获取所有结果集 #

用RODBC包获取多结果集 #

RODBC提供sqlMoreResults()函数切换结果集，可先获取sp_main返回的第一个结果（refid），再处理后续结果：

library(RODBC)

# 建立ODBC连接
conn <- odbcConnect("你的DSN名称")

# 执行存储过程，获取第一个结果集（refid）
refid_result <- sqlQuery(conn, "CALL sp_main(参数1, 参数2)")

# 若需跳过第二个结果集，调用sqlMoreResults()即可
sqlMoreResults(conn)

# 关闭连接
odbcClose(conn)

用DBI+RMariaDB包（推荐） #

DBI标准接口支持遍历多个结果集，更灵活可控：

library(DBI)
library(RMariaDB)

# 建立MySQL连接
conn <- dbConnect(MariaDB(),
                  host = "localhost",
                  user = "root",
                  password = "你的密码",
                  dbname = "你的数据库名")

# 执行存储过程
res <- dbSendQuery(conn, "CALL sp_main(参数1, 参数2)")

# 获取第一个结果集（refid）
refid_result <- dbFetch(res)

# 遍历并丢弃后续结果集（sp_child的输出）
while (!dbHasCompleted(res)) {
  dbFetch(res)
}

# 清理结果对象并断开连接
dbClearResult(res)
dbDisconnect(conn)

内容的提问来源于stack exchange，提问作者AZhu