vba - VBA + ADODB + Oracle 中的参数化查询

Question

我是使用 Oracle 11g 的新手，在让参数化查询顺利工作时遇到了很多问题。

此代码有效:

    Dim rs As ADODB.Recordset
    Dim con As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim prm As ADODB.Parameter

    Set con = New ADODB.Connection
    With con
        .ConnectionString = GetConnection() '<-- the driver here is Driver={Oracle in OraClient11g_home1_32bit}
        .Open
    End With

    Set cmd = New ADODB.Command

    With cmd
        Set .ActiveConnection = con
        .CommandType = adCmdText
        .CommandText = "SELECT * FROM MPA_LINEPLAN.REF_BRAND_SEASON_DROP WHERE DROP_ID = ?" 
        Set prm = .CreateParameter("dropID", adVarChar, adParamInput, 50, "P_SP19_5")
        .Parameters.Append prm
        Set rs = .Execute
    End With

但是我要运行的实际查询会多次引用 dropID 参数。为了让它工作，我必须一遍又一遍地添加相同的参数。告诉我有没有更好的方法？我尝试了以下方法:

    With cmd
        Set .ActiveConnection = con
        .CommandType = adCmdText
        .CommandText = "SELECT * FROM MPA_LINEPLAN.REF_BRAND_SEASON_DROP WHERE DROP_ID = :dropID" 
        Set prm = .CreateParameter("dropID", adVarChar, adParamInput, 50, "P_SP19_5")
        .Parameters.Append prm
        Set rs = .Execute
    End With

但它击中 unspecified error当我尝试执行到 rs.

另外，假设对于我的特殊情况，存储过程不是最佳选择(即使它应该是最佳选择:-/)

编辑:
实际的查询很长，是为了不让您搜索所有 :dropID。引用文献，我在这里减少了它，但留下了足够的空间来显示多个引用文献。

WITH 
--...
DropDim AS (
SELECT DROP_ID
     , DROP_NAME
     , SEASON_ID
     , SEASON_NAME
     , BRAND_ID
     , SEASON_YEAR
     , 'DROP_' || substr(DROP_ID, LENGTH(DROP_ID),1) AS LP_Join_Drop
     , SEASON_NAME || '_' || SEASON_YEAR AS LP_Join_Season
FROM MPA_LINEPLAN.REF_BRAND_SEASON_DROP
WHERE DROP_ID = :dropID),
--...
LYMap AS
(SELECT DC.DROP_ID
     , DC.CHANNEL_ID
     , BSD.SEASON_YEAR
     , BSD.SEASON_NAME
     , BSD.DROP_NAME
     , FW.WEEKENDINGDATE  AS LY_WEEKENDING_DATE
     , FW.YEARWEEK AS LY_YEARWEEK
FROM MPA_LINEPLAN.REF_DROP_CHANNEL DC
  INNER JOIN MPA_MASTER.FISCALWEEK FW
    ON FW.YEARWEEK BETWEEN DC.LY_START_DT AND DC.LY_END_DT
  INNER JOIN MPA_LINEPLAN.REF_BRAND_SEASON_DROP BSD ON BSD.DROP_ID = dc.DROP_ID
WHERE DC.DROP_ID = :dropID),

LLYMap AS
(SELECT DC.DROP_ID
     , DC.CHANNEL_ID
     , BSD.SEASON_YEAR
     , BSD.SEASON_NAME
     , BSD.DROP_NAME
     , FW.WEEKENDINGDATE  AS LLY_WEEKENDING_DATE
     , FW.YEARWEEK AS LLY_YEARWEEK
FROM MPA_LINEPLAN.REF_DROP_CHANNEL DC
  INNER JOIN MPA_MASTER.FISCALWEEK FW
    ON FW.YEARWEEK BETWEEN DC.LLY_START_DT AND DC.LLY_END_DT
  INNER JOIN MPA_LINEPLAN.REF_BRAND_SEASON_DROP BSD ON BSD.DROP_ID = dc.DROP_ID
WHERE DC.DROP_ID = :dropID  ),
--....

Answer 1

继续使用 qmarks 占位符，只需使用 for循环附加相同的参数对象。具体来说，qmarks 对应于查询中放置的位置。假设以下查询

sql = "SELECT * FROM MPA_LINEPLAN.REF_BRAND_SEASON_DROP" _
        & " WHERE DROP_ID = ? AND DROP_ID2 = ? AND DROP_ID3 = ?" 

With cmd
   Set .ActiveConnection = con
   .CommandType = adCmdText
   .CommandText = sql
   For i = 1 To 3  ' ADJUST TO NUMBER OF PARAMS
      Set prm = .CreateParameter("prm" & i, adVarChar, adParamInput, 50, "P_SP19_5")
      .Parameters.Append prm
   Next i
   Set rs = .Execute
End With

或者，将您的查询转换为 stored procedure (避免在 VBA 中读取非常大的 SQL 字符串或文本文件)，然后定义 一个 范围。

甲骨文

CREATE OR REPLACE PROCEDURE my_procedure_name(dropID IN VARCHAR2) IS
BEGIN
   ...long query using dropID (without any symbol)...
END;
/

VBA

With cmd
   Set .ActiveConnection = con
   .Properties("PLSQLRSet") = TRUE  
   .CommandType = adCmdText
   .CommandText = "{CALL my_procedure_name(?)}"       
   Set prm = .CreateParameter("prm", adVarChar, adParamInput, 50, "P_SP19_5")
   .Parameters.Append prm

   Set rs = .Execute
End With