获取多个查询值在一个嵌入脚本在Oracle c# /VB

本文关键字：Oracle 脚本 VB 查询 获取 一个 | 更新日期: 2023-09-27 18:12:49

我试图在VB的一个嵌入式脚本中获得数据集的多个查询值。SQL Server脚本工作正常，Oracle脚本不工作。

<scripts id="getReportDetails">
    <mssql>
          SELECT GOAL_DESC, COALESCE(NULLIF(GOAL_EVALUATOR_COMMENT,''),'-') AS GOAL_EVALUATOR_COMMENT,COALESCE(NULLIF(GOAL_ASSESSEE_COMMENT,''),'-') AS GOAL_ASSESSEE_COMMENT
          FROM HS_HR_PEA_EXGOAL_TOIMPROVE
          WHERE TEMP_ID=@TEMP_ID AND TEMP_VERSION=@TEMP_VERSION AND FREQ_ID=@FREQ_ID AND EMP_NUMBER=@EMP_NUMBER
          SELECT EVALUATION_TYPE FROM HS_HR_PEA_TEMPLATE
          WHERE TEMP_ID=@TEMP_ID AND TEMP_VERSION=@TEMP_VERSION
    </mssql>
    <oracle>
          SELECT GOAL_DESC, COALESCE(NULLIF(GOAL_EVALUATOR_COMMENT,''),'-') AS GOAL_EVALUATOR_COMMENT,COALESCE(NULLIF(GOAL_ASSESSEE_COMMENT,''),'-') AS GOAL_ASSESSEE_COMMENT
          FROM HS_HR_PEA_EXGOAL_TOIMPROVE
          WHERE TEMP_ID=@TEMP_ID AND TEMP_VERSION=@TEMP_VERSION AND FREQ_ID=@FREQ_ID AND EMP_NUMBER=@EMP_NUMBER;
          SELECT EVALUATION_TYPE FROM HS_HR_PEA_TEMPLATE
          WHERE TEMP_ID=@TEMP_ID AND TEMP_VERSION=@TEMP_VERSION;
    </oracle>
</scripts>

是否有办法从这两个查询值在一个脚本在Oracle?

获取多个查询值在一个嵌入脚本在Oracle c# /VB

这在很大程度上取决于你使用的Oracle版本。在11g上，与SQL Server相同的方法是不可能的，因为它不支持隐式游标。因此，实现它的唯一方法是定义两个显式游标并在。net中使用它们。

在Oracle 12中，它和在SQL Server中一样简单，但是我在异步读取隐式游标时遇到了ODAC崩溃。

我给你做了个榜样:

using (var connection = new OracleConnection("DATA SOURCE=HQ_PDB_TCP;PASSWORD=oracle;USER ID=HUSQVIK"))
{
    connection.Open();
    using (var command = connection.CreateCommand())
    {
        // Oracle 11
        command.CommandText = "BEGIN OPEN :C1 FOR SELECT 1 FROM DUAL; OPEN :C2 FOR SELECT 2, 3 FROM DUAL; END;";
        var p1 = command.CreateParameter();
        p1.OracleDbType = OracleDbType.RefCursor;
        p1.Direction = ParameterDirection.Output;       
        command.Parameters.Add(p1);
        var p2 = command.CreateParameter();
        p2.OracleDbType = OracleDbType.RefCursor;
        p2.Direction = ParameterDirection.Output;
        command.Parameters.Add(p2);
        command.ExecuteNonQuery();
        using (var reader1 = ((OracleRefCursor)p1.Value).GetDataReader())
        {
            reader1.Read();
            Console.WriteLine($"Reader 1 values: {reader1[0]}");
        }
        using (var reader2 = ((OracleRefCursor)p2.Value).GetDataReader())
        {
            reader2.Read();
            Console.WriteLine($"Reader 2 values: {reader2[0]}, {reader2[1]}");
        }
        command.Parameters.Clear();
        // Oracle 12
        command.CommandText = "DECLARE C1 SYS_REFCURSOR; C2 SYS_REFCURSOR; BEGIN OPEN C1 FOR SELECT 1 FROM DUAL; DBMS_SQL.RETURN_RESULT(C1); OPEN C2 FOR SELECT 2, 3 FROM DUAL; DBMS_SQL.RETURN_RESULT(C2); END;";
        using (var implicitReader = command.ExecuteReader())
        {
            implicitReader.Read();
            Console.WriteLine($"Implicit cursor 1 values: {implicitReader[0]}");
            implicitReader.NextResult();
            implicitReader.Read();
            Console.WriteLine($"Implicit cursor 2 values: {implicitReader[0]}, {implicitReader[1]}");
        }
    }
}
相关文章：