executeStoredProcedure：执行 SQL 存储过程

executeStoredProcedure：执行注册到数据库的存储过程

用法

  executeStoredProcedure(sqlSP, ..., connectionString = NULL)

参数

sqlSP

有效的 StoredProcedure 对象

...

存储过程的可选输入和输出参数。 必须提供未为其分配默认查询或值的所有参数

connectionString

一个字符串（如果在创建 StoredProcedure 对象时没有连接字符串，则必须提供）。 此函数需要使用支持 ODBC 3.8 功能的 ODBC 驱动程序。

verbose

布尔值。 是否打印用于执行存储过程的命令

值

如果成功，则为 TRUE；如果失败，则为 FALSE

备注

此函数依赖于使用的 ODBC 驱动程序支持 ODBC 3.8 功能。 否则，操作将失败。

示例

 ## Not run:

 # See ?StoredProcedure for creating the "cleandata" table.

 ############# Example 1 #############
 # Create a linear model and store in the "rdata" table.
 train <- function(in_df) {
   factorLevels <- c("Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")
   in_df[,"DayOfWeek"] <- factor(in_df[,"DayOfWeek"], levels=factorLevels)
   # The model formula
   formula <- ArrDelay ~ CRSDepTime + DayOfWeek + CRSDepHour:DayOfWeek

   # Train the model
   mm <- rxLinMod(formula, data = in_df, transformFunc = NULL, transformVars = NULL)

   # Store the model into the database
   # rdata needs to be created beforehand
   conStr <- paste0("Driver={ODBC Driver 13 for SQL Server};Server=.;",
                    "Database=RevoTestDB;Trusted_Connection=Yes;")
   out.table = "rdata"
   # write the model to the table
   ds = RxOdbcData(table = out.table, connectionString = conStr)

   rxWriteObject(ds, "linmod.v1", mm, keyName = "key",
                 valueName = "value")
   return(NULL)
 }

 conStr <- "Driver={ODBC Driver 13 for SQL Server};Server=.;Database=RevoTestDB;Trusted_Connection=Yes;"
 # create  an InputData object for the input data frame in_df
 indata <- InputData("in_df",
                     defaultQuery = paste0("select top 10000 ArrDelay,CRSDepTime,",
                                           "DayOfWeek,CRSDepHour from cleanData"))
 # create the sql server stored procedure object
 trainSP1 <- StoredProcedure('train', "spTrain_df_to_df", indata,
                             dbName = "RevoTestDB",
                             connectionString = conStr,
                             filePath = ".")
 # spRegisterSp and executeStoredProcedure do not require a connection string since we
 # provided one when we created trainSP1
 registerStoredProcedure(trainSP1)
 executeStoredProcedure(trainSP1, verbose = TRUE)


 ############# Example 2 #############
 # score1 makes a batch prediction given clean data(indata),
 # model object(model_param), and the new name of the variable
 # that is being predicted
score1 <- function(in_df, model_param, predVarNameInParam) {
   factorLevels <- c("Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")
   in_df[,"DayOfWeek"] <- factor(in_df[,"DayOfWeek"], levels=factorLevels)
   mm <- rxReadObject(as.raw(model_param))
   # Predict
   result <- rxPredict(modelObject = mm,
                       data = in_df,
                       outData = NULL,
                       predVarNames = predVarNameInParam,
                       extraVarsToWrite = c("ArrDelay"),
                       writeModelVars = TRUE,
                       overwrite = TRUE)
   return(list(result = result, pvOutParam = mm$f.pvalue))
}

# create  an InputData object for the input data frame in_df
indata <- InputData(name = "in_df", defaultQuery = "SELECT top 10 * from cleanData")
# create InputParameter objects for model_param and predVarNameInParam
model <- InputParameter("model_param", "raw",
                       defaultQuery = paste("select top 1 value from rdata",
                                            "where [key] = 'linmod.v1'"))
predVarNameInParam <- InputParameter("predVarNameInParam", "character")
# create OutputData object for the data frame inside the return list
outData <- OutputData("result")
# create OutputParameter object for non data frame variable inside the return list
pvOutParam <- OutputParameter("pvOutParam", "numeric")
scoreSP1 <- StoredProcedure(score1, "spScore_df_param_df", indata, model, predVarNameInParam, outData, pvOutParam,
                           filePath = ".")
conStr <- "Driver={ODBC Driver 13 for SQL Server};Server=.;Database=RevoTestDB;Trusted_Connection=Yes;"
# connection string necessary for registrations and execution
# since we did not pass it to StoredProcedure
registerStoredProcedure(scoreSP1, conStr)
model <- executeStoredProcedure(scoreSP1, predVarNameInParam = "ArrDelayEstimate", connectionString = conStr, verbose = TRUE)
model$data
model$params[[1]]
## End(Not run)