处理要包含在oracle select语句中的大量数据

本文关键字：数据 语句 select 包含 oracle 处理 | 更新日期: 2023-09-27 18:27:18

最近的错误报告指出，被调用的方法正在崩溃服务，导致服务重新启动。经过故障排除，发现原因是传递了数千个字符串的令人讨厌的Oracle SQL调用。从外部服务传递给方法的字符串集合通常超过10000条记录。原始代码使用LIKE关键字对传递的集合使用where子句，我认为这非常非常糟糕。

public IList<ContainerState> GetContainerStates(IList<string> containerNumbers)
{
    string sql = 
    String.Format(@"Select CTNR_NO, CNTR_STATE FROM CONTAINERS WHERE CTRN_SEQ = 0 AND ({0})", 
        string.Join("OR", containerNumbers
                .Select(item => string.Concat(" cntr_no LIKE '", item.SliceLeft(10), "%' ")))
    );
    return DataBase.SelectQuery(sql, MapRecordToContainerState, new { }).ToList();
}

澄清可能令人困惑的内部方法：

DataBase.SelectQuery是一个使用泛型的内部库方法，它传递sql字符串、将记录映射到.NET对象的函数以及正在传递的参数，并返回由Mapping函数重新调整类型的对象的IEnumerable。

SliceLeft是另一个内部帮助程序库的扩展方法，它只返回字符串的第一部分，最多返回参数指定的字符数。

之所以明显使用LIKE语句，是因为传递的字符串和数据库中的字符串只能保证与前10个字符匹配。示例（传递的字符串中的"XXXX00000-1"应与类似"XXXX00000-8"的数据库记录匹配）。

我相信使用SUBSTR的IN子句比使用多个LIKE子句更有效，并将代码替换为：

public IList<ContainerRecord> GetContainerStates(IList<string> containerNumbers)
{
    string sql = 
String.Format(@"Select CTNR_NO, CNTR_STATE FROM CONTAINERS WHERE CTRN_SEQ = 0 AND ({0})", 
              string.Format("SUBSTR(CNTR_NO, 1, 10) IN ({0}) ", 
                            string.Join(",", containerNumbers.Select(item => string.Format("''{0}''",  item.SliceLeft(10) ) ) )
                            )
              );
    return DataBase.SelectQuery(sql, MapRecordToContainerState, new { }).ToList();
}

这略有帮助，在我的测试中问题也较少，但当传递了大量记录时，仍然会引发异常，并发生核心转储，因为SQL比服务器在这段时间内解析的时间长。DBA建议保存传递到临时表的所有字符串，然后根据该临时表进行连接。

根据这个建议，我将功能更改为：

public IList<ContainerRecord> GetContainerStates(IList<string> containerNumbers)
{
    string sql = 
@"
        CREATE TABLE T1(cntr_num VARCHAR2(10));
        DECLARE GLOBAL TEMPORARY TABLE SESSION.T1 NOT LOGGED;
        INSERT INTO SESSION.T1 VALUES (:containerNumbers);
        SELECT 
                DISTINCT    cntr_no, 
                            '_IT' cntr_state 
        FROM 
                tb_master 
        WHERE 
                cntr_seq = 0 
            AND cntr_state IN ({0})
            AND adjustment <> :adjustment
            AND SUBSTR(CTNR_NO, 1, 10) IN (SELECT CNTR_NUM FROM SESSION.T1);
";
        var parameters = new
        {
            @containerNumbers = containerNumbers.Select( item => item.SliceLeft(10)).ToList()
        };
    return DataBase.SelectQuery(sql, MapRecordToContainerState, parameters).ToList();
}

现在我得到了一个"ORA-00900:无效SQL语句"。这真的很令人沮丧，我如何才能正确地编写一个SQL语句，将这个字符串列表放入一个临时表中，然后在SELECT语句中使用它来返回我需要的列表？

处理要包含在oracle select语句中的大量数据

有几个可能的地方会导致这个错误，它表明"DECLARE GLOBAL TEMPORARY"是JAVA API，我认为.net没有这个功能。请尝试"创建全局临时表"。而且，我不知道您的内部API是否可以在一个选择sql中处理多个sql。据我所知，ODP.net命令类每次调用只能执行一个sql。此外，"创建表"是一个DDL，因此它有自己的事务。我看不出有什么理由我们应该把它们放在同一个sql中执行。以下是ODP.net的示例代码，

using (OracleConnection conn = new OracleConnection(BD_CONN_STRING))
        {
            conn.Open();
            using (OracleCommand cmd = new OracleCommand("create global temporary table t1(id number(9))", conn))
            {
                // actually this should execute once only
                cmd.ExecuteNonQuery();
            }
            using (OracleCommand cmd = new OracleCommand("insert into t1 values (1)", conn)) {
                cmd.ExecuteNonQuery();
            }
            // customer table is a permenant table 
            using (OracleCommand cmd = new OracleCommand("select c.id from customer c, t1 tmp1 where c.id=tmp1.id", conn)) {
                cmd.ExecuteNonQuery();
            }
        }
相关文章：