[转].net 调用oracle存储过程返回多个记录集

本文转自：http://www.netwinform.com/articleinfo.aspx?id=17

存储过程： 

CREATE OR REPLACE PROCEDURE p_query_cs ( 
p_infotype IN VARCHAR2, 
p_fromareacode IN VARCHAR2, 
p_toareacode IN VARCHAR2, 
p_keytype IN NUMBER, 
r_cursor1 OUT sys_refcursor, --结果集 
r_cursor2 OUT sys_refcursor --结果集 
) 
IS 
BEGIN 
OPEN r_cursor1 FOR 
SELECT * 
FROM permit_menu; 

OPEN r_cursor2 FOR 
SELECT * 
FROM permit_privilege; 
EXCEPTION 
WHEN NO_DATA_FOUND 
THEN 
NULL; 
WHEN OTHERS 
THEN 
RAISE; 
END p_query_cs; 
/


cs程序
 

using System.Data.OleDb; 
using System.Data.OracleClient; 


protected void cs1() 
{ 
DataSet ds = new DataSet(); 
using (OleDbConnection conn = new OleDbConnection("Provider=OraOLEDB.Oracle.1;Password=***;User ID=***;Data Source=***;Persist Security Info=True;PLSQLRSet=1;")) 
{ 
OleDbCommand comm = new OleDbCommand(); 
comm.Connection = conn; 
comm.CommandText = "p_query_cs"; 
comm.CommandType = CommandType.StoredProcedure; 
OleDbDataAdapter da = new OleDbDataAdapter(comm); 
// da.TableMappings.Add("table1", "PERMIT_MENU"); 
// da.TableMappings.Add("table2", "PERMIT_PRIVILEGE"); 
da.Fill(ds); 
for (int j = 0; j < ds.Tables.Count; j++) 
{ 
for (int i = 0; i < ds.Tables[j].Rows.Count; i++) 
{ 
for (int k = 0; k < ds.Tables[j].Columns.Count; k++) 
{ 
Response.Write(ds.Tables[j].Rows[i][k].ToString() + "|"); 
} 
Response.Write("<br/>"); 
} 
} 

} 
} 


protected void cs3() 
{ 
OracleConnection conn = new OracleConnection("Data Source=***;User Id=***;Password=***"); 
OracleCommand cmd = new OracleCommand(); 
cmd.Connection = conn; 
cmd.CommandText = "p_query_cs"; 
cmd.Parameters.Add("r_cursor", OracleType.Cursor).Direction = ParameterDirection.Output; 
cmd.Parameters.Add("r_cursor1", OracleType.Cursor).Direction = ParameterDirection.Output; 
cmd.CommandType = CommandType.StoredProcedure; 
OracleDataAdapter da = new OracleDataAdapter(cmd); 
da.TableMappings.Add("Table", "PERMIT_MENU"); 
da.TableMappings.Add("Table1", "PERMIT_PRIVILEGE"); 
DataSet ds = new DataSet(); 
da.Fill(ds); 
for (int j = 0; j < ds.Tables.Count; j++) 
{ 
for (int i = 0; i < ds.Tables[j].Rows.Count; i++) 
{ 
for (int k = 0; k < ds.Tables[j].Columns.Count; k++) 
{ 
Response.Write(ds.Tables[j].Rows[i][k].ToString() + "|"); 
} 
Response.Write("<br/>"); 
} 
} 

}