create table processing_result ( status varchar2(64) );
create or replace package cursor_in_out as type emp_cur_type is ref cursor return employees%rowtype;
procedure process_cursor(p_cursor in emp_cur_type);
end;
/
create or replace package body cursor_in_out as
procedure process_cursor(p_cursor in emp_cur_type) is employee employees%rowtype;
begin loop fetch p_cursor into employee; exit when p_cursor%notfound; insert into processing_result values('Processed employee #' || employee.employee_id || ': ' || employee.first_name || ' ' || employee.last_name); end loop; end; end;
/ 创建.NET代码
数据库已经准备好了，接下来我们就准备创建.NET代码。
using System; using System.Data; using Oracle.DataAccess.Client; using Oracle.DataAccess.Types;
namespace CursorInCursorOut { /// <summary> /// Summary description for Class1. /// </summary> class Class1 { /// <summary> /// The main entry point for the application. /// </summary> [STAThread] static void Main(string[] args) { // create connection to database // change for your environment string constr = "User Id=hr; Password=hr; Data Source=oramag; Pooling=false"; OracleConnection con = new OracleConnection(constr); con.Open();
// command and parameter objects to get ref cursor OracleCommand cmd = con.CreateCommand(); cmd.CommandText = "begin open :1 for select * from employees where manager_id=101; end;"; OracleParameter p_rc = cmd.Parameters.Add("p_rc", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);
// get the ref cursor cmd.ExecuteNonQuery();
// clear parameters to reuse cmd.Parameters.Clear();
// command and parameter objects to pass ref cursor // as an input parameter cmd.CommandText = "cursor_in_out.process_cursor"; cmd.CommandType = CommandType.StoredProcedure; OracleParameter p_input = cmd.Parameters.Add("p_input", OracleDbType.RefCursor, p_rc.Value, ParameterDirection.Input);
// process the input cursor cmd.ExecuteNonQuery();
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
