问题:是否可以使用 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)
}
}
我认为这是不可能的,因为我如何读取值和光标,因为..
如果仅将参数标记出来则我会用db.ExecuteNonQuery(..)
& 如果仅将光标移出则我会用db.ExecuteReader(..)
是的,可以有多个输出参数。下面是我用 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()) {
}
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)