从存储过程,返回OUT参数和放大器; OUT光标&安培;分析结果(甲骨文) [英] From Stored Procedure, return OUT parameter & OUT cursor & parse result (Oracle)
本文介绍了从存储过程,返回OUT参数和放大器; OUT光标&安培;分析结果(甲骨文)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我看到了一个类似的问题为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;
在后面的代码..
In Code Behind..
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光标&安培;分析结果(甲骨文)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文