[ASP.NET] 解決Oracle IN clause 超過1000個參數

2011-12-21

4289
0

不管在SQL Plus或其他Tool，甚至ASP.NET中，只要出現以下錯誤訊息：
ORA-01795 maximum number of expressions in a list is 1000

不用懷疑，就是你的SQL中，IN的參數超過1000個了。

最快的解決方法有兩種：
1. OR
2. Union

OR Sample如下：

select * from TestTable where ID in (1,2,3,4,...,1000) or ID in (1001,1002,...,2000)

Union Sample如下：


select * from TestTable where ID in (1,2,3,4,...,1000)
union all
select * from TestTable where ID in (1001,1002,...)

在ASP.NET中，Sample如下(使用OR)：

strSQL = "select * from TestTable where {0} ";
int limitCount = 900;
                double dLoopCycle = (IDs.Count / limitCount);
                int loopCycle = Convert.ToInt32(Math.Ceiling(dLoopCycle));
                loopCycle = loopCycle == 0 ? 1 : loopCycle;

                string strIDs = string.Empty;
                string strTempSQL = string.Empty;
                //more than 1000 parameter would raise error
                for (int i = 0; i < loopCycle; i++)
                {
                    int rangeIndex = (i * limitCount);
                    List<STRING> tempList = IDs.GetRange(rangeIndex, Math.Min(limitCount, IDs.Count - rangeIndex));

                    strIDs = string.Join(",", tempList.ToArray());
                    if (i == 0)
                        strTempSQL = string.Format("ID in ({0})", strIDs);
                    else
                        strTempSQL += string.Format("or ID in ({0}) ", strIDs);
                }

strSQL = string.Format(strSQL, strTempSQL);

文章簽名檔

回首頁