问题：是否可以使用OUT返回：

两者都是：一个变量&一个光标，来自我下面的代码

我在SqlDB中看到了类似的问题，但经过长时间的搜索，没有找到OracleDB的解决方案。

在PLSQL中：

CREATE OR REPLACE
PROCEDURE SPGETRESULTANDSETFLAG
(
 pFilter VARCHAR2,
 pMaxRowCount VARCHAR2,
 pTableID RAW,
 myFlag OUT NUMBER,
 myCursor OUT types.cursorType
)
AS
BEGIN
 Declare
  CountQuery VARCHAR(20000) := '';
  DataQuery VARCHAR(20000) := '';
  ResultingRows NUMBER := -1;
 Begin
  myFlag := -1;
  CountQuery := 'SELECT COUNT(*) FROM ' 
                || F_GET_TABLENAME_FROM_ID(PTABLEID => pTableID)
                || ' WHERE ' || pFilter;
  EXECUTE IMMEDIATE CountQuery INTO ResultingRows;

  --Get the Return Value
  if( pMaxRowCount > ResultingRows ) then myFlag := 1; end if;

  DataQuery := 'SELECT * FROM '
                || F_GET_TABLENAME_FROM_ID(PTABLEID => pTableID) 
                || ' WHERE ' || pFilter; 
  --Get the Return Cursor
  Open myCursor for DataQuery;
 End;
END SPGETRESULTANDSETFLAG;

在代码隐藏中

Database db = DBSingleton.GetInstance();
using (DbCommand command = db.GetStoredProcCommand(spName))
{
    //The three Add In Parameters... & then the Add out Parameter as below
    db.AddOutParameter(command, "myFlag", System.Data.DbType.Int32, LocVariable );
    using ( IDataReader reader = db.ExecuteReader(command))
    {
         //Loop through cursor values & store them in code behind class-obj(s)
    }
}

我认为这是不可能的，因为我如何阅读价值和；光标，因为

如果仅标记param out，则我将使用db.ExecuteNonQuery(..(&如果只将光标移出，则我将使用db.ExecuteReader(..(

是的，可以有多个out参数。下面是一个例子，我用它来调用c#中的Oracle存储过程：

OracleParameter op = null;
OracleDataReader dr = null;
/* custom code here. Yours would look a little different */
OracleCommand cmd = (OracleCommand) this.FactoryCache.Connection.CreateCommand();
cmd.CommandText = "pkg_prov_index.getNextPanel";
cmd.CommandType = CommandType.StoredProcedure;
op = new OracleParameter("pCurrentPanelId", OracleType.VarChar);
op.Direction = ParameterDirection.Input;
op.Value = masterProviderIndex.CurrentPanelId;
cmd.Parameters.Add(op);
op = new OracleParameter("pRefCursor", OracleType.Cursor);
op.Direction = ParameterDirection.Output;
cmd.Parameters.Add(op);
op = new OracleParameter("pReturnCode", OracleType.Number);
op.Direction = ParameterDirection.Output;
op.Size = 5;
cmd.Parameters.Add(op);
op = new OracleParameter("pReturnMessage", OracleType.VarChar);
op.Direction = ParameterDirection.Output;
op.Size = 4000;
cmd.Parameters.Add(op);
cmd.ExecuteNonQuery();
returnCode = Convert.ToInt16(cmd.Parameters[2].Value);
returnMessage = cmd.Parameters[3].Value.ToString();
dr = (OracleDataReader) cmd.Parameters[1].Value;
while (dr.Read()) {
}

感谢您的回答

我真的非常渴望得到一个工作结果；不知怎的找到了一个解决方案&在阅读了一些之后，发现了它的工作原理：

Oracle存储过程保持原样，没有任何更改

代码隐藏-更改如下：

Database db = DBSingleton.GetInstance();
using (DbCommand command = db.GetStoredProcCommand(spName))
{
    //The three Add In Parameters... & then the Add out Parameter as below
    db.AddOutParameter(command, "myFlag", System.Data.DbType.Int32, LocVariable );
    using ( IDataReader reader = db.ExecuteReader(command))
    {
         //Loop through cursor values & store them in code behind class-obj(s)
         //The reader must be closed before trying to get the "OUT parameter"
         reader.Close();
         //Only after reader is closed will any parameter result be assigned
         //So now we can get the parameter value.
         //if reader was not closed then OUT parameter value will remain null
         //Getting the parameter must be done within this code block
         //I could not get it to work outside this code block
         <Type> result = (typecast)command.Parameters["OUT_parameter_name"];
    }
}
//I USED THIS APPROACH TO RETURN MULTIPLE PARAMETERS ALONG WITH THE CURSOR READ

            using (myCmd)
            {
                myCmd.Parameters.AddWithValue("p_session_id", sessionId);
                myCmd.Parameters.AddWithValue("p_user", SessionHelper.UserEmailID);
                OracleParameter retval = new OracleParameter("p_status", OracleType.NVarChar, 35);
                retval.Direction = ParameterDirection.Output;
                myCmd.Parameters.Add(retval);

                OracleParameter retval2 = new OracleParameter("p_status_dtl", OracleType.NVarChar, 300);
                retval2.Direction = ParameterDirection.Output;
                myCmd.Parameters.Add(retval2);
                OracleParameter retval3 = new OracleParameter("p_output", OracleType.Cursor);
                retval3.Direction = ParameterDirection.Output;
                myCmd.Parameters.Add(retval3);
                myCmd.ExecuteNonQuery();
                status = myCmd.Parameters["p_status"].Value.ToString();
                statusDetail = myCmd.Parameters["p_status_dtl"].Value.ToString();
                using (OracleDataReader reader = (OracleDataReader)myCmd.Parameters["p_output"].Value)
                {
                    outPutDt.Load(reader);
                }
            }

}

我不知道您使用哪个库进行Oracle访问。。。但通常可以将游标out和param out同时声明为Parameters，并将ExecuteNonQuery与anoynmous PL/SQL块(在其中调用存储过程(一起使用。。。例如，使用Devart dotconnect组件，这是可能的。。。(没有加入，只是一个快乐的客户(

可以考虑在过程中使用重复查询的替代方法。例如：

CREATE OR REPLACE
PROCEDURE SPGETRESULTANDSETFLAG
(
 pFilter VARCHAR2,
 pTableID RAW,
 myCursor OUT types.cursorType
)
AS
  DataQuery VARCHAR(20000) := '';
BEGIN
  DataQuery := 'SELECT COUNT(*) OVER () AS TheCount, T.* FROM '
                || F_GET_TABLENAME_FROM_ID(PTABLEID => pTableID) 
                || ' AS T WHERE ' || pFilter; 
  --Get the Return Cursor
  Open myCursor for DataQuery;
END SPGETRESULTANDSETFLAG;

通过这种方式，您不必查询表两次，结果集中的每一行都有计数。您也可以去掉处理最大行数的参数，并通过获取一行来检查调用例程中的计数值。

只是另一种想法。。。

从存储过程返回 OUT 参数和 OUT 游标和解析结果 (Oracle)