VBA + ADODB + 预言机中的参数化查询

我是使用 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

但是当我尝试执行 rs 时，它会unspecified error

。

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

编辑：实际查询很长，为了不让你找到所有: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  ),
--....

继续使用

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

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

或者，将查询转换为存储过程(避免在 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

最好的解决方案是停止使用Oracle的ODBC驱动程序，而是开始使用Oracle的OLEDB作为提供程序。

旧连接字符串： .ConnectionString = "Driver={Oracle in OraClient11g_home1_32bit};UID=MyUname;PWD=MyPW;DBQ=MyDB;"

新连接字符串： .ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=MyDB; User ID=MyUname;Password=MyPW;"

OraOLEDB支持命名参数，这正是我最初想要达到的目标。现在，我可以在 SQL 语句中引用带有前缀:参数名称。

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

这现在有效！

另一种方法是使用 CTE 获得所有标量参数，然后联接回您感兴趣的表：

-- Outer SELECT * due to limitations of ODBC drivers in VBA
SELECT *
FROM
(
 WITH lu as (
   SELECT ? as drop_id 
   FROM dual
    )
  )
  SELECT t1.*
  FROM mpa_lineplan.ref_brand_season_drop t1
  CROSS JOIN lu -- could be inner join or whatever type you are interested in
  WHERE t1.drop_id = lu.drop_id
)

相关内容

最新更新

热门标签：