Let's demonstrate the scenario when a stored function returns a cursor as its return value.
CREATE OR REPLACE FUNCTION get_emps_by_dept_id_func (id INTEGER)
RETURN SYS_REFCURSOR
IS
emp_cur SYS_REFCURSOR;
BEGIN
OPEN emp_cur FOR
SELECT * FROM emp WHERE deptno = id;
RETURN emp_cur;
END;
/
We have already mentioned that Oracle does not allow to write queries to cursors, thus this function is not composable. So, this method must return IsingleResult<Emp> or just IEnumerable<Emp> (instead of IQueryable<Emp>).
[Function(Name = @"GET_EMPS_BY_DEPT_ID_FUNC")]
[ResultType(typeof(Emp), 0, ResultTypeOrigin.ReturnValue)]
public ISingleResult<Emp> GetEmpsByDeptIdFunc(
[Parameter(Name = "ID", DbType = "NUMBER")] decimal? id
)
{
IExecuteResult _GetEmpsByDeptIdFuncResult =
this.ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod(), id);
return (ISingleResult<Emp>)_GetEmpsByDeptIdFuncResult.ReturnValue;
}
<[Function](Name:="GET_EMPS_BY_DEPT_ID_FUNC")> _
<ResultType(GetType(Emp), 0, ResultTypeOrigin.ReturnValue)> _
Public Function GetEmpsByDeptIdFunc( _
<Parameter(Name:="ID", DbType:="NUMBER")> id As System.Nullable(Of Decimal) _
) As ISingleResult(Of Emp)
Dim _GetEmpsByDeptIdFuncResult As IExecuteResult = _
Me.ExecuteMethodCall( _
Me, _
DirectCast(MethodInfo.GetCurrentMethod(), MethodInfo), _
id _
)
Return DirectCast(_GetEmpsByDeptIdFuncResult.ReturnValue, ISingleResult(Of Emp))
End Function
As in previous examples, the ResultType attribute is the key mapping element. It specifies the type of the returned collection elements (Emp), the source of this collection (the stored function return value), and the resultset number (0). In our case the resultset number does not have any meaning and will be ignored because stored function cannot have more than one cursor return value.
As ISingleResult<> is inherited from IEnumerable<>, we can simply iterate the result.
ScottDataContext context = new ScottDataContext() { Log = Console.Out };
var list = context.GetEmpsByDeptIdFunc(30);
foreach (var item in list)
Console.WriteLine(item.Ename);
Dim context As New ScottDataContext() With { _
.Log = Console.Out _
}
Dim list = context.GetEmpsByDeptIdFunc(30)
For Each item In list
Console.WriteLine(item.Ename)
Next
Stored function can return only one cursor as their return value; however, cursor function can return additional resultsets by other means (Oracle function can use output cursor parameters for it).
CREATE OR REPLACE FUNCTION SCOTT.get_dept_and_emps_by_id_func (id INTEGER, emp_cur OUT SYS_REFCURSOR)
RETURN SYS_REFCURSOR
IS
dept_cur SYS_REFCURSOR;
BEGIN
OPEN dept_cur FOR
SELECT * FROM dept WHERE deptno = id;
OPEN emp_cur FOR
SELECT * FROM emp WHERE deptno = id;
RETURN dept_cur;
END;
/
Such function can be mapped either to a method with the IMultipleResults return type or to a method with an IEnumerable<> output parameter. In first case both resultsets must be described with the ResultTypeAttribute (or with the ElementType tag in case of XML mapping).
[Function(Name = @"GET_DEPT_AND_EMPS_BY_ID_FUNC")]
[ResultType(typeof(Dept), 0, ResultTypeOrigin.ReturnValue)]
[ResultType(typeof(Emp), 1, ResultTypeOrigin.OutCursorParameter)]
public IMultipleResults GetDeptAndEmpsByIdFunc(
[Parameter(Name = "ID", DbType = "NUMBER")] decimal? id
)
{
IExecuteResult _GetDeptAndEmpsByIdFuncResult = this.ExecuteMethodCall(
this,
(MethodInfo)MethodInfo.GetCurrentMethod(),
id
);
return (IMultipleResults)_GetDeptAndEmpsByIdFuncResult.ReturnValue;
}
<[Function](Name:="GET_DEPT_AND_EMPS_BY_ID_FUNC")> _
<ResultType(GetType(Dept), 0, ResultTypeOrigin.ReturnValue)> _
<ResultType(GetType(Emp), 1, ResultTypeOrigin.OutCursorParameter)> _
Public Function GetDeptAndEmpsByIdFunc( _
<Parameter(Name:="ID", DbType:="NUMBER")> id As System.Nullable(Of Decimal) _
) As IMultipleResults
Dim _GetDeptAndEmpsByIdFuncResult As IExecuteResult = _
Me.ExecuteMethodCall(Me, DirectCast(MethodInfo.GetCurrentMethod(), MethodInfo), id)
Return DirectCast(_GetDeptAndEmpsByIdFuncResult.ReturnValue, IMultipleResults)
End Function
Here is an example of using this method:
ScottDataContext context = new ScottDataContext() { Log = Console.Out };
var res = context.GetDeptAndEmpsByIdFunc(30);
Console.WriteLine(
res.GetResult<Dept>().Single().Dname
);
foreach (var emp in res.GetResult<Emp>())
Console.WriteLine("{0}, {1}", emp.Ename, emp.Job);
Dim context As New ScottDataContext() With { _
.Log = Console.Out _
}
Dim res = context.GetDeptAndEmpsByIdFunc(30)
Console.WriteLine(res.GetResult(Of Dept)().[Single]().Dname)
For Each emp In res.GetResult(Of Emp)()
Console.WriteLine("{0}, {1}", emp.Ename, emp.Job)
Next
 Note: |
|
If you use a method, wrapping a stored function, returning resultsets in a different ways, the resultset, returned as the return value, must be read the first (depts in our case).
|
The output cursor parameters of this function can also be mapped to output parameters of the function wrapper method. Its return value should be mapped to the method's return value in such case:
[Function(Name = @"GET_DEPT_AND_EMPS_BY_ID_FUNC")]
[ResultType(typeof(Dept), 0, ResultTypeOrigin.ReturnValue)]
[ResultType(typeof(Emp), 1, ResultTypeOrigin.OutCursorParameter)]
public ISingleResult<Dept> GetDeptAndEmpsByIdFunc(
[Parameter(Name = "ID", DbType = "NUMBER")] decimal? id,
[Parameter(Name = "EMP_CUR", DbType = "INT")] out IEnumerable<Emp> emp_cur
)
{
emp_cur = null;
IExecuteResult _GetDeptAndEmpsByIdFuncResult = this.ExecuteMethodCall(
this,
(MethodInfo)MethodInfo.GetCurrentMethod(),
id,
emp_cur
);
emp_cur = (IEnumerable<Emp>)_GetDeptAndEmpsByIdFuncResult.GetParameterValue(1);
return (ISingleResult<Dept>)_GetDeptAndEmpsByIdFuncResult.ReturnValue;
}
<[Function](Name:="GET_DEPT_AND_EMPS_BY_ID_FUNC")> _
<ResultType(GetType(Dept), 0, ResultTypeOrigin.ReturnValue)> _
<ResultType(GetType(Emp), 1, ResultTypeOrigin.OutCursorParameter)> _
Public Function GetDeptAndEmpsByIdFunc( _
<Parameter(Name:="ID", DbType:="NUMBER")> id As System.Nullable(Of Decimal), _
<Parameter(Name:="EMP_CUR", DbType:="INT")> ByRef emp_cur As IEnumerable(Of Emp) _
) As ISingleResult(Of Dept)
emp_cur = Nothing
Dim _GetDeptAndEmpsByIdFuncResult As IExecuteResult = _
Me.ExecuteMethodCall( _
Me, _
DirectCast(MethodInfo.GetCurrentMethod(), MethodInfo), _
id, _
emp_cur _
)
emp_cur = DirectCast( _
_GetDeptAndEmpsByIdFuncResult.GetParameterValue(1), _
IEnumerable(Of Emp) _
)
Return DirectCast( _
_GetDeptAndEmpsByIdFuncResult.ReturnValue, _
ISingleResult(Of Dept) _
)
End Function
The ResultType attribute for the resultset, returned as an output parameter, may be omitted in this case. However, the ResultType attribute for the resultset, returned as the return value is mandatory, because otherwise, the runtime considers that stored function returns it as an output parameter by default.
Here is an example of using this method:
ScottDataContext context = new ScottDataContext() { Log = Console.Out };
IEnumerable<Emp> emps;
var depts = context.GetDeptAndEmpsByIdFunc(30, out emps);
Console.WriteLine(
depts.Single().Dname
);
foreach (var emp in emps)
Console.WriteLine("{0}, {1}", emp.Ename, emp.Job);
Dim context As New ScottDataContext() With { _
.Log = Console.Out _
}
Dim emps As IEnumerable(Of Emp)
Dim depts = context.GetDeptAndEmpsByIdFunc(30, emps)
Console.WriteLine(depts.[Single]().Dname)
For Each emp In emps
Console.WriteLine("{0}, {1}", emp.Ename, emp.Job)
Next
Note that both method implementation and use becomes more complicated because of the output parameter. See Out Cursor Parameters for more details.
See Also
Non-Composable Functions, Returning Resultsets
| Explicit Queries inside Procedure
| Out Cursor Parameters
