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

1. 2. CREATE OR REPLACE PROCEDURE p_query_cs (
   3.    p_infotype       IN       VARCHAR2,
   4.    p_fromareacode   IN       VARCHAR2, 
   5.    p_toareacode     IN       VARCHAR2,        
   6.    p_keytype        IN       NUMBER,                     
   7.    r_cursor1        OUT      sys_refcursor,                           --结果集
   8.    r_cursor2        OUT      sys_refcursor                            --结果集
   9. )
   10. IS
   11. BEGIN
   12.    OPEN r_cursor1 FOR
   13.       SELECT *
   14.         FROM permit_menu;
   16.    OPEN r_cursor2 FOR
   17.       SELECT *
   18.         FROM permit_privilege;
   19. EXCEPTION
   20.    WHEN NO_DATA_FOUND
   21.    THEN
   22.       NULL;
   23.    WHEN OTHERS
   24.    THEN
   25.       RAISE;
   26. END p_query_cs;
   27. /
   using System.Data.OleDb;
2. using System.Data.OracleClient;
5.  protected void cs1()
6.         {
7.             DataSet ds = new DataSet();
8.             using (OleDbConnection conn = new OleDbConnection("Provider=OraOLEDB.Oracle.1;Password=***;User ID=***;Data Source=***;Persist Security Info=True;PLSQLRSet=1;"))
9.             {
10.                 OleDbCommand comm = new OleDbCommand();
11.                 comm.Connection = conn;
12.                 comm.CommandText = "p_query_cs";
13.                 comm.CommandType = CommandType.StoredProcedure;
14.                 OleDbDataAdapter da = new OleDbDataAdapter(comm);
15.                 // da.TableMappings.Add("table1", "PERMIT_MENU");
16.                 // da.TableMappings.Add("table2", "PERMIT_PRIVILEGE");
17.                 da.Fill(ds);
18.                 for (int j = 0; j < ds.Tables.Count; j++)
19.                 {
20.                     for (int i = 0; i < ds.Tables[j].Rows.Count; i++)
21.                     {
22.                         for (int k = 0; k < ds.Tables[j].Columns.Count; k++)
23.                         {
24.                             Response.Write(ds.Tables[j].Rows[i][k].ToString() + "|");
25.                         }
26.                         Response.Write("<br/>");
27.                     }
28.                 }
30.             }
31.         }
34.  protected void cs3()
35.         {
36.             OracleConnection conn = new OracleConnection("Data Source=***;User Id=***;Password=***");
37.             OracleCommand cmd = new OracleCommand();
38.             cmd.Connection = conn;
39.             cmd.CommandText = "p_query_cs";
40.             cmd.Parameters.Add("r_cursor", OracleType.Cursor).Direction = ParameterDirection.Output;
41.             cmd.Parameters.Add("r_cursor1", OracleType.Cursor).Direction = ParameterDirection.Output;
42.             cmd.CommandType = CommandType.StoredProcedure;
43.             OracleDataAdapter da = new OracleDataAdapter(cmd);
44.             da.TableMappings.Add("Table", "PERMIT_MENU");
45.             da.TableMappings.Add("Table1", "PERMIT_PRIVILEGE");
46.             DataSet ds = new DataSet();
47.             da.Fill(ds);
48.              for (int j = 0; j < ds.Tables.Count; j++)
49.                 {
50.                     for (int i = 0; i < ds.Tables[j].Rows.Count; i++)
51.                     {
52.                         for (int k = 0; k < ds.Tables[j].Columns.Count; k++)
53.                         {
54.                             Response.Write(ds.Tables[j].Rows[i][k].ToString() + "|");
55.                         }
56.                         Response.Write("<br/>");
57.                     }
58.                 }
60.         }