ORA-01795: maximum number of expressions in a list

解决 ora-01795 问题
找出抛异常时执行的SQL语句，貌不惊人啊，很平常一SQL语句，内容类似：SELECT * FROM DUAL T WHERE T.DUMMY IN ('1', '2', '3', ...)，只是IN后括号里的主键值多了些，其它没啥特别的。
看ORA-01795中给出的内容是SQL语句的 expressions 中list接受的最大值是1000，查了下ORA-01795的说明，确定问题出在IN后括号里的主键值超过1000上。
解决思路：将 SQL 中超过1000个的in list转换为 id in (1,2,3...999) or id in (1000,1001....1999) ...
 
namespace MetarNet.MetarView.Toolkit
{
    public class Oracle01795Helper
    {
        private string m_SQL = string.Empty;
        private List<string> m_SqlSpliter = new List<string>();
        public Oracle01795Helper(string m_SQL)
        {
            this.m_SQL = m_SQL;
            m_SqlSpliter.Add(" ");
            m_SqlSpliter.Add("\n");
            m_SqlSpliter.Add("\r");
            m_SqlSpliter.Add("(");
            m_SqlSpliter.Add(")");
            Convert();
        }
        private void Convert()
        {
            if (string.IsNullOrEmpty(m_SQL))
                return;
            string sql = m_SQL.ToLower();
            int index = sql.IndexOf("in");
            Dictionary<string, string> replaceSql = new Dictionary<string, string>();
            while (index >= 0)
            {
                string col;
                int colIndex = GetBeforeWordBeginPosition(sql, index, out col);
                if (colIndex > 0)
                {
                    string condition = string.Empty;
                    int conditionIndex = GetAfterWordEndPosition(sql, index, out condition);
                    if (conditionIndex >= 0)
                    {
                        string[] ids = SplitCondition(condition);
                        if (ids.Length < 1000)
                        {
                            index = sql.IndexOf("in", index + 1);
                            continue;
                        }
                        else
                        {
                            // 分解 id in list
                            string convertedSql = ConvertCondition(col, ids);
                            string key = m_SQL.Substring(colIndex, conditionIndex - colIndex + 1);
                            if (!replaceSql.ContainsKey(key))
                                replaceSql.Add(key, convertedSql);
                        }
                    }
                }
                // 处理下一个 in
                index = sql.IndexOf("in", index + 1);
            }
            if (replaceSql.Count > 0)
            {
                foreach (string key in replaceSql.Keys)
                {
                    m_SQL = m_SQL.Replace(key, replaceSql[key]);
                }
            }
        }
        private int GetBeforeWordBeginPosition(string sql, int inIndex, out string beforeIn)
        {
            System.Collections.Generic.Stack<string> beforeInCharList = new Stack<string>();
            bool beginInput = false;
            int i;
            for (i = inIndex - 1; i >= 0; i--)
            {
                string currentChar = sql.Substring(i, 1);
                if (!beginInput)
                {
                    if (m_SqlSpliter.Contains(currentChar))
                    {
                        beginInput = true;
                        continue;
                    }
                    else
                    {
                        beforeIn = string.Empty;
                        return -1;
                    }
                }
                if (beginInput)
                {
                    if (m_SqlSpliter.Contains(currentChar))
                    {
                        if (beforeInCharList.Count == 0)
                            continue;
                        else
                            break;
                    }
                    beforeInCharList.Push(currentChar);
                }
            }
            StringBuilder sbBeforeIn = new StringBuilder();
            while (beforeInCharList.Count > 0)
                sbBeforeIn.Append(beforeInCharList.Pop());
            beforeIn = sbBeforeIn.ToString();
            return i;
        }
        private int GetAfterWordEndPosition(string sql, int inIndex, out string afterIn)
        {
            System.Collections.Generic.Queue<string> afterCharList = new Queue<string>();
            afterIn = string.Empty;
            bool afterInput = false;
            bool lookupBracket = false;
            int i;
            bool isInContent = false;    // 标示当前字符是否在 '' 之间
            bool hasComma = false;
            for (i = inIndex + 2; i < sql.Length; i++)
            {
                string currentChar = sql.Substring(i, 1);
                if (!afterInput)
                {
                    if (m_SqlSpliter.Contains(currentChar))
                    {
                        afterInput = true;
                        continue;
                    }
                }
                if (afterInput & !lookupBracket)
                {
                    if (currentChar != "(" && m_SqlSpliter.Contains(currentChar))
                    {
                        continue;
                    }
                    else
                    {
                        if (currentChar == "(")
                        {
                            lookupBracket = true;
                            afterCharList.Enqueue(currentChar);
                            continue;
                        }
                        else
                        {
                            // in 关键字后面第一个字符不是 (
                            return -1;
                        }
                    }
                }
                if (lookupBracket)
                {
                    afterCharList.Enqueue(currentChar);
                    if (currentChar == "'")
                        isInContent = !isInContent;
                    if (!isInContent && currentChar == ",")
                        hasComma = true;
                    if (!isInContent && currentChar == "(") // 不处理括号嵌套的语句
                        return -1;
                    if (!isInContent && currentChar == ")")
                        if (hasComma)
                            break;
                        else
                            return -1;
                }
            }
            StringBuilder sbAfterIn = new StringBuilder();
            while (afterCharList.Count > 0)
                sbAfterIn.Append(afterCharList.Dequeue());
            afterIn = sbAfterIn.ToString();
            if (i == sql.Length)
                return -1;
            else
                return i;
        }
        private string[] SplitCondition(string condition)
        {
            string con = condition.Substring(1, condition.Length - 2);
            return con.Split(',');
        }
        private string ConvertCondition(string col, string[] ids)
        {
            List<List<string>> idList = new List<List<string>>();
            List<string> currentList = null;
            if (ids.Length > 0)
            {
                currentList = new List<string>();
                idList.Add(currentList);
            }
            foreach (string id in ids)
            {
                if (currentList.Count > 999)
                {
                    currentList = new List<string>();
                    idList.Add(currentList);
                }
                currentList.Add(id);
            }
            List<string> sqlList = new List<string>();
            foreach (List<string> list in idList)
            {
                if (list.Count > 0)
                {
                    sqlList.Add(col + " in (" + string.Join(",", list.ToArray()) + ")");
                }
            }
            if (sqlList.Count == 0)
                return string.Empty;
            else
            {
                return " (" + string.Join(" or ", sqlList.ToArray()) + ") ";
            }
        }
        public override string ToString()
        {
            return m_SQL;
        }
    }
}