如何用R的DBI库调用Oracle存储过程并获取输出参数？

你遇到的核心问题是**DBI::dbExecute返回的是SQL语句执行后受影响的行数，而不是存储过程输出参数status的值**——这就是为什么你修改存储过程里的status为3后，返回值还是1的原因，因为PL/SQL块本身执行成功，受影响行数就是1。

要正确捕获存储过程的输出参数，你需要使用支持绑定输出变量的方式调用存储过程，下面是两种可行的解决方案：

方法1：使用DBI::dbSendQuery绑定输出参数 #

这种方法适用于大多数兼容DBI的Oracle驱动，通过显式定义输出参数的模式来获取返回值：

# 定义包含输出参数绑定变量的PL/SQL块
query <- "BEGIN DATATRANS_N.DC_CPT_PATIENT_NEW(:study_id, :subject, :subject_dict, :site, :cancer_type, :comments, :patient_status, :icf_date, :status); END;"

# 准备参数列表，将status标记为输出模式
params <- hold_patient[c("study_id", "subject", "subject_dict", "site", "cancer_type", "comments", "patient_status", "icf_date")]
# 新增输出参数配置：类型为NUMBER，模式为OUT
params$status <- list(type = "NUMBER", mode = "OUT")

# 执行查询并获取结果
res <- DBI::dbSendQuery(con, query, params)
# 提取输出参数的值
status_result <- DBI::dbFetch(res)[[1]]
# 清理结果集（必须执行，避免连接泄漏）
DBI::dbClearResult(res)

# 打印存储过程返回的status值
cat("存储过程返回的status值：", status_result, "\n")

关键说明： #

• 使用命名绑定变量（:参数名）替代sqlInterpolate的占位符，这样可以更清晰地处理输入/输出参数。
• 输出参数需要明确指定mode = "OUT"和对应的类型（这里是NUMBER），驱动才能正确识别并返回该变量的值。

方法2：使用ROracle::dbCallProc（推荐，如果使用ROracle驱动） #

如果你使用的是ROracle驱动（专门针对Oracle的DBI实现），可以直接用dbCallProc方法调用存储过程，语法更简洁：

# 直接调用存储过程，指定输入和输出参数
proc_result <- ROracle::dbCallProc(
  conn = con,
  name = "DATATRANS_N.DC_CPT_PATIENT_NEW",
  params = list(
    study_id = hold_patient$study_id,
    subject = hold_patient$subject,
    subject_dict = hold_patient$subject_dict,
    site = hold_patient$site,
    cancer_type = hold_patient$cancer_type,
    comments = hold_patient$comments,
    patient_status = hold_patient$patient_status,
    icf_date = hold_patient$icf_date,
    # 配置输出参数
    status = list(type = "NUMBER", mode = "OUT")
  )
)

# 提取输出参数的值（可以按索引或名称获取）
status_result <- proc_result$status
# 或者按索引：proc_result[[9]]（因为status是第9个参数）

cat("存储过程返回的status值：", status_result, "\n")

关键说明： #

• dbCallProc是ROracle专门为调用存储过程设计的方法，不需要手动写PL/SQL块，直接传入参数列表即可。
• 输出参数的配置方式和方法1一致，通过list(type = "...", mode = "OUT")定义。

额外注意事项 #

1. 确保你的Oracle驱动支持输出参数绑定：大部分主流驱动（如ROracle、odbc连接Oracle）都支持，但某些轻量驱动可能有限制。
2. 避免使用sqlInterpolate处理存储过程调用：sqlInterpolate主要用于防止SQL注入的普通查询，对于带输出参数的存储过程，直接使用命名绑定变量更可靠。

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