在单元格上执行 Oracle 查询的最佳方法?
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/2956559/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to perform an Oracle query on a cell?
提问by Flukey
I have an Excel spreadsheeet with a cell containing a concatenated name and surname (don't ask why). For example, Cell A2: BLOGGSJOE
.
我有一个 Excel 电子表格,其中一个单元格包含一个串联的名字和姓氏(不要问为什么)。例如,单元格A2: BLOGGSJOE
。
On this cell, I would like to run the following SQL and output it to cell A3, A4 and A5:
在此单元格上,我想运行以下 SQL 并将其输出到单元格 A3、A4 和 A5:
SELECT i.id, i.forename, i.surname FROM individual i WHERE UPPER(REPLACE('" & A2 & "', ' ', '')) = UPPER(REPLACE(i.surname|| i.forename, ' ', '')) AND NVL(i.ind_efface, 'N') = 'N'
Any idea how I could perform an Oracle query on each cell and return the result?
知道如何对每个单元格执行 Oracle 查询并返回结果吗?
I have enabled an Oracle datasource connection in Excel, just not sure what to do now.
我在 Excel 中启用了 Oracle 数据源连接,只是不确定现在该怎么做。
Is this a stupid approach, and can you recommend a better more proficient way?
这是一个愚蠢的方法,你能推荐一个更好更熟练的方法吗?
I am aware that I could just write a simple Ruby/PHP/Python/whatever script to loop through the Excel spreadsheet (or .csv file) and then perform the query etc. but I thought there might be a quick way in Excel itself.
我知道我可以编写一个简单的 Ruby/PHP/Python/任何脚本来循环遍历 Excel 电子表格(或 .csv 文件),然后执行查询等。但我认为 Excel 本身可能有一种快速的方法。
采纳答案by Mike
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Set cn = New ADODB.Connection
cn.ConnectionString = 'Get one from http://www.connectionstrings.com/oracle
'eg. "Driver={Oracle in OraHome92};Dbq=tns;Uid=uid;Pwd=pwd;"
cn.Open
sql = Your SQL
Set rs = cn.Execute(sql)
for loop or something to paste records
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
if you format your spreadsheet properly you could assign rs to a Variant and then assign the variant to a range instead of looping through it.
如果您正确设置了电子表格的格式,您可以将 rs 分配给一个 Variant,然后将该变体分配给一个范围,而不是循环遍历它。
Edit
编辑
With your current SQL you would need to loop through each cell and query the db, then save the results to A3:A5. Edit 2, Something like that:
使用当前的 SQL,您需要遍历每个单元格并查询数据库,然后将结果保存到 A3:A5。编辑 2,类似的东西:
Private Function GetRow(user As String) As Variant
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Dim v As Variant
Set cn = New ADODB.Connection
cn.ConnectionString = "Driver={Oracle in OraHome92};Dbq=tns;Uid=uid;Pwd=pwd;"
cn.Open
sql = "SELECT i.id, i.forename, i.surname FROM individual i WHERE UPPER(REPLACE('" & user & "', ' ', '')) = UPPER(REPLACE(i.surname|| i.forename, ' ', '')) AND NVL(i.ind_efface, 'N') = 'N'"
Set rs = cn.Execute(sql)
v = rs.GetRows()
GetRow = v
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Function
Sub a()
Dim user As String
For i = 2 To x
user = ActiveSheet.Cells(i, 1).Value
GetRow (user)
ActiveSheet.Cells(i, 2).Value = GetRow(0)
ActiveSheet.Cells(i, 3).Value = GetRow(1)
ActiveSheet.Cells(i, 4).Value = GetRow(2)
Next
End Sub
Note that I have used different cells that you wanted and the GetRow is 2D not 1D, but is hard for me to write it up with no access to actual spreadsheet/db
请注意,我使用了您想要的不同单元格,并且 GetRow 是 2D 而不是 1D,但我很难在无法访问实际电子表格/数据库的情况下编写它
回答by Patrick Honorez
Pls check my answer here. Using that code, you can call your Oracle query (recursively or not). I think it would be faster to:
请在这里检查我的答案。使用该代码,您可以调用您的 Oracle 查询(递归或不递归)。我认为这样做会更快:
- first build a list of items to be retrieved (BLOGGSJOE, JOEBAR, JUSTINCASE)
- then retrieve your Oracle data in a separate list using IN (BLOGGSJOE, JOEBAR, JUSTINCASE) as your criteria
- finally make a lookup in Excel on that newly downloaded list
- 首先构建要检索的项目列表(BLOGGSJOE、JOEBAR、JUSTINCASE)
- 然后使用 IN (BLOGGSJOE, JOEBAR, JUSTINCASE) 作为条件在单独的列表中检索您的 Oracle 数据
- 最后在 Excel 中对新下载的列表进行查找