从存储过程，返回 OUT 参数 &OUT 光标 &解析结果 (Oracle) [英] From Stored Procedure, return OUT parameter & OUT cursor & parse result (Oracle)

问题描述

我看到了一个关于 SqlDB 的类似问题，但经过很长时间的搜索，没有找到 OracleDB 的解决方案.

I saw a similar question for SqlDB but after a really long search found no solution for OracleDB.

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)
    }
}

---

我认为这是不可能的，因为我如何同时读取值和值?光标，因为..

如果只标记参数我会使用 db.ExecuteNonQuery(..)&如果只有光标出来我会使用db.ExecuteReader(..)

---

I Thought this was not possible as how do I read both the value & the cursor, because..

if only flag param out then i would use db.ExecuteNonQuery(..) & if only cursor out then i would use db.ExecuteReader(..)

推荐答案

感谢您的解答

我真的很想得到一个有效的结果 &不知何故遇到了一个解决方案&阅读后发现它为什么有效:

Thank you for the answers

I was really desperate to get a working result & somehow came across a solution & after reading a bit found out why it worked :

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

这篇关于从存储过程，返回 OUT 参数 &OUT 光标 &解析结果 (Oracle)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！