ROracle SELECT语句中的绑定参数 [英] Bound parameters in ROracle SELECT statements

问题描述

在R中使用ROracle时，我想将某些参数绑定到数据，所以我这样做:

When using ROracle in R, I want to bind some parameters to data, so I do this:

> dbh <- dbConnect('Oracle', 'user/[e-mail]:port/sid')
> st <- dbPrepareStatement(dbh, statement="SELECT x FROM mytab WHERE id=:1",
                           bind="character")
> st <- dbExecStatement(st, data.frame(id=c("9ae", "1f3"), stringsAsFactors=F))
> fetch(st)
    x
0 FOO

出乎意料的是，它仅使用数据帧的 first 行来执行SELECT(如果同时使用了两行，则在这种情况下我将获得2条输出行，而不是1)，与ROracle文档相反:

What's unexpected is that it only uses the first row of the data frame to do the SELECT (if it were using both rows, I'd get 2 output rows in this case, not 1), contrary to the ROracle documentation:

dbPrepareStatement的对象 然后将其与 data.frame(应与 的绑定规范中) 要执行的dbExecStatement data.frame的每一行.

The object that dbPrepareStatement produces is then used together with a data.frame (which should agree with the bound specification) in calls to dbExecStatement to be executed for each row of the data.frame.

我做错了吗，还是上面的说法可能只适用于INSERT/UPDATE语句?

Am I doing this wrong, or does the above verbiage perhaps only apply to INSERT/UPDATE statements?

推荐答案

我认为您误会了.

本质上，您的SQL具有一个绑定变量，但是您试图绑定两个值.第二个值被丢弃.也许您可以在SQL中使用IN运算符执行类似的操作.

Essentially, your SQL has one bind variable, but you are attempting to bind two values. The second value gets thrown away. Perhaps you can do something similar using an IN operator in your SQL.

这篇关于ROracle SELECT语句中的绑定参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！