GetReviewTask

public string GetReviewTask(GetReviewTaskInput input)
{
    /*
     * 试过三种写法都不对，暂时找不出是什么原因，改成拼Sql；
     * 
    OracleParameter[] sqlParameters =
    {
        new OracleParameter (":UserId", input.UserId),
        new OracleParameter (":ExamCourseId", input.ExamCourseId),
        new OracleParameter (":QuestionsId", input.QuestionsId),
        new OracleParameter (":TaskType", input.TaskType)
    };
    */

    /*
    DbParameter[] sqlParameters =
    {
        new OracleParameter { ParameterName = ":UserId", Value = input.UserId },
        new OracleParameter { ParameterName = ":ExamCourseId", Value = input.ExamCourseId },
        new OracleParameter { ParameterName = ":QuestionsId", Value = input.QuestionsId },
        new OracleParameter { ParameterName = ":TaskType", Value = input.TaskType }
    };
    */

    /*
    var pars = new OracleParameter[4];
    pars[0] = new OracleParameter(":UserId", input.UserId);
    pars[1] = new OracleParameter(":ExamCourseId", input.ExamCourseId);
    pars[2] = new OracleParameter(":QuestionsId", input.QuestionsId);
    pars[3] = new OracleParameter(":TaskType", input.TaskType);
    */


    var outputParameter = new OracleParameter
    {
        ParameterName = "CUR",
        OracleDbType = OracleDbType.RefCursor,
        Direction = ParameterDirection.Output
    };


    DbParameter[] parameters = {
        new OracleParameter("P_USER_ID", input.UserId),
        new OracleParameter("P_EXAM_COURSE_ID", input.ExamCourseId),
        new OracleParameter("P_QUESTIONS_ID", input.QuestionsId),
        new OracleParameter("P_TASK_TYPE", input.TaskType),
        outputParameter
        };

    /*
    var sqlQuery = $@" SELECT T1.TASK_ID TaskId
        FROM T_RVW_REVIEW_WORK T1 
        INNER JOIN T_RVW_QUESTIONS_PERMISSION T2 
        ON T1.EXAM_COURSE_ID = T2.EXAM_COURSE_ID AND T1.QUESTIONS_ID = T2.QUESTIONS_ID
        WHERE T1.QUESTIONS_ID = '{input.QuestionsId}'
        AND T1.EXAM_COURSE_ID = '{input.ExamCourseId}'
        AND T1.TASK_TYPE = {input.TaskType}
        AND T1.USER_ID IS NULL
        AND T2.USER_ID = '{input.UserId}'
        AND T1.IS_DONE = 0
        AND ROWNUM = 1
        FOR UPDATE SKIP LOCKED";
    */



    var result = "";
    var dt = ExecuteProc("USP_RVW_GET_REVIEW_TASK", parameters);
    result = dt?.Rows.Count == 1 ? dt.Rows[0][0].ToString() : "";
    dt?.Dispose();
    return result;

}

ExecuteProc

private DataTable ExecuteProc(string procName, params DbParameter[] parms)
{
    using (var conn = new OracleConnection(Context.Database.Connection.ConnectionString))
    {
        var outParms = parms.Where(p => p.Direction == System.Data.ParameterDirection.Output || p.Direction == System.Data.ParameterDirection.ReturnValue).ToList();
        var command = new OracleCommand(procName)
        {
            Connection = conn,
            CommandType = CommandType.StoredProcedure
        };

        command.Parameters.AddRange(parms);
        conn.Open();
        command.ExecuteNonQuery();

        var ds = new DataSet();
        IDbDataAdapter da = new OracleDataAdapter(command);
        da.Fill(ds);

        command.Parameters.Clear();
        command.Dispose();
        conn.Close();

        return ds.Tables[0];
    }
}

USP_RVW_GET_REVIEW_TASK

CREATE OR REPLACE PROCEDURE USP_RVW_GET_REVIEW_TASK
      ( P_USER_ID        VARCHAR2,
        P_EXAM_COURSE_ID VARCHAR2,
        P_QUESTIONS_ID   VARCHAR2,
        P_TASK_TYPE      INT,
        CUR              OUT SYS_REFCURSOR )
                                                  
  
 IS
   V_TASK_ID VARCHAR2(50);  
   
BEGIN   
    BEGIN
       SELECT T1.TASK_ID 
          INTO V_TASK_ID
          FROM T_RVW_REVIEW_WORK T1
         INNER JOIN T_RVW_QUESTIONS_PERMISSION T2
            ON T1.EXAM_COURSE_ID = T2.EXAM_COURSE_ID
           AND T1.QUESTIONS_ID = T2.QUESTIONS_ID
         WHERE T1.QUESTIONS_ID = P_QUESTIONS_ID
           AND T1.EXAM_COURSE_ID = P_EXAM_COURSE_ID
           AND T1.TASK_TYPE = P_TASK_TYPE
           AND T1.USER_ID IS NULL
           AND T2.USER_ID = P_USER_ID
           AND T1.IS_DONE = 0
           AND ROWNUM = 1;
           
           DBMS_OUTPUT.PUT_LINE('TaskId:'||V_TASK_ID); 
           
           UPDATE T_RVW_REVIEW_WORK
           SET USER_ID = P_USER_ID
           WHERE TASK_ID = V_TASK_ID AND IS_DONE = 0;
           
           COMMIT;
       
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
  END;
  
   OPEN CUR FOR
    SELECT TASK_ID 
      FROM T_RVW_REVIEW_WORK
      WHERE TASK_ID = V_TASK_ID;
      
END;